Excel cell text is trimmed when writen in xlsx file when using C# & OpenXML

Question

When writing inside an excel file the text in the cell gets trimmed after writing.

For example:
Let's say I am trying to write :"\r\nThis text starts with a new line " I would expect when I open the xlsx file for the cell to start with an empty line, but instead I get this "This text starts with a new line"

This happens only to the whitespace at the beginning and the end, which essentially gets trimmed.

I have tried setting the cell format like this but it doesn't seem to work:

new CellFormat()
{
    ApplyAlignment = true,
    Alignment = new Alignment
    {
        WrapText = new BooleanValue(false)
    }
}

Answer 1

The problem is that the underlying format is XML. Any whitespace at the start or end of a value is ignored unless you mark the space as preserved.

To do this you need to set the Space property to SpaceProcessingModeValues.Preserve, for example:

Cell cell = new Cell() { CellReference = "A1" };
CellValue value = new CellValue("\r\nThis text starts with a new line");
value.Space = SpaceProcessingModeValues.Preserve;
cell.CellValue = value;
cell.DataType = new EnumValue<CellValues>(CellValues.String);

This produces XML that looks like:

<x:c r="A1" t="str">
    <x:v xml:space="preserve">
This text starts with a new line</x:v>
</x:c>

The xml:space="preserve" will prevent the whitespace from being removed from the beginning or end of the value. This produces a file that looks like the below where you can see the newline is preserved.