c# - OpenXML 动态创建 "Table/Pivot Table"

Question

我正在创建一个在内存中生成 excel 的示例程序。该程序已经生成了 excel，但是我无法使表格正常工作...

程序:

public class Program
{
    static void Main(string[] args)
    {
        CreateCurrentAccount();
    }

    static void CreateCurrentAccount()
    {
        byte[] _buffer = ExcelGenerator.CurrentAccount.GetExcel();
        File.WriteAllBytes("CurrentAccount.xlsx", _buffer);
    }
}

ExcelGenerator 类:

public class ExcelGenerator
{
    static uint? _generalStyle = 0;
    static uint? _dateStyle = 1;
    static uint? _currencyStyle = 2;
    static uint? _percentageStyle = 3;
    static int maxStyleSheetName = 31;

    public static class CurrentAccount
    {
        static uint? _headerStyle = 4;
        static uint? _tableHeaderStyle = 5;
        static uint? _totalHeaderStyle = 6;

        public static byte[] GetExcel()
        {
            using (MemoryStream ms = new MemoryStream())
            {
                SpreadsheetDocument spreadsheet;
                Worksheet worksheet;

                spreadsheet = OpenXML.CreateWorkbook(ms);

                // styles
                OpenXML.AddBasicStyles(spreadsheet);
                AddAdvancedStyles(spreadsheet);

                string styleSheetName = ("Conta Corrente" /* + customer.Name*/);
                OpenXML.AddWorksheet(spreadsheet, styleSheetName.Length > maxStyleSheetName ? styleSheetName.Substring(0, maxStyleSheetName) : styleSheetName);
                worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;

                // Sheet 1 Header
                OpenXML.MergeTwoCells(worksheet, "A1", "F1");
                OpenXML.SetCellValue(spreadsheet, worksheet, 1, 1, CellValues.String, "Conta Corrente", _headerStyle, true);

                // Header Table
                OpenXML.SetCellValue(spreadsheet, worksheet, 1, 3, CellValues.String, "Tipo de Doc.", _tableHeaderStyle, true);
                OpenXML.SetCellValue(spreadsheet, worksheet, 2, 3, CellValues.String, "Nr. Doc.", _tableHeaderStyle, true);
                OpenXML.SetCellValue(spreadsheet, worksheet, 3, 3, CellValues.String, "Valor Doc.", _tableHeaderStyle, true);
                OpenXML.SetCellValue(spreadsheet, worksheet, 4, 3, CellValues.String, "Valor Pendente", _tableHeaderStyle, true);
                OpenXML.SetCellValue(spreadsheet, worksheet, 5, 3, CellValues.String, "Data Registo", _tableHeaderStyle, true);
                OpenXML.SetCellValue(spreadsheet, worksheet, 6, 3, CellValues.String, "Data Vencimento", _tableHeaderStyle, true);

                // Table
                var currentAccountData = GetData();

                uint rowIdx = 4;
                foreach (var item in currentAccountData)
                {
                    OpenXML.SetCellValue(spreadsheet, worksheet, 1, rowIdx, CellValues.String, item.TypeDoc, _generalStyle, true);
                    OpenXML.SetCellValue(spreadsheet, worksheet, 2, rowIdx, CellValues.String, item.NrDoc, _generalStyle, true);
                    if (item.DocValue != null)
                        OpenXML.SetCellValue(spreadsheet, worksheet, 3, rowIdx, Convert.ToDouble(item.DocValue), _currencyStyle, true);
                    if (item.PendingValue != null)
                        OpenXML.SetCellValue(spreadsheet, worksheet, 4, rowIdx, Convert.ToDouble(item.PendingValue), _currencyStyle, true);
                    if (item.RecordDate != null)
                        OpenXML.SetCellValue(spreadsheet, worksheet, 5, rowIdx, item.RecordDate, _dateStyle, true);
                    if (item.SalaryDate != null)
                        OpenXML.SetCellValue(spreadsheet, worksheet, 6, rowIdx, item.SalaryDate, _dateStyle, true);

                    rowIdx++;
                }

                TableParts tableParts1 = new TableParts() { Count = (uint)1U };
                TablePart tablePart1 = new TablePart() { Id = "rId1" };
                tableParts1.Append(tablePart1);
                worksheet.Append(tableParts1);

                OpenXML.CreateTable(spreadsheet, worksheet, "table1", 3U, 1U, (uint)currentAccountData.Count + 3, 6U);
                TableDefinitionPart tableDefinitionPart1 = worksheet.WorksheetPart.TableDefinitionParts.FirstOrDefault<TableDefinitionPart>();

                // Total
                OpenXML.SetCellValue(spreadsheet, worksheet, 8, 2, CellValues.String, "Total Documento", _totalHeaderStyle, true);
                OpenXML.SetCellFormula(spreadsheet, worksheet, 9, 2, "=SUM(C4:C1048576)", 2, true);

                OpenXML.SetCellValue(spreadsheet, worksheet, 8, 3, CellValues.String, "Total Pendente", _totalHeaderStyle, true);
                OpenXML.SetCellFormula(spreadsheet, worksheet, 9, 3, "=SUM(D4:D1048576)", 2, true);

                // Last Sync
                var lastSync = DateTime.Now;
                OpenXML.SetCellValue(spreadsheet, worksheet, 8, 1, CellValues.String, "Última Sincronização", _totalHeaderStyle, true);
                OpenXML.SetCellValue(spreadsheet, worksheet, 9, 1, lastSync, _dateStyle, true);

                // Set column widths
                OpenXML.SetColumnWidth(worksheet, 1, 22);
                OpenXML.SetColumnWidth(worksheet, 2, 22);
                OpenXML.SetColumnWidth(worksheet, 3, 22);
                OpenXML.SetColumnWidth(worksheet, 4, 22);
                OpenXML.SetColumnWidth(worksheet, 5, 22);
                OpenXML.SetColumnWidth(worksheet, 6, 22);

                OpenXML.SetColumnWidth(worksheet, 8, 22);
                OpenXML.SetColumnWidth(worksheet, 9, 22);

                worksheet.Save();
                spreadsheet.Close();

                return ms.ToArray();
            }
        }
    }
}

创建表格方法

public static void CreateTable(SpreadsheetDocument document, Worksheet worksheet, string TableName, uint rowStart, uint columnStart, uint rowEnd, uint columnEnd)
    {
        string cellAddressStart = ColumnNameFromIndex(columnStart) + rowStart;
        string cellAddressEnd = ColumnNameFromIndex(columnEnd) + rowEnd;
        string tableAddress = cellAddressStart + ":" + cellAddressEnd;

        TableDefinitionPart tableDefinitionPart1 = worksheet.WorksheetPart.AddNewPart<TableDefinitionPart>("rId1");

        Table table1 = new Table() { Id = (UInt32Value)2U, Name = TableName, DisplayName = TableName, Reference = tableAddress, TotalsRowShown = false };
        AutoFilter autoFilter1 = new AutoFilter() { Reference = tableAddress };

        uint cellRangeCount = columnEnd - columnStart;
        TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)(cellRangeCount) };
        uint cellCount = 1;
        for (uint idxColumns = columnStart; idxColumns < columnEnd; idxColumns++)
        {
            CellValue cell = GetCellValue(document, worksheet, idxColumns, rowStart);
            TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)cellCount, Name = cell.InnerText };
            tableColumns1.Append(tableColumn1);
            cellCount++;
        }

        TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = "TableStyleMedium2", 
                                                                ShowFirstColumn = false, 
                                                                ShowLastColumn = false, 
                                                                ShowRowStripes = true, 
                                                                ShowColumnStripes = false };

        table1.Append(autoFilter1);
        table1.Append(tableColumns1);
        table1.Append(tableStyleInfo1);

        tableDefinitionPart1.Table = table1;
    }

我正在添加单元格(行)

var currentAccountData = GetData();

uint rowIdx = 4;
foreach (var item in currentAccountData)
{
    OpenXML.SetCellValue(spreadsheet, worksheet, 1, rowIdx, CellValues.String, item.TypeDoc, _generalStyle, true);
    OpenXML.SetCellValue(spreadsheet, worksheet, 2, rowIdx, CellValues.String, item.NrDoc, _generalStyle, true);
    if (item.DocValue != null)
        OpenXML.SetCellValue(spreadsheet, worksheet, 3, rowIdx, Convert.ToDouble(item.DocValue), _currencyStyle, true);
    if (item.PendingValue != null)
        OpenXML.SetCellValue(spreadsheet, worksheet, 4, rowIdx, Convert.ToDouble(item.PendingValue), _currencyStyle, true);
    if (item.RecordDate != null)
        OpenXML.SetCellValue(spreadsheet, worksheet, 5, rowIdx, item.RecordDate, _dateStyle, true);
    if (item.SalaryDate != null)
        OpenXML.SetCellValue(spreadsheet, worksheet, 6, rowIdx, item.SalaryDate, _dateStyle, true);

    rowIdx++;
}

然后创建表格部分

TableParts tableParts1 = new TableParts() { Count = (uint)1U };
TablePart tablePart1 = new TablePart() { Id = "rId1" };
tableParts1.Append(tablePart1);
worksheet.Append(tableParts1);

之后，我使用 OpenXML.CreateTable 方法

OpenXML.CreateTable(spreadsheet, worksheet, "table1", 3U, 1U, (uint)currentAccountData.Count + 3, 6U);

我做错了什么？

如果您需要更多代码，我会上传应用程序。谢谢。

编辑:

当我打开 excel 时，它显示:

Removed Part: /xl/tables/table.xml part with XML error. (Table) A document must contain exactly one root element. Line 1, column 0.

Answer 1

what am I doing wrong?

我觉得你的调试方法不对。我建议以下之一:

• 一个。将生成的 xml 与手工制作的 xlsx 文件进行比较。
• b。从代码开始，由 OpenXML 生产力工具生成(因此它在您的应用程序中生成静态表)并修改/重构它逐步使用来自您应用程序的动态数据并匹配您的编码标准 - 这样您就可以发现您的在一次迭代中出错，或者只是得到一个干净的工作解决方案