// Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer
// reference the specified SharedStringItem and removes the item.
private static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
{
bool remove = true;
foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
{
Worksheet worksheet = part.Worksheet;
foreach (var cell in worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
{
// Verify if other cells in the document reference the item.
if (cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString &&
cell.CellValue.Text == shareStringId.ToString())
{
// Other cells in the document still reference the item. Do not remove the item.
remove = false;
break;
}
}
if (!remove)
{
break;
}
}
// Other cells in the document do not reference the item. Remove the item.
if (remove)
{
SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;
if (shareStringTablePart == null)
{
return;
}
SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
if (item != null)
{
item.Remove();
// Refresh all the shared string references.
foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
{
Worksheet worksheet = part.Worksheet;
foreach (var cell in worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
{
if (cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString)
{
int itemIndex = int.Parse(cell.CellValue.Text);
if (itemIndex > shareStringId)
{
cell.CellValue.Text = (itemIndex - 1).ToString();
}
}
}
worksheet.Save();
}
document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
}
}
}
public int RemoveSharedStringItem(int sharedStringId)
{
bool remove = true;
if (this.workbook == null || this.workbook.Workbook == null)
{
Console.WriteLine("Error: This spreadsheet has no workbook!");
return(-1);
}
foreach (var part in this.workbook.GetPartsOfType <WorksheetPart>())
{
Worksheet worksheet = part.Worksheet;
foreach (var cell in worksheet.GetFirstChild <SheetData>().Descendants <Cell>())
{
if (cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString &&
cell.CellValue.Text == sharedStringId.ToString())
{
remove = false;
break;
}
}
if (!remove)
{
break;
}
}
if (remove)
{
if (this.sharedStrings == null)
{
Console.WriteLine("Error: This spreadsheet has no sharedString table!");
return(-1);
}
SharedStringItem item = this.sharedStrings.SharedStringTable
.Elements <SharedStringItem>().ElementAt(sharedStringId);
if (item != null)
{
item.Remove();
// Refresh all the shared string references.
foreach (var part in this.workbook.GetPartsOfType <WorksheetPart>())
{
Worksheet worksheet = part.Worksheet;
foreach (var cell in worksheet.GetFirstChild <SheetData>().Descendants <Cell>())
{
if (cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString)
{
int itemIndex = int.Parse(cell.CellValue.Text);
if (itemIndex > sharedStringId)
{
cell.CellValue.Text = (itemIndex - 1).ToString();
}
}
}
worksheet.Save();
}
this.sharedStrings.SharedStringTable.Save();
}
else
{
Console.WriteLine("Warning: No item found at {0}!", sharedStringId);
return(-1);
}
}
else
{
Console.WriteLine("Warning: No shared string item deleted!");
return(-1);
}
return(sharedStringId);
}