What is the difference between CellValues.InlineString and CellValues.String in OpenXML?

mhttk picture mhttk · Jun 24, 2011 · Viewed 15.6k times · Source

I am trying to write some code to generate an Excel spreadsheet and I am not sure what the difference between CellValues.InlineString and CellValues.String to insert text on the cells.

Shall I use this:

private void UpdateCellTextValue(Cell cell,string cellValue)
{
    InlineString inlineString = new InlineString();
    Text cellValueText = new Text { Text = cellValue };
    inlineString.AppendChild(cellValueText);

    cell.DataType = CellValues.InlineString;
    cell.AppendChild(inlineString); 
}

this

private void UpdateCellTextValue(Cell cell, string cellValue)
{
    cell.CellValue = new CellValue(cellValue);
    cell.DataType = new EnumValue<CellValues>(CellValues.String);
}

or just this (InsertSharedStringItem returns the Id of the newly inserted shared string item)

private void SetCellSharedTextValue(Cell cell,string cellValue)
{        
    int stringId = InsertSharedStringItem(cellValue);
    cell.CellValue = new CellValue(stringId.ToString());
    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}

Answer

amurra picture amurra · Jun 27, 2011

According to the documentation at 18.18.11 ST_CellType:

str (String) Cell containing a formula string.

You will only want to use the CellValues.String when you are inserting a formula in the cell. Here is how the XML should look:

<x:c r="C6" s="1" vm="15" t="str">
   <x:f>CUBEVALUE("xlextdat9 Adventure Works",C$5,$A6)</x:f>
   <x:v>2838512.355</x:v>
</x:c>

CellValues.InlineString should only be used if you don't want to store the string in the SharedStringTable. Then anything you mark as an inlineString will be treated as rich text. Although note that when you use CellValues.InlineString that your text must be surounded by a text element and <is> tag:

<x:c r="B2" t="inlineStr">
   <is><t>test string</t></is>
</c>