Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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);
}
like image 464
mhttk Avatar asked Jun 24 '11 13:06

mhttk


1 Answers

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>
like image 91
amurra Avatar answered Oct 15 '22 06:10

amurra