I have a template xltx with an data table and one pivot table. I want programmatically refresh the data table.
When I change the values of the data table everything worksfine, because Excel refreshes the pivot table. If I want to refresh then myself before save as pdf then because of the call of pivotTable.calculateData() my table header changes value to english names (Row Labels, Grand Total) and the totalcolumn of the datatables becomes part of the pivot table.
The renaming I could supress, if I rename these cells in template. Is there another way?
What can I do against the including of the total row in the pivot table?
I have included the aspose.cells.7.6.1. for evaluation.
By the way why does the upload not allow xltx.
Thank you for using Aspose products.
I have evaluated your presented issue a bit. I have noticed that the XLTX file included in the archive already has Pivot Filed header as “Row Labels” therefore I am unable to detect if this change was due to the refreshing of Pivot data source or not. It would be of great help if you could provide the actual input and desired output files for our review. You can manually create the desired output by converting the spreadsheet with MS Excel. The requested information will help us to evaluate the said problem on our end.
Many file formats (including html, exe, lic) are not allowed for direct upload to the forum posts, instead it is requested to zip such files and attach the archive to the post. In my opinion, one of the main reasons for not allowing such file types is the security of our community sever.
I total understand the reasons of security of the server, but wonder why it is allowed to upload xsl but not xlt.
Thats sounds like it is an localizing problem of excel itself. The pivot table cells show in Excel gui the header “Spaltenbeschreibung” and “Zeilenbeschreibung” if I change the text of the cells, these changes are obtained in the process with aspose.cells, else the become changed to “Row Labels” which Excel seems not to relocalize. I have attached a jpg perhaps this could clearify what I mean.
The main problem is the including of the total row which is not desired.
Thank you for writing back.
Yes, the change of header text could be due to the Excel locale settings. I am looking into it further to suggest you a solution. For the problem of Grand Total row, we suspect it to be the part of template (xltx) file itself, so when data is updated the Grand Total is refreshed accordingly. You can simply hide it using pivotTable.setColumnGrand(false). This routine removes the Grand Total row from the Pivot Table but unfortunately, the change does not reflect in resultant PDF file. Although if the same is saved as XLS, the Grand Total row gets removed. We have also tried by manually removing the row from the template file, update the data and render to PDF. In this case, row “Gesamt” adds to the Pivot Table in PDF. The said behavior could be a problem in Aspose.Cells API therefore an investigative ticket has been logged in our bug tracking system to probe further into this matter. The ticket Id for your future reference is CELLSJAVA-40669. We will soon analyze the said behavior on our end to provide a solution. In meanwhile we will keep you posted with updates.
We are sorry for the inconvenience caused.