Copy entire Pivot Table values and style to another sheet

[trozzi1957]

Hi-

I'd like to copy an entire pivot table... values and style to another worksheet. How do I do that.

Thanks
-Thomas

[Nina.Tang]

Hi Thomas,

Thanks for your inquiry.
You can directly achieve your need by copying a worksheet that includes pivot tables. Please refer to the code below. If this does not meet your requirement, please provide more details such as your input and output files. Thanks in advance.

Code: Select all

// Create a new excel document
Workbook book = new Workbook();

// Load an excel document with Pivot table from the file
book.LoadFromFile("PivotTableExample.xlsx");

// Find the Pivot Table sheet
Worksheet sheet = book.Worksheets["PivotTable"];

// Create an empty sheet
Worksheet newSheet= book.CreateEmptySheet();

// Copy the data from sheet to newSheet
newSheet.CopyFrom(sheet);

book.SaveToFile("result.xlsx", ExcelVersion.Version2013);


Sincerely,
Nina
E-iceblue support team

[trozzi1957]

Hi-

This cope copies the pivot table. I just want the pivot table values and cell styles copied.

Thanks
-Thomas

[Nina.Tang]

Hi,

Thanks for your feedback.
The following code can copy the value of a pivot table, but cannot copy the BuiltInStyle of a pivot table. Could you please verify if this meets your requirements? If this is indeed what you need, we will proceed to consult with our development team regarding the possibility of also copying the pivot table's styles.

Code: Select all

// Create a new instance of Workbook
Workbook workbook = new Workbook();

// Load an Excel file from the specified path
workbook.LoadFromFile("PivotTable.xlsx");

// Get the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];

// Create an empty worksheet
Worksheet copysheet = workbook.CreateEmptySheet();

// Retrieve the first PivotTable from the worksheet
PivotTable table = sheet.PivotTables[0] as PivotTable;

// Get the range location of the pivot table
CellRange ranges = table.Location;

// Copy the pivot table value to the new worksheet
sheet.Copy(ranges, copysheet,1,1,true);

// Save to file
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2016);


Sincerely,
Nina
E-iceblue support team