CODEWITHSUNDEEP
c#asp.net.netexcelweb-services
Vasil Indzhev
Vasil Indzhev

Reputation: 695

Reading large XLSX files

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

Upvotes: 4

Views: 15067

Answers (2)

Innat3
Innat3

Reputation: 3576

If you plan on only performing a read on the excel file content, I suggest you use the ExcelDataReader library instead Link.

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        do
        {
            // Read row
            while (reader.Read())
            {
                // Read column values
                for (int i = 0; i < reader.FieldCount; i++)
                {               
                    var cellValue = reader.GetValue(i);
                }
            }
        }
        while (reader.NextResult());
    }
}

Upvotes: 10

ktyson
ktyson

Reputation: 84

Use ExcelDataReader. It is easy to install through Nuget and should only require a few lines of code:

Nuget:

Install-Package ExcelDataReader

Usage:

 using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
    {
        using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
        {
            DataSet result = excelReader.AsDataSet();
            foreach (DataRow dr in result[0])
            {
                //Do stuff
            }
        }
    }

Upvotes: 0

Related Questions