如何使用OpenXML从模板复制填充Excel并保留列格式？

你的代码目前无法保留模板列格式的核心原因是直接创建了新的Worksheet和SheetData对象，完全覆盖了模板中原有的工作表内容与格式定义。以下是问题拆解和修正方案：

核心问题点 #

• 代码中Dim worksheet As Worksheet = New Worksheet()和WorksheetPart.Worksheet = worksheet会彻底替换模板里的工作表，导致模板预设的列宽、单元格样式、格式规则全部丢失。
• 重新生成表头行，没有复用模板中已有的表头样式。
• 填充数据时未继承对应列的单元格格式。

修正后的代码 #

Public Sub ExportDTfromModel(ByVal pathmodel As String, ByVal pathdestination As String, ByVal dt As System.Data.DataTable)
    u.SegnalaSoloLog("Inizio Metodo: " & MethodBase.GetCurrentMethod().Name.ToString())
    Try
        If System.IO.File.Exists(pathmodel) Then
            System.IO.File.Copy(pathmodel, pathdestination, True)

            Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(pathdestination, True)
                Dim WorksheetPart As WorksheetPart = GetWorksheetPartByName(spreadSheet, dt.TableName)
                If (Not WorksheetPart Is Nothing) Then
                    ' 获取模板中已有的SheetData，而非创建新对象
                    Dim sheetData As SheetData = WorksheetPart.Worksheet.GetFirstChild(Of SheetData)()
                    If sheetData Is Nothing Then
                        sheetData = New SheetData()
                        WorksheetPart.Worksheet.AppendChild(sheetData)
                    End If

                    ' 清空模板中已有的数据行（保留第1行表头）
                    Dim rowsToRemove = sheetData.Elements(Of Row)().Where(Function(r) r.RowIndex.Value > 1).ToList()
                    For Each row In rowsToRemove
                        row.Remove()
                    Next

                    ' 读取表头单元格的样式索引，用于后续数据行复用
                    Dim headerRow = sheetData.Elements(Of Row)().FirstOrDefault(Function(r) r.RowIndex.Value = 1)
                    Dim columnStyleIndices As New List(Of Integer?)()
                    If headerRow IsNot Nothing Then
                        For Each cell In headerRow.Elements(Of Cell)()
                            columnStyleIndices.Add(cell.StyleIndex)
                        Next
                    End If

                    ' 填充数据行
                    For rowIndex As Integer = 0 To dt.Rows.Count - 1
                        Dim dsrow As DataRow = dt.Rows(rowIndex)
                        Dim newRow As New DocumentFormat.OpenXml.Spreadsheet.Row()
                        newRow.RowIndex = New UInt32Value(rowIndex + 2) ' 数据行从第2行开始

                        For colIndex As Integer = 0 To dt.Columns.Count - 1
                            Dim colName As String = dt.Columns(colIndex).ColumnName
                            Dim cell As New DocumentFormat.OpenXml.Spreadsheet.Cell()

                            ' 复用对应列的样式
                            If columnStyleIndices.Count > colIndex Then
                                cell.StyleIndex = columnStyleIndices(colIndex)
                            End If

                            ' 根据数据类型设置单元格属性
                            Select Case dsrow(colName).GetType().ToString
                                Case "System.DateTime"
                                    cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToDateTime(dsrow(colName)).ToString("yyyy-MM-dd"))
                                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Date
                                Case "System.Decimal"
                                    cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToDecimal(dsrow(colName)))
                                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number
                                Case "System.String"
                                    cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow(colName).ToString())
                                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
                                Case Else
                                    cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow(colName).ToString())
                                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
                            End Select

                            newRow.AppendChild(cell)
                        Next

                        sheetData.AppendChild(newRow)
                    Next

                    WorksheetPart.Worksheet.Save()
                End If
                spreadSheet.WorkbookPart.Workbook.Save()

                GC.Collect()
                u.SegnalaSoloLog("File Excel Generato in : " & pathdestination)
            End Using
        Else
            u.SegnalaSoloLog("Errore Modello non trovato, impossibile esportare il file")
        End If
    Catch ex As Exception
        u.SegnalaSoloLog("Errore in Export from model: " & ex.Message)
    End Try
End Sub

关键修正说明 #

1. 复用模板原有SheetData：直接读取模板工作表中的SheetData对象，避免创建新对象覆盖格式配置。
2. 保留表头结构：仅清空表头后的旧数据行，保留模板预设的表头样式。
3. 继承列样式：读取表头单元格的StyleIndex并应用到对应列的数据单元格，确保格式与模板一致。
4. 显式设置行索引：指定新数据行的RowIndex，避免Excel文件出现结构异常。

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