如何用DocumentFormat.OpenXml创建含SharedStringTablePart的Excel文件

你生成的Excel文件在读取时触发错误，核心原因是SharedStringTablePart为空。你的读取代码中直接使用workbookPart.SharedStringTablePart!强制访问该部件，但创建Excel的代码里既没有初始化这个部件，也没有用共享字符串机制存储单元格文本。

读取代码（可正常运行，但依赖SharedStringTablePart） #

using SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("filePath", true);
    
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart!;

SheetData sheetData = GetSheetData(workbookPart, outFile);
    
Worksheet worksheet = sheetData.Parent as Worksheet;

List<SharedStringItem> sharedStringTableElements = workbookPart.SharedStringTablePart!.SharedStringTable.Elements<SharedStringItem>().ToList();

List<Row> sheetRows = sheetData.Elements<Row>().ToList();

...

原创建Excel代码（缺少SharedStringTablePart初始化） #

using SpreadsheetDocument spreadSheet = CreateSpreadsheet(filePath);
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

SheetData sheetData = new();
worksheetPart.Worksheet = new Worksheet(sheetData);

Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
string relationshipId = workbookPart.GetIdOfPart(worksheetPart);

Sheet sheet = new() {
    Id = relationshipId,
    SheetId = FirstSheetId,
    Name = "SheetName",
};

sheets.Append(sheet);

//fill sheetData here

workbookPart.Workbook.Save();
spreadSheet.Close();

1. 先初始化SharedStringTablePart #

在创建Workbook对象之后，立即添加并初始化SharedStringTablePart，这是核心修复步骤：

// 在workbookPart.Workbook = new Workbook();之后添加以下代码
SharedStringTablePart sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
sharedStringPart.SharedStringTable = new SharedStringTable();

2. 封装共享字符串添加方法 #

添加一个辅助方法，用于将文本存入共享字符串表（避免重复存储相同文本），并返回对应的索引值：

private static int InsertSharedStringItem(string text, SharedStringTablePart sharedStringPart)
{
    // 检查文本是否已存在，存在则返回已有索引
    foreach (SharedStringItem item in sharedStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            return sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToList().IndexOf(item);
        }
    }
    // 不存在则新增文本项
    SharedStringItem newItem = new SharedStringItem(new Text(text));
    sharedStringPart.SharedStringTable.Append(newItem);
    return sharedStringPart.SharedStringTable.Elements<SharedStringItem>().Count() - 1;
}

3. 使用共享字符串填充单元格 #

填充sheetData时，不再直接给Cell赋值文本，而是通过共享字符串索引来设置单元格：

// 示例：创建A1单元格并写入文本
Row row = new Row();
// 获取文本对应的共享字符串索引
int textIndex = InsertSharedStringItem("第一行第一列内容", sharedStringPart);
// 创建使用共享字符串的单元格
Cell cell = new Cell
{
    CellValue = new CellValue(textIndex.ToString()),
    DataType = CellValues.SharedString, // 指定单元格类型为共享字符串
    CellReference = "A1"
};
row.Append(cell);
sheetData.Append(row);

4. 完整修改后的创建代码 #

整合所有修改后的完整代码示例：

using SpreadsheetDocument spreadSheet = CreateSpreadsheet(filePath);
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();

// 初始化SharedStringTablePart
SharedStringTablePart sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
sharedStringPart.SharedStringTable = new SharedStringTable();

WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
SheetData sheetData = new();
worksheetPart.Worksheet = new Worksheet(sheetData);

Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
string relationshipId = workbookPart.GetIdOfPart(worksheetPart);

Sheet sheet = new() {
    Id = relationshipId,
    SheetId = FirstSheetId,
    Name = "SheetName",
};
sheets.Append(sheet);

// 填充sheetData示例
Row row = new Row();
int textIndex = InsertSharedStringItem("测试文本", sharedStringPart);
Cell cell = new Cell
{
    CellValue = new CellValue(textIndex.ToString()),
    DataType = CellValues.SharedString,
    CellReference = "A1"
};
row.Append(cell);
sheetData.Append(row);

// 保存共享字符串表和工作簿
sharedStringPart.SharedStringTable.Save();
workbookPart.Workbook.Save();
spreadSheet.Close();

// 辅助方法：添加共享字符串并返回索引
private static int InsertSharedStringItem(string text, SharedStringTablePart sharedStringPart)
{
    foreach (SharedStringItem item in sharedStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            return sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToList().IndexOf(item);
        }
    }
    SharedStringItem newItem = new SharedStringItem(new Text(text));
    sharedStringPart.SharedStringTable.Append(newItem);
    return sharedStringPart.SharedStringTable.Elements<SharedStringItem>().Count() - 1;
}

关键说明 #

• 即使Excel中没有文本内容，也建议初始化SharedStringTablePart，避免读取代码因部件不存在报错
• 使用共享字符串存储文本是OOXML规范的推荐做法，能有效减少重复文本带来的文件体积膨胀
• 如果确实不需要共享字符串，也可以修改读取代码，先判断workbookPart.SharedStringTablePart是否为null，再做分支处理，但创建时初始化部件是更稳妥的方案

内容的提问来源于stack exchange，提问作者Telepnev Andrey