we have a specific use-case where our application needs to manipulate PivotCache directly to change a pivot in excel based on an external data source
We are currently using an older .NET application that does this, but we would like to migrate it into Java. We have evaluated and tested a combination of Apache POI + Aspose Cells which seems to be able to pull this off, but I was wondering if there is a way to manipulate PivotCahe (either directly or indirectly) in Aspose Cells as that would be easier for us.
@jbures,
You can use PivotTable API for your requirements however if it does not fulfil your needs, you may share more details about your requirements. Share a runnable .NET application project which is already performing this task along with the details of the required output. We will analyze the information and provide assistance accordingly.
Our desired output is that we need to provide users with a single XLSX file containing a pivot which has 3 million underlying rows. This means we can’t load the data directly into the Excel as there is a 1 million row limitation.
I cannot readily share the NET application, but from what I understood, it uses a running Excel installed on the machine to link an MDB file as data source to the pivot, then removes the data source to force Excel to keep the data only in PivotCache, thus bypassing the 1 million row limitation.
As I said, we are able to replicate this using Apache POI, which can manipulate PivotCache directly - however it is not able to refresh the pivot table shown to the user. We managed to verify Aspose Cells can do that refresh on top of that, but it does not seem it can manipulate PivotCache either directly or indirectly.
@jbures,
We have reviewed your requirement and need to analyze it in detail. This issue is logged in our database for further investigation as follows:
CELLSJAVA-43327 - Manipulating PivotCache with Aspose.Cells for Java