filter & save to xml function doesnt work viewmodel

asked 2023-01-23T15:35:49.1233333+00:00
Kran2022 241 Reputation points

Hello Morning:

First i read xml file to fill the datagrid edit, save the xml file, then export the datagrid to excel file using OpenXML, then read the excel file to fille the datagrid.

When i jsut read the xml file, i can filter the datagrid and edit datagrid cells & save back to the file.

when i read the excel file to datagrid though i exported as obervable collectoion, the filter function returns nothing and SaveFile button click doesnt work, data.Save("xxx.txt"); the xml elemnts return empty.

It looks like when i read the excel file, i need to bind with class product or i need to change to something else? Any help? thanks.

Below attached example exml file and excel file.

Filter datagrid using combo box & radio buttons:

	<!-- combobox filter items -->
   <StackPanel VerticalAlignment="Top" Margin="10,0,10,0" Grid.Column="0">
				<Label Content="Product Category Search" HorizontalAlignment="Left" Margin="0,0,0,0" />
				<TextBox Text="{Binding MainProductSearch,UpdateSourceTrigger=PropertyChanged}" 
			 HorizontalAlignment="Left"  Width="150" Margin="5,5" />
				<ComboBox Width="150" ItemsSource="{Binding MainProductCategory}"
			  Text="{Binding MainProductSearch,UpdateSourceTrigger=PropertyChanged}"  
			  Margin="5,5" HorizontalAlignment="Left" />
				<Label Content="Product Name Search" Margin="5,5" HorizontalAlignment="Left" />
				<TextBox Text="{Binding SizeSearch, UpdateSourceTrigger=PropertyChanged}" 
			 Margin="5,5" HorizontalAlignment="Left" Width="150"  />
				<ComboBox Width="150" ItemsSource="{Binding ProductName}"
			  Text="{Binding SizeSearch, UpdateSourceTrigger=PropertyChanged}"
			  Margin="5,5" HorizontalAlignment="Left"/>
	</StackPanel>
    
	<!-- Radio button filter items -->          
	<StackPanel VerticalAlignment="Bottom" Margin="01,0,10,10" Grid.Column="1" Width="150" HorizontalAlignment="Left">
		<Button Content="Reset Filter" HorizontalAlignment="Left" Margin="0,10,10,10" Width="75" 
  Command="{Binding MyCommand}" CommandParameter="Reset" />
		<RadioButton Content="Metallic" 
	   IsChecked="{Binding Path=MediaType, Converter={StaticResource mconv}, ConverterParameter=Metallic}"/>
		<RadioButton Content="Sticker"
	   IsChecked="{Binding Path=MediaType, Converter={StaticResource mconv}, ConverterParameter=Sticker}"/>
		<RadioButton Content="Perfo"
	   IsChecked="{Binding Path=MediaType, Converter={StaticResource mconv}, ConverterParameter=Perfo}"/>
		<RadioButton Content="SuperMatte"
	   IsChecked="{Binding Path=MediaType, Converter={StaticResource mconv}, ConverterParameter=SuperMatte}"/>
	</StackPanel>

Datagrid:

<DataGrid VerticalAlignment="Top" HorizontalAlignment="Left" 

                  SelectedItem="{Binding SelectedProduct}"

                  ItemsSource="{Binding View}" AutoGenerateColumns="False" 

                  CanUserAddRows="False" ScrollViewer.VerticalScrollBarVisibility="Visible" 

                  Margin="0,2,0,0"   

                  Height="{Binding RelativeSource={RelativeSource AncestorType={x:Type DockPanel}}, Path=ActualHeight}"  >

                    <DataGrid.Columns>

                        <DataGridTextColumn Header="MianProduct" Binding="{Binding Mainproduct}" Width="*" IsReadOnly="True"/>

                        <DataGridTextColumn Header="Name" Binding="{Binding Name}" Width="*" IsReadOnly="True"/>

                        <DataGridTextColumn Header="Price" Binding="{Binding Price}" Width="*" />


                        <DataGridTemplateColumn Header="Visible"  Width="100" >

                            <DataGridTemplateColumn.CellTemplate>

                                <DataTemplate>

                                    <CheckBox IsChecked="{Binding Visible, UpdateSourceTrigger=PropertyChanged}" />

                                </DataTemplate>

                            </DataGridTemplateColumn.CellTemplate>

                        </DataGridTemplateColumn>

                        <DataGridTextColumn Header="NameIcon" Binding="{Binding NameIcon}" Width="*" />

                    </DataGrid.Columns>

      </DataGrid>

Product View Model:


		class ProductPriceViewModel : INotifyPropertyChanged
	{

		

		public ProductPriceViewModel()
		{

		}
		
		public DataTable DataTableCollection { get; set; }

		
		public List<string> MainProductCategory // to load combobox items for fitler purpose
		{
			get => new List<string>() {"PrintProduct","RetroPrint"};
		}

		public List<string> ProductName // to load another combobox items for fitler purpose
		{
			get => new List<string>() {
				"10x10","10x15","10x8" };
		}

		public event PropertyChangedEventHandler PropertyChanged;
		public event EventHandler? CanExecuteChanged;

		public void OnPropertyChanged([CallerMemberName] String info = "") =>
			PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(info));

		
		private bool Filter(object item) //filer is not work if the datagrid source loaded from exel file
		{
			Product p = item as Product;
			if (p == null) return true;
			var ret = true;
			if (!String.IsNullOrEmpty(MainProductSearch))
				ret = ret && p.Mainproduct.IndexOf(MainProductSearch, StringComparison.OrdinalIgnoreCase) >= 0 ||
				p.Name.IndexOf(MainProductSearch, StringComparison.OrdinalIgnoreCase) >= 0;
			if (!String.IsNullOrEmpty(SizeSearch))
				ret = ret && p.Name.IndexOf(SizeSearch, StringComparison.OrdinalIgnoreCase) >= 0;
			if (!String.IsNullOrEmpty(MediaType))
				ret = ret && p.Name.IndexOf(MediaType, StringComparison.OrdinalIgnoreCase) >= 0;
			if (Visible.HasValue)
				ret = ret && p.Visible.IndexOf(Visible.Value.ToString(), StringComparison.OrdinalIgnoreCase) >= 0;
			return ret;
		}

		private ICollectionView cvs;

		public ICollectionView View
		{
			get { return cvs; }
			set
			{
				cvs = value;
				OnPropertyChanged("View");
			}
		}
		private readonly ICommand command;
		public ICommand Command
		{
			get
			{
				return command;
			}
		}
	


		private Product selectedProduct;

		public Product SelectedProduct
		{
			get { return selectedProduct; }
			set
			{
				selectedProduct = value;
				OnPropertyChanged("SelectedProduct");
			}
		}

		private string _MainProductSearch;
		public string MainProductSearch
		{
			get { return _MainProductSearch; }
			set
			{
				_MainProductSearch = value;
				OnPropertyChanged();
				View.Refresh();
			}
		}

		private string _SizeSearch;
		public string SizeSearch
		{
			get { return _SizeSearch; }
			set
			{
				_SizeSearch = value;
				OnPropertyChanged();
				View.Refresh();
			}
		}

		private string _MediaType;
		public string MediaType
		{
			get { return _MediaType; }
			set
			{
				_MediaType = value;
				OnPropertyChanged();
				View.Refresh();
			}
		}

		private bool? _Visible;
		public bool? Visible
		{
			get { return this._Visible; }
			set
			{
				this._Visible = value;
				OnPropertyChanged();
				View.Refresh();
			}
		}
		public static DataTable ConvertToDataTable<T>(IList<T> data)
		{
			PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
			System.Data.DataTable table = new System.Data.DataTable();
			foreach (PropertyDescriptor prop in properties)
				table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
			foreach (T item in data)
			{
				DataRow row = table.NewRow();
				foreach (PropertyDescriptor prop in properties)
					row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
				table.Rows.Add(row);
			}
			return table;
		}

	

		public ICommand MyCommand { get => new RelayCommand(executemethod, canexecutemethod); }


		string excelpath = @"C:\Users\Desktop\Price_202301111303.xlsx";
		string pricinglocalfile = @"C:\xmltest\Prices.txt";

		public  ObservableCollection<Product> Items { get; set; }
	

		private void executemethod(object parameter)
		{
			switch (parameter.ToString())
			{
				case "ExcelExport": //export datragrid to excel file 
					ExcelUtlity obj = new ExcelUtlity();
					System.Data.DataTable dt = ConvertToDataTable(GetProductsPriceListXML());
					string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "
					obj.WriteDataTableToExcel(dt, path);
					System.Windows.MessageBox.Show("Excel Pricing File created on the desktop");
					return;
					
				case "ReadExcel": //to read excel file

                    OpenFileDialog fileDialog = new OpenFileDialog();
                    fileDialog.Filter = "Excel Files|
                    fileDialog.FilterIndex = 1;
                    fileDialog.Multiselect = false;
                    dynamic result = fileDialog.ShowDialog();

                    if (result == true)
                    {
                        string excelfilepath = fileDialog.FileName;
                        DataTableCollection = ExcelUtlity.ReadExcelFile(excelfilepath);
						ObservableCollection<object> collection = GetCollection(DataTableCollection);
		

						//var test = new ObservableCollection<Product>();
						//foreach (var row in DataTableCollection.Rows)
						//{
						//	Product obj1 = new Product()
						//	{
						//		Mainproduct = (string)row["Mainproduct"],
						//		Name = (string)row["Name"],
						//		Price = (string)row["Price"],
						//		Visible = (string)row["Visible"],
						//		NameIcon = (string)row["NameIcon"]
						//	};
						//	test.Add(obj1);
						//}

						View = CollectionViewSource.GetDefaultView(collection);
                        View.Filter = Filter; //filter is not working
                        View.Refresh();

                    }

                   return;
				   
				case "ReadInternalPricing": //to read local pricing file
					Items = GetProductsPriceListXML();
					
					View = CollectionViewSource.GetDefaultView(Items);
					View.Filter = Filter;
					View.Refresh();
					return;

				case "SaveFile":
					data.Save("xxx.txt"); // data is null/empty if the datagrid is loaded from excel file 
					return;
					
				case "Reset":
					MainProductSearch = string.Empty;
					SizeSearch = string.Empty;
					SizeSearch = string.Empty;
					Visible = null;
					MediaType = string.Empty;
					break;
				default:
					MediaType = parameter.ToString();
					break;
			}
			View.Refresh();
		}

		private static bool canexecutemethod(object obj) => true;

		public List<Product> GetProductsListPriceExcel() //method to read from excel file
		{
			DataTable ResultDT = new DataTable();
			ResultDT = ExcelUtlity.ReadExcelFile(excelpath);
			var productlist = new List<Product>();
			productlist = CommonMethod.ConvertToList<Product>(ResultDT);
			return productlist;
			
		}


	

		public ObservableCollection<object> GetCollection(DataTable dataTable)
		{
			Type dynamicClassType = GetDynamicClassType(dataTable);

			ObservableCollection<object> collection = new ObservableCollection<object>();

			foreach (DataRow dataRow in dataTable.Rows)
			{
				object instance = Activator.CreateInstance(dynamicClassType);

				foreach (DataColumn dataColumn in dataTable.Columns)
				{
					object value = dataRow[dataColumn];

					if (value != DBNull.Value)
					{
						dynamicClassType.InvokeMember(dataColumn.ColumnName, BindingFlags.SetProperty, null, instance, new object[] { value });
					}
				}

				collection.Add(instance);
			}

			return collection;
		}
		public Type GetDynamicClassType(DataTable dataTable)
		{
			AssemblyName assemblyName = new AssemblyName("DynamicAssembly");

			AssemblyBuilder assemblyBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly
			(
				assemblyName,
				System.Reflection.Emit.AssemblyBuilderAccess.Run
			);

			ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule("DynamicModule");

			TypeBuilder typeBuilder = moduleBuilder.DefineType("DynamicClass", TypeAttributes.Public);

			foreach (DataColumn dataColumn in dataTable.Columns)
			{
				AddDynamicProperty(typeBuilder, "_" + dataColumn.ColumnName, dataColumn.ColumnName, dataColumn.DataType);
			}

			Type dynamicClassType = typeBuilder.CreateType();

			return dynamicClassType;
		}
		public void AddDynamicProperty(TypeBuilder typeBuilder, string fieldName, string propertyName, Type propertyType)
		{
			FieldBuilder fieldBuilder = typeBuilder.DefineField(fieldName, propertyType, FieldAttributes.Private);

			PropertyBuilder propertyBuilder = typeBuilder.DefineProperty
			(
				propertyName,
				System.Reflection.PropertyAttributes.HasDefault,
				propertyType,
				null
			);

			MethodAttributes methodAttributes = MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig;

			MethodBuilder getMethodBuilder = typeBuilder.DefineMethod
			(
				string.Format("get_{0}", propertyName),
				methodAttributes,
				propertyType,
				Type.EmptyTypes
			);

			ILGenerator getILGenerator = getMethodBuilder.GetILGenerator();

			getILGenerator.Emit(OpCodes.Ldarg_0);
			getILGenerator.Emit(OpCodes.Ldfld, fieldBuilder);
			getILGenerator.Emit(OpCodes.Ret);

			MethodBuilder setMethodBuilder = typeBuilder.DefineMethod
			(
				string.Format("set_{0}", propertyName),
				methodAttributes,
				null,
				new Type[] { propertyType }
			);

			ILGenerator setILGenerator = setMethodBuilder.GetILGenerator();

			setILGenerator.Emit(OpCodes.Ldarg_0);
			setILGenerator.Emit(OpCodes.Ldarg_1);
			setILGenerator.Emit(OpCodes.Stfld, fieldBuilder);
			setILGenerator.Emit(OpCodes.Ret);

			propertyBuilder.SetGetMethod(getMethodBuilder);
			propertyBuilder.SetSetMethod(setMethodBuilder);
		}


		XElement data;

		private ObservableCollection<Product> GetProductsPriceListXML() 
		{
			var mylist = new ObservableCollection<Product>();

			data = XElement.Load(pricinglocalfile);
			foreach (XElement xe1 in data.Elements())
				if (xe1.Name == "Products")
					foreach (var xe2 in xe1.Elements()) mylist.Add(new Product(xe2));
			return mylist;
		}

		

	}


	
	public class Product
	{
		public Product(XElement xe3) => this.xe4 = xe3;
		private XElement xe4;
		public string Mainproduct 
		{ 
			get => xe4.Name.LocalName;  
			
		}
		public string Name 
		{
			get => xe4.Attribute("Name").Value; 
			
		}
		public string Price
		{
			get
			{
				XElement xe5 = xe4.Descendants("ProductPrice").FirstOrDefault();
				if (xe5 == null) return string.Empty;
				return xe5.Attribute("Price").Value;
			}
			set
			{
				XElement xe5 = xe4.Descendants("ProductPrice").FirstOrDefault();
				if (xe5 != null) xe5.Attribute("Price").Value = value;
			}
		}
		public string Visible
		{
			get
			{
				XElement xe5 = xe4.Descendants("ProductVisibility").FirstOrDefault();
				if (xe5 == null) return string.Empty;
				return xe5.Attribute("Visible").Value;
			}
			set
			{
				XElement xe5 = xe4.Descendants("ProductVisibility").FirstOrDefault();
				if (xe5 != null) xe5.Attribute("Visible").Value = value;
			}
		}
		public string NameIcon
		{
			get
			{
				XAttribute xe5 = xe4.Attribute("DefaultIconName");
				return (xe5 == null) ? string.Empty : xe5.Value;
			}
			set
			{
				XAttribute xe5 = xe4.Attribute("DefaultIconName");
				if (xe5 == null) xe4.Add(new XAttribute("DefaultIconName", value));
				else xe5.Value = value;
			}
		}

	
	}
	
		public class MediaConverter : IValueConverter
	{
		public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
			=> value != null && parameter != null && value.ToString() == parameter.ToString();
		public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
			=> parameter;

	}
	public class RelayCommand : ICommand
	{
		private readonly Predicate<object> _canExecute;
		private readonly Action<object> _action;
		public RelayCommand(Action<object> action, Predicate<object> canExecute) { _action = action; _canExecute = canExecute; }
		public void Execute(object o) => _action(o);
		public bool CanExecute(object o) => _canExecute == null ? true : _canExecute(o);

		public event EventHandler CanExecuteChanged
		{
			add { CommandManager.RequerySuggested += value; }
			remove { CommandManager.RequerySuggested -= value; }
		}
		public RelayCommand(Action<object> execute)
		   : this(execute, null)
		{
		}
	}

//Excel class to export to excel file and to read excel file to datagrid
	public class ExcelUtlity
	{
		public bool WriteDataTableToExcel(System.Data.DataTable table, string destination)
        {
			var ds = new DataSet();
			ds.Tables.Add(table);

			ExportDSToExcel(ds, destination);
			
			return true;
        }

	
		public static void ExportDSToExcel(DataSet ds, string destination)
		{
			// https://stackoverflow.com/questions/11811143/export-datatable-to-excel-with-open-xml-sdk-in-c-sharp
			using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
			{
				var workbookPart = workbook.AddWorkbookPart();
				workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
				workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

				uint sheetId = 1;

				foreach (DataTable table in ds.Tables)
				{
					var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
					var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
					sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

					DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
					string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

					if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
					{
						sheetId =
							sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
					}

					DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
					sheets.Append(sheet);

					DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

					List<String> columns = new List<string>();
					foreach (DataColumn column in table.Columns)
					{
						columns.Add(column.ColumnName);

						DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
						cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
						cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
						headerRow.AppendChild(cell);
					}

					sheetData.AppendChild(headerRow);

					foreach (DataRow dsrow in table.Rows)
					{
						DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
						foreach (String col in columns)
						{
							DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
							cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
							cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
							newRow.AppendChild(cell);
						}

						sheetData.AppendChild(newRow);
					}
				}
			}
		}

	
					
		
	public static DataTable ReadExcelFile (string fname)
        {
			var table = new DataTable();
			using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fname,false))
			{
				WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
				IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
				string relationshipId = sheets.First().Id.Value;
				WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
				Worksheet workSheet = worksheetPart.Worksheet;
				SheetData sheetData = workSheet.GetFirstChild<SheetData>();
				IEnumerable<Row> rows = sheetData.Descendants<Row>();
				foreach (Cell cell in rows.ElementAt(0))
				{
					table.Columns.Add(GetCellValue(spreadSheetDocument, cell));
				}
				//this will also include your header row...
				foreach (Row row in rows)
				{
					DataRow tempRow = table.NewRow();
					for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
					{
						tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
					}
					table.Rows.Add(tempRow);
				}
			}
			table.Rows.RemoveAt(0);
			return table;
		}
	}
	

		public static string GetCellValue(SpreadsheetDocument doc, Cell cell)
		{
			string value = cell.CellValue.InnerText;
			if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
			{
				return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
			}
			return value;
		}

	
	}

Pricefile.txt example

Excel file: example

|User's image

Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,170 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
6,920 questions
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2023-01-23T20:21:31.8466667+00:00
    Peter Fleischer (Freelancer) 17,396 Reputation points

    If you load data from Excel you use DataTable with DataRows but Filter works with "item as Product". GetCollection returns a ObservableCollection<object>. In your code object is a dynamic object, no Product. Your Filter returns true for every item if there's no ObservableCollection<Product>.