Free Support Forum - aspose.com

Pivot Table From External Source

Hey,

I am creating Pivot Table from two Sources.

  1. Excel Worksheet It Self.
  2. SQL Server Connection - Using Pivot Table.

I am able to get both the pivot table but not able get the source of SQL server pivot table.
the simple pivot table from the worksheet provides the details regarding pivot table but not able to find DataSource of SQL Query pivot table.

I am attaching a sample file so you can look at the issue.
Sample Pivot Table.zip (12.7 KB)

@harshCIPL22,
Thank you for your query.
Please give a try to the following sample code and share the feedback. If your requirement is not fulfilled, please elaborate your requirement more along with images and expected information which you want to extract from the Excel file. We will analyze the information and provide assistance accordingly.

Workbook workbook = new Workbook(path + "Sample Pivot Table.xlsx");
// Load workbook object
//Workbook workbook = new Workbook(sourceDir + "sampleFindQueryTablesAndListObjectsOfExternalDataConnections.xlsm");

// Check all the connections inside the workbook
for (int i = 0; i < workbook.DataConnections.Count; i++)
{
    Aspose.Cells.ExternalConnections.ExternalConnection externalConnection = workbook.DataConnections[i];
    DBConnection dbConn = externalConnection as DBConnection;
    Console.WriteLine(dbConn.Command);
    Console.WriteLine(dbConn.CommandType);
    Console.WriteLine(dbConn.ConnectionInfo);
}

Thanks for your help by suggesting the code.

I am able to get the DataSource of the SQL Query using that externalConnection object and it returns me the ConnectionInfo property that provides me the DataSource of Pivot Table.

But still I am having an issue that I am not able to relate that Pivot Table with externalConnection.
as Pivot Table is not having any Property that indicates that the which external connection is refer to which pivot table.

for more details I am attaching two images from that you can understand that what I am actually facing.
ExternalConnection.png (43.4 KB)
PivotTable.png (44.3 KB)

There is no such a bridge or connection between this two things to identify themselves.
There should be ConnectionID property in PivotTable if it is created from external sources.

@harshCIPL22,
We have reviewed your requirement but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47141 – Link between Pivot Table and external connection

Hey.

Is there any update on the current issue?
Or could you provide me any way that I am able to get it done?

@harshCIPL22,
Could you please spare us a little more time (3-5 days or so) for complete evaluation and investigation for your issue. If it is not much complex, you should be receiving the fix before the end of next week or even before it. If the issue is complex, it might take a couple of weeks. Once we have an update on it, we will let you know here.

@harshCIPL22,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-47141”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes. The fix will contain PivotTable.ExternalConnectionDataSource property to return external connection which is referred by PivotTable.

Thanks alot for the quickness.
We’ll wait for the patch.

@harshCIPL22,
You are welcome.

@harshCIPL22,

Please try our latest version/fix: Aspose.Cells for .NET v20.1.11 (attached)

Your issue should be fixed in it. Please try PivotTable.ExternalConnectionDataSource property for your needs.

Let us know your feedback.
Aspose.Cells20.1.11 For .Net2_AuthenticodeSigned.Zip (5.0 MB)
Aspose.Cells20.1.11 For .Net4.0.Zip (5.0 MB)
Aspose.Cells20.1.11 For .NetStandard20.Zip (4.1 MB)

Thanks for the quick fix.
My issue is resolved with PivotTable.ExternalConnectionDataSource property.
I am getting all that details which is required for external sources.

@harshCIPL22,

Thanks for your feedback and good to know that you are up and running again.

The issues you have found earlier (filed as CELLSNET-47141) have been fixed in Aspose.Cells for .NET v20.2. This message was posted using Bugs notification tool by Amjad_Sahi