This project has moved. For the latest updates, please go here.

Streaming large excel files

Apr 8, 2016 at 12:10 PM
Hi,
I have been using this library for more than 3 years without any issues. Thank you very much for providing a wonderful library.

I am trying to stream the Excel file contents as it gets generated through Web API. I save the woorkbook using .SaveAs to a memory stream Below is the code which does that
var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Sheet1");
var rowIndex =1;
var streamLimit = 0;
var buffer = new byte[65536];
 foreach (var rowDatain items)
{
 streamLimit ++;
var headerColIndex =1;
foreach (var column in rowData.Split(','))
                    {
 sheet.Cell(rowIndex, headerColIndex).SetValue(column );
headerColIndex++;
}
rowIndex++;
while(streamLimit ==1000){
var memStream = new MemoryStream();
                        workbook.SaveAs(memStream);
var bytesRead = memStream.Read(buffer, 0, Math.Min(intlEngth, buffer.Length));
                            memStream.Position = 0;
                          
await outputStream.WriteAsync(buffer, 0, bytesRead);
workbook = new XLWorkbook();
sheet = workbook.Worksheets.Add("Sheet1");
}
}
I could stream the first batch without issues, but consequent loops is not published at all. I think this is the issue with the header content in the serialized byte array. Is there a way i could separate out the header, content and the footer in the memory stream so that i could stream accordingly. Some kind of separator i could look for in the byte array??

Thank you very much.