0

I'm trying to build an export functionality that exports data to xlsx format. I've already done csv and it works perfectly, however I'm having trouble with exporting data that is larger than 60,000 records. Can anyone please assist with this dilemma? 

uint sheetId = 1;
if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
    sheetId =
        sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}

DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
sheets.Append(sheet);

DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

int iCol = 1;
List<String> columns = new List<string>();
foreach (System.Data.DataColumn column in table.Columns)
{
    columns.Add(column.ColumnName);

    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
    cell.CellReference = ExcelColumnLetter(iCol).ToString().Trim() + "1";
    headerRow.AppendChild(cell);

    iCol++;
}

sheetData.AppendChild(headerRow);

int iRow = 2;
foreach (System.Data.DataRow dsrow in table.Rows)
{
    iCol = 1;
    DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
    foreach (String col in columns)
    {

        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
        cell.CellReference = ExcelColumnLetter(iCol).ToString().Trim() + iRow.ToString().Trim();
        newRow.AppendChild(cell);
        iCol++;
    }
    sheetData.AppendChild(newRow);
    iRow++;
}
workbook.Close();
FortyTwo
  • 2,414
  • 3
  • 22
  • 33
K.Reed
  • 25
  • 5
  • 1
    `if (... Count() > 0)` is almost always better written as `if (... Any())`. If you wanted to know if there are any socks in the pile, you wouldn't count all of them. You'd see if there was at least 1 and stop. – itsme86 May 21 '18 at 15:14
  • Apparently this is a [known issue](https://github.com/OfficeDev/Open-XML-SDK/issues/110) with OpenXML. According to that link, the issue is actually an [open issue](https://github.com/dotnet/corefx/issues/24457) in .NET that causes a memory leak when writing large ZIP streams. – Icemanind May 21 '18 at 15:14
  • Just speculating based on the error it sounds like you need to do it in batches or compile and run it as 64 bit if possible. – Michael Puckett II May 21 '18 at 15:15
  • out of interest how big was the csv you were making out of the similar routine? – BugFinder May 21 '18 at 15:16
  • @MichaelPuckettII How would I go about doing it in batches? 64 bit doesn't work either. – K.Reed May 21 '18 at 15:25
  • @K.Reed Well to be honest the method is a bit of a mess to unroll so I'm not going to spend a lot of time trying to work out the code, but, use the Skip / Take features of linq and batch the processes into groups of however many make sense. During this time make sure you're closing and disposing of sources properly. I'm also pretty sure that ```new DocumentFormat.OpenXml.Spreadsheet.Sheet()``` is disposable and I don't see you using a proper ```using``` statement or disposing of it manually anywhere. That would help a lot also. If it is disposable then that might actually fix your problem. – Michael Puckett II May 21 '18 at 15:40
  • 1
    Are those strings large? And often the same? Check if a sharedstringtable works for you: https://msdn.microsoft.com/en-us/library/office/gg278314.aspx. I've created sheets with openxml with 100K of rows and around 50MB worth of data in a 32bits setup so it can work. – rene May 21 '18 at 15:43

0 Answers0