Viewing full ODATA connection details in CELLS JAVA

Aspose.Cells Product Family
1

I am using

workbook.getDataConnections()

to get the defined ODATA connections in an excel.
However the

dbconnection.getConnectionInfo()

method returns

“Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=2 auto tables_bananas_6314;Extended Properties=”"",

but not the actual connection info with the external source url.

I would like a method to get all the external source url data that IS available in the included file in a
[customXml] \ [item1.xml] -> (encoded in base64)

odata_query.xlsx.zip (22.9 KB)

2

@turaaa,

Thanks for providing us template file and details.

Well, Aspose.Cells follows Ms Excel standards when retrieving or specifying connection attributes. In MS Excel, I do not find any option to get your mentioned base64 data as external connections. i can only see the connection string which Aspose.Cells does retrieve it fine, see the screenshot for your reference:
https://i.imgur.com/lOk2p7l.png

How could you retrieve your mentioned base64 string to be taken as external data connection in MS Excel manually? Then, we will check on how to do it via Aspose.Cells APIs.

3

Hi
thanks for the quick response.

what excel version are you using?
I think odata integration feature is in excel 2013 and later (older versions need an addin for odata)

in a new excel version go to :
Data -> query and connections -> double click the right pane -> advanced editor
you will see the actual url of the odata source

if you want to see this data in the excel file itself then :
rename the excel file i sent to a zip file
extract it
open customXml directory
open item1.xml
it has a long base64 encoded string -> decode it
it has an xml with the odata data source (https:\/\/app.datarails.com:443\/OData.svc …)

let me know if you have further questions

4

@turaaa,

Could you provide some screenshots captured in Ms Excel to show it as I still could not find it. I am using MS Excel 2013. We will check it soon.

5

i am attaching an image that shows how to get to the odata source url in excel 2016
image.png (124.1 KB)

and the location of the base64 encoded string in the unzipped excel file in the customXml directory
Screen Shot 2019-03-12 at 14.52.35.png (37.7 KB)

see also this for the format of saving odata connection information:

ttps://docs.microsoft.com/en-us/openspecs/office_file_formats/ms-qdeff/27b1dd1e-7de8-45d9-9c84-dfcc7a802e37

6

@turaaa,

Thanks for the screenshots and further details.

It seems like the feature “Get ODATA connection details” is not supported at the moment but we need to evaluate it thoroughly. I have logged at ticket with an id “CELLSJAVA-42851” for issue. We will look into it soon.

Once we have an update on it, we will let you know here.

7

@turaaa,

After further investigation, we found we do provide relevant APIs which can extract xml data from custom xml parts. See the following sample code for your reference:
e.g
Sample code:

Workbook workbook = new Workbook("f:\\files\\odata_query.xlsx");
        byte[] data = workbook.getCustomXmlParts().get(0).getData();
        FileOutputStream fos = new FileOutputStream("f:\\files\\item1.xml");
        fos.write(data);
        fos.close();

But we do not support parsing this xml data. We plan to support parsing it in the next month though.

8

thanks,
please update me when done
.notice that the xml in item1.xml has inside it a long base64 encoded string with data on the odata/external connections , that should also be parsed

9

@turaaa,

Sure. We will let you know once any update will be available.

10

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

11

The issues you have found earlier (filed as CELLSJAVA-42851) have been fixed in Aspose.Cells for Java v19.11. This message was posted using Bugs notification tool by ahsaniqbalsidiqui