I am using Aspose Cell for .net of version (21.5.0.0).
I am trying to get the Data Connection from the Workbook those are used in Pivot Tables. There are two Connections and I am not getting the Information of one of them. There is a twist that the “objWorkbook.DataConnections” shows me three Data Connections which is actually two.
I am attaching the sample project as well as the sample file with in it.
ExternalDataSources.zip (118.2 KB)
Thanks for the template file and sample app.
I tested your scenario/case using your sample project with sample file and found Aspose.Cells retrieved two data connections. One of them is data model for which not much information was retrieved. Do you want to retrieve details for this data connection? Or you need to get 3rd data connection as well which is not retrieved?
Hey,
thanks for your quick repone.
Excel it self is showing that there are only two connections in the Workbook that you can refer in the attached image and can verify it as well in the previously attached Excel File.
But the Aspose is providing the 3 Connections that’s the issue or not i don’t know.
My issue is that i am able to get the details of one connection but not the other one. that also you can refer in the attached screen shot too. So, i need the information for the other connection too.
DataConnections.png (33.0 KB)
Thanks for providing further details and screenshot.
We reproduced the issue as you mentioned. We found Aspose.Cells could not retrieve a data connection from the Excel spreadsheet as per your attached screenshot. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-56651
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
If you unzip the Pivot Table3.xlsx and check xl/connections.xml, you will find 3 connections.
After research , if creating power pivot table and adding data to Data Model, MS Excel will create two connections(a global data warehouse connection and an external data connection). BTW if adding again, only one external data connection will be inserted. All connection ids of pivot tables are the id of global data warehouse connection.
Now Aspose.Cells does not support power pivot table with Data Model, we can not simply find which connection is real data source of pivot tables.
The feature ( power pivot table with Data Model) is very complex. We can not support it soon.
Please do not select “adding this data to the Data Model” when creating pivot table now.
Hey,
Thanks for your brief explanation.
We’ll wait for the proper fix.
@harshCIPL22
Though we have supported getting correct connection from pivot table with the following codes in the next version 24.10:
Workbook workbook = new Workbook(dir + "Pivot Table.xlsx");
foreach(Worksheet sheet in workbook.Worksheets)
{
PivotTable pt = sheet.PivotTables[0];
ExternalConnection[] conns = pt.GetSourceDataConnections();
ExternalConnection conn = conns[0];
Console.WriteLine(conn.Name);
Console.WriteLine(conn.SourceFile);
if(conn.ConnType == ExternalConnectionType.Database)
{
DBConnection dbConn = (DBConnection)conn;
Console.WriteLine( dbConn.ConnectionInfo);
Console.WriteLine(dbConn.CommandType);
Console.WriteLine(dbConn.Command);
}
else if (conn.ConnType == ExternalConnectionType.DataModel)
{
DataModelConnection dbConn = (DataModelConnection)conn;
//Console.WriteLine(dbConn.ConnectionInfo);
//Console.WriteLine(dbConn.Command);
}
// Console.WriteLine(conns[0].Name);
//Console.WriteLine(conns[0].SourceFile);
}
we could not find ConnectionInfo and command from DataModel Connection in the file:
<connection id="2" xr16:uid="{0D243E86-419D-4DA5-A83B-AF168C5F4262}" sourceFile="\\sqaclient\D-Drive\QA_TEAM_Data\Amit Data\CIMCON's Data\Accounts\1000 mix files\72.xlsx" name="721"
type="100" refreshedVersion="8" minRefreshableVersion="5">
<extLst>
<ext uri="{DE250136-89BD-433C-8126-D09CA5730AF9}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main">
<x15:connection id="ba3a90f2-8968-497a-b356-96c58ede41a2" autoDelete="1"/>
</ext>
</extLst>
</connection>
I am glad to know that you are going to provide this in new version and worked for it.
As you said that you are still not able to get those details form your side too.
I am wondering that than how excel it self is managing the connection details because we can see those details int the Excel’s connections and without the details.
image.png (13.4 KB)
We tried to provide same connection as MS Excel . And we have changed some API of external connection in the next version, so try the following test codes to get connection info:
Workbook workbook = new Workbook(Constants.PivotTableSourcePath + “CellsNet56651.xlsx”);
ExternalConnection conn = workbook.Worksheets[0].PivotTables[0].GetSourceDataConnections()[0];
Assert.AreEqual(“721”, conn.Name);
Assert.AreEqual(ConnectionDataSourceType.OLEDBDataModel, conn.SourceType);
Assert.AreEqual(@“\sqaclient\D-Drive\QA_TEAM_Data\Amit Data\CIMCON’s Data\Accounts\1000 mix files\72.xlsx”,conn.ConnectionFile);
Assert.IsTrue(conn.ConnectionString!= null);
Assert.AreEqual(OLEDBCommandType.TableCollection, conn.CommandType);
Assert.AreEqual(“"Sheet1$"”, conn.Command);
conn = workbook.Worksheets[1].PivotTables[0].GetSourceDataConnections()[0];
Assert.AreEqual(“72”, conn.Name);
Assert.AreEqual(ConnectionDataSourceType.OLEDBBasedSource, conn.SourceType);
Assert.AreEqual(@“\sqaclient\D-Drive\QA_TEAM_Data\Amit Data\Risk File\External Data\excel query.xlsx”, conn.ConnectionFile);
Assert.IsTrue(conn.ConnectionString != null);
Assert.AreEqual(OLEDBCommandType.TableName, conn.CommandType);
Assert.AreEqual(“Sheet1$”, conn.Command);
@harshCIPL22
There is still an issue about “connection type”, we only can return OLEDBDataModel as descripted in the file, not “Excel file” or “Worksheet Data Connection”.
We need more tests to find a solution.
@harshCIPL22
Please try the latest version 24.10 with PivotTable.GetSourceDataConnections() method to get external connections.
The issues you have found earlier (filed as CELLSNET-56651) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi