PHPExcel support for cell metadata and/or connection strings

Topics: Developer Forum, User Forum
Mar 28, 2012 at 6:37 PM

Hello all

We are building an application that will generate and render spreadsheets in XLSX format wherein some cells have data from a dynamic source ie. a Web service. Every time the spreadsheet is reopened, we'd like the dynamic cells to update their values automatically by connecting to the data source and fetching the latest data.

I considered two options to accomplish this but couldn't find support in PHPExcel for either:

1. Attach the Web service resource URL to the dynamic cells: Each dynamic value can be directly mapped to a Web service resource URL eg. an account value in a spreadsheet cell can be mapped to a REST resource like /api/company/[id]/account/[id]. So I thought of storing the resource URL inside each cell as metadata. When the application opens and renders the XLSX file, it will automatically inspect the cell metadata, connect to the Web service and get the latest data for each resource URL.

However, I couldn't find any methods in PHPExcel to read/write cell metadata (only worksheet metadata seems to be supported), even though this is included in the ECMA OpenXML specification (cellMetadata element). Can someone point me to these methods if they exist?

2. Another approach that came up on reading the ECMA spec is the notion of connection strings. My understanding is that it is possible to embed connection strings in an Open XML spreadsheet for precisely this purpose ie. dynamic data. However I couldn;t find detailed documentation on this, nor any information in the PHPExcel docs that this is supported. Can someone advise if this is supported or if not, how it could be added?

3. Given the requirements above, are there other ways to do what I'm trying to accomplish?

Thanks in advance for your help,

Vikram

Mar 28, 2012 at 7:10 PM

You could use a link to your app as how to open the XLSX, upon which your app would get the updated data and dynamically create the XLSX with PHPExcel.

Mar 29, 2012 at 5:05 AM
Hi Scott
Thanks for the suggestion. Indeed, I'd already considered this option but my understanding is that it would entail maintaining a separate representation of the spreadsheet on the server (which has all the links etc) and then referring to rhis to create the updated XLSX file each time. It can certainly be done (and we might end up doing this) but I was wondering if I could somehow use cell metadata and/or connections - both of which are part of open XML - to make the job a bit easier.

Do you know if either ot these are supported in PHPExcel?

Vikram

ScottMSanders wrote:

You could use a link to your app as how to open the XLSX, upon which your app would get the updated data and dynamically create the XLSX with PHPExcel.