如何使用OpenXML向Excel模板写入数据且保留原有模板格式

问题说明 #

可通过OpenXML读取Excel模板、向工作表追加数据，但存在两类异常：

• 无法保留现有Excel模板的预设格式
• 生成的文件无法识别、显示列标题
• 原生成文件无列标题且所有单元格无预设格式，预期生成文件保留模板原有样式并正确显示列标题

原问题代码 #

if (ds.Tables.Count > 0 && ds.Tables[0] != null || ds.Tables[0].Columns.Count > 0)
{
    System.Data.DataTable table = ds.Tables[0];

    using (var spreadsheetDocument = SpreadsheetDocument.Open(filePath, true))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

        Sheet sheet = spreadsheetDocument.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
        Worksheet worksheet = (spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;

        SheetData sheetData = worksheet.GetFirstChild<SheetData>();
        List<String> columns = new List<string>();
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();

        foreach (DataColumn column in table.Columns)
        {
            columns.Add(column.ColumnName);
            Cell cell = new Cell();
            cell.DataType = CellValues.String;
            cell.CellValue = new CellValue(column.ColumnName);
        }
        

        foreach (DataRow row in table.Rows)
        {
            Row newRow = new Row();
            columns.ForEach(col =>
            {
                Cell cell = new Cell();
                //If value is DBNull, do not set value to cell  
                if (row[col] != System.DBNull.Value)
                {
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(row[col].ToString());
                }
                newRow.AppendChild(cell);
            });
            sheetData.AppendChild(newRow);
        }
        spreadsheetDocument.WorkbookPart.Workbook.Save();
        spreadsheetDocument.Close();
    }
}

问题原因 #

1. 列标题未挂载：循环创建了列标题对应的Cell对象，但从未将这些Cell添加到行对象、也未插入到SheetData中，生成的文件自然没有列标题。
2. 格式丢失：直接new Cell/Row创建的对象默认不带样式索引，不会继承模板预设的单元格、行、列格式。
3. 插入位置错误：直接将新行追加到SheetData末尾，没有判断模板原有内容的结束行号，容易覆盖模板内容，也不会对应到模板预设的行格式位置。
4. 逻辑判断错误：原判断条件中的||会导致ds.Tables[0]为null时仍执行后续逻辑，有抛出空引用异常的风险。

修复后代码 #

if (ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Columns.Count > 0)
{
    DataTable table = ds.Tables[0];
    using (var spreadsheetDocument = SpreadsheetDocument.Open(filePath, true))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        Sheet sheet = workbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
        WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id.Value);
        Worksheet worksheet = worksheetPart.Worksheet;
        SheetData sheetData = worksheet.GetFirstChild<SheetData>();
        
        // 获取模板最后一行的行号，避免覆盖原有内容
        uint lastRowIndex = sheetData.Elements<Row>().Any() ? sheetData.Elements<Row>().Max(r => r.RowIndex.Value) : 0;
        // 取模板第二行（一般模板第二行是数据行格式模板）的样式作为新增数据行的默认样式
        Row templateDataRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == 2);
        List<uint> cellStyleIndexes = new List<uint>();
        if (templateDataRow != null)
        {
            cellStyleIndexes.AddRange(templateDataRow.Elements<Cell>().Select(c => c.StyleIndex?.Value ?? 0));
        }

        List<string> columns = table.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList();

        // 写入列标题（如果模板第一行是标题位置，直接覆盖第一行的单元格值，保留原有样式）
        Row headerRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == 1) ?? new Row() { RowIndex = 1 };
        for (int i = 0; i < columns.Count; i++)
        {
            Cell headerCell = headerRow.Elements<Cell>().ElementAtOrDefault(i) ?? new Cell();
            headerCell.CellValue = new CellValue(columns[i]);
            headerCell.DataType = CellValues.String;
            // 保留原有标题样式，如果是新增单元格则用默认标题样式
            if (headerCell.StyleIndex == null && templateDataRow != null)
            {
                headerCell.StyleIndex = templateDataRow.Elements<Cell>().FirstOrDefault()?.StyleIndex ?? 0;
            }
            if (i >= headerRow.Elements<Cell>().Count())
            {
                headerRow.AppendChild(headerCell);
            }
        }
        if (!sheetData.Elements<Row>().Any(r => r.RowIndex == 1))
        {
            sheetData.AppendChild(headerRow);
        }
        lastRowIndex = Math.Max(lastRowIndex, 1);

        // 追加数据行，继承模板样式
        foreach (DataRow row in table.Rows)
        {
            lastRowIndex++;
            Row newRow = new Row() { RowIndex = lastRowIndex };
            // 继承模板行的样式
            if (templateDataRow != null)
            {
                newRow.StyleIndex = templateDataRow.StyleIndex;
            }

            for (int i = 0; i < columns.Count; i++)
            {
                Cell cell = new Cell();
                // 套用对应列的单元格样式
                if (i < cellStyleIndexes.Count)
                {
                    cell.StyleIndex = cellStyleIndexes[i];
                }
                if (row[columns[i]] != DBNull.Value)
                {
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(row[columns[i]].ToString());
                }
                newRow.AppendChild(cell);
            }
            sheetData.AppendChild(newRow);
        }

        worksheet.Save();
        spreadsheetDocument.Close();
    }
}

修复说明 #

• 列标题直接写入到模板第一行的对应位置，保留原有标题样式
• 新增数据行默认继承模板第二行的行样式和对应列的单元格样式，保证格式和模板一致
• 自动计算插入行的行号，不会覆盖模板原有内容
• 修复了原逻辑判断的空引用风险

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