当使用c#和OpenXML在xlsx文件中写入时，Excel计算单元文本将被删除

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

当在excel文件中写入时，单元格中的文本会在写入后被修改。

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"

例如:假设我正在尝试写:“\r\nThis text start with a new line”当我打开xlsx文件时，我希望单元格以空行开始，但我得到的却是“this text start 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)
    }
}

2 个解决方案

#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.

问题是底层格式是XML。值开始或结束处的任何空格都将被忽略，除非您将空格标记为保留。

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

为此，需要将空间属性设置为SpaceProcessingModeValues。保护,例如:

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:

这就产生了如下XML:

<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.

xml:space="preserve"将防止从值的开头或结尾删除空格。这将生成一个文件，该文件看起来如下所示，您可以看到新行被保留。

#2

Instead of adding both \r and \n, try adding \r only.

不要同时添加\r和\n，尝试只添加\r。

#1