Pivot table data

Aspose.Cells Product Family
1

Hello,
How to change data source of existing pivot table. main purpose of this code is to update the data source of pivot table because if no. of rows of data is increased then it would won’t come in the pivot table so that’s why i have to change the data source of pivot table again and as the data source is updated.

actually below is my code please check and give me your suggestions.

path = r"E:\sample\Pivot1.xlsx"
path2 = r"E:\sample\Pivot2.xlsx"
wb = Workbook(path)

ws = wb.getWorksheets()

sheet_ds = wb.getWorksheets().get(0)
sheet_pt = wb.getWorksheets().get(1)

pts = sheet_pt.getPivotTables().get(0)

sheet_ds_range = sheet_ds.getCells().getMaxDisplayRange().getRefersTo()
print(sheet_ds_range)
pts.changeDataSource([sheet_ds_range])

pts.refreshData()

pts.calculateData()

wb.save(path2)

so i am using above code but i am getting error of java exception and error is “com.aspose.cells.CellsException: The PivotTable field name is invalid.”

and i am getting error on refreshData() so if you can help me with this, that what i am doing wrong it will be great.
Thanks

2

@asposeLegit,

Thanks for the details.

The error denotes that some pivot field name is incorrect, so you may evaluate the first row (which should contain the pivot table field names/labels) if everything is ok or not. Moreover, you may try creating pivot table based on your changed data source in MS Excel manually if you are successful. If you still could not evaluate your issue, could you please zip and attach your source Excel file (containing the pivot table). We will check your issue soon.

3

Thanks @amjad.sahi

actually i found the issue my data source file actually starting from second row and because i am using “getMaxdisplayRange” so it is taking datasource from 1st row due to which field name is blank due to which i think i am getting this error. so can you suggest me something using which i should get the range of the data from the row which is starting from, till the row where it is ending and as you said i have attached my zip file for your reference.
Thanks

trial.7z (12.5 KB)

4

@asposeLegit,

Thanks for the template file.

You are right as I checked using your file, the data starts from the second row. That was the reason you are getting this exception.

You may make sure of getMinDataRow and getMinDataColumn of Cells and other relevant attributes to know about the source range in part wise. Get the data sheet name and finally gather all the parts in the required/correct format (i.e.,"=Sheet1!$A$2:$D$8") to specify pivot table’s changed source range. See the following sample code segment for your reference.

.....
pts = sheet_pt.getPivotTables().get(0)

sheet_ds_stRow = sheet_ds.getCells().getMinDataRow()
sheet_ds_stColumn = sheet_ds.getCells().getMinDataColumn()
sheet_ds_endRow = sheet_ds.getCells().getMaxDataRow()
sheet_ds_endColumn = sheet_ds.getCells().getMaxDataColumn()

sheet_ds_name = sheet_ds.getName()
st_Range_Cell = CellsHelper.cellIndexToName(sheet_ds_stRow, sheet_ds_stColumn)
end_Range_Cell = CellsHelper.cellIndexToName(sheet_ds_endRow, sheet_ds_endColumn)

print(sheet_ds_name)
print(st_Range_Cell)
print(end_Range_Cell)

sheet_ds_range = ..................

Hope, this helps a bit.

5

Thanks @amjad.sahi your solution works for me.

Just one more thing, is their any way using which i can copy the value of pivot table and paste it in excel as “pastevalue” means only value of pivot table and pivot table should be removed and only value should be shown.

and again thank you so much for your help.

6

@asposeLegit,
For your needs, you can copy all the data in the pivot table range first, and then delete the pivot table.
The attachment is the original document and the result document. result.zip (15.5 KB)
Please refer to the following code:

Workbook workbook = new Workbook(filePath + “a.xlsx”);
PivotTableCollection pivots = workbook.getWorksheets().get(0).getPivotTables();
PivotTable pivot = pivots.get(0);
CellArea range = pivot.getTableRange2();
Cells cells = workbook.getWorksheets().get(0).getCells();
Range source = cells.createRange(range.StartRow, range.StartColumn,
range.EndRow - range.StartRow + 1, range.EndColumn - range.StartColumn + 1);
int newRow = range.EndRow + 5;
Range dest = cells.createRange(newRow, range.StartColumn,
range.EndRow - range.StartRow + 1, range.EndColumn - range.StartColumn + 1);
PasteOptions options = new PasteOptions();
options.setPasteType(PasteType.VALUES);
dest.copy(source, options);
pivots.remove(pivot);
workbook.save(filePath + “out_java.xlsx”);

7

Thanks @John.He

Your solution works but is their any way using which we can paste the value of pivot table on the same range where pivot table is present?

Thanks

8

@asposeLegit,
I’m glad your needs have been solved. You cannot paste values where the pivot table covers. If you operate in MS-Excel, you will get an error prompt. Aspose.Cells follows the rules of Excel. If you have any questions, please feel free to contact us.

9

Thanks for your reply @John.He or @amjad.sahi

But the problem is in my sheet there are multiple pivot table is their and below that pivot table there is some data basically i won’t be able to paste my pivot table data below or somewhere else due to which i was asking you this and also i thought to add new sheet and paste that pivot table on that but in this case if i do this then if pivot table is present on 7 sheets then 7 more sheets need to be added and due to which my file become heavy and also processing time of this code will also increase and in some cases of my file there are more than 20 to 22 sheets in excel and if i do this then it would take much more time. so is their any other way according to you doing that i will able to do this basically pasting data of excel and at same place.

Thanks again for the help

10

@asposeLegit,

May be you could try to copy your pivot table range values to some other area/sheet temporarily (you may remove the pasted area/sheet(s) later on in code) and then copy/paste values back to your original position.