Created attachment 38981 [details] Zip containing error messages, java code and error log file. We are creating pivot table with attached code(Please refer PivotTableCode.java). As a result intermediate subtotal is not getting disabled and file gets corrupted with attached error messages (Please refer Error1.jpg and Error2.jpg). Also we have attached log file(Please refer ErrorLogFile.txt). We are using below apache poi libraries – 1. poi-ooxml-5.2.3.jar 2. poi-5.2.3.jar 3. poi-ooxml-full-5.2.3.jar Please respond as soon as possible.
Hi Komal, Could you please attach the input xlsx file as well? Otherwise nobody will be able to reproduce your test case. Thanks.
Created attachment 39003 [details] Zip containing input file and output file.
Hi Matthias, Please find the input file as well as output file. After opening output file 2 warnings appear because of setDefaultSubtotal() method that we used in our code. please click yes to access the pivot table result. Thanks, Komal Ghare
Created attachment 39006 [details] Updated the example code to initialize the pivot field items and the pivot cache definition Hi Komal, The pivot table support in Apache POI is rather limited. When creating a new pivot table, POI only creates the general structure and data references. Excel will then refresh the pivot data upon opening the file. Regarding your issue, see this StackOverflow post for further helpful information: https://stackoverflow.com/questions/37305976/apache-poi-xssfpivottable-setdefaultsubtotal/40480654#40480654 It is not enough to set the defaultSubtotal field, which essentially just updates the underlying XML attribute. In order to remove the subtotal row from the pivot table, the pivot field items have to be adapted as well. I adapted your example code (PivotTableCode.java) to add the necessary changes.
Hi Matthias, Thank you for providing the updated code. But it has some limitation, after opening file if we add new fields to pivot table through excel then it shows subtotal for those fields which will not resolve our problem. Thanks, Komal Ghare
Created attachment 39007 [details] Remove subtotal rows for all pivot fields. Hi Komal, For pivot fields which are not initially used as rows, you can just set the defaultSubtotal to false anyways. This way, if fields are added to the table in excel, there will not be a subtotal row. Please check out the updated code. Br, Matthias
Hi Matthias, Thanks for the updated code. Thanks, Komal Ghare