Reading large XLSX files

Question

I have an application that have to read excel and convert it to array. So far so good. Everything works file until I try to convert a larger file. I try OpenXML and try SAX approach:

using (SpreadsheetDocument xlsx = SpreadsheetDocument.Open(filePath, false))
{
   WorkbookPart workbookPart = xlsx.WorkbookPart;
   List<List<string>> parsedContent = new List<List<string>>();
   foreach (WorksheetPart worksheet in workbookPart.WorksheetParts)
       {
           OpenXmlReader xlsxReader = OpenXmlReader.Create(worksheet);

           while (xlsxReader.Read())
           {
           }
        }
 }

This is working well for files in range 1 - 10MB. My problem is when I try to load 10+ MB file. The result is OutOfMemoryException. How to proper read that big chunk of data? How to do it memory efficient?

P.s. I try libraries like ClosedXML, EPPlus and few others.

Every solution will be appreciated. Thank you in advance

Solution

If you plan on only performing a read on the excel file content, I suggest you use the ExcelDataReader library instead Link, which extracts the worksheetData into a DataSet object.

        IExcelDataReader reader = null;
        string FilePath = "PathToExcelFile";

        //Load file into a stream
        FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);

        //Must check file extension to adjust the reader to the excel file type
        if (Path.GetExtension(FilePath).Equals(".xls"))
            reader = ExcelReaderFactory.CreateBinaryReader(stream);
        else if (Path.GetExtension(FilePath).Equals(".xlsx"))
            reader = ExcelReaderFactory.CreateOpenXmlReader(stream);

        if (reader != null)
        {
            //Fill DataSet
            DataSet content = reader.AsDataSet();
            //Read....
        }

Comments

[Daniel]

A code sample would go a long way to improving the quality of this answer

[Ghost Worker]

It works just like magic. this should be accepted as the answer