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