/// <summary>
/// Returns the index of a shared string.
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="stringItem">String to search for</param>
/// <returns>Index of a shared string. -1 if not found</returns>
public static int IndexOfSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem)
{
DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
bool found = false;
int index = 0;
foreach (DocumentFormat.OpenXml.Spreadsheet.SharedStringItem sharedString in sharedStringTable.Elements <DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>())
{
if (sharedString.InnerText == stringItem)
{
found = true;
break;;
}
index++;
}
return(found ? index : -1);
}
/// <summary>
/// Add a single string to shared strings table.
/// Shared string table is created if it doesn't exist.
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="stringItem">string to add</param>
/// <param name="save">Save the shared string table</param>
/// <returns></returns>
public static bool AddSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
if (0 == sharedStringTable.Where(item => item.InnerText == stringItem).Count())
{
sharedStringTable.AppendChild(
new DocumentFormat.OpenXml.Spreadsheet.SharedStringItem(
new DocumentFormat.OpenXml.Spreadsheet.Text(stringItem)));
// Save the changes
if (save)
{
sharedStringTable.Save();
}
}
return(true);
}
