How to save from c# a very big Excel workbook

[mgb76an]

Hi,

 

first of all, sorry for my English.

 

The problem: I receive a lot of data from an inquiry and I need to create a Excel file using interop (c#). I write data in the Excel file using a "do while". Now I save the file at the end of the do...while statement, but it means that I used a lot of memory (I could have up to 9.000.000 of rows to write in different sheets in the same file). So I thought to save the data in the Excel file on every loop. I use the saveAs() command at the first loop and then save(), but I saw that program go very slowy with this solution. I think it is because the file is rewrite completely.

 

There is a way to save only the new data? any other solution are welcome. Thank you!

 

I post my code with the save at every loop:

 

do //(this loop it's for the sheets for the same Excel file)

{

 

excelSheet = (Worksheet)excelWorkbook.Sheets.Add(Type.Missing, excelWorkbook.Sheets.get_Item(frmtExcel.position++), 1,

XlSheetType.xlWorksheet);

 

...some other operations...

 

do //(inside this loop I get blocks of data to put in the same

sheets)

{

 

rawData = Utility.Export.GetRawPage(storageEngine, table,

frmtExcel.nextPage, (frmtExcel.cursor == 0), pageRetriever);

 

...other operation about format cell...

 

 

if (contBlock == 1)

{

excelWorkbook.SaveAs(fileName

, XlFileFormat.xlOpenXMLWorkbook

, Type.Missing

, Type.Missing

, Type.Missing

, Type.Missing

, XlSaveAsAccessMode.xlExclusive

, Type.Missing

, Type.Missing

, Type.Missing

, Type.Missing

, Type.Missing);

}

else

{

excelWorkbook.Save();

}

 

} while ((frmtExcel.nextPage != -1 && (frmtExcel.cursor +

Utility.Excel.ExcelRowsChunkLenght) <=

Utility.Excel.ExcelRowsPerSheet));

 

...other operation for create a header

 

 

} while ((frmtExcel.nextPage != -1));

 

 

excelWorkbook.Save();