How to bind excel worksheet data to a datagrid in WPF?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • BradmanDon
    New Member
    • Feb 2021
    • 3

    How to bind excel worksheet data to a datagrid in WPF?

    I'm trying to build a WPF app where the data source of a datagrid are some filtered data of a excel worksheet from a specific workbook. The worksheet looks something like this :


    I'm loading the filtered data from that sheet to a datagrid using the below code on window load event :

    Code:
                 DataTable dt = new DataTable();
        
                 dt.Columns.Add("Party");
                 dt.Columns.Add("Bill Number");
                 dt.Columns.Add("Bill Date");
                 dt.Columns.Add("Amount");
        
                 ExcelPackage.LicenseContext =LicenseContext.NonCommercial;
        
                 using (ExcelPackage excelPackage = new ExcelPackage(file_Bills))
                 {
        
                     ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();
        
                     //loop all rows
                     for (int i = worksheet.Dimension.Start.Row+1; i <= worksheet.Dimension.End.Row; i++)
                     {
                         DataRow dr = dt.NewRow();
                         bool addRow = false;
        
                         for (int j = worksheet.Dimension.Start.Column; j <= 6; j++)
                         {
                             //filtering criteria for showing in data grid
                             if (DateTime.Parse(worksheet.Cells[i, 5].Text) >= DateTime.Today && DateTime.Parse(worksheet.Cells[i, 5].Text) <= DateTime.Today.AddDays(10) && string.IsNullOrEmpty(worksheet.Cells[i, 6].Text))
                             {
                                 addRow =true;
                             }
        
                         }
                         if (addRow)
                         {
                             dt.Rows.Add(worksheet.Cells[i, 1].Text, worksheet.Cells[i, 2].Text, worksheet.Cells[i, 3].Text, worksheet.Cells[i, 4].Text);
                             dt.AcceptChanges();
                         }
        
                     }
        
                 }
                 pendingBillsGrid.ItemsSource=dt.DefaultView;
                    
                 int pendingBills=pendingBillsGrid.Items.Count;
                 if (pendingBills > 0)
                 {
                     txtBlk.Inlines.Clear();
                     txtBlk.Inlines.Add(new System.Windows.Documents.Run("Bills pending for Processing : ") { Foreground = System.Windows.Media.Brushes.Aquamarine });
                     txtBlk.Inlines.Add(new System.Windows.Documents.Run(pendingBills.ToString()) { Foreground = System.Windows.Media.Brushes.Red, FontSize = 14 });
                 }
                 else
                 {
                     txtBlk.Inlines.Clear();
                     txtBlk.Inlines.Add(new System.Windows.Documents.Run("No Bill/Bills pending for Processing...") { Foreground = System.Windows.Media.Brushes.Aquamarine });
                 }
    Now I have a button click event which modifies the above excel file, saves it & then closes the workbook which in turn could change the rows that are shown in the datagrid but in order to do that I have to either manually close the app and then run it again or do this programmaticall y. I was wondering whether this can be done more efficiently than that like using INotifyProperty Changed like interface where the datagrid data automatically gets updated when the button click event finishes.

    Can anyone show me how to do that ?
Working...