C#使用OpenXML导出DataTable至Excel时无法生成列名

使用OpenXML库将DataTable数据导出到Excel的控制台应用中，当前代码可正常导出数据行，但尝试添加列标题行时无法成功追加内容。

原导出代码：

class ProgramCheck
{
    private static void Main()
    {
        SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(@"C:\Users\nilam\Desktop\Work\project\GeneratedExcel.xlsx", SpreadsheetDocumentType.Workbook);

        WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

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

        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
        
        Sheet sheet = new Sheet()
        {
            Id = workbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1,
            Name = "TestSheet2"
        };

        sheets.Append(sheet);
        SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
        DataTable dt = Data.SampleData();

        foreach (DataRow item in dt.Rows)
        {
            Row row = new Row();

            for (int i = 0; i < item.ItemArray.Length; i++)
            {
                foreach (DataColumn itemCol in dt.Columns)
                {
                    Columns col = new Columns();
                    Cell cell1 = new Cell()
                    {
                        CellValue = new CellValue(item[i].ToString()),
                        DataType = CellValues.String
                    };
                    col.Append(cell1);
                    Cell cell = new Cell()
                    {
                        CellValue = new CellValue(item[i].ToString()),
                        DataType = CellValues.String
                    };
                    row.Append(cell);
                }
            }
            sheetData.Append(row);
        }
        workbookPart.Workbook.Save();
        spreadsheetDocument.Close();   
    }
}

DataTable定义：

class Data
{
    public static DataTable SampleData()
    {
        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add("ID");
        dt.Columns.Add("Name");
        dt.Columns.Add("Salary");

        dt.Rows.Add("01", "Anis", "1000");
        dt.Rows.Add("02", "AK", "2000");
        dt.Rows.Add("03", "Mak", "3000");
        return dt;
    }
}

1. 缺少标题行生成逻辑：原代码未单独遍历DataTable的列集合来创建标题行
2. 循环逻辑错误：生成数据行时嵌套了遍历列的循环，导致每个数据项被重复创建多次（列数有多少就重复多少次）
3. 错误使用Columns对象：Columns类用于设置列格式（如列宽），并非存储单元格的容器，这段代码完全无效

class ProgramCheck
{
    private static void Main()
    {
        string filePath = @"C:\Users\nilam\Desktop\Work\project\GeneratedExcel.xlsx";
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            // 创建工作簿部件
            WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

            // 创建工作表部件
            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // 添加工作表到工作簿
            Sheets sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
            Sheet sheet = new Sheet()
            {
                Id = workbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "TestSheet2"
            };
            sheets.Append(sheet);

            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
            DataTable dt = Data.SampleData();

            // 生成标题行
            Row headerRow = new Row();
            foreach (DataColumn column in dt.Columns)
            {
                Cell headerCell = new Cell()
                {
                    CellValue = new CellValue(column.ColumnName),
                    DataType = CellValues.String
                };
                headerRow.Append(headerCell);
            }
            sheetData.Append(headerRow);

            // 生成数据行
            foreach (DataRow rowData in dt.Rows)
            {
                Row dataRow = new Row();
                foreach (var cellValue in rowData.ItemArray)
                {
                    Cell dataCell = new Cell()
                    {
                        CellValue = new CellValue(cellValue.ToString()),
                        DataType = CellValues.String
                    };
                    dataRow.Append(dataCell);
                }
                sheetData.Append(dataRow);
            }

            workbookPart.Workbook.Save();
        } // using语句自动释放资源并关闭文档，无需手动调用Close()
    }
}

• 添加标题行生成逻辑：单独遍历DataTable列集合，创建列名单元格并组成标题行，优先插入到SheetData中
• 简化数据行循环：移除多余嵌套循环，直接遍历行数据生成单元格，避免重复创建
• 使用using管理文档对象：自动处理资源释放，无需手动关闭文档
• 移除无效的Columns代码：消除无意义的操作

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