0

I have developed a VBA spreadsheet that runs locally and with obtain data from other sources continuously.

I want to find a way to extract / access the data inside this spreadsheet through php and then output some processed data using a browser and these data will update accordingly as the spreadsheet updates itself.

Anyone can show me how to achieve this? Both the spreadsheet and the php will run locally on a windows machine if that is easier...

Thanks very much for your help :)

Fluffeh
  • 31,925
  • 16
  • 62
  • 77
LawrenceH
  • 255
  • 1
  • 5
  • 17

3 Answers3

2

I just developed an application that does this the idea is to use http requests on_change events in the spreadsheet.

vba code for request in excel

Function httpRequest(ByVal path As String) As String
  Set HttpReq = CreateObject("MSXML2.ServerXMLHTTP")
  HttpReq.Open "GET", path, False
  HttpReq.Send
   httpRequest = HttpReq.ResponseText
End Function

On your php side you have to develop the part where the data is mapped in the database. And of course the view of the data. As for realtime you have to go for something more elaborate like a table that holds the latest changes and check for such changes every now and then unless there is some mechanism to do so.

In excel there exist timers to acomplish polled checking for updates for web you should to use ajax for seamless updates have a look at http://datatables.net/

SGalea
  • 637
  • 9
  • 18
  • This is so nice, thanks! Is there a similar way to do this with Java too? – LawrenceH Aug 28 '12 at 08:59
  • J2SE application or J2EE ? need more info about what do u mean about java. – SGalea Aug 28 '12 at 09:07
  • J2SE? Since what I'm developing is pretty much for personal use on local computer(s) – LawrenceH Aug 28 '12 at 09:51
  • 1
    Its pretty much the same concept http request http://stackoverflow.com/questions/1359689/how-to-send-http-request-in-java or connect directly to your database – SGalea Aug 28 '12 at 10:13
  • If your datasource is a database try to leverage Database Triggers to create latest changes entries if you are going to poll updates for realtime – SGalea Aug 28 '12 at 10:22
0

You can use PHP Excel Reader to display the data quite easily from the spreadsheet - I think this is the simplest of the PHP Excel libraries that will do what you want and simply set the page to refresh on a regular basis.

Using this library is pretty much as simple as this:

<?php echo $data->dump(true,true); ?>

Once you have told it where the file is to display the output pretty much as would be expected to see in an Excel Workbook. (Here is a link to an example that they have up and running online)

This means you won't get any locking issues as the read is done rather quickly (assuming a reasonable file size and users won't really ever be more than a few minutes/seconds behind.

Fluffeh
  • 31,925
  • 16
  • 62
  • 77
  • So when the php is refreshing, do I have to have the workbook saved in order for the ExcelReader to update its values? – LawrenceH Aug 28 '12 at 08:38
0

You want PHP to just generate HTML from Excel sheets? Excel supports HTML generation natively.

Try Save as Web Page to test the quality of HTML produced by Excel. If it's OK then all you'll have to do is write a small piece of VBA that will be generating HTML from your spreadsheets automatically.

Sort of CMS build in Excel.

dmitryungurean
  • 108
  • 7