Using Excel's external data connection with openpyxl

[Dave]

Hi,

I'm currently playing around with openpyxl and plan to use it in my
Django project.

My aim is to do the following:
1. Read a template .xlsx file which contains an excel 'external data'
connection. The external data is a HTML table on a web page.
wb = load_workbook(filename = r'template.xlsx')

2. Add initial data to the wb workbook.

3. Return the workbook as a download file to the user.
response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachement; filename=%s.xlsx' %
( file_name )
wb.save(response)
return response

After testing the above, I've found that the resulting downloaded file
does not preserve the 'external data' connection in the excel file. Is
it possible to preserve the external data connection?

Alternatively, is it possible to add an external data connection
programmatically?

Cheers,
David

[Eric Gazoni]

Hi Dave,
actually it's not yet possible to preserve non-supported existing data
when saving. I have a solution in mind, that would be to only write the
bits that were modified by openpyxl, and to preserve the original data
for the rest of the file, but I'm afraid I don't have time those days to
implement it :(

And I don't think the external data support is ready either, so you
might not be able to add it right now, unless you patch openpyxl
yourself first ;-)

Let me know if I can be of any help.

Cheers,
Eric

Le 8/04/11 11:05, Dave a �crit :

[Dave]

Hi Eric,

Thank you for the clarification. I agree, the 2 possibilities are:
a) Preserve the original data bits and pieces with the external data
connection.
b) Include support for external data in openpyxl.

First I will need to compare the Microsoft Excel implementation of
external data connection with OpenOffice. There seems be different
implementations?

I've also noticed that the python xlwt module also fails to preserve
this external data connection with .xls files.
Even GoogleDocs doesn't manage to keep the external data connection
alive when converting a xlsx file (with data connection) -> Google doc
-> xls

Do you know if PHPExcel has solved this requirement?

Cheers,
David

[Eric Gazoni]

Hi Dave,
sad to learn that no module suits your need for now. OOCalc and Excel
shouldn't have two different ways of dealing with external data, but I
can't be sure as I haven't studied this part yet.

I don't know either if phpexcel did succeed in doing this, sorry.

Cheers,
Eric

Le 8/04/11 17:03, Dave a �crit :

[eremi]

this is sad, that up to this day, openpyxl doesn't allow to preserve data connections.

[Charlie Clark]

On 22 Mar 2022, at 11:17, eremi wrote:

> this is sad, that up to this day, openpyxl doesn't allow to preserve data
> connections.

The only sad thing is that, no one who has the problem∞ has offered to try add support to the library. I don't have files like this myself so I can't really test them.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Barry Penner]

I am missing this feature as well. I wish I had the knowledge to take on the task of trying to add support for external connections to the library, but I'm very much a novice with Python still and have a heck of a lot to learn before I can contribute at that level. One day.

I did go poking around in the source code, though, and noticed that maybe there is someone working towards implementing this? I found a method called def _write_external_links(self) near the bottom of openpyxl/writer/excel.py (line 257). Is someone currently working on this?

[Charlie Clark]

On 13 Feb 2023, at 4:44, Barry Penner wrote:

> I did go poking around in the source code, though, and noticed that maybe
>
> there is someone working towards implementing this? I found a method called
>
> def _write_external_links(self) near the bottom of openpyxl/writer/excel.py
>
> (line 257). Is someone currently working on this?

External links have nothing to do with data connections.