Updating excel data as per DGV selected rows using c#

Question

I have a excel file which has 6 (Client Name, Invoice, Date, Amount, Credit date, Remarks) columns of data. I have a dataGridView with 3(Bill No., Bill Date, Amount) columns of data and a combobox in a winform app. What I'm trying to do is when I select some row or rows of the dataGridView I want to open the excel file and find row/rows that in column A of the excel file has the text which is in the combobox and in column B it has the value of 1st column of the datagridview and if it finds both of them in a single row that then write some stuff in column F of those rows if that cell is empty.

I'm using EPPlus for excel file handling. Here is the sample code to help you understand better (button click event):

 using (ExcelPackage excelPackage = new ExcelPackage(fileName))  
    {  
          
        ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();  
  
        bool allMatched =true;              
          
        string cellRange = "A2:A"+LastRow(fileName);  
          
        foreach (DataGridViewRow element in dataGridView1.SelectedRows)  
        {  
  
            var searchCell = worksheet.Cells[cellRange].Where(cell=>cell.Value.ToString() == comboBox1.Text)  
                .Select(cell=>cell.Start.Row);  
  
            foreach (var rowNum in searchCell)  
            {  
                if (worksheet.Cells["B"+rowNum].Value.ToString() == element.Cells[0].Value.ToString() && string.IsNullOrEmpty(worksheet.Cells["F"+rowNum].Text))  
                {  
                    worksheet.Cells["F"+rowNum].Value = "Utilized on " + DateTime.Today.ToString("dd.MM.yyyy");  
                }  
                else  
                {  
                    MessageBox.Show("Something is wrong!");  
                    allMatched = false;  
                    break;  
                    //return;  
                }  
            }  
            if (!allMatched)  
            {  
                break;  
            }  
  
        }  
        if (allMatched)  
        {  
            excelPackage.Save();  
        }  
  
        MessageBox.Show("Done");  
          
    }  

I've used a helper method (LastRow) to find the Last filled row of the excel file

    public static string LastRow(string fpath)  
    {  
        using (ExcelPackage excelPackage = new ExcelPackage(fpath))  
        {  
              
            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();  
              
            int lastRow = 0;  
              
            for (int i = 2; i <= worksheet.Dimension.End.Row; i++)  
            {  
                if (!string.IsNullOrEmpty(worksheet.Cells["A"+i].Text))  
                {  
                    lastRow =i;  
                }  
            }  
            return lastRow.ToString();  
        }  
    }  

The problem is I'm struggling to figure out how do I deal with when column F of the excel file is not empty, it should ideally show a message and exit out of the button click event and not modify the excel file data at all even if there is only one case where a non-empty cell is found out of all the rows.

But my above code is not working as intended. Help !

Ideally what should happen is say the text in combo box is Trintop Environ & Ces and the rows shown in the picture of the DGV is selected then the excel file rows that have Trintop Environ & Ces in column A and 22307222 in column B & Trintop Environ & Ces in column A and 22307223 in column B those rows column F data should be modified to Utilized on 03.01.2022 if those cells are empty and if even one of them is non-empty then the program should show a message and exit out of the button click event without making any changes to excel file.

The excel file looks something like this

The DGV looks something like

Answer 1

Hi @don bradman ,
The following code is more efficient, and you can refer to it.

                    foreach (DataGridViewRow element in dataGridView1.SelectedRows)  
                    {  
                        var searchCell = worksheet.Cells[cellRange].Where(cell => cell.Value.ToString() == comboBox1.Text)  
                            .Select(cell => cell.Start.Row);    
                        foreach (var rowNum in searchCell)  
                        {  
                            if (worksheet.Cells["B" + rowNum].Value.ToString() == element.Cells[0].Value.ToString())  
                            {  
                                if (!string.IsNullOrEmpty(worksheet.Cells["F" + rowNum].Text))  
                                {  
                                    MessageBox.Show("Something is wrong!");  
                                    allMatched = false;  
                                    rowLst.Clear();  
                                    break;  
                                }  
                                else  
                                {  
                                    rowLst.Add(rowNum);  
                                }                   
                            }  
                        }  
                    }  
                    foreach (var rowNum in rowLst)  
                    {  
                        worksheet.Cells["F" + rowNum].Value = "Utilized on " + DateTime.Today.ToString("dd.MM.yyyy");  
                    }  
                    if (allMatched)  
                    {          
                        excelPackage.Save();  
                        MessageBox.Show("Done");  
                    }  

Answer 2

Hi @don bradman ,
Take a look at the following code which works for me.

            using (ExcelPackage excelPackage = new ExcelPackage(fileName))  
            {  
                ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();  
                bool allMatched = true;  
                string cellRange = "A2:A" + LastRow(fileName);  
                List<int> rowLst = new List<int>();  
  
                if (dataGridView1.SelectedRows.Count > 0)  
                {  
                    foreach (DataGridViewRow element in dataGridView1.SelectedRows)  
                    {  
                        var searchCell = worksheet.Cells[cellRange].Where(cell => cell.Value.ToString() == comboBox1.Text)  
                            .Select(cell => cell.Start.Row);  
  
                        foreach (var rowNum in searchCell)  
                        {  
                            if (worksheet.Cells["B" + rowNum].Value.ToString() == element.Cells[0].Value.ToString())  
                            {  
                                rowLst.Add(rowNum);  
                            }  
                        }  
                    }  
                    foreach (var rowNum in rowLst)  
                    {  
                        if (!string.IsNullOrEmpty(worksheet.Cells["F" + rowNum].Text))  
                        {  
                            MessageBox.Show("Something is wrong!");  
                            allMatched = false;  
                            break;  
                        }  
                    }  
                    if (allMatched)  
                    {  
                        foreach (var rowNum in rowLst)  
                        {  
                            worksheet.Cells["F" + rowNum].Value = "Utilized on " + DateTime.Today.ToString("dd.MM.yyyy");  
                        }  
                        excelPackage.Save();  
                        MessageBox.Show("Done");  
                    }  
                }                          
            }  

Hope it could be helpful.

Best Regards,
Xingyu Zhao
*
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.