Hello, I´ve several problems with flexcel (V 7.11) and given xlsx-Files containing PowerPivot elements.
If loading one of this files and saving it (no changes made) the powerpivot information is broken, all pivots are broken.
I've also tested your demo-project No 80 for reports:
FlexCelVCLNT\Demo\Delphi\Modules\20.Reports\80.Pivot Tables\ PivotTables.dpr
I've added a worksheet with a pivot with named table
It´s the same result: The resulting file is broken
In the Bmp you can see the start situation - everything looks fine.
After adding lines to the datasheet the pivot is broken.
Used Excel-Version 2013 German language 32 bit
Is there any chance to get this working? I've produced a demo-project and Xlsx-files based on your DemoNo 80 if you are interested
Can you send me the the files to firstname.lastname@example.org so we can take a look?
Pivot tables should work fine, porwer-pivot is not really tested (it is a completely different thing, and outside our scope), but anyway, we'll try to fix it if feasible
Thank You very much for the fast reply. In 2014 You have added the remark that powerpivot is save Version(6.20?) to the changelog, so I was not worried about it . But the error occurs not only when using Power pivot! If placing a simple pivot with the option to put more than one datasource(Table on worksheet) and the tableName(s) is/are shown in the pivot-expert then you are lost.
I'm sending the files to you now
thanks, I got the files.
About powerpivot, yes, once we had issues with a customer using it, so we added a fix for it, but in general, powerbi is a different product than Excel, and we can't really fully support it without spending years of development on it. But of course, we will always try to fix what we can fix.
I'll take a look at the files and comment later.
Looking at the files, they all have a powerBI model inside. You can check this by renaming the files as zip and searching for "power" in them.
In particular, the one with the pivot table on the table has this line:
<Relationship Id="rId12" Target="model/item.data" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/powerPivotData"/>
From what you wrote, I think you are checking the checkbox "Add this data to the Data Model"
And this is creating a "data model" part, which is from PowerBI (see Create a Data Model in Excel )
Sadly this PowerBI datamodel is not documented in any xlsx document that I could find, and it has a big blob of binary data which makes it difficult also to reverse-engineer.
Given that, it is not something we can easily support. What we can do, and already have today, is to persist this blob when you open and save the file. I can't guarantee that the blob will stay valid when you modify things in the file, but given that it should contain information about PowerBI connections, I think it should be relatively safe to modify, add or delete worksheets and properties of the file. The example you sent is now working correctly.
The next release (scheduled for next week) should have this fixed.
thanks for your help! I will test next week and inform You. In worst case I will try good old Com-api or perhaps a dbf File and odbc :-(
Please give 7.12 a try, I think it should work now.