当值在 SharedStringTable 中时，OpenXML Excel 如何更改单元格的值 [英] OpenXML Excel how to change value of a cell when value is in SharedStringTable

问题描述

我正在寻找一种安全有效的方法来更新文本可能位于 SharedStringTable 中的单元格的值(这似乎是由 MS Excel 创建的任何电子表格的情况).

I am looking for a safe and efficient way to update the value of a cell where the text may be in the SharedStringTable (this appears to be the case of any spreadsheet created by MS Excel).

顾名思义，SharedStringTable 包含可以在多个单元格中使用的字符串.

As the name implies SharedStringTable contains strings that may be used in multiple cells.

因此，仅在字符串表中查找项目并更新值并不是可行的方法，因为它也可能被其他单元格使用.

So just finding the item in the string table and update the value is NOT the way to go as it may be in use by other cells as well.

据我所知，必须执行以下操作:

As far as I understand one must do the following:

1. 检查单元格是否使用字符串表

1. Check if the cell is using string table

如果是，请检查新字符串是否已经存在，在这种情况下只需使用它(如果任何其他单元格不再使用旧字符串，请记住删除带有旧字符串的项目！)

If so, check if the new string is already there in which case just use it (remember to remove the item with the old string if it is no longer in use by any other cells!)

如果不是，请检查电子表格中的任何其他单元格是否引用了带有旧字符串的项目

If not, check if the item with old string is refered to by any other cells in the spreadsheet

如果是这样，使用新字符串创建新项目并引用它

If so, create new item with the new string and refer to it

如果没有，只需用新字符串更新现有项目

If not, just update existing item with new string

是否有使用 OpenXML SDK 的更简单的解决方案?

Are there any easier solution to this using the OpenXML SDK?

还要考虑到可能不仅要更新一个单元格，还要为多个单元格设置新的(不同的)值.所以我们可能会在循环中调用更新单元格方法......

Also consider that one may want to update not only one cell but rather set new (different) values for several cells. So we may be calling the update cell method in a loop ...

推荐答案

首先解决这个问题.似乎适用于我的特殊情况.但必须有可能改进，或者甚至更好地做完全不同的事情:

First take on this. Appears to work for my particular case. But it must be possible to improve on or, even better, do totally different:

private static void UpdateCell(SharedStringTable sharedStringTable, 
   Dictionary<string, SheetData> sheetDatas, string sheetName, 
   string cellReference, string text)
{
   Cell cell = sheetDatas[sheetName].Descendants<Cell>()
    .FirstOrDefault(c => c.CellReference.Value == cellReference);
   if (cell == null) return;
   if (cell.DataType == null || cell.DataType != CellValues.SharedString)
   {
    cell.RemoveAllChildren();
    cell.AppendChild(new InlineString(new Text { Text = text }));
    cell.DataType = CellValues.InlineString;
    return;
   }
   // Cell is refering to string table. Check if new text is already in string table, if so use it.
   IEnumerable<SharedStringItem> sharedStringItems 
    = sharedStringTable.Elements<SharedStringItem>();
   int i = 0;
   foreach (SharedStringItem sharedStringItem in sharedStringItems)
   {
    if (sharedStringItem.InnerText == text)
    {
       cell.CellValue = new CellValue(i.ToString());
       // TODO: Should clean up, ie remove item with old text from string table if it is no longer in use.
       return;
    }
    i++;
   }
   // New text not in string table. Check if any other cells in the Workbook referes to item with old text.
   foreach (SheetData sheetData in sheetDatas.Values)
   {
    var cells = sheetData.Descendants<Cell>();
    foreach (Cell cell0 in cells)
    {
       if (cell0.Equals(cell)) continue;
       if (cell0.DataType != null 
       && cell0.DataType == CellValues.SharedString 
       && cell0.CellValue.InnerText == cell.CellValue.InnerText)
       {
        // Other cells refer to item with old text so we cannot update it. Add new item.
        sharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
        cell.CellValue.Text = (i).ToString();
        return;
       }
    }
   }
   // No other cells refered to old item. Update it.
   sharedStringItems.ElementAt(int.Parse(cell.CellValue.InnerText)).Text = new Text(text);
}

....

private static void DoIt(string filePath)
{
   using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filePath, true))
   {
    SharedStringTable sharedStringTable 
       = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>()
        .First().SharedStringTable;
    Dictionary<string, SheetData> sheetDatas = new Dictionary<string, SheetData>();
    foreach (var sheet in spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>())
    {
       SheetData sheetData 
        = (spreadSheet.WorkbookPart.GetPartById(sheet.Id) as WorksheetPart)
           .Worksheet.GetFirstChild<SheetData>();
       sheetDatas.Add(sheet.Name, sheetData);
    }
    UpdateCell(sharedStringTable, sheetDatas, "Sheet1", "A2", "Mjau");
   }
}

警告:不要按原样使用上述内容，它适用于特定的电子表格.如果在其他情况下使用它，很可能没有处理.这是我第一次尝试用于电子表格的 OpenXML.最终遵循 George Polevoy 的建议.更容易，而且似乎没有不良副作用(也就是说，在处理可能超出您控制范围的电子表格时，还有一百万个其他问题需要处理……)

WARNING: Do NOT use the above as is, it works with a particular spreadsheet. It is very likely things not handled if one use it in other situations. This is my first attempt at OpenXML for spreadsheet. Ended up following the suggestion made by George Polevoy. Much easier and appears to have no ill side-effects (That said there are a million other issues to handle when manipulating spreadsheets which may be edited outside your control...)

这篇关于当值在 SharedStringTable 中时，OpenXML Excel 如何更改单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！