使用DocumentFormat.OpenXml从Dictionary动态生成CSV数据透视表求助

核心思路 #

• 封装列索引转Excel列名的工具方法，动态计算单元格引用位置，无需硬编码
• 字典Dictionary<string, List<string>>的Key作为每行的首列值，对应的List<string>元素按顺序填充该行后续列
• 先处理表头行，再遍历字典生成所有数据行，逻辑清晰可适配大量数据批量写入

完整实现代码 #

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;

public class ExcelGenerator
{
    // 列索引转Excel列名（0->A, 1->B, 26->AA以此类推）
    private static string GetExcelColumnName(int columnIndex)
    {
        int dividend = columnIndex + 1;
        string columnName = string.Empty;
        int modulo;
        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
            dividend = (int)((dividend - modulo) / 26);
        }
        return columnName;
    }

    // 生成单元格的公共方法
    private static Cell CreateCell(int rowIndex, int columnIndex, string value)
    {
        return new Cell()
        {
            CellReference = $"{GetExcelColumnName(columnIndex)}{rowIndex + 1}",
            DataType = CellValues.String,
            CellValue = new CellValue(value)
        };
    }

    public void GeneratePivotStyleExcel(string filePath, Dictionary<string, List<string>> dataSource, List<string> headerColumns = null)
    {
        // 校验入参
        if (dataSource == null || !dataSource.Any()) return;

        // 创建Excel文档
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            // 初始化工作簿
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // 初始化工作表
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            SheetData sheetData = new SheetData();
            worksheetPart.Worksheet = new Worksheet(sheetData);

            // 关联工作表到工作簿
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "透视表"
            };
            sheets.Append(sheet);

            int currentRowIndex = 0;

            // 生成表头行（如果有传入表头）
            if (headerColumns != null && headerColumns.Any())
            {
                Row headerRow = new Row();
                for (int colIndex = 0; colIndex < headerColumns.Count; colIndex++)
                {
                    headerRow.Append(CreateCell(currentRowIndex, colIndex, headerColumns[colIndex]));
                }
                sheetData.Append(headerRow);
                currentRowIndex++;
            }

            // 遍历字典生成数据行
            foreach (var item in dataSource)
            {
                Row dataRow = new Row();
                // 首列填字典Key
                dataRow.Append(CreateCell(currentRowIndex, 0, item.Key));
                // 后续列填List中的值
                for (int colIndex = 0; colIndex < item.Value.Count; colIndex++)
                {
                    dataRow.Append(CreateCell(currentRowIndex, colIndex + 1, item.Value[colIndex] ?? string.Empty));
                }
                sheetData.Append(dataRow);
                currentRowIndex++;
            }

            // 保存修改
            workbookpart.Workbook.Save();
            spreadsheetDocument.Close();
        }
    }

    // 调用示例
    public static void Test()
    {
        var generator = new ExcelGenerator();
        // 构造测试数据源
        var testData = new Dictionary<string, List<string>>
        {
            {"分类A", new List<string>{"10", "20", "30"}},
            {"分类B", new List<string>{"15", "25", "35"}},
            {"分类C", new List<string>{"5", "18", "40"}}
        };
        // 构造表头
        var headers = new List<string> {"分类", "指标1", "指标2", "指标3"};
        generator.GeneratePivotStyleExcel(@"C:\test\pivot.xlsx", testData, headers);
    }
}

补充说明 #

如果你需要生成支持交互筛选、汇总的动态数据透视表而非静态的透视样式表格，需要额外添加透视缓存、透视表定义对象，指定行标签、列标签、值字段的映射规则即可。

内容的提问来源于stack exchange，提问作者The Inquisitive Coder