Why is data not showing in datagrid in WPF c# app?

don bradman 621

I have a Datagrid (named 'dg') & a Combobox (named 'cmb2') in my c# WPF app. What I'm trying to do is when I select an item from the combobox the app opens a excel file (*.xlsx) in read-only mode and filters the excel data by matching the text of the combobox with first column of the excel file and displays the filtered excel file data in the datagrid.

So, I have implemented a SelectionChanged event in cmb2 as follows :

        private void cmb2_SelectionChanged(object sender, SelectionChangedEventArgs e)  
        {  
			dg.ItemsSource=null;  
			cmb2.Text="";  
  
			int rowno = 0;  
  
			string kword = cmb2.Text;  
  
  
			using (ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(file_Bills), false))  
			{  
				ExcelWorksheet workSheet = package.Workbook.Worksheets[0];  
  
				for (int i = 2; i <= workSheet.Dimension.End.Row; i++)  
				{  
					if (!string.IsNullOrEmpty(workSheet.Cells["A"+i].Text))  
					{  
						rowno =i;  
					}  
				}  
  
				DataTable dtTemp = new DataTable();  
  
				dtTemp.Columns.Add("Party");  
				dtTemp.Columns.Add("Bill No.");  
				dtTemp.Columns.Add("Bill Date");  
				dtTemp.Columns.Add("Amount");  
				dtTemp.Columns.Add("Due Date");  
				dtTemp.Columns.Add("Remarks");  
				dtTemp.Columns.Add("Payment Released on");  
  
				//DataRow drAddItem;  
				for (int row = 2; row <= rowno; row++)  
				{  
  
					if (workSheet.Cells[row, 1].Text == kword)  
					{  
						FilterDGV(dtTemp, workSheet, row);  
						dtTemp.AcceptChanges();  
					}  
				}  
				dg.ItemsSource=dtTemp.DefaultView;  
			}  
		}

Using the below helper method :

		public static void FilterDGV(DataTable tbl, ExcelWorksheet sht, int rowIndex)  
		{  
  
			DataRow drAddItem;  
  
			var columnMapping = new[]  
			{  
				"Party",  
				"Bill No.",  
				"Bill Date",  
				"Amount",  
				"Due Date",  
				"Remarks",  
				"Payment Released on"  
			};  
  
			drAddItem = tbl.NewRow();  
			tbl.Rows.Add(drAddItem);  
			var columnIterator = columnMapping.GetEnumerator();  
			for (int column = 1; column < 9; column++)  
			{  
				if (column == 7) continue;  
				columnIterator.MoveNext();  
  
				object columnValue = sht.Cells[rowIndex, column].Text;  
				drAddItem[columnIterator.Current.ToString()] = columnValue.ToString();  
			}  
			//tbl.AcceptChanges();  
		}

But when I select an item from the combobox, only the headers appear and not the actual data.

datagrid

Help

Viorel 114.7K Reputation points

2022-03-03T16:06:09.987+00:00

Maybe rowno is zero?
don bradman 621 Reputation points

2022-03-03T16:15:37.43+00:00

No, it is showing 347.
Viorel 114.7K Reputation points

2022-03-03T16:22:29.213+00:00

Maybe 'workSheet.Cells[row, 1].Text == kword' is never true?

1 answer

Michael Taylor 51,346 Reputation points

2022-03-03T15:35:44.837+00:00

Set a breakpoint on the line dg.ItemsSource=dtTemp.DefaultView; and run your code. When you hit the breakpoint look at the value of dtTemp. Does it have any rows in it? Now look at the results of DefaultView. Do you lose the rows? If so then the problem is with your default view. If both values have rows then the issue is with setting the grid.

If the grid was populated properly before then we can assume it is a data issue. If you haven't gotten the grid to populate at all yet then it could also be a configuration issue with the grid. Perhaps your template is wrong. It would be hard to say at this point.
Please sign in to rate this answer.

1 person found this answer helpful.
don bradman 621 Reputation points

2022-03-03T15:48:45.637+00:00

Hi, this is what I get after setting a breakpoint on the line dg.ItemsSource=dtTemp.DefaultView;

and on DefaultView

Why am I losing the rows & how do I fix it?

BTW, this is what my excel worksheet looks like

Michael Taylor 51,346 Reputation points

2022-03-03T15:54:16.11+00:00

The screenshot is showing the start of the DataTable properties but not the row information. Go further down to where it shows how many values are in the Rows property. How many are there?

don bradman 621 Reputation points

2022-03-03T15:58:34.037+00:00

I believe 0. Have a look

Michael Taylor 51,346 Reputation points

2022-03-03T16:22:17.127+00:00

Correct, there are no rows. Therefore the issue isn't with the grid but your filtering logic. So now it is time to start debugging your FilterDGV function. Looking at your code you add a new row and then enumerate the columns of the DataTable. While this works it is redundant since you already have the columns in the table. But this doesn't seem like where the problem is. So stepping back to the caller of this function I see that you're enumerating the rows of the spreadsheet starting with the second row. Set a breakpoint on that for statement and run your code. If it doesn't break then the Excel wrapper you're using isn't returning any rows to you and hence your problem.

