使用Microsoft OpenXML写入Excel单元格无内容问题求助

首次接触Microsoft OpenXML，遇到功能异常：创建xls文件后，向指定单元格写入值并保存，但代码仅能生成空文件，无法写入任何内容。以下是我的代码（已补充InsertCellInWorksheet函数）：

Sub Main()
    CreateSpreadsheetWorkbook("IMPOVO.XLS")
    Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open("IMPOVO.XLS", True)
    Using (spreadSheet)

        Dim worksheetPart As WorksheetPart = spreadSheet.WorkbookPart.WorksheetParts.First
        Dim cell As Cell = InsertCellInWorksheet("A", 1, worksheetPart)

        ' 设置单元格A1的值
        cell.CellValue = New CellValue("Hi World")
        cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)

        ' 保存新工作表
        worksheetPart.Worksheet.Save()
        
    End Using
End Sub

Public Sub CreateSpreadsheetWorkbook(ByVal filepath As String)
    ' 通过路径创建电子表格文档，默认AutoSave=true、Editable=true、类型为xlsx
    Dim spreadsheetDocument As SpreadsheetDocument =
    SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)
    
    ' 向文档添加WorkbookPart
    Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart
    workbookpart.Workbook = New Workbook
    
    ' 向WorkbookPart添加WorksheetPart
    Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
    worksheetPart.Worksheet = New Worksheet(New SheetData())
    
    ' 向Workbook添加Sheets集合
    Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())
    
    ' 添加新工作表并关联到工作簿
    Dim sheet As Sheet = New Sheet
    sheet.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)
    sheet.SheetId = 1
    
    sheets.Append(sheet)
    
    workbookpart.Workbook.Save()
    
    ' 关闭文档
    spreadsheetDocument.Close()
End Sub

Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Cell
    Dim worksheet As Worksheet = worksheetPart.Worksheet
    Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
    Dim cellReference As String = (columnName + rowIndex.ToString())

    ' 如果工作表中没有指定索引的行，插入新行
    Dim row As Row
    If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
        row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
    Else
        row = New Row()
        row.RowIndex = rowIndex
        sheetData.Append(row)
    End If

    ' 如果行中没有指定列的单元格，插入新单元格
    If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
        Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
    Else
        ' 单元格必须按CellReference顺序排列，确定新单元格的插入位置
        Dim refCell As Cell = Nothing
        For Each cell As Cell In row.Elements(Of Cell)()
            If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
                refCell = cell
                Exit For
            End If
        Next

        Dim newCell As Cell = New Cell
        newCell.CellReference = cellReference

        row.InsertBefore(newCell, refCell)
        worksheet.Save()

        Return newCell
    End If
End Function

问题根源及修复方案 #

1. 共享字符串类型使用错误 #

你设置了cell.DataType = CellValues.SharedString，但OpenXML要求使用共享字符串时，必须先创建SharedStringTablePart并将字符串添加到该表中，不能直接给CellValue赋值文本。

如果只是简单写入文本，更简单的方式是直接使用CellValues.String类型，无需维护共享字符串表：

' 直接使用String类型，无需共享字符串
cell.CellValue = New CellValue("Hi World")
cell.DataType = New EnumValue(Of CellValues)(CellValues.String)

如果一定要用共享字符串，需要在创建工作簿时添加SharedStringTablePart，并将字符串存入表中：

' 在CreateSpreadsheetWorkbook方法中添加SharedStringTablePart
Dim sharedStringPart As SharedStringTablePart = workbookpart.AddNewPart(Of SharedStringTablePart)()
sharedStringPart.SharedStringTable = New SharedStringTable()

' 在Main方法中写入共享字符串
Dim sharedStringPart As SharedStringTablePart = spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
Dim stringItem As SharedStringItem = New SharedStringItem(New Text("Hi World"))
sharedStringPart.SharedStringTable.AppendChild(stringItem)
sharedStringPart.SharedStringTable.Save()

' 单元格值为共享字符串在表中的索引
cell.CellValue = New CellValue((sharedStringPart.SharedStringTable.Elements.Count - 1).ToString())
cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)

2. 文件格式不匹配 #

你创建的是SpreadsheetDocumentType.Workbook（对应XLSX格式），但文件名使用.XLS（旧版二进制格式），这会导致Excel识别文件格式异常，建议统一使用.xlsx后缀：

CreateSpreadsheetWorkbook("IMPOVO.XLSX")
Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open("IMPOVO.XLSX", True)

3. 未完整保存工作簿变更 #

仅保存worksheetPart.Worksheet不够，还需要保存WorkbookPart确保所有变更生效：

' 在Main方法的Using块内添加
worksheetPart.Worksheet.Save()
spreadSheet.WorkbookPart.Workbook.Save()

4. 工作表未命名（可选） #

建议给Sheet添加Name属性，避免Excel打开文件时出现警告：

' 在CreateSpreadsheetWorkbook方法中
sheet.Name = "Sheet1"

修复后的完整示例（简化版） #

Sub Main()
    CreateSpreadsheetWorkbook("IMPOVO.XLSX")
    Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open("IMPOVO.XLSX", True)
    Using (spreadSheet)
        Dim worksheetPart As WorksheetPart = spreadSheet.WorkbookPart.WorksheetParts.First
        Dim cell As Cell = InsertCellInWorksheet("A", 1, worksheetPart)

        ' 直接写入字符串
        cell.CellValue = New CellValue("Hi World")
        cell.DataType = New EnumValue(Of CellValues)(CellValues.String)

        ' 保存工作表和工作簿
        worksheetPart.Worksheet.Save()
        spreadSheet.WorkbookPart.Workbook.Save()
    End Using
End Sub

Public Sub CreateSpreadsheetWorkbook(ByVal filepath As String)
    Dim spreadsheetDocument As SpreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)
    
    Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart
    workbookpart.Workbook = New Workbook
    
    ' 添加SharedStringTablePart（如果后续要用共享字符串）
    Dim sharedStringPart As SharedStringTablePart = workbookpart.AddNewPart(Of SharedStringTablePart)()
    sharedStringPart.SharedStringTable = New SharedStringTable()
    
    Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
    worksheetPart.Worksheet = New Worksheet(New SheetData())
    
    Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())
    
    Dim sheet As Sheet = New Sheet
    sheet.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)
    sheet.SheetId = 1
    sheet.Name = "Sheet1"
    
    sheets.Append(sheet)
    
    workbookpart.Workbook.Save()
    spreadsheetDocument.Close()
End Sub

' InsertCellInWorksheet函数保持不变
Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Cell
    Dim worksheet As Worksheet = worksheetPart.Worksheet
    Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
    Dim cellReference As String = (columnName + rowIndex.ToString())

    Dim row As Row
    If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
        row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
    Else
        row = New Row()
        row.RowIndex = rowIndex
        sheetData.Append(row)
    End If

    If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
        Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
    Else
        Dim refCell As Cell = Nothing
        For Each cell As Cell In row.Elements(Of Cell)()
            If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
                refCell = cell
                Exit For
            End If
        Next

        Dim newCell As Cell = New Cell
        newCell.CellReference = cellReference

        row.InsertBefore(newCell, refCell)
        worksheet.Save()

        Return newCell
    End If
End Function

内容的提问来源于stack exchange，提问作者Gabriele Cozzolino