0

I want to generate big xlsx file, but don't want to keep it in memory of the server and get OutOfMemoryException . So I read the data from database page by page, generate rows with OpenXmlWriter and send it part by part to the client:

// I use `MemoryStream` as the buffer
OpenXmlWriter = OpenXmlWriter.Create(OutputStream);
OpenXmlWriter.WriteStartElement(new Worksheet());
OpenXmlWriter.WriteStartElement(new SheetData());
foreach(var row in rows)
{
    //...write cells with OpenXmlWriter and then
    OutputStream.Position = 0;
    var buffer = new byte[OutputStream.Length];
    OutputStream.Read(buffer, 0, (int)OutputStream.Length);
    FlushCalback(buffer);
    OutputStream.SetLength(0);
    System.Web.HttpContext.Current.Response.BinaryWrite(dataBuffer);
}

But after downloading I found, that it generates only xml markup, while xlsx is actualy "package". I can't find any example how to do that. Is there any solution? Or another libraries ?

UPDATE: SpreadsheetDocument could help, but it writes ALL the data to the stream after calling Save() method. And it will rewrite everithing after each call.

SpreadsheetDocument = SpreadsheetDocument.Create(OutputStream, SpreadsheetDocumentType.Workbook);
//... generate rows
SpreadsheetDocument.Save();
Alex White
  • 155
  • 1
  • 1
  • 10
  • There are other more intuitive ways to do this like using `System.Net.Http.PushStreamContent`. Basically you write directly to the output stream instead of trying to buffer it in memory. – Igor Jul 13 '21 at 17:47
  • @Igor good point, but it will not solve the problem of package generation. Until I call the `Save()` method of `SpreadsheetDocument`, all the data will be stored in memory – Alex White Jul 13 '21 at 18:19
  • The following post may be helpful: https://stackoverflow.com/questions/68166919/update-xlsx-file-changes-whilst-reading-the-file-with-xmlreader/68353178#68353178 – Tu deschizi eu inchid Jul 13 '21 at 18:33
  • as it is a large xlsx file why dont you first store it on disk and then send to client using resume supported link, check https://stackoverflow.com/questions/7948316/how-resume-able-file-download-in-asp-net-with-c-sharp-best-way-for-large-fil – Nitin Sawant Jul 13 '21 at 19:01
  • You can setup virtual space on your machine that puts memory on your hard drive when you run out of memory. It runs slow, but you do not get out of memory. – jdweng Jul 13 '21 at 19:59

0 Answers0