Dealing With Shared Strings In OOXML


Shared strings are the way that Excel reduced redundant data in a worksheet.  They are also important if you are working with charts in Word documents or PowerPoint slide decks.  Instead of inserting string constants into a cell you give it the index of the string from the SharedStringTable and mark the cell as having a shared string reference.

So what does it take to work with the SharedStringTable

The first thing you need to do is retrieve the existing shared string table.  This is a fairly simple operation as is demonstrated below.

SharedStringTablePart sharedStrings = tempSpreadsheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();


Another operation that you will want to do is finding strings within the table since the entire purpose of using it is to eliminate redundancy.  I handled this by creating a simple method after converting the SharedStringItems of the table into a List<T>.

private static int GetIndexOfSharedString(List<spreadsheet.SharedStringItem> items, string text){    int result = -1;

    for (int index = 0; index < items.Count(); index++)    {        if (items[index].Descendants<spreadsheet.Text>().First().Text.Trim() == text.Trim())        {            return index;        }    }

    return result;}


Lastly you will want to add new strings to the table.  This is fairly straight forward, but note the last three lines.  If you don’t update the counts you will find your new items never save.

sharedItem = new spreadsheet.SharedStringItem();sharedStrings.SharedStringTable.AppendChild<spreadsheet.SharedStringItem>(sharedItem);sharedStrings.SharedStringTable.Count++;sharedStrings.SharedStringTable.UniqueCount++;sharedStrings.SharedStringTable.Save();


That is really all there is to it.

(Note: for those who have complained about the code formatting I hope this is better)


%d bloggers like this: