vba 保存 OpenXML 文档时操作共享字符串表

Manipulating Shared Strings Table when saving OpenXML document

提问by mcalex

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

我正在尝试使用实体框架数据创建 Excel 2010 工作簿（EF 不是问题）。我或多或少地关注以下代码： CodeProject 链接

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 hereto help.

在上面的链接中创建文本单元格的代码将所有文本创建为内联字符串。为了遵循最佳实践（至少就我了解的 OpenXML 而言），我想将其更改为使用共享字符串表，因此我遵循此处找到的代码来提供帮助。

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();
  }

  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 OpenXmlParton the stringTable.Save(); line

不幸的是，我InvalidOperationException: Cannot save DOM tree since this element is not associated with an OpenXmlPart在 stringTable.Save(); 上得到了一个 线

I have tried to correct this by changing the if(!found) block to:

我试图通过将 if(!found) 块更改为：

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.

当代码遇到新行时，它返回一个 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 stringtableis associated with an OpenXmlPart or not.

在这一点上，我对 mystringtable是否与 OpenXmlPart 相关联感到困惑。

Can someone see what I'm doing wrong or maybe point out a better way to do this?

有人可以看到我做错了什么，或者指出一个更好的方法来做到这一点？

回答by Vincent Tan

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:

添加 SharedStringTablePart 时，请立即初始化与该部件关联的 SharedStringTable。然后您不检查 SharedStringTable 对象的无效性。尝试这个：

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.

我希望该片段足以让您识别功能中的部分。如果 SharedStringTablePart 不为 null，则其中已包含 SharedStringTable 对象。如果它为空，那么......好吧，同时创建两者。

回答by GolfFan

Probably not useful for the original poster, but in case anyone else stumbles across this. Microsoft's example has a template excel file they use to modify. This answer is for creating an excel document from scratch. Hope this helps someone.

可能对原始海报没有用，但以防其他人偶然发现这一点。微软的例子有一个他们用来修改的模板excel文件。此答案用于从头开始创建 excel 文档。希望这可以帮助某人。

Try adding the SharedStringTablePartand SharedStringTablewhen creating and first saving the document. I'm creating a file and place it in the temp folder, then reopen the file to modify.

在创建和首次保存文档时尝试添加SharedStringTablePart和SharedStringTable。我正在创建一个文件并将其放在临时文件夹中，然后重新打开该文件进行修改。

public string CreateSpreadsheet()
 {
    string filepath = Path.GetTempFileName();
    filepath = filepath.Replace(".tmp", ".xlsx");
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
    Create(filepath, SpreadsheetDocumentType.Workbook);


    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();
    workbookpart.Workbook.Save();


    **WorkbookPart wbPart = spreadsheetDocument.WorkbookPart;
    var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
    if (stringTablePart == null)
    {
      // Create it.
      stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
    }
    stringTablePart.SharedStringTable = new SharedStringTable();**

    // Close the document.
    spreadsheetDocument.Close();
    return filepath;
    }