How to open in readonly mode from stream and minimise memory usage

[Malachy Walsh]

I am opening a large Excel file for reading and wondering if it is possible to open from a stream in readonly mode?

IApplication.Workbooks.OpenReadOnly does not have an overload taking a stream.

Also I want to minimise memory usage while reading all cell values from the file. At the moment memory usage seems quite high even using OptimiseImport set to true. Can you suggest any settings to optimise just reading all cell values once?

Memory usage is about 3.55Gb for a 220Mb file with 13 sheets.

I have tried using IMigrantRange and UseRangesCache set to false but neither seems to help.

ParseWorkheetsOnDemand does not help as I am reading all cells so all sheets needs to be parsed eventually.

[Ramya Sivakumar]

Hi Malachy, 

Greetings from Syncfusion. 

Kindly try the following code snippet to open the file in read-only mode. 

Code Snippet: 

FileStream fileStream = new FileStream(DataPathBase + "Sample.xlsx",  FileMode.Open, FileAccess.Read, FileShare.ReadWrite); 

 

IWorkbook workbook = application.Workbooks.Open(fileStream); 

The file size is large. So, reading the files might take some time and memory. Also, only reading the cell values won't consume memory, holding the data will consume more memory. 

So, we request you to share the corresponding Excel document and the code snippet which you are using at your end, which will be helpful for us in investigating the query and providing details. 

Regards, 

Ramya. 

[Malachy Walsh]

Hi Ramya

Unfortunately the file is confidential and can't be shared.

I am aware of the memory being used in storing the data and just want to minimise memory use inside XLSIO objects.

Below I have added sample code. Note that we are using a MemoryStream not a FileStream.

ExcelEngine excelEngine = new ExcelEngine { ThrowNotSavedOnDestroy = false };

IApplication application = excelEngine.Excel;

application.DefaultVersion = ExcelVersion.Excel2016;

// memStream is a memory stream containing the file bytes.

IWorkbook workbook = application.Workbooks.Open(memStream, ExcelOpenType.Automatic);

List<string> allData = new List<string>();

// SheetNames contains the names of each sheet in the workbook

foreach(string sheetName in sheetNames)

{

  IWorksheet sourceSheet = inputWorkbook.Worksheets.Cast<IWorksheet>().FirstOrDefault(currentWorksheet => currentWorksheet.Name == sheetName);

  IMigrantRange range = sourceSheet.MigrantRange;

  for (int col = 1; col < lastColumn + 1; col++)

  {

    for (int row = 1; row < lastRow + 1; row++)

    {

      range.ResetRowColumn(row, col);

      string value = range.Value;

      allData.Add(value);

    }

  }

}

Regards,

Malachy

[Ramya Sivakumar]

Hi Malachy, 

Thanks for sharing the code snippet. 

In the given code snippet we found that you are holding the data in a <List>, which causes high memory consumption. We have ensured this with the 10 MB file with various data types. Adding such data to a collection consumes 96 MB. Kindly check this scenario by removing the collection at your end and let us know if the suggestion helps. Otherwise, let us know why the collection is used separately. This will help us to assist you with a better usage. 

Regards, 

Ramya. 

[Malachy Walsh]

Hi Ramya

Please ignore how the data is held in the sample code. I am seeing high memory usage inside XLSIO objects. How the data is stored outside XLSIO is not relevant to this issue.

Regards,

Malachy

[Ramya Sivakumar]

Hi Malachy, 

Based on the contents in the Excel document, the memory consumption will change. The Excel document is a compressed format of Open XML documents. The XML document will contain more data than actual Excel file size. Opening and reading these XML document will consume more memory.  For the 10MB file which contains data, charts, and styles, it consumes nearly 136 MB of memory. You are using a 220MB file which is a large document so it consumes more memory. Unfortunately, we can’t reduce the memory usage further.  

Please let us know if you have any other queries. 

Regards, 

Ramya.