Reading range by Microsoft.Office.Interop.Excel

Question

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 :)

Do you know a way how to get values from Excel's range and paste it to array? — jabol333, Oct 01 '18 at 16:33
sure, but you didn't specify which range, where is the problem, what you tried so far, you didn't include a snippet of your code in order to recreate a complete and verifiable example. You didn't post your desired results. You didn't do nothing. This is not a free coding service. Please read [how to ask](https://www.stackoverflow.com/help/how-to-ask) and how to recreate a [complete and verifiable](https://www.stackoverflow.com/help/how-to-ask) example before asking again. And edit your post in order to add the above infromations. — Simo, Oct 01 '18 at 16:36
@jabool333 comments and tag me with "@Simo" when you'll edit your post — Simo, Oct 01 '18 at 16:52
Sure, I can do it but it will only helps me to know range size but I need data from this range. From another topic: range.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, indexMatrix); this line is copying data from array to range. I need copy data from range to array (range size is known for me) — jabol333, Oct 01 '18 at 17:07
I agree wtih @Simo. Use Datatables. But to answer your query, `MYARRAY = Rng.Value` will store all the values into the array in 1 go. You do not need a loop for this — Siddharth Rout, Oct 01 '18 at 17:30

Simo · Accepted Answer · 2018-10-01T17:21:32.797

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.

It works! but I made one adjustemnt for *.XLS `string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";", fullPath);` — jabol333, Oct 01 '18 at 17:40
@jabol333, also you don't need to use different connection strings for different excel files, ACE is fine for all. And also DataAdapter would open and close the connection as needed. — Cetin Basoz, Oct 01 '18 at 17:50

Cetin Basoz · Answer 2 · 2018-10-01T17:51:10.327

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).

Reading range by Microsoft.Office.Interop.Excel

2 Answers2