Paths to linked Datasources in Excel

Aspose.Cells Product Family
21

@JohnGrahamLT,
We parsed DataMashup, then got the following data:

shared Sheet1 = let
Source = Excel.Workbook(File.Contents(“C:\Users\MaxColvin\Documents\Test folder to migrate\Source 1.xlsx”), null, true),
Sheet1_Sheet = Source{[Item=“Sheet1”,Kind=“Sheet”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(Sheet1_Sheet,{{“Column1”, Int64.Type}, {“Column2”, Int64.Type}, {“Column3”, Int64.Type}, {“Column4”, Int64.Type}, {“Column5”, Int64.Type}, {“Column6”, Int64.Type}, {“Column7”, Int64.Type}, {“Column8”, Int64.Type}, {“Column9”, Int64.Type}, {“Column10”, Int64.Type}, {“Column11”, Int64.Type}, {“Column12”, Int64.Type}, {“Column13”, Int64.Type}, {“Column14”, Int64.Type}, {“Column15”, Int64.Type}, {“Column16”, Int64.Type}, {“Column17”, Int64.Type}, {“Column18”, Int64.Type}, {“Column19”, Int64.Type}, {“Column20”, Int64.Type}, {“Column21”, Int64.Type}})
in
#“Changed Type”;

Could you please explain your need little more? We are checking how to privode data by API .

22

If you decode the DataMashups in the customXml/item1 part from Excel Master Test File.xlsx, you will get the definitions for 3 “queries”.

section Section1;

shared Stuff = let
    Source = Access.Database(File.Contents("E:\...Repositories\...8258\...\Test Files\Special Files\Excel Test Files\Child Files\child1.accdb"), [CreateNavigationProperties=true]),
    _Stuff = Source{[Schema="",Item="Stuff"]}[Data]
	in
    _Stuff;
	
	shared Query1 = let
    Source = Odbc.Query("driver={Microsoft Access Driver (*.mdb, *.accdb)};dbq=E:\...Repositories\...8258\...\Test Files\Special Files\Excel Test Files\Child Files\child2.accdb;dsn=MS Access Database", "SELECT * FROM `E:\...Repositories\...8258\...\Test Files\Special Files\Excel Test Files\Child Files\child2.accdb`.[Stuff];")
	in
    Source;
	
	shared Query2 = let
    Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\... Repositories\...8258\...\Test Files\Special Files\Excel Test Files\Child Files\child3.accdb;", [Query="SELECT * FROM `E:\...Repositories\...8258\...\Test Files\Special Files\Excel Test Files\Child Files\child3.accdb`.[Stuff];"])
	in
    Source;

You can see a data source (with an external file path), and a query (also, sometimes with external file paths) in each definition. I need read/write on these two fields - data source and query.

Thanks!

23

@JohnGrahamLT,
Thank you for the information. We will analyse it and provide our feedback soon.

24

@JohnGrahamLT,

I am afraid, we cannot support parsing Stuff.Source or Query1.Source because there are too many database query definitions. We only can provide the whole Stuff definition as a string property which you have to parse them by yourself.

25
  1. Don’t you parse the multiple connection definitions in DataMashups for CELLSNET-46830?

I thought you could do the same for query definitions?

  1. If you can only provide read/write to the entire string then this is sufficient (but not as good :slight_smile: ).
26

@JohnGrahamLT,
We have recorded this feedback and will provide our feedback when ready to share.

27

The issues you have found earlier (filed as CELLSNET-46829,CELLSNET-46830) have been fixed in Aspose.Cells for .NET v19.8. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

28

Hi there. thank you for your work so far. they work well.

Just to be clear… Will you be implementing access to file-paths and other values from datamashups?

29

@JohnGrahamLT,
Logged issues are resolved now and it is not planned yet. Do you want us to implement this?

30

I would indeed like access to link paths contained in DataMashups… Yes please.
I also think this would contribute to a complete functionality for an Excel API.

31

@JohnGrahamLT,
We have analysed your feature request but we need to look into it more. We have logged the issue in our database for investigation and for a fix(if possible). Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46924 – Access to link paths contained in DataMashups

32

@JohnGrahamLT,

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

We have fixed your issue “CELLSNET-46924” partially.

We have supported getting data sources in mashups, but we do not support to update them for now.
e.g
Sample code:

   Workbook workbook = new Workbook(Constants.sourcePath + "CellsNet46924.xlsx");
            DataMashup mashupData = workbook.DataMashup;
            Assert.AreEqual(3, mashupData.PowerQueryFormulas.Count);
            foreach (PowerQueryFormula f in mashupData.PowerQueryFormulas)
            {
                Console.WriteLine(f.Name);
                foreach (PowerQueryFormulaItem item in f.PowerQueryFormulaItems)
                {
                    Console.Write(item.Name + " = ");
                    Console.WriteLine(item.Value);
                }
            }

Let us know your feedback.
Aspose.Cells19.10.2 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.10.2 For .Net4.0.Zip (5.0 MB)

33

Hi Aspose team,

can you please create request to support changes of PowerQueryFormulaItems. I’ll need to change name and values.

At the end I’ll need same thing like John and by adding this you’ll complete this part of API.
I suppose that this should be addressed over CELLSNET-46924, right?

Thanks,
Oliver

34

@dr.doc,
Thank you for your query. Could you please share more details about your requirement along with the sample code, source Excel file, expected modified file created by MS Excel and snapshots showing exact requirements. We will look into the details and share our feedback.

For the sake of clarity and better tracking of issue details and sample data, please create a separate thread for your requirements.

35

Done over: Feature missing - update of PowerQueryItems Value and Name

36

@dr.doc,
Thank you for the feedback.

37

@dr.doc,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46924”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.