I have the following issue. I am trying to retrieve data from a pivot table. However when I run my test code to print out all data lines from the selected pivots some of the pivots selected will produce no data even tho data does exist in the workbook. I’ve noticed two things when trying to debug this issue:
For the file I’m trying to collect data from refreshing the pivot table doesn’t work. Instead i get the following error “Exception in thread “main” com.aspose.cells.CellsException: this data source is not supported.”.
It seems that if I manually change the pivots fields before parsing it so that all the pivots are set to all or to make sure the pivots are not being set to multiple items and having a few selected I can collect all the data. If one of the pivots is set to multiple items and has a few items selected only data for these selected pivots will be collected.
I’m not sure if 1 is related to 2. As in since refresh doesn’t work it means that when a pivot is set to multiple items it can’t collect any other pivot data unless its one of the selected.
I have attached my Java code and the file. Any help would be great. TestCode.zip (1.2 KB) TestFile.zip (6.8 MB)
@AR1,
We are working on this query and require more information. In part two you mentioned that if you perform manual processing with pivot tables, code will work. Could you please provide us few sample Excel files which have been modified manually and work fine with this code? We will analyze those files as well and provide feedback after detailed analysis.
I have provided an additional file. This file has the same data as the one I provided however if you open it and look at the first two pivots you will see they are set differently from the first file. In the new file select multiple is not selected and “All” is selected for both pivots instead. When this file is run you should see that data for all combinations of the first two pivots are printed out. This differs from the original file i provided which has ‘select multiple’ checked for the two pivots and has 2018 selected for the first pivot and has the following selected in the second pivot Gas de Alta Presion, Gas de Baja Presion and Gas de Media Presion. When this file is used my code will only print out data if the first pivot is set to 2018 and the second pivot is one of the three selected pivot i mentioned above, no other combination will provide data. I would like it so that I didn’t have to manually change the file to get all data.
Also just to explain what my code does, it goes through all combinations of the first and second piviot and prints out all data associated with the pivots selected. When the data has been printed it will print out the pivots used for the data at the end.
@AR1,
We have analysed the issue and found that this file contains external source for data. Aspose.Cells does not support external sources, therefore no data is fetched when code is executed. The file which you shared later in the previous post, contains all the data populated, therefore you get complete data. It seems that it is not possible to fetch data from external source used in your earlier file hence this is not an issue with the Aspose.Cells API.
If you face some issue with any Excel file which contains entire data and not fetched for a pivot table, please share the simplified Excel file along with the simple console based application to test the scenario. We will analyse the issue and provide our assistance.
I believe the first file I provided should have all the data the second one does. There is no difference between the data the two files contain. The only difference is that the pivot fields for one are set differently from the other. And it seems these previously selected pivots are defining the data available to be collected when creating the workbook from them. I don’t have access to the external source you mentioned but if I manually open the pivot table and make a selection with pivot fields that were not originally selected then data still appears. Can you explain why the excel file then can still get all data even though I don’t have the external source and why this isn’t able to be replicate with the aspose library?
@AR1,
Well your template file contains two connections as follows:
Database Query:
Connection Name:
energia vi_producciondegas_2009_actual
Connection String:
DSN=Energia Unicode 64;DATABASE=energia;SERVER=10.10.12.16;PORT=5432;UID=postgres;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=1
Command Text:
SELECT * FROM "energia"."unificacion"."vi_producciondegas_2009_actual"
OFFICE DATA CONNECTION
Connection Name:
energia vi_producciondepetroleo_2009_actual
Connection String:
DSN=Energia Unicode 64;DATABASE=energia;SERVER=10.10.12.16;PORT=5432;UID=postgres;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;GssAuthUseGSS=0;XaOpt=0
Command Text:
SELECT * FROM "energia"."unificacion"."vi_producciondepetroleo_2009_actual"
If we call pivotTable.refreshData(), it shows “This data source is not supported” error message which shows that it is accessing this external data source.
However for further detailed analysis, we have logged the issue in our database for investigation and for a fix (if applicable). Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-42982 – All data cannot be parsed from a Pivot Table
I am afraid, Aspose.Cells does not support refreshing the PivotTables with external data source(s). Also, we cannot support this feature for now. If we still have further updates, we will let you know.
I have found what the issue is. It looks like when a pivot is set to select multiple the table is “hiding” all the fields that are not selected for the pivot. So what needs to be done instead of selecting the pivot you want to select with setCurrentPageItem(), you need to unhide the pivot field with hideItem(index, false), you would also need to make sure all the other fields in the pivot are set to hidden so that they are not included so use hideItem(index, true) to do that.
I have one follow up question tho. I have found that this method of looking at data can take a while to do since going through all combination of pivots well take a long time. I have found that if I open the file I provided in Excel, right click the pivot table and select pivot field list I have the option to change the filters for the pivot table to rows. Is this do able using Aspose? So from the initial file i provided can I switch the pivots into becoming rows. I have attached a copy of what I want the file to look like in the end.
You may try the sample code like following:
e.g Sample code:
System.out.println("Aspose.Cells for Java Version: " + CellsHelper.getVersion());
// Create load options
final LoadOptions options = new LoadOptions();
// Load the sample Excel file containing pivot table cached records
final String fileName = "TD_S_SescoWebUP_06_2019.xlsx";
final Workbook wb = new Workbook("f:\\files\\" + fileName, options);
// Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);
PivotTable pivotTable = ws.getPivotTables().get(0);
System.out.println(pivotTable.getName());
final int pageCount = pivotTable.getPageFields().getCount()-1;
for (int j = pageCount; j>0;j--)
{
pivotTable.addFieldToArea(PivotFieldType.ROW, pivotTable.getPageFields().get(j));
}
wb.save("f:\\files\\out1.xlsx");
But, I am afraid, this might not work since the pivot table has external data sources. Even you may check the PivotTable Fields list, you will see your desired pivot fields from Page/Filters section are successfully moved to ROWS section but still the data is not properly calculated/updated.
Thanks for the example, its was exactly what I was looking for. I was able to get everything to work. I’m not sure about the whole refresh issue, I assume it just means that if data is updated from the Pivot tables source then the file will not be updated but I have no interested if the data from the source is changed I just want to see all the data currently in the file at the moment I get the file so this isn’t an issue for me. You can close this ticket. Thanks again to you and @ahsaniqbalsidiqui for the help.