question

donbradman-4481 avatar image
0 Votes"
donbradman-4481 asked karenpayneoregon answered

Data not updating in datagridview c#

Hi,
I have a windows form app with a combo box, a datagridview and a button.
What I'm trying to do is when I hit the button the data from a specific excel files specific columns is filtered in a specific way and added to the datagridview.
Also the unique column values of the column 'Party' is added to the combo box in the form Load event. Please check the below code for better understanding

 static Workbook wbk;  
 static string[] headers = new string[] { "Party", "Bill No.", "Bill Date", "Amount" };  
 static int[] columnsIndexs = new int[headers.Length];  
      
 void MainFormLoad(object sender, EventArgs e)    
 {    
     wbk = new Workbook();    
     wbk.LoadFromFile(@"C:\Users\Don\Downloads\bill_consumables\bill_consumables.xlsx");    
     Worksheet sheet = wbk.Worksheets[0];    
     int index = 0;    
     for (int i = 0; i < sheet.LastColumn; i++)    
     {    
         if (sheet.Rows[0].Cells[i].DisplayedText.Equals(headers[index]))    
         {    
             columnsIndexs[index] = i;    
             index++;    
             if (index == 4)    
             {    
                 break;    
             }    
         }    
     }    
     comboBox1.Items.Clear();    
     dataGridView1.AllowUserToAddRows = false;    
     CellRange[] crs = sheet.Columns[0].Cells;    
     string[] arr = new string[crs.Length-1];    
     for (int i = 0; i < crs.Length-1; i++)    
     {    
         arr[i] = crs[i+1].DisplayedText;    
     }    
     String[] new_arr = arr.GroupBy(p => p).Select(p => p.Key).ToArray();    
     foreach (string cr in new_arr)    
     {    
         if (!string.IsNullOrEmpty(cr)) {    
             comboBox1.Items.Add(cr);    
         }    
     }    
            
 }    
 void GetdataBtnClick(object sender, EventArgs e)    
 {    
     dataGridView1.Rows.Clear();    
     Worksheet sht = wbk.Worksheets[0];    
     sht.AutoFilters.Clear();    
     AutoFiltersCollection filters = sht.AutoFilters;    
     filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn];    
     filters.AddFilter(0, this.comboBox1.Text);    
     filters.MatchBlanks(5);    
     filters.Filter();    
     dataGridView1.ColumnCount = headers.Length;    
     for (int i = 1; i < headers.Length; i++)    
     {    
         dataGridView1.Columns[i].HeaderText = headers[i];    
     }    
     foreach (var row in filters.Range.Rows)    
     {    
         if (row.Row > 1)    
         {    
             if (sht.IsRowVisible(row.Row))    
             {    
                 string[] rowdata = new string[headers.Length];    
                 for (int i = 0; i < columnsIndexs.Length; i++)    
                 {    
                     rowdata[i] = row.Cells[columnsIndexs[i]].DisplayedText;    
                 }    
                 dataGridView1.Rows.Add(rowdata);    
             }    
         }    
     }    
     dataGridView1.Columns.RemoveAt(0);    
 } 

But when I run the code and hit the button for the first time it works as it should but when I select a new item in the combo box and hit the button the datagridview is showing blank. I'm note sure why is this happening...82448-untitled.jpg


BTW, I'm using spire.xls for excel file manipulation. Excel file image attached.


Help


dotnet-csharpwindows-forms
untitled.jpg (533.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

Here is what you need to do

Create a new class named ExcelItem, add the using statements at the top of the class.

 using System.ComponentModel;
 using System.Runtime.CompilerServices;

Then after the namespace replace the default class definition with the following.

 public class ExcelItem : INotifyPropertyChanged
 {
     private string _column1;
     private int _column2;
    
     public string Column1
     {
         get => _column1;
         set
         {
             _column1 = value;
             OnPropertyChanged();
         }
     }
    
     public int Column2
     {
         get => _column2;
         set
         {
             _column2 = value;
             OnPropertyChanged();
         }
     }
    
     public event PropertyChangedEventHandler PropertyChanged;
     protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
     {
         PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
     }
 }





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

A DataGridView works best when setting the DataSource property of the DataGridView rather than adding data without setting the data source. Either setup a class which implements INotifyPropertyChanged Interface, populate a list of the class or use a DataTable, either ways with changes the data source of the DataGridView is notified of changes.

Example

You would create a


 public class ExcelItem : INotifyPropertyChanged
 {
     private string _column1;
     private int _column2;
    
     public string Column1
     {
         get => _column1;
         set
         {
             _column1 = value;
             OnPropertyChanged();
         }
     }
    
     public int Column2
     {
         get => _column2;
         set
         {
             _column2 = value;
             OnPropertyChanged();
         }
     }
    
     public event PropertyChangedEventHandler PropertyChanged;
     protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
     {
         PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
     }
 }


Create a new instance of a list of ExcelItem

 List<ExcelItem> _excelItemsList = new List<ExcelItem>();

Then as needed, assign to the DataGridView.DataSource and when needed use .Clear on the list to start again. If you need to work with rows setup a BindingSource, set the list to it's DataSource, the BindingSource becomes the DataSource for the DataGridView. To get at say the current row, cast .Current property of the BindingSource to ExcelItem and access property values and/or change values.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

donbradman-4481 avatar image
0 Votes"
donbradman-4481 answered donbradman-4481 edited

Hi, I've tried to implement your suggestion as below :

      private void button1_Click(object sender, EventArgs e)
      {
      List<ExcelItem> _excelItemsList = new List<ExcelItem>();
        
      this.dataGridView1.Rows.Clear();
      Worksheet sht = wbk.Worksheets[0];
      sht.AutoFilters.Clear();
      AutoFiltersCollection filters = sht.AutoFilters;
      filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn];
      filters.AddFilter(0, this.comboBox1.Text);
      filters.Filter();
      this.dataGridView1.ColumnCount = headers.Length;
      for (int i = 0; i < headers.Length; i++)
      {
      this.dataGridView1.Columns[i].HeaderText = headers[i];
      }
      foreach (var row in filters.Range.Rows)
      {
      if (row.Row > 1)
      {
      if (sht.IsRowVisible(row.Row))
      {
      string[] rowdata = new string[headers.Length];
      for (int i = 0; i < columnsIndexs.Length; i++)
      {
      rowdata[i] = row.Cells[columnsIndexs[i]].DisplayedText;
      }
      _excelItemsList.Add(rowdata.ToString());
      //this.dataGridView1.Rows.Add(rowdata);
      }
      }
      }
      dataGridView1.DataSource=_excelItemsList;
        
      // MessageBox.Show("Done");
      }
      public class ExcelItem : INotifyPropertyChanged
      {
      private string _column1;
      private int _column2;
        
      public string Column1
      {
      get {_column1;}
      set
      {
      _column1 = value;
      OnPropertyChanged();
      }
      }
        
      public int Column2
      {
      get { _column2;}
      set
      {
      _column2 = value;
      OnPropertyChanged();
      }
      }
      }
      public event PropertyChangedEventHandler PropertyChanged;
      protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)

However I'm getting an error 'winformapp1.MainForm1.ExcelItem' does not implement interface member 'System.ComponentModel.INotifyPropertyChanged.PropertyChanged' (CS0535) on the line public class ExcelItem : INotifyPropertyChanged


How to fix this? Also did I implement your code properly...I'm not sure ?

Thanks & Regards


DON

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.