Importing filtered excel data to a dgv using epplus & c#.

don bradman 621 Reputation points
2021-12-29T13:08:42.21+00:00

I've got a spreadsheet with 6 columns in the order "Party, Bill No., Bill Date, Amount, Due date, Remarks". I'm trying to filter data from this excel where the column Party is filtered by text in textbox & Remarks is blank and then add the filtered data to a datagridview.
I've done:

static string[] headers = new string[] { "Party", "Bill No.", "Bill Date", "Amount" };  
  
			ExcelPackage.LicenseContext =LicenseContext.NonCommercial;  
			  
			dataGridView1.ColumnCount = headers.Length;  
			  
			for (int i = 0; i < headers.Length; i++)  
			{  
				dataGridView1.Columns[i].HeaderText = headers[i];  
			}  
			  
			//create a list to hold all the values  
			List<string> excelData = new List<string>();  
			  
			//read the Excel file as byte array  
			byte[] bin = File.ReadAllBytes(@"D:\BILLS.xlsx");  
  
  
			//create a new Excel package in a memorystream  
			using (MemoryStream stream = new MemoryStream(bin))  
				using (ExcelPackage excelPackage = new ExcelPackage(stream))  
			{  
				  
				ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();  
				//loop all rows  
				for (int i = worksheet.Dimension.Start.Row; i <= worksheet.Dimension.End.Row; i++)  
				{  
					//loop all columns except the last two columns in a row  
					for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column - 2; j++)  
					{  
						//add the cell data to the List  
						if (worksheet.Cells[i, 1].Text == comboBox1.Text && worksheet.Cells[i, j].Value != null)  
						{  
							dataGridView1.Rows.Add(worksheet.Cells[i, 1].Value.ToString(),worksheet.Cells[i, 2].Value.ToString(),worksheet.Cells[i, 3].Value.ToString(),worksheet.Cells[i, 4].Value.ToString());  
						}  
  
					}  
  
				}  
  
			}  

But each row appears 4 times in DGV.

How to solve this?

Developer technologies | Windows Forms
Developer technologies | C#
0 comments No comments
{count} votes

Accepted answer
  1. Ken Tucker 5,861 Reputation points
    2021-12-29T14:53:47.547+00:00

    The code is adding the row once for each column. You should add row outside of the second for loop. Try something like this

               for (int i = worksheet.Dimension.Start.Row; i <= worksheet.Dimension.End.Row; i++)
               {
                   bool addRow = false;
                  //loop all columns except the last two columns in a row
                  for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column - 2; j++)
                  {
                 //add the cell data to the List
                if (worksheet.Cells[i, 1].Text == comboBox1.Text && worksheet.Cells[i, j].Value != null)
                {
                     addRow=true;
                }
    
                    }
                    if(addRow)
                    {
                            dataGridView1.Rows.Add(worksheet.Cells[i, 1].Value.ToString(),worksheet.Cells[i, 2].Value.ToString(),worksheet.Cells[i, 3].Value.ToString(),worksheet.Cells[i, 4].Value.ToString());
                    }
    
                }
    
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.