Reading Excel sheet range wise

Question

We want to read excel sheet data based on certain range and store it into Database. And our excel sheet contains 1807 columns and 541 rows, so we can't use Datatable to store the data into it. Also We need to check excel sheet per cell wise, for storing it into the Datatable.

For now We have used "Microsoft.Office.Interop.Excel" component for reading excel sheet data. But while using this component, its taking very long time to read data from excel sheet, We want to make it fast process. We have tried it with NPOI also, but we didn't get any option for getting range wise data.

Interop, NPOI

So, We just want to know is there any other component for doing this?

Here is the code shown, that We have tried:

 public System.Data.DataTable getCellRangeValueAsDataTable(string filename, string startCell, string endCell)
    {
        System.Data.DataTable newTable = new System.Data.DataTable();
        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        string result = "";

        Workbook wb = xlApp.Workbooks.Open(filename,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);

        Worksheet ws = (Worksheet)wb.Worksheets[2];
        Range aRange = ws.get_Range(startCell, endCell);

        for (int cCnt = 1; cCnt <= aRange.Columns.Count; cCnt++)
        {
            newTable.Columns.Add("String" + cCnt, typeof(string));
        }

        for (int rCnt = 1; rCnt <= aRange.Rows.Count; rCnt++)
        {
            object[] objArr = new object[aRange.Columns.Count];

            for (int cCnt = 1; cCnt <= aRange.Columns.Count; cCnt++)
            {
                objArr[cCnt - 1] = (aRange.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value;
            }
            newTable.Rows.Add(objArr);
        }

       return newTable;
    }

Possible duplicate of [How to read an Excel spreadsheet in c# quickly](https://stackoverflow.com/questions/7613898/how-to-read-an-excel-spreadsheet-in-c-sharp-quickly) — mjwills, Jul 12 '17 at 13:46
https://stackoverflow.com/questions/13139213/fastest-way-to-read-from-and-write-to-excel-using-c-sharp may be worth a read also. — mjwills, Jul 12 '17 at 13:47

score 0 · Answer 1 · answered Jul 12 '17 at 13:55

The best solution here is to use OpenXML SDK, this will be much faster than the Interop assembly. As may know Interop works via COM and it cannot be fast, because you have to make a call for each operation in your code (read data from cell for example). The OpenXML SDK is good solution here, as it reads data directly from excel file without any calls to Microsoft Office API and it is free. You can see the example of reading data with OpenXML SDK here.