EPPlus数据透视表-将值复制到新表 [英] EPPlus Pivot Table - Copy Values to New Sheet

问题描述

我已经使用EPPlus成功创建了数据透视表。数据透视表与原始数据位于单独的工作表中。我想将数据透视表数据复制到新的第三张表中，但仅将值而不是数据透视表定义复制到。 EPPlus是否支持此功能？

I've successfully created a pivot table using EPPlus. The pivot table resides in a seperate sheet from the raw data. I would like to copy the pivot table data to a new, third, sheet, but just the values, not the pivot defintions. Does EPPlus support this?

推荐答案

您只需通过缓存定义复制数据源范围：

You can just copy the datasource range via Cache Definition:

public void PivotDataCopy()
{
    const string FORMATCURRENCY = "#,###;[Red](#,###)";

    var file = new FileInfo(@"c:\temp\temp.xlsx");
    if (file.Exists)
        file.Delete();

    var pck = new ExcelPackage(file);
    var workbook = pck.Workbook;
    var dataworksheet = workbook.Worksheets.Add("datasheet");

    //The data
    dataworksheet.Cells["A20"].Value = "Col1";
    dataworksheet.Cells["A21"].Value = "sdf";
    dataworksheet.Cells["A22"].Value = "wer";
    dataworksheet.Cells["A23"].Value = "ghgh";
    dataworksheet.Cells["A24"].Value = "sdf";
    dataworksheet.Cells["A25"].Value = "wer";

    dataworksheet.Cells["B20"].Value = "Col2";
    dataworksheet.Cells["B21"].Value = "Group A";
    dataworksheet.Cells["B22"].Value = "Group B";
    dataworksheet.Cells["B23"].Value = "Group A";
    dataworksheet.Cells["B24"].Value = "Group C";
    dataworksheet.Cells["B25"].Value = "Group A";

    dataworksheet.Cells["C20"].Value = "Col3";
    dataworksheet.Cells["C21"].Value = 453.5;
    dataworksheet.Cells["C22"].Value = 634.5;
    dataworksheet.Cells["C23"].Value = 274.5;
    dataworksheet.Cells["C24"].Value = 453.5;
    dataworksheet.Cells["C25"].Value = 634.5;

    dataworksheet.Cells["D20"].Value = "Col4";
    dataworksheet.Cells["D21"].Value = 686468;
    dataworksheet.Cells["D22"].Value = 996440;
    dataworksheet.Cells["D23"].Value = 185780;
    dataworksheet.Cells["D24"].Value = 686468;
    dataworksheet.Cells["D25"].Value = 996440;

    //The pivot table
    var pivotworksheet = workbook.Worksheets.Add("pivotsheet");
    var pivotTable = pivotworksheet.PivotTables.Add(pivotworksheet.Cells["A1"], dataworksheet.Cells["A20:D29"], "test");

    //The label row field
    pivotTable.RowFields.Add(pivotTable.Fields["Col1"]);
    pivotTable.DataOnRows = false;

    //The data fields
    var field = pivotTable.DataFields.Add(pivotTable.Fields["Col3"]);
    field.Name = "Sum of Col2";
    field.Function = DataFieldFunctions.Sum;
    field.Format = FORMATCURRENCY;

    field = pivotTable.DataFields.Add(pivotTable.Fields["Col4"]);
    field.Name = "Sum of Col3";
    field.Function = DataFieldFunctions.Sum;
    field.Format = FORMATCURRENCY;

    //Get the pivot table data source
    var newworksheet = workbook.Worksheets.Add("newworksheet");
    var pivotdatasourcerange = pivotTable.CacheDefinition.SourceRange;
    pivotdatasourcerange.Copy(newworksheet.Cells["A1"]);

    pck.Save();

}

---

编辑：使用VBA宏，然后将其重新保存为非宏XLSX：

---

Doing with a VBA macro which then resaves the sheet as a non-macro XLSX:

public void PivotDataCopy()
{
    const string FORMATCURRENCY = "#,###;[Red](#,###)";

    var file = new FileInfo(@"c:\temp\temp.xlsm");
    if (file.Exists)
        file.Delete();

    var pck = new ExcelPackage(file);
    var workbook = pck.Workbook;
    var dataworksheet = workbook.Worksheets.Add("datasheet");

    //The data
    dataworksheet.Cells["A20"].Value = "Col1";
    dataworksheet.Cells["A21"].Value = "sdf";
    dataworksheet.Cells["A22"].Value = "wer";
    dataworksheet.Cells["A23"].Value = "ghgh";
    dataworksheet.Cells["A24"].Value = "sdf";
    dataworksheet.Cells["A25"].Value = "wer";

    dataworksheet.Cells["B20"].Value = "Col2";
    dataworksheet.Cells["B21"].Value = "Group A";
    dataworksheet.Cells["B22"].Value = "Group B";
    dataworksheet.Cells["B23"].Value = "Group A";
    dataworksheet.Cells["B24"].Value = "Group C";
    dataworksheet.Cells["B25"].Value = "Group A";

    dataworksheet.Cells["C20"].Value = "Col3";
    dataworksheet.Cells["C21"].Value = 453.5;
    dataworksheet.Cells["C22"].Value = 634.5;
    dataworksheet.Cells["C23"].Value = 274.5;
    dataworksheet.Cells["C24"].Value = 453.5;
    dataworksheet.Cells["C25"].Value = 634.5;

    dataworksheet.Cells["D20"].Value = "Col4";
    dataworksheet.Cells["D21"].Value = 686468;
    dataworksheet.Cells["D22"].Value = 996440;
    dataworksheet.Cells["D23"].Value = 185780;
    dataworksheet.Cells["D24"].Value = 686468;
    dataworksheet.Cells["D25"].Value = 996440;

    //The pivot table
    var pivotworksheet = workbook.Worksheets.Add("pivotsheet");
    var pivotTable = pivotworksheet.PivotTables.Add(pivotworksheet.Cells["A1"], dataworksheet.Cells["A20:D29"], "test");

    //The label row field
    pivotTable.RowFields.Add(pivotTable.Fields["Col1"]);
    pivotTable.DataOnRows = false;

    //The data fields
    var field = pivotTable.DataFields.Add(pivotTable.Fields["Col3"]);
    field.Name = "Sum of Col2";
    field.Function = DataFieldFunctions.Sum;
    field.Format = FORMATCURRENCY;

    field = pivotTable.DataFields.Add(pivotTable.Fields["Col4"]);
    field.Name = "Sum of Col3";
    field.Function = DataFieldFunctions.Sum;
    field.Format = FORMATCURRENCY;

    //add the macro to copy the table data on open
    workbook.Worksheets.Add("newworksheet");

    var sb = new StringBuilder();
    sb.AppendLine("Private Sub Workbook_SheetCalculate(ByVal Sh As Object)");
    sb.AppendLine("    Sheets(\"pivotsheet\").Cells.Copy");
    sb.AppendLine("    Sheets(\"newworksheet\").Range(\"A1\").PasteSpecial Paste:=xlPasteValues");
    sb.AppendLine("    Selection.Clear");
    sb.AppendLine("    Application.DisplayAlerts = False");
    sb.AppendLine(String.Format("    ActiveWorkbook.SaveAs \"{0}\", xlOpenXMLWorkbook", file.FullName.Replace("xlsm", "xlsx")));
    sb.AppendLine("    Application.DisplayAlerts = True");

    sb.AppendLine("End Sub");
    pck.Workbook.CreateVBAProject();
    pck.Workbook.CodeModule.Code = sb.ToString();

    pck.Save();
}

这篇关于EPPlus数据透视表-将值复制到新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！