1

I need to append rows to the end of xls/xlsx document with OpenXmlSDK.

There is similar question but suggested approach allocates entire sheet in memory which throws Out Of Memory for my case.

I followed SAX approach but it also generates Out of memory.

Is there a way to xlsx file for append? I have not found such option in OpenXmlWriter.

  private static void OpenXMLSDKAppendRows(string fileName, int numRows, int numCols)
    {
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, true))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

                using (OpenXmlWriter openXmlWriter = OpenXmlWriter.Create(worksheetPart))
                {
                    Row row = new Row();
                    Cell cell = new Cell();
                    CellValue value = new CellValue("Test");
                    cell.AppendChild(value);

                    openXmlWriter.WriteStartElement(new Worksheet());
                    openXmlWriter.WriteStartElement(new SheetData());
                    for (int rowIndex = 0; rowIndex < numRows; rowIndex++)
                    {
                        openXmlWriter.WriteStartElement(row);
                        for (int colIndex = 0; colIndex < numCols; colIndex++)
                        {
                            openXmlWriter.WriteElement(cell);
                        }
                        openXmlWriter.WriteEndElement();
                    }
                    openXmlWriter.WriteEndElement();
                    openXmlWriter.WriteEndElement();

                    openXmlWriter.Close();
                }
            }
        }
    }

I can not use EPPlus because of LGPL license.

Community
  • 1
  • 1
makambi
  • 41
  • 1
  • 1
  • 5
  • Is the exception thrown beacuse of the large volume of data You are trying to append or size of the original file? Is it thrown when the code is run on a small file with only few rows to be added? – Lukasz M May 30 '16 at 17:58
  • @lukasz-m that particular snippet throws out of memory. OpenXmlReader allocates lots of data in memory. I need kind of append solution which .Net StreamWriter has – makambi May 31 '16 at 07:25

0 Answers0