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
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,