Skip to content

String vs Inline String vs Shared String in Open XML

August 25, 2015

While working on a spreadsheet generation process, I came across three different ways to specify a string cell value in Open XML. While that is confusing enough, if you look up on MSDN, this is all you see:

SharedString Shared String. When the item is serialized out as xml, its value is “s”.
String String. When the item is serialized out as xml, its value is “str”.
InlineString Inline String. When the item is serialized out as xml, its value is “inlineStr”.

ref: https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellvalues(v=office.14).aspx

Obviously, that does not give any information on why I should choose one type over the rest. So, for people like me that’s looking for a basic difference, here’s a summary of what each type is:

String

String datatype works best if you want to store a function in a cell.

<c>

<f>SUM(A2,A3)</f>

<v>1234</v>

</c>

The v element stores formula value that was generated when the formula executed the last time. So, if there is a value on v, that value is directly read. If not, Open XML reader computes the value based on the formula and stores it for future use. The formula itself is stored as text.

Shared String

When you use a Shared String to represent your string, the size of the file is drastically reduced, especially if it has a lot of repeating strings. This is because, when a string is added, it checks if it is already present and if it does, only a reference to that original string is added. If it is not present, then it is added to the file, reference stored in the cell and that reference is used later on when that string appears again.

<c r=”A1″ t=”s”>
<v>0</v>
</c>

The v element holds the reference.

Inline String

Inline String allows you to directly save a text without having to save the reference in its place. To create a cell with text you use the ‘is’ element, which stands for inline-string. An InlineString is saved as rich text.

<c r=”A0″>  <is><t> Test String </t></is></c>

This is a much more easier way to code and easy to read. However, for data containing strings that repeat, this becomes inefficient.

That’s just the basic difference. Pick your option based on performance vs readability.

Advertisements
3 Comments leave one →
  1. Anonymous permalink
    March 29, 2017 12:23 pm

    Great, I was looking for this explanation

  2. Anonymous permalink
    April 10, 2017 7:20 pm

    For string, I find this works instead.
    string

  3. Anonymous permalink
    April 10, 2017 7:24 pm

    Sorry, re-typed in html entities:
    <c r="A1" t="str"><v>String</v></c>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: