Free Support Forum - aspose.com

File streaming to save excel with append

Hi,


I want to ask how does the streaming option for saving excel files work actually. I am trying to append the data to an excel file in portions through streaming to the file.

I am trying to do the following:

Public fs As FileStream
Private ExcRep As Workbook

Protected Overrides Sub GenerateExcelReport(ByVal FileName As String)
fs = New FileStream(FileName, FileMode.Create, FileAccess.ReadWrite)
ExcRep = New Workbook(fs)
.
.
.
RenderDataToReport()
.
.
.
ExcRep.Save(fs, New XlsSaveOptions(SaveFormat.Excel97To2003))
End Sub


it seems that this doesnt actually stream the data to the report in real time rather than waiting for the ExcRep.Save method then it will stream the whole document in the file.

i tried adding ExcRep.Save in between iterations for the data object. which did not help as in each time i read
the file in the fs (file stream object) it will load the whole document in the memory.

I want a way where i can actually stream and append data to the excel blindly without reading the whole
object into the memory. as the files im dealing with are actually huge. (very HUGE) and this will cause my
app to crash when the file gets bigger and bigger…

Your help is very much appreciated guys.

Hi,

If the out of memory error is thrown while saving workbook (e.g Workbook.Save), you may try to set SaveOptions.CachedFileFolder while saving the workbook/file. When this option is not set, we have to save something to memory first, then save to the file. Setting this option will directly save to the file.

Please see the sample code below

VB.NET


Dim SaveOptions As SaveOptions = New Aspose.Cells.XlsSaveOptions(SaveFormat.Excel97To2003)

SaveOptions.CachedFileFolder = stringFilePath

worbkook.Save(stringFileName, SaveOptions)

OR


Dim SaveOptions As SaveOptions = New Aspose.Cells.OoxmlSaveOptions(SaveFormat.Xlsx)

SaveOptions.CachedFileFolder = stringFilePath

worbkook.Save(stringFileName, SaveOptions)