-1

I followed this very promising link to make my program read Excel files, but the problem I get is System.OutOfMemoryException. As far as I can gather, it happens because of this chunk of code

object[,] valueArray = (object[,])excelRange.get_Value(
            XlRangeValueDataType.xlRangeValueDefault);

which loads the whole list of data into one variable. I do not understand why the developers of the library decided to do it this way, instead of making an iterator, that would parse a sheet line by line. So, I need some working solution that would enable to read large (>700K rows) Excel files.

Community
  • 1
  • 1
Jacobian
  • 10,122
  • 29
  • 128
  • 221
  • You should specify what exactly you need to read from an Excel file. It might be worth considering OLEDB way of reading Excel files. E.g. if you need to read some specific lines and you are using OLEDB, you could narrow down your search query in the connection string: `String query = "SELECT * FROM [Sheet1$A1:E346]";` – Wiktor Stribiżew Mar 02 '15 at 22:43
  • also without seeing exactly what you have done code wise in regards to the link, it hard to tell if you missed something or not can you edit and post your actual code.. – MethodMan Mar 02 '15 at 23:13
  • http://stackoverflow.com/questions/7244971/how-do-i-import-from-excel-to-a-dataset-using-microsoft-office-interop-excel/7245188#7245188 – reggie Mar 03 '15 at 00:25

1 Answers1

1

I am using the following function in one of my C# applications:

  string[,] ReadCells(Excel._Worksheet WS, 
                      int row1, int col1, int row2, int col2)
        {
            Excel.Range R = WS.get_Range(GetAddress(row1, col1), 
                                         GetAddress(row2, col2));
            ....
        }

The reason to read a Range in one go rather than cell-by-cell is performance. For every cell access, a lot of internal data transfer is going on. If the Range is too large to fit into memory, you can process it in smaller chunks.

Axel Kemper
  • 10,544
  • 2
  • 31
  • 54