Copy values from pivot table to another tab

[trozzi1957]

Hi-

I want to copy the values from the last 2 columns of a pivot table (The Grand totals) to a new tab. Values ONLY.

Thanks
-Tom

[amy.zhao]

Hello Tom,

Thank you for your inquiry.

Are you asking to copy the data from the last two columns of a PivotTable to a new sheet? If so, please refer to the example code below:

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromFile(path+ "PivotTableExample.xlsx");

Worksheet sheet = workbook.Worksheets["PivotTable"];
Worksheet newSheet = workbook.Worksheets.Add("New Sheet");

XlsPivotTable pt = (XlsPivotTable)sheet.PivotTables[0];

CellRange cr  = pt.Location;
int firstRow = cr.Row;
int lastRow = cr.LastRow;
int lastColumn = cr.LastColumn;

CellRange cellRange = sheet.Range[firstRow,lastColumn-1,lastRow,lastColumn];

sheet.Copy(cellRange, newSheet, 1, 1, true, true);

workbook.SaveToFile(path+"result.xlsx",ExcelVersion.Version2016);

workbook.Dispose();


If I have misunderstood your request, please provide a more detailed description. Thank you for your assistance.

Sincerely,
Amy
E-iceblue support team

[trozzi1957]

Hi-

Did you try this against a pivot table on the worksheet?

-Tom

[amy.zhao]

Hi Tom,

Thank you for your response.

I created the above demo using the code in a worksheet that contains a pivot table. May I ask if this meets your requirements? If not, could you please provide detailed information about the effect you desire? Thank you!

Sincerely,
Amy
E-iceblue support team