EPPlus透视表 - 折叠整个领域 [英] EPPlus Pivot Table - Collapse entire field
问题描述
有谁知道如何做到这一点?
Does anyone know how to do this?
我使用EPPlus在.Net和已创建了2行字段和数据透视表中的汇总数据字段:
I'm using EPPlus in .Net and have created a pivot table with 2 row fields and one summary datafield:
Dim Pivot As OfficeOpenXml.Table.PivotTable.ExcelPivotTable
Pivot = wksPivot.PivotTables.Add(wksPivot.Cells("A1"), Datarange, "pName")
Pivot.RowFields.Add(Pivot.Fields("Fld1")).Sort = Table.PivotTable.eSortType.Ascending
Pivot.RowFields.Add(Pivot.Fields("Fld2")).Sort = Table.PivotTable.eSortType.Ascending
Dim dtaFld As OfficeOpenXml.Table.PivotTable.ExcelPivotTableDataField
dtaFld = Pivot.DataFields.Add(Pivot.Fields("XYZ"))
dtaFld.Function = Table.PivotTable.DataFieldFunctions.Sum
一切都很正常,但我想有数据透视表开始为当用户打开工作簿(在Excel中倒塌,当你创建数据透视表,您可以用鼠标右键单击数据元素,并选择展开/收起>关闭整场
Everything works great, but I want to have the Pivot Table start off as collapsed when the user opens the workbook (In excel, when you're creating the pivot table, you can right-click in the data element and select "Expand / Collapse" > "Collapse Entire Field"
热,我可以做到这一点通过code? (我愿意用直接的OpenXML如果EPPlus不支持此尚未...)
Hot can I do this via code?? (And I'm willing to use direct OpenXML if EPPlus doesn't support this yet...)
同时,有没有办法删除从工作簿,以便数据透视表仍然有效的原始数据?我试过,当我打开工作簿,我的数据透视表是空白的? - 我现在的逻辑,使我这个问题 ...有什么想法? ?
ALSO, is there a way to delete out the Raw data from the workbook so that the pivot table still works? i've tried and when I open the workbook, my pivot table is blank? - My current logic has led me to this question... Any thoughts??
(我只知道我在VB写了这个问题,但我加了两个 C#
&安培; VB
标签这个问题 - 我很舒服与code。在任何一种语言 - 谢谢!)
(I do know I wrote this question in VB. but I added both the C#
& VB
tags to this question - I'm comfortable with code in either language - Thanks!!)
推荐答案
可以使它XLSM,并添加VBA吧。这可能是最糟糕的答案,这个解决方案,但高校档案完全崩溃。我提供一个工作的例子,只要复制过去到一个新的控制台应用程序。添加epplus依赖,F5。
Could make it xlsm and add vba to it. This is probably the worst answer to this solution but it achives full collapse. I have provided a working example, just copy past into a new console app. add the epplus dependency, "F5".
修改/从 HTTP采取://epplus.$c$ cplex.com/SourceControl/latest#SampleApp/Sample15.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml.Table;
using OfficeOpenXml.Table.PivotTable;
using OfficeOpenXml;
using System.IO;
namespace pTable
{
class Program
{
static void Main(string[] args)
{
//ExcelPackage _pck = new ExcelPackage();
Directory.CreateDirectory(string.Format("Test"));
//ExcelPackage _pck = new ExcelPackage(new FileInfo("Test\\Worksheet.xlsx"));
ExcelPackage _pck = new ExcelPackage(new FileInfo("Test\\Worksheet.xlsm"));
var wsPivot1 = _pck.Workbook.Worksheets.Add("Rows-Data on columns");
var ws = _pck.Workbook.Worksheets.Add("Data");
ws.Cells["K1"].Value = "Item";
ws.Cells["L1"].Value = "Category";
ws.Cells["M1"].Value = "Stock";
ws.Cells["N1"].Value = "Price";
ws.Cells["O1"].Value = "Date for grouping";
ws.Cells["K2"].Value = "Crowbar";
ws.Cells["L2"].Value = "Hardware";
ws.Cells["M2"].Value = 12;
ws.Cells["N2"].Value = 85.2;
ws.Cells["O2"].Value = new DateTime(2010, 1, 31);
ws.Cells["K3"].Value = "Crowbar";
ws.Cells["L3"].Value = "Hardware";
ws.Cells["M3"].Value = 15;
ws.Cells["N3"].Value = 12.2;
ws.Cells["O3"].Value = new DateTime(2010, 2, 28);
ws.Cells["K4"].Value = "Hammer";
ws.Cells["L4"].Value = "Hardware";
ws.Cells["M4"].Value = 550;
ws.Cells["N4"].Value = 72.7;
ws.Cells["O4"].Value = new DateTime(2010, 3, 31);
ws.Cells["K5"].Value = "Hammer";
ws.Cells["L5"].Value = "Hardware";
ws.Cells["M5"].Value = 120;
ws.Cells["N5"].Value = 11.3;
ws.Cells["O5"].Value = new DateTime(2010, 4, 30);
ws.Cells["K6"].Value = "Crowbar";
ws.Cells["L6"].Value = "Hardware";
ws.Cells["M6"].Value = 120;
ws.Cells["N6"].Value = 173.2;
ws.Cells["O6"].Value = new DateTime(2010, 5, 31);
ws.Cells["K7"].Value = "Hammer";
ws.Cells["L7"].Value = "Hardware";
ws.Cells["M7"].Value = 1;
ws.Cells["N7"].Value = 4.2;
ws.Cells["O7"].Value = new DateTime(2010, 6, 30);
ws.Cells["K8"].Value = "Saw";
ws.Cells["L8"].Value = "Hardware";
ws.Cells["M8"].Value = 4;
ws.Cells["N8"].Value = 33.12;
ws.Cells["O8"].Value = new DateTime(2010, 6, 28);
ws.Cells["K9"].Value = "Screwdriver";
ws.Cells["L9"].Value = "Hardware";
ws.Cells["M9"].Value = 1200;
ws.Cells["N9"].Value = 45.2;
ws.Cells["O9"].Value = new DateTime(2010, 8, 31);
ws.Cells["K10"].Value = "Apple";
ws.Cells["L10"].Value = "Groceries";
ws.Cells["M10"].Value = 807;
ws.Cells["N10"].Value = 1.2;
ws.Cells["O10"].Value = new DateTime(2010, 9, 30);
ws.Cells["K11"].Value = "Butter";
ws.Cells["L11"].Value = "Groceries";
ws.Cells["M11"].Value = 52;
ws.Cells["N11"].Value = 7.2;
ws.Cells["O11"].Value = new DateTime(2010, 10, 31);
ws.Cells["O2:O11"].Style.Numberformat.Format = "yyyy-MM-dd";
var pt = wsPivot1.PivotTables.Add(wsPivot1.Cells["A1"], ws.Cells["K1:N11"], "Pivottable1");
pt.Compact = true;
pt.CompactData = true;
pt.GrandTotalCaption = "Total amount";
pt.RowFields.Add(pt.Fields[1]);
pt.RowFields.Add(pt.Fields[0]);
pt.DataFields.Add(pt.Fields[3]);
pt.DataFields.Add(pt.Fields[2]);
pt.DataFields[0].Function = DataFieldFunctions.Product;
pt.DataOnRows = false;
_pck.Workbook.CreateVBAProject();
var sb = new StringBuilder();
sb.AppendLine("Private Sub Workbook_Open()");
sb.AppendLine(" Range(\"A1\").Select");
sb.AppendLine(" ActiveSheet.PivotTables(\"Pivottable1\").PivotFields(\"Category\").PivotItems(\"Hardware\").ShowDetail = False");
sb.AppendLine("End Sub");
_pck.Workbook.CodeModule.Code = sb.ToString();
_pck.Save();
}
}
}
这篇关于EPPlus透视表 - 折叠整个领域的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!