Extracting dynamically changing data in excel via php

Question

I have an open excel sheet that's constantly being updated by another program via DDE. I wish to have a php script that accesses some of the data in this excel sheet. I have tried using PHPExcel and it seems that I cannot have the changes I make (e.g. via setCellValue) being immediately reflected in the open excel sheet. Similarly, if I change value of a cell (without saving sheet to the file system) the new value of the cell is not available via getValue().

Is this functionality supported by phpExcel? If so, could someone please point me to documentation that shows how this can be done? Alternatively, is there another way (not using phpExcel, for example) to do this?

Thanks.

You would need to write any change you make immediately, then reload the file from disk. This is likely to break things. Excel is not designed to operate like this - it was not designed with anything approaching support for concurrency - and you *need* to use a proper database engine. — DaveRandom, Jan 30 '12 at 10:33
I doubt by phpExcel, but you could try to access the file via COM or DDE from PHP. — hakre, Jan 30 '12 at 10:35

score 4 · Accepted Answer · answered Jan 31 '12 at 07:03

4

I was able to do this using the method shown at the rarified blog webpage

This worked for me, both for "pushing" cell values from php to excel, as well as getting modified values (without saving the file) from excel to perl. This site also has a nifty ajax-based function that keeps auto-refreshing my webpage with the latest values in excel.

Many thanks to the author of the blog.

answered Jan 31 '12 at 07:03

KS1

165
1
10

Don't forget to enable the "php_com_dotnet" extension. Thanks for the reference btw! – ishan shah Mar 02 '23 at 17:59

score 1 · Answer 2 · answered Jan 30 '12 at 10:36

It's not supported by PHPExcel.... PHPExcel loads the workbook into memory at the point in time when you issue the load() call , and at that point it can't "autorefresh" whenever the workbook is changed by your DDE because the DDE update is to the workbook on disk, not the PHPEXcel copy that's in PHP memory.

You'd need to be constantly loading and reloading to pick up changes to the underlying file.

Likewise, if you change the workbook in PHPExcel, it doesn't write that change back to the file on disk unless you explicitly save(), so the change will not be visible to your DDE program.

I'm not aware whether you can even do this with MS Excel itself... if you load a workbook using MS Excel itself, you're loading from disk into memory, and if anything else is accessing that workbook at the time, you find that you've loaded it in read-only mode, and (as far as I'm aware) it won't automatically refresh whenever the DDE program updates the original version. If anything can work with this the way you need, it's likely to be COM, but I wouldn't build up your hopes too much.

Extracting dynamically changing data in excel via php

2 Answers2

Linked