Manipulating Shared Strings Table when saving OpenXML document

c#, excel, openxml, vba

Solution

When you add the SharedStringTablePart, immediately initialise the SharedStringTable associated with the part. Then you don't check for nullity on the SharedStringTable object. Try this:

if (stringTablePart == null)
{
    // Create it.
    stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
    stringTablePart.SharedStringTable = new SharedStringTable();
}

var stringTable = stringTablePart.SharedStringTable;
//if (stringTable == null)
//{
//    stringTable = new SharedStringTable();
//}

I hope that fragment is good enough for you to identify the part in your function. If the SharedStringTablePart is not null, it will already have a SharedStringTable object in it. If it's null, then... well, create both at the same time.

Problem

I am trying to create an Excel 2010 workbook using Entity Framework data (EF isn't the issue). I'm more or less following code in: CodeProject link The code that creates a text cell in the link above has all text created as Inline Strings. In order to follow best practices (at least as far as I understand OpenXML) I want to change this to use the Shared Strings table and so I'm following code found here to help. To do this, I grab the Shared strings table from the workbook, add my string to it and then save the table. My code: ``` private static int InsertSharedStringItem(WorkbookPart wbPart, string value) { int index = 0; bool found = false; var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>() .FirstOrDefault(); if (stringTablePart == null) { // Create it. stringTablePart = wbPart.AddNewPart<SharedStringTablePart>(); } var stringTable = stringTablePart.SharedStringTable; if (stringTable == null) { stringTable = new SharedStringTable(); } foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>()) { if (item.InnerText == value) { found = true; break; } index += 1; } ``` ``` if (!found) { stringTable.AppendChild(new SharedStringItem(new Text(value))); stringTable.Save(); } ``` ``` return index; } ``` I've highlighted (and apparently separated) the problem area) Unfortunately I get an `InvalidOperationException: Cannot save DOM tree since this element is not associated with an OpenXmlPart` on the stringTable.Save(); line I have tried to correct this by changing the if(!found) block to: ``` if (!found) { stringTable.AppendChild(new SharedStringItem(new Text(value))); wbPart.SharedStringTablePart.SharedStringTable = stringTable; stringtable.Save(); } ``` When the code hits the new line it returns an `UnhandledArgument Exception: Cannot set the given root element to this part. The given part root element has already been associated with another OpenXmlPart.` At this point, I'm confused as to whether or not my `stringtable` is associated with an OpenXmlPart or not. Can someone see what I'm doing wrong or maybe point out a better way to do this?

Original source