Hello, we are interested in replacing a custom application that uses Excel Interop to open a file, set Pivot Table and Slicer fields to a particular value(s), and then resaves the file. We are not looking to alter the structure of the document at all The pivots are connected to a live OLAP data source (SQL Server Analysis Services) Can ASPOSE.Cells fill this need? Can anything do this other than Excel Interop?
@tval,
Please spare us little time to analyze this. We will write back here soon to share our feedback.
Thanks for your query.
I am afraid, currently, Aspose.Cells does not support interacting pivot tables and slicers with OLAP data source. A ticket is logged with an id “CELLSNET-47280” for your demanded feature. We will look into it soon.
Once we have an update on it, we will let you know.
This has been asked for by numerous people for at least 10 years.
@Idealist,
We understand its importance but this feature is quite complicated. We will let you know once it will be supported.
As a side note, I was able to take an Excel file with a cube connected Pivot table and change the connection data… then save the file… Then open the new file in Excel and confirm the Cube is still ‘connected’. So, it looks like it might be possible to prefill the data from the cube… create pivot tables off of that and then set their connection to the cube for refreshing… but… I’m still testing.
Even if I did succeed, Different cubes, generally, have different fields that must be queried before building the pivot table and pivot fields… so it wouldn’t necessarily be good for tutorial code.
You may please take your time to perform further testing and share feedback with us.
Your understanding seems to be correct. We have noted these comments with the ticket for future reference.
When trying to create one from scratch, I’m running into the issue where the workbook DataConnections object has zero entries and there is no way to add a new one with the API. The DataConnections Property is Get only and IEnumerable which means there is no ‘Add’ method. Furthermore there is no public constructor for Aspose.Cells.ExternalConnections.DBConnection. So it looks like I cannot start , completely from scratch, and create an External Connection.
As I said before, this may not be a deal-breaker. If I start with an empty Excel document with a Cube datasource, I might be able to get around this. I don’t mind starting with a mostly empty template… as long as I can adjust all of the ranges, Pivot Fields and their MDX query values.
— Update–
Looks like this is the same way for Pivot Tables. The property ExternalConnectionSource is Read Only and ‘null’ by default.
— Update 2—
Looks like the pivot table fields are Read Only also. Unfortunately, that may be a deal breaker.
This means that I can’t
-
Create a new pivot table with the cell area that I need because the ExternalDataConnection object is null and Read Only and there’s no public constructor for DBConnection.
-
I can’t take an existing Pivot table and reconfigure it based on my needs because the ranges of the pivot table are Get only.
-
I can’t Create a new pivot table with a DataConnection. I can’t edit the Pivot Fields and ranges of an existing Pivot Table with a proper DataConnection. This seems like the best that I can do is change the connection string… and this is because I submitted a request in 2015(CELLSNET-42514) and the developers created Setters in DataConnection. I’ll spend a little bit more time looking for alternatives… however, If I don’t find one, modifying an existing OLAP cube connected excel file may also be impossible. I can take an excel connected cube file in Aspose.Cells and resave it as something else… and change its existing connection string… and it still works… so long as the underlying data, pivot fields and other such things have not changed in the new datasource. This still isn’t suitable for tval’s request because tval, and myself, would like to be able to change these pivot tables.
Unless I missed some documentation, It seems like Aspose.Cells would be capable of doing this, if the Devs lightened up with the read-only properties and the API lockdown. Aspose.Cells can, already, read all of the required pivot table properties… Unfortunately, it seems like the API is so locked down it actively prevents you from changing these properties. This is a design choice, not an engine functionality limitation. This comes down to the architecture and developers controlling what you can and can’t do with the library. They only want you to create pivot tables a certain way. It limits what Developers have to maintain and QA has to test. It looks like the underlying engine could do it if the developers wanted to allow it. Again, I’ll go through the documentation and Example code a bit more to see if there’s a way to get this done… but, based on the issues that I ran into today, it looks doubtful to be able to create or reconfigure an existing cube connected pivot table with Aspose.Cells.
If I don’t find any other way, I might examine the properties with reflection and see if I can modify the data that way.
@Idealist,
We have noted this feedback and will share our comments once we resume work on this issue and analyze this information in detail.
Thanks ahsaniqbalsidiqui
As a result of CELLSNET-42514 having been completed, Here’s what I can do. I can take an existing olap cube connected Excel file, Open it in Excel, remove all of the pivot fields but keep the pivot table so it ends up being a blank pivot table with all of the pivot zones saying ‘Drop fields here’. I can then save it.
After that, in Aspose.Cells, I can modify the Connection string so it connects with a different user and modify the Command and change it to the name of a cube accessible to this user… then save it with Aspose.Cells.
Then, when I load the Excel file that Aspose.Cells created in Excel, Excel will pull down the Pivot Table fields from the cube. This provides a functional connected cube. That might be enough in some circumstances. And, it has a user-friendliness challenge… it leaves the onus on the User to know to drag the pivot table fields into the correct drop areas on the pivot table.
This is much better than nothing. So thanks for completing CELLSNET-42514.
What I’m trying to do now is ‘pre-configure’ pivot fields so the user starts off with a sensible default cube report.
Here’s some code in C# using Aspose.Cells v21.7.0 from NuGet to alter an existing cube to point to a new cube;
Workbook wb0 = new Workbook(“OLAPTemplate.xlsx”, new LoadOptions() { }); // Load the connected cube that you made directly in excel using the wizard with no pivot fields in the drop areas
Aspose.Cells.ExternalConnections.DBConnection dbconn = wb0.DataConnections[0] as Aspose.Cells.ExternalConnections.DBConnection;
dbconn.ConnectionInfo = “Provider=MSOLAP.7;Persist Security Info=True;Data Source=https://YourOlapGateway.com/msmdpump.dll;Location=https://YourOlapGateway.com/msmdpump.dll;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2”; // Replace URL with your Analysis services gateway
dbconn.ConnectionInfo+=";User ID=AuthorizedCubeUsername;Initial Catalog=CubeDataDB;Password=PasswordForCubeUser"; // Replace UserName, Catalog/Database, and Password for your cube database
dbconn.Command = “CubeName”; // Replace CubeName with the name of the Cube you want to use
wb0.Save(“Output Filename.xlsx”);
Then you can load the saved Excel file in Excel and enable External connections… and drop the pivot fields into the pivot buckets and it works on the new cube.
This might be enough for you… and is waaaaay better than trying to do this with Excel Interop. Also, note, your Excel version and analysis services client drivers may require a different version of MSOLAP in the connection string. There is some documentation about this on the web.
@Idealist,
We are glad that you found the alternate option to perform this task. We will review it as well while working on this ticket.