1
\$\begingroup\$

I am a civil engineer who happens to love coding. I have written code which imports Excel data from a specific region in to specific columns of datagridview (where columns are already created using a designer). I wrote terrible code which does the following things:

  • Reads all the Excel sheet
  • Store values in list
    • Bypass and avoid blank/null cells in sheets
    • Delete empty cells
    • Add rows in datagridview
    • Populate them using loops

I did it somehow but it takes time and I am not satisfied even if it doesn't take time. I want to use interop Excel, which is the limation. Is there any way to optimize my code in a better way?

string ExcelFinalPath = tb_ExcelPathUCC.Text;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
progressBar1.Value = 2;
Workbook workbook = app.Workbooks.Open(ExcelFinalPath);
progressBar1.Value = 4;
Worksheet sheet = (Worksheet)workbook.Sheets[cmb_SheetUCC.Text]; // Sheet Access
progressBar1.Value = 10;
Microsoft.Office.Interop.Excel.Range Mycells = sheet.Cells; // Getting cells
Mycells = sheet.UsedRange; //Getting Used Cells
object CellValue = null;
object CellValue2 = null;



//Collecting all cell values
for (int i = 1; i < Mycells.Rows.Count; i++)
{

    //Avoid Null Exception due empty cell values
    if (Mycells.Item[i, 1].Value == null)
    {

    }
    else
    {
        //Adding All values to Column 1
        CellValue = Mycells.Item[i, 1].Value;
        // Adding Valuues to a List of Column 1
        UCCValue1.Add(CellValue.ToString());


    }
    //Avoid Null Exception due empty cell values
    if (Mycells.Item[i, 6].Value == null)
    {

    }
    else
    {
        //Adding All Values to Column 2
        CellValue2 = Mycells.Item[i, 6].Value;
        // Adding Values to List of Column 2
        UCCValue2.Add(CellValue2.ToString());
    }



}
progressBar2.Value = 20;
//Removing Empty Cells from Values in Cell for Column1 
for (int i = 0; i < UCCValue1.Count; i++)
{

    if (UCCValue1[i] == "")
    {
        UCCValue1.Where(item => item != string.Empty).ToArray();
    }

}
progressBar2.Value = 30;
//Removing Empty Cells from Values in Cell for Column 2
for (int i = 0; i < UCCValue2.Count; i++)
{

    if (UCCValue2[i] == "")
    {
        UCCValue2.Where(item => item != string.Empty).ToArray();
    }

}


progressBar2.Value = 50;
//Adding rows in Datagridview
for (int i = 4; i < UCCValue1.Count - 1; i++)
{

    dataGridView2.Rows.Add();
}
progressBar2.Value = 60;
//Adding Values of Just Guages
for (int i = 4; i < UCCValue1.Count; i++)
{

    dataGridView2.Rows[i - 4].Cells[0].Value = UCCValue1[i];

}
progressBar2.Value = 70;
//Adding Values of Just Guages
for (int i = 3; i < UCCValue2.Count; i++)
{

    dataGridView2.Rows[i - 3].Cells[5].Value = UCCValue2[i];

}
\$\endgroup\$
  • \$\begingroup\$ What is the purpose of UCCValue2.Where(item => item != string.Empty).ToArray(); and why are you looping on it? UCCValue1 is not defined. \$\endgroup\$ – paparazzo Mar 11 '18 at 11:21
  • \$\begingroup\$ Its a list which is defined publicaly and this code is used to remove empty values \$\endgroup\$ – usama khan Mar 13 '18 at 2:24
  • \$\begingroup\$ @paparazzo: Excel keeps a "range of used cells". E.g. if you make a table with 50 rows, and then empty the bottom 25 rows; you will still receive a list of 50 rows (= the "used" range) where the last 25 items will have empty values. OP's snippet makes sense, as it skips over the empty rows/columns/cells that are still within the "used" range that Excel inherently bases itself on. \$\endgroup\$ – Flater Mar 26 '18 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.