How to pull data and export to excel within an ajax framework.

Question

Hi,

I have a very specific problem and i'm looking for input on a good way to approach it.

edit:(simplified question)

Essentially I have been working on a remote monitoring system for retail kiosks. I am operating within a framework that is entirely dependent on AJAX. I have been using XML to pass data between the clientside/serverside scripts that I use.

On the clientside a user is presented with a datepicker (jquery) and my script pulls any data that is relevant.

Now, I have been charged with the task of taking these tables (populated via ajax) and exporting them to excel. I would like this functionality to be as flexible as possible. This is a very young project and the requirements will be changing from client to client. Flexibility, simplicity, and rapid development are my primary concerns. Data will vary from one table with simple data structure, to very non-uniform ways of storing data. ex: multiple tables, images, graphs.

EDIT: A key to my situation is that I am not open to any third-party extensions that are not widely utilized, professional, and will continue to be developed for quite some time. Ex: JQuery.

Easy enough... right?

I did a bit of research and stumbled upon this PEAR plugin to make exporting to excel via PHP easy. Good to go, I wrote a script that takes an array and writes it to a spreadsheet.

Now, how do I pass the data to this function?

I have decided that it is essential to do this from the clientside. I have hard-coded table headers that I am planning on including in the spreadsheet.

I've recently discovered it's not really all that practical to ship the data via AJAX to my Excel-exporting class due to the inability to actually send the file (which is done via the headers).

I have a scope issue. How to pass the data from clientside -> serverside outside of my ajax driven framework.

I have several approaches I'm looking at for where to actually retrieve the data from.

1)Read the data from the HTML of a table, format it into JSON, pass via form submission to my excelWriter.php file. This has the advantage of efficiency and clarity.

BUT it limits the excelWriter.php to only printing elements that I specifically code it to handle. This function will be complex, given that data might be formatted differently in the future. I would need to be able to pull data from multiple tables (how do I pull that data?), images, graphs rendered in flash (might not be possible...).

I am concerned about rewriting this function every time I add a new type of data or display.

2) Pull the data as I am formatting it into html. Essentially, I am parsing this response object and formatting it into a string and rendering it into HTML. I could take relevant data, images, graphical elements, and shove them into a json generating function.

The benefit to this is I would have exact control over when and where I pull data. It would be really simple to execute, modify, and get a finished version of this script.

The drawbacks are obvious. Readability is sacrificed, it's not object oriented... not all that creative (i like being creative =P)

snippet:

$.each(results.sale, function(i)
{
 if(difference >=2)
 {
  this.time = String(this.time);
  this.time = this.time.substr(5,2)+'/'+this.time.substr(8)+'/'+this.time.substr(0,4);
  //this.time would be packaged in a json object, along with other data i'd like to export to excel
 }

 htmlStr += "    <tr>"; 
 htmlStr += '     <td class="contentTableKey">'+ this.time +'</td>';
 htmlStr += '     <td class="contentTableValue">$'+this.amount+'</td>';
 htmlStr += "    </tr>";

 total += parseInt(this.amount);
});

htmlStr += '    </tbody>';
htmlStr += '    <tfoot>';
htmlStr += '     <tr>';
htmlStr += '      <td id="salesTotal" class="contentTableValue"></td>';
htmlStr += '      <td id="salesTotal" class="contentTableValue">$'+total+'</td>';
htmlStr += '     </tr>';
htmlStr += '    </tfoot>';
htmlStr += '   </table>';
htmlStr += '  </div></div>';
htmlStr += ' </li>';
htmlStr += '</ul>';

$("#contentData").html(htmlStr);

It's a bit long, and detailed (too detailed??). Thanks for taking the time to read this.

Answer 1

I can't claim to fully grasp your question yet (it is a long one! :), and I'm not sure whether it will help you but do you know Downloadify?

Downloadify is a tiny JavaScript + Flash library that enables the generation and saving of files on the fly, in the browser, without server interaction.

You may be able to write out a HTML file, or a new XML-Style excel file (or at least some format that Excel can digest) and offer it as a file download that way.

Answer 2

If I understand correctly, the only user interaction is picking a date.

I cannot tell if this is a solution for you, but maybe you can regenerate the tables from a simple get parameter.

I am thinking to something like this:

<?php
$date = isset($_REQUEST['d']) ? $_REQUEST['d'] : false;
?>
<html>
...
  $(document).ready(function(){
    var received_date = <?= json_encode($date) ?>;
    if(received_date){
      generate_my_tables(received_date);
    }

  });

If so, maybe you can

1. Create a simple PHP script that will generate a .IQY file from a date it is passed and force the browser to download it and open it with Excel.
   The IQY will point the url that regenerates the tables on demand.
2. From you client application, when the customer wants to export his tables to Excel, you set the src of a hidden iframe on the page to the PHP script to make the IQY available from there.