Calculate ("refresh data") pivot table and save as PDF

Aspose.Cells Product Family
1

Hello,

For a project, we have the desire to do the following:

  1. Generate an XLSX document with one sheet with a pivot table and a separate sheet with the data that the pivot relies on (so actually a template)
  2. Run a process in our application to populate the data sheet;
  3. Run the pivot table so it is calculated based on the just-filled sheet
  4. Save the result sheet PDF

Looking at the website, it looks like this is possible when combining Create Pivot Tables and Pivot Charts|Documentation and Pdf|Documentation , but can anyone confirm me if this is possible in the way described while using Aspose Cells?

Thanks a lot

2

@joostvandermaarel,

Please give a try to the following sample code which performs all the desired tasks.

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the newly added worksheet
Worksheet sheet1 = workbook.Worksheets[0];

Cells cells = sheet1.Cells;

// Setting the value to the cells
Cell cell = cells["A1"];
cell.PutValue("Sport");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Sales");

cell = cells["A2"];
cell.PutValue("Golf");
cell = cells["A3"];
cell.PutValue("Golf");
cell = cells["A4"];
cell.PutValue("Tennis");
cell = cells["A5"];
cell.PutValue("Tennis");
cell = cells["A6"];
cell.PutValue("Tennis");
cell = cells["A7"];
cell.PutValue("Tennis");
cell = cells["A8"];
cell.PutValue("Golf");

cell = cells["B2"];
cell.PutValue("Qtr3");
cell = cells["B3"];
cell.PutValue("Qtr4");
cell = cells["B4"];
cell.PutValue("Qtr3");
cell = cells["B5"];
cell.PutValue("Qtr4");
cell = cells["B6"];
cell.PutValue("Qtr3");
cell = cells["B7"];
cell.PutValue("Qtr4");
cell = cells["B8"];
cell.PutValue("Qtr3");

cell = cells["C2"];
cell.PutValue(1500);
cell = cells["C3"];
cell.PutValue(2000);
cell = cells["C4"];
cell.PutValue(600);
cell = cells["C5"];
cell.PutValue(1500);
cell = cells["C6"];
cell.PutValue(4070);
cell = cells["C7"];
cell.PutValue(5000);
cell = cells["C8"];
cell.PutValue(6430);
workbook.Worksheets.Add("Sheet2");
Worksheet sheet2 = workbook.Worksheets["Sheet2"];
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;

// Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Sheet1!A1:C8", "E3", "PivotTable2");

// Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

// Unshowing grand totals for rows.
pivotTable.RowGrand = false;

// Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

// Draging the second field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1);

// Draging the third field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);

pivotTable.CalculateData();

// Save output file to PDF
Aspose.Cells.PdfSaveOptions pdfSaveOptions = new Aspose.Cells.PdfSaveOptions(Aspose.Cells.SaveFormat.Pdf);
pdfSaveOptions.CalculateFormula = true;
workbook.Save("output.pdf",pdfSaveOptions);

Output file is attached here for your reference.
output.pdf (19.4 KB)