How to remove a Pivot Table without losing the values shown in the cells?

Aspose.Cells Product Family
1

Is this something that can be done?

Formulas in a worksheet can be removed and values put in place. How can this be done for PivotTables?

2

@jose.cornado,
There is no direct way to achieve this using MS Excel and same is the case with Aspose.Cells. However we can achieve this by following the steps below:

  1. Open the workbook
  2. Get the pivot table
  3. Fetch the cells area which is covered by the pivot table
  4. Create a source range based on this area
  5. Create another destination range where data is to be copied
  6. Copy the source range data at destination
  7. Remove the pivot table
  8. Move the copied data at the same place where pivot table was present

Here is a sample code to perform this task:

Workbook workbook = new Workbook(@"C:\test\book1.xlsx");
Worksheet worksheet =  workbook.Worksheets["Sheet2"];
Cells cells = worksheet.Cells;
PivotTable pivotTable = worksheet.PivotTables[0];
var r1 = pivotTable.TableRange1;
Aspose.Cells.Range source = cells.CreateRange(r1.StartRow, r1.StartColumn, r1.EndRow - r1.StartRow + 1, r1.EndColumn - r1.StartColumn + 1);
Aspose.Cells.Range dest = cells.CreateRange("D3","E10");
dest.CopyData(source);
worksheet.PivotTables.Remove(pivotTable);
dest.MoveTo(2, 0);
workbook.Save(@"c:\test\book2.xlsx");

Here are the template files:
book2.xlsx.zip (8.3 KB)
Book1.xlsx.zip (10.3 KB)

Here is the comparison of input and output files:
PivotTable.jpg (297.7 KB)

3

Yes, that was our suspicion. Thanks!

Best regards,

Jose

4

@jose.cornado,

You are welcome.