0

I generate excel spreadsheet like this:

public void Export<TRow>(string fileName, IEnumerable<TRow> data, ColumnDef<TRow>[] columnDefitions)
{
    using (SpreadsheetDocument document = CreateSpreadsheet(fileName))
    {
        var sheetData = GenerateSheetData(data, columnDefitions);
        var columns = GetAutoSizedColumns(sheetData);
        var workSheetPart = document.WorkbookPart.WorksheetParts.First();
        workSheetPart.Worksheet.AppendChild(columns);
        workSheetPart.Worksheet.AppendChild(sheetData);
        document.Save();
        document.Close();
    }
}

SpreadsheetDocument CreateSpreadsheet(string fileName)
{
   SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);
   document.AddWorkbookPart();
   document.WorkbookPart.Workbook = new Workbook();
   AddStyles(document.WorkbookPart);

   var _worksheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
   _worksheetPart.Worksheet = new Worksheet();

   Sheets sheets = document.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
   Sheet sheet = new Sheet()
   {
       Id = document.WorkbookPart.GetIdOfPart(_worksheetPart),
       SheetId = 1,
       Name = "Sheet1"
   };
   sheets.Append(sheet);
   return document;
}

I want to generate excel with tens of thousands rows.

Is it possible without having all rows in memory at once?

I mean something like this: I would generate 1000 rows and save and then append another 1000 rows without having to load existing data.

Liero
  • 25,216
  • 29
  • 151
  • 297
  • 1
    If it works for you, then PowerQuery may be of help. Setup a Query to load/append data, even filter them before letting them rest in an excel sheet. Then Refresh the PowerQuery through your code when needed. It adds some steps but you can handle up to a couple of million rows (even more on x64 Excel and OS) and work on them much faster than with Excel & VBA. – Apostolos55 Oct 22 '21 at 11:07
  • I need to export data in a web application. Does PowerQuery SDK require some server or a special software installed on the webserver? – Liero Oct 22 '21 at 11:26
  • PowerQuery can be reached from within Excel (Data- New Query) and the results be shown in a sheet (existing or new). For SDK handling I can't tell. I think needs Excel 2010 at least. – Apostolos55 Oct 25 '21 at 07:58
  • 1
    My answer here might help - https://stackoverflow.com/questions/32690851/export-big-amount-of-data-from-xlsx-outofmemoryexception/32787219#32787219 – petelids Nov 01 '21 at 13:57
  • 1
    @petelids: yes, it is exactly what I was looking for. This Question might be a duplicate – Liero Nov 02 '21 at 12:27
  • @Apostolos55: Thanks for the powerquery hint. I'm using it for some scenarios thanks to you. – Liero Nov 02 '21 at 12:29
  • yes, PowerQuery is great, for automating large rows without VBA and a good introduction to PowerBI etc... – Apostolos55 Nov 04 '21 at 23:09

0 Answers0