Keep original data type when creating a CellValue instead of converting to string to save memory.

[MagnusBeijer]

Is your feature request related to a problem? Please describe.
When writing millions of cells to a sheet the memory usage is very high.

Describe the solution you'd like
Currently CellValue converts all values added to strings.
For example:

public CellValue(int value)
   : this(value.ToString(CultureInfo.InvariantCulture))
{
}

Would it not make more sense to add (keep) the int (or whatever native type is being added) and write that to the XmlWriter when serializing the data to reduce the memory footprint?

[mikeebowen]

Hi @MagnusBeijer, Thanks for the suggestion, but in the definition for the XmlWriter.WriteValue Method it states:

The WriteValue method accepts common language runtime (CLR) simple-typed values, converts them to their string representations according to the XML schema definition language (XSD) data type conversion rules, and writes them out by using the WriteString method.

Unfortunately, that means if we don't convert the value to a string before writing to the XmlWriter it is only moving where the ToString method is called, not removing it, so there wouldn't reduce the memory usage.

If you are running into memory issues, you can look into using the SAX approach to writing and parsing XML files. There is a current documentation on how to Parse and read a large spreadsheet document using SAX and 2 upcoming updates to the documentation that demonstrate How to: Copy a Worksheet Using SAX (Simple API for XML) and How to: Replace text in a word document using SAX (Simple API for XML) to help get started.

[MagnusBeijer]

But if ToString() is run just before the value is written to the writer it is then garbage collectable. The current implementation converts all values to strings and keeps them in memory.

[mikeebowen]

I'll reopen this, but we will need to do a memory consumption analysis to determine if we will make this change. For memory saving though, I still recommend using the SAX approach, it saves significant memory over the DOM approach.

[twsouthwick]

Can you collect some performance metrics here so we can see what the actual amount is percentage of other types? see https://learn.microsoft.com/en-us/dotnet/core/diagnostics/trace-perfcollect-lttng for one way to do it

[MagnusBeijer]

Here is a small program to showcase the issue.

static void Main()
{
    string filePath = "RandomDoubles.xlsx";
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        SheetData sheetData = new SheetData();
        worksheetPart.Worksheet = new Worksheet(sheetData);

        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
        Sheet sheet = new Sheet()
        {
            Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1,
            Name = "Sheet1"
        };
        sheets.Append(sheet);

        Random random = new Random();
        for (int rowIndex = 0; rowIndex < 100_000; rowIndex++)
        {
            Row row = new Row();
            for (int colIndex = 0; colIndex < 100; colIndex++)
            {
                Cell cell = new Cell()
                {
                    DataType = CellValues.Number,
                    CellValue = new CellValue(random.NextDouble())
                };
                row.Append(cell);
            }
            sheetData.Append(row);
        }

        workbookPart.Workbook.Save();
    }

    Console.WriteLine($"Excel file '{filePath}' created successfully.");
}

[mikeebowen]

Hi @MagnusBeijer, to save memory, the best option is to use SAX (Simple API for XML). SAX provides a writer that provides fast, non-cached, forward-only way to generate streams or files that contain XML. Using SAX will save significantly more memory than not converting all CellValue values to strings. Below is your sample rewritten with the Open XML SDK's implementation of SAX. Copy it and use the instructions at Analyze memory usage by using the .NET Object Allocation tool to compare the memory usage of your code using the DOM method and the SAX version below. I suggest testing the SAX sample first, the difference is enormous.

string path = "RandomDoubles.xlsx";

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
    WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    string relId = workbookPart.GetIdOfPart(worksheetPart);

    using (OpenXmlWriter workbookPartWriter = OpenXmlPartWriter.Create(workbookPart))
    {
        workbookPartWriter.WriteStartDocument();
        workbookPartWriter.WriteStartElement(new Workbook());
        workbookPartWriter.WriteStartElement(new Sheets());
        workbookPartWriter.WriteElement(new Sheet() { Id = relId, SheetId = 1, Name = "Sheet1" });
        workbookPartWriter.WriteEndElement();
        workbookPartWriter.WriteEndElement();
    }

    using (OpenXmlWriter worksheetPartWriter = OpenXmlPartWriter.Create(worksheetPart))
    {

        Worksheet worksheet = new();
        SheetData sheetData = new();
        Row row = new();
        Cell cell = new() { DataType = CellValues.Number };
        CellValue cellValue = new();
        Random random = new();

        worksheetPartWriter.WriteStartDocument();
        worksheetPartWriter.WriteStartElement(worksheet);
        worksheetPartWriter.WriteStartElement(sheetData);

        for (int rowIdx = 0; rowIdx < 100000; rowIdx++)
        {
            worksheetPartWriter.WriteStartElement(row);

            for (int cellIdx = 0; cellIdx < 100; cellIdx++)
            {
                worksheetPartWriter.WriteStartElement(cell);
                worksheetPartWriter.WriteStartElement(cellValue);
                worksheetPartWriter.WriteString(random.NextDouble().ToString());
                worksheetPartWriter.WriteEndElement();
                worksheetPartWriter.WriteEndElement();
            }

            worksheetPartWriter.WriteEndElement();
        }

        worksheetPartWriter.WriteEndElement();
        worksheetPartWriter.WriteEndElement();
    }
}

Console.WriteLine($"Excel file '{path}' created successfully.");

Here are 3 additional examples from the documentation demonstrating how to use the SAX approach with the Open XML SDK:

• Copy a Worksheet Using SAX (Simple API for XML)
• Replace Text in a Word Document Using SAX (Simple API for XML)
• Parse and read a large spreadsheet document