How to read an excel and do the mapping

Question

I need to read an excel file after reading it I need to do a mapping to a DataGridView.

This is the code I've tried:

DataTable dt = new DataTable();
Microsoft.Office.Interop.Excel.Application exlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook exlWb = exlApp.Workbooks.Open(@"C:\Users\HP8200\Desktop\2003.xls");
Microsoft.Office.Interop.Excel.Worksheet exlWs = exlWb.Sheets["PARAC1"];
Microsoft.Office.Interop.Excel.Range usedRange = exlWs.UsedRange;
int col = Convert.ToInt32(usedRange.Columns.Count);
int row = Convert.ToInt32(usedRange.Rows.Count);
exlApp.Visible = true;
string[,] cellValue = new string[row + 1, col + 1];
for (int j = 1; j <= row - 1; j++)
{
    for (int k = 1; k <= col - 1; k++)
    {
        cellValue[j, k] = exlWs.Cells[j, k + 1].ToString();

        dt.Rows[j]["Customer No"] = cellValue[j, 1];
        dt.Rows[j]["Card Prog"] = cellValue[j, 2];
        dt.Rows[j]["LOS No"] = cellValue[j, 3];
    }
}
exlWb.Close();
exlWs = null;
exlWb = null;
exlApp.Quit();
exlApp = null;

dataGridView1.DataSource = dt;

score 0 · Accepted Answer · answered Sep 25 '17 at 08:50

there are some good answers here:

How to read an excel file in C# without using Microsoft.Office.Interop.Excel libraries

It is always a good idea to read an excel file without being required to have office installed and you will see there an answer related to this, there are libraries that can do that for you easily.

Next, try to apply a little SOC ( separation of concerns ) in your code. This means you build a layer responsible for reading your excel files and that one returns some data in a format you can use in the UI. That's the layer you call and then you apply the results of that call to your DataGrid or whatever other UI thing you want to display it in.

How to read an excel and do the mapping

1 Answers1