Data not updating in datagridview c#

Question

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...

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

Help

Answer 1

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));
    }
}

Answer 2

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.

Answer 3

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