1

Problem with opening a large EXCEL file. For example, when I declare XLWorkbook, it's going to load ALL data to this object. I decided to read it partly, because, it's returned an error: outOfMemory. Is it possible to read part with range? Is there any more methods?

Sample:

using ClosedXML.Excel;        
public void FileOpen(string path)
{
   var workBook = new XLWorkbook(path);
// . . .
}
XMIII
  • 19
  • 4
  • Use interop so that you can leverage this method: http://stackoverflow.com/q/356371/495455 – Jeremy Thompson Mar 14 '17 at 10:52
  • It's a bad way in solving this problem. We have not ms Office products. – XMIII Mar 14 '17 at 10:55
  • @JeremyThompson that's a *very* bad idea. The xlsx format was created so you *don't* need to have Excel installed on a desktop or server. – Panagiotis Kanavos Mar 14 '17 at 16:23
  • Another possibility is to use this library https://github.com/ExcelDataReader – Kevin Mar 14 '17 at 17:24
  • @Vadim what is the size of your file can you share the file, Because if you want to read it, You have to open it and then you can get ranges with this method "CellsUsed()" this method give you all the cell range like A2:A100000 Range. And then you can get the range in one shot – Ammar Ahmed Mar 16 '17 at 13:10

1 Answers1

0

You can use a good ole' OleDbAdapter to select a given range of rows. See an example below where I select the first 10,000 rows (from the sheet "Sheet1") and then later another set of 10,000 rows from an excel file :

    DataSet excelDataSet = new DataSet();
    using (OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
    {
         connection.Open();
         OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [Sheet1$1:10000]", connection);
         cmd.Fill(excelDataSet);

...

         OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [Sheet1$10000:20000]", connection);
         connection.Close();
    }
Frederic
  • 2,015
  • 4
  • 20
  • 37