But let's assume it is returning rows. You then have an if statement that checks the first column of the row to make sure it matches a keyword, case sensitive. My guess is that this is failing so you'll need to adjust your logic. Note that to do a insensitive comparison (recommended) you should use something like String.Equals(workSheet.Cells[row, 1].Text, kword, StringComparison.OrdinalIgnoreCase)` instead.

Once the if statement passes it is going to get added to the table so I believe the error is there. Note that you don't need to call AcceptChanges at all since your table is in-memory only.

Michael Taylor 51,346 Reputation points

2022-03-03T16:27:22.287+00:00

I should also point out, while trying to refactor your code, that kword will always be empty. You are setting cmb2.Text to an empty string and then you later use that value as the kword value.

don bradman 621 Reputation points

2022-03-03T16:31:42.99+00:00

I've tried deleting kword = 0, but issue still occurs :(

Michael Taylor 51,346 Reputation points

2022-03-03T16:37:08.867+00:00

Here's a cleaned up version, I believe, but not tested.

private void cmb2_SelectionChanged(object sender, SelectionChangedEventArgs e) { var results = SelectExcelRows(file_Bills, cmb2.Text); dg.ItemsSource = results; } private DataTable SelectExcelRows(string filename, string key) { using (ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(filename), false)) { ExcelWorksheet workSheet = package.Workbook.Worksheets[0]; DataTable dtTemp = new DataTable(); dtTemp.Columns.Add("Party"); dtTemp.Columns.Add("Bill No."); dtTemp.Columns.Add("Bill Date"); dtTemp.Columns.Add("Amount"); dtTemp.Columns.Add("Due Date"); dtTemp.Columns.Add("Remarks"); dtTemp.Columns.Add("Payment Released on"); for (int row = 2; row <= workSheet.Dimension.End.Row; row++) { if (String.Equals(workSheet.Cells[row, 1].Text, key, StringComparison.OrdinalIgnoreCase)) FilterDGV(dtTemp, workSheet, row); }; return dtTemp; }; } private static void FilterDGV(DataTable tbl, ExcelWorksheet sht, int rowIndex) { var drAddItem = tbl.NewRow(); tbl.Rows.Add(drAddItem); foreach (var column in tbl.Columns.OfType<DataColumn>()) { if (column.Ordinal == 6) continue; drAddItem[column] = sht.Cells[rowIndex, column.Ordinal].Text; }; }

Michael Taylor 51,346 Reputation points

2022-03-03T16:37:34.857+00:00

Specifically check the ordinal calculations because ordinals are 0 based and your code was using 1 based for Excel.

don bradman 621 Reputation points

2022-03-03T16:44:45.427+00:00

I've removed the cmb2.Text=""; and just run the code and noticed that when I first select an combobox item it only shows the header and then when I choose the next item it shows the filtered data using the first item that I chose. Here is a gif. I'm also trying to see what your cleaned up version does.

Michael Taylor 51,346 Reputation points

2022-03-03T16:51:32.75+00:00

Then the code is now working properly. The problem that you're seeing now is because you're using Text. Text is the value of the control but it isn't set at the point SelectionChanged is called. So you're seeing the old value here (hence why there is a delay). To get the new selected value you can do a couple of different things depending upon how you are binding the combo.

string kword = (sender as ComboBox).SelectedItem as string; //Or string kword = (sender as ComboBox).SelectedItem?.Content?.ToString();

You could also use the event args but I think that is harder.

don bradman 621 Reputation points

2022-03-03T16:53:10.313+00:00

I'm getting an exception

System.ArgumentException: 'Column out of range'

on the line drAddItem[column] = sht.Cells[rowIndex, column.Ordinal].Text;

Michael Taylor 51,346 Reputation points

2022-03-03T17:20:55.04+00:00

Sorry EPPlus uses 1-based so columnOrdinal + 1

don bradman 621 Reputation points

2022-03-04T12:42:36.82+00:00

Thanks, it is working. However, I was wondering whether the code can be made more fast & efficient as the excel worksheet data can be pretty big.
BTW, really appreciate your support :)

Michael Taylor 51,346 Reputation points

2022-03-04T15:15:27.373+00:00

It's Excel so it isn't going to be fast. Of course EPPlus may be getting in the way to so you could try alternative libraries. The closest equivalent to Excel would be the Excel interop that is built into the framework. But it is COM based so it is going to have its own issues. An alternative is to look at the OpenXML SDK. It eliminates the need for Excel altogether and might prove to be faster for your use case.

Of course if you're talking about thousands of rows of data then performance becomes a mute point anyway. You would probably do better to import the data into a database and then do the processing from there but that would require your app to move away from a file-based data approach which may or may not be reasonable for you.

I would first consider profiling the app to see where the time is being spent. It may not actually be the Excel part. If you do find the Excel processing to be slow then consider a replacement library from there. In the code you posted that I updated there is nothing there that is particularly bad performance wise so your not going to be able to optimize it much.

don bradman 621 Reputation points

2022-03-06T15:26:23.26+00:00

BTW, the Payment Released on column data is not showing in Datagrid using your cleaned up version code

Michael Taylor 51,346 Reputation points

2022-03-06T20:35:48.323+00:00

That's because in your original code you had if (column == 7) continue;. Working backwards to my zero-based ordinal approach that is column 6 (Payment Received) which is I believe what you were trying to filter out. If you don't want to filter out that column then remove the if statement. If you want to filter out a different column then adjust the ordinal (starts at 0).
Sign in to comment

Share via

Why is data not showing in datagrid in WPF c# app?

1 answer