c# - 在 C# 中使用 OpenXML Power Tool 数据透视表

Question

我正在尝试使用 C# OpenXML Power 工具以编程方式生成数据透视表。我正在尝试将原始数据导出到工作表 1 和工作表 1 上的数据透视表。当我将整数列添加为数据值时，会生成具有适当值的数据透视表。但是，如果我选择一个非整数列，则生成的计数和总和始终为零。以下是它生成的 excel 文件的快照。

Pivot.txt 中的数据格式如下：Year,Quarter,Region,Category,Product,Amount 2010,Q1,North,Bicycles,B100,3448.00 2010,Q1,North,Bicycles,B200,8448.00 2010,Q1,North ,自行车,B300,922.00 2010,Q1,North,Accessories,Mirrors,303.00 2010,Q1,North,Accessories,Carriers,455.00 2010,Q1,South,Bicycles,B100,2887.00 2010,Q1,South,Bicycles,B200,4477.00 2010,Q1,南,自行车,B300,1011.00 2010,Q1,南,配件,镜子,80.00 2010,Q1,南,配件,载具,205.00

以下是我正在使用的代码：

private static void generatePivot()
        {
            int row;
            row = 1;
            int maxColumn = 1;
            using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
            {
                using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
                {
                    WorksheetPart sheet = WorksheetAccessor.AddWorksheet(doc, "Range");
                    using (System.IO.StreamReader source = new System.IO.StreamReader("../../PivotData.txt"))
                    {
                        while (!source.EndOfStream)
                        {
                            string line = source.ReadLine();
                            if (line.Length > 3)
                            {
                                string[] fields = line.Split(',');
                                int column = 1;
                                foreach (string item in fields)
                                {
                                    double num;
                                    if (double.TryParse(item, out num))
                                        WorksheetAccessor.SetCellValue(doc, sheet, row, column++, num);
                                    else
                                        WorksheetAccessor.SetCellValue(doc, sheet, row, column++, item);
                                }
                                maxColumn = column - 1;
                            }
                            row++;
                        }
                    }
                    sheet.PutXDocument();
                    WorksheetAccessor.SetRange(doc, "Sales", "Range", 1, 1, row - 1, maxColumn);
                    WorksheetPart pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
                    WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);


                    // Configure pivot table rows, columns, data and filters
                    //WorksheetAccessor.AddPivotAxis(doc, pivot, "Year", WorksheetAccessor.PivotAxis.Column);
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Category", WorksheetAccessor.PivotAxis.Row);
                    //WorksheetAccessor.AddPivotAxis(doc, pivot, "Product", WorksheetAccessor.PivotAxis.Row);
                    WorksheetAccessor.AddDataValue(doc, pivot, "Product");
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Region", WorksheetAccessor.PivotAxis.Page);
            }
            streamDoc.GetModifiedSmlDocument().SaveAs("../../NewPivot.xlsx");
        }
    }

标签： c#openxmlopenxml-powertools