Reading and Writing Data from an Excel File into BlueBox2.0

Today I will be explaining a very simple technique for reading and writing data in Excel files (xls) for use with the BlueBox2.0 platform, but this concept is generically valuable to any PHP/MySQL developer.

The Scenario:
This technique will be specifically useful for anyone who uses offline spreadsheets (xls/Excel) and wants to use these to show data from their BlueBox2.0 system in real time, and even more excitingly, wants to be able to enter/change data into their spreadsheet and have it automatically populate their BlueBox2.0 system in real time.

The Overview:
In essence what I am describing here is a simple web-query that is uploaded into a cell in your spreadsheet with a pre-defined query built into it. This query is set up to request a result from your BlueBox2.0 system. In the case of a read-only requirement, the result will be shown in the cell. In the case where you want data sent to the BlueBox2.0 system the query sends the value of another cell as part of the query, which is then processed on the server and the result is sent back to show in the cell.

For this to be most useful, I am building an example which sends data to the server, but in essence the only difference is that in the URL below you would simply leave out the bit in square-brackets.

The Ingredients:
To get this technique to work you will need the following:

• A notepad/text file on your desktop in the following format, with 3 lines, saved with the extension of .iqy
  
  WEB
  1
  http://www.yourdomain.com/?&class=bb_some_class&method=some_method&global_noincludes=rawtext&global_value=["my value"]
  
  Notes: Even though there seems to be 4 lines in my example, make sure that your notepad file is not breaking the 3rd line, as it should be one continuous line. Also: I have specifically used the new global_noincludes=rawtext option above as opposed to the usual global[noincludes]=rawtext because the URL in an iqy file cannot include square-brackets other than those specifying dynamic variables. For the same reason the value is passed as global_value and not global[value] etc.
• A BlueBox2.0 PHP class/method file set up in your system to receive the message, which would look something like this
  
  class bb_some_class extends baseclass{
          var $bbsetting_module_name="Some Class Name";
          var $bbsetting_skip_permissions=array("all");
      function some_method($conf=NULL){
          global $global,$global_value;
          resp("I got $global_value from Excel!");
      }
  }
  
  Notes: make sure you declare your bespoke global variables for this method (ie global_value etc) as these are not passing through the normal global[] name space. I also set bbsetting_skip_permissions to 'all' so that Excel can access this function easily, but this is a security concern. In a live version of this script you should add a client-side key and check the key in PHP before processing any data.

How to set it up:
Once you have the above 2 files set up, you can then open a new or existing excel spreadsheet and go to Data->Existing Connections->Browse for More. Select the .iqy file from your desktop.

You will then be asked to select the target cell for the response, and after that you will be challenged for the cell position where the script must load the value for ["my value"]. Once you have specified these 2 cells it will store the info and your cell will be automated to read/write to and from your BlueBox2.0 system.


Making it automatic:
When you are in the final stages of specify which cell to load the data from (above) you will see some checkboxes. make sure to select the 'Refresh automatically when cell value changes' option.