CODEWITHSUNDEEP
c#.netexcel
jabol333
jabol333

Reputation: 57

Reading range by Microsoft.Office.Interop.Excel

I want to read a lot of cells from Excel to the 2-dimensional array in C#. Using Microsoft.Office.Interop.Excel and reading cells one by one is too slow. I know how to write the array to the range (Microsoft.Office.Interop.Excel really slow) but I would like to do it in the opposite direction

        _Excel.Application xlApp = new _Excel.Application();
        _Excel.Workbook xlWorkBook;
        _Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlWorkBook = xlApp.Workbooks.Open(path);

        xlWorkSheet = xlWorkBook.Worksheets["Engineering BOM"];


        _Excel.Range range = (_Excel.Range)xlWorkSheet.Cells[1, 1];
        range = range.get_Resize(13000, 9);


        string[,] indexMatrix = new string[13000, 9];

        // below code should be much faster

        for (int i = 1; i < 1300; i++)
        {
            for (int j = 1; j < 9; j++)
            {
                indexMatrix[i, j] = xlWorkSheet.Cells[i, j].Value2;
            }
        }

As a result I want to have values from cells range in array (range size is exactly the same as array size). Now app is reading cell by cell and writing data to array but it is too slow. Is any way to copy a whole range to cells directly?

thank you in advance :)

Upvotes: 2

Views: 4605

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23797

I guess that range is somewhat defining a 'data table'. If that is right, then fastest would be to read that as Data using OleDb or ODbc (and doesn't even need excel to be installed):

DataTable tbl = new DataTable();
using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
        $"Data Source={path};" +
        @"Extended Properties=""Excel 12.0;HDR=Yes"""))
using (OleDbCommand cmd = new OleDbCommand(@"Select * from [Engineering BOM$A1:i13000]", con))
{
    con.Open();
    tbl.Load(cmd.ExecuteReader());
}

If it was not, then you could do this:

Excel.Application xl = new Excel.Application();
var wb = xl.Workbooks.Open(path);

Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets["Engineering BOM"];

var v = ws.Range["A1:I13000"].Value;

(Not sure if excel itself could do such a big array allocation).

Upvotes: 0

Simo
Simo

Reputation: 953

You can try this, it should be faster but:

  • You have to use data tables(in this case it is better to use a data table instead a multidimensional array.)

  • You don't need to care about range anymore.

So what are we going to do? connect to excel and make a query to select all the data and fill a data table. What we need? a few lines of code.

First we declare our connection string:

For Excel 2007 or above (*.XLSX files)

string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\";", fullPath);

For Excel 2003 (*.XLS files)

string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";", fullPath);

where fullPath is the full file path of your excel file

Now we have to create the connection and fill the data table:

OleDbConnection SQLConn = new OleDbConnection(strConnectionString);
SQLConn.Open();
OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();
string sql = "SELECT * FROM [" + sheetName + "$]";
OleDbCommand selectCMD = new OleDbCommand(sql, SQLConn);
SQLAdapter.SelectCommand = selectCMD;
SQLAdapter.Fill(dtXLS);
SQLConn.Close();

where sheetName is your sheet name, and dtXLS is your data table populated with all your excel value.

This should be faster.

Upvotes: 4

Related Questions