Passing data from PHP class to PHPExcel via AJAX

Question

i got stuck with OOP PHP and json data. i'm not completely new to OOP, but i can't get my head around this. if anyone can please explain to me, would be great!

i have the following grid object in PHP:

    Class Grid {

        var $data;
        var $joins;
        var $fields;
        var $where;
        var $table;
        var $groupBy;
        var $having;
        var $limit;
        var $order_by;
        var $sort;
        var $security;
        var $set;
        var $sql;

....

        // loads data into the grid
        function load() {
    ...
            // setup the sql - bring it all together
            $sql = "
                SELECT $post[cols]
                FROM `$table`
                $joins
                $where
                $groupBy
                $having
                ORDER BY $order_by $sort
                $limit
            ";

            $this->sql = $sql;

            // execute the sql, get back a multi dimensial array
            $rows = $this->_queryMulti($sql);

            // form an array of the data to send back
            $data = array();
            $data['rows'] = array();
            foreach($rows as $i=>$row) {
                foreach($row as $col=>$cell) {
                    // use primary key if possible, other wise use index
                    $key = $primaryKey ? $row[$primaryKey] : $i;
                    // primary key has an _ infront becuase of google chrome re ordering JSON objects
                    //http://code.google.com/p/v8/issues/detail?id=164
                    $data['rows']["_".$key][$col] = $cell;
                }
            }

    ...        
            $data['order_by'] = $order_by;
            $data['sort'] = $sort;
            $data['page'] = $page;
            $data['start'] = $startRow + 1;
            $data['end'] = $startRow + $nRowsShowing;
            $data['colData'] = $colData;

            $this->data = $data;
        }

and it's called by AJAX callgrid.php:

$grid->load();
        // here we need to add field in data[sql] = sql query, then we can pass it to toExcel() - how?
        echo json_encode($grid->data);

what i'm trying to get is to be able to export current sql query (it can be all or searched results) into Excel using PHPExcel. So i've got toExcel.php with function toexcel($query) - that will take a query and export it to excel.

now - HOW do i pass sql query from grid to toexcel via AJAX?

1. I understand that i need to add to $data():

   $data['sql'] = $sql;

what next?

---

UPDATE: I'm using the following jquery grid: http://square-bracket.com/openjs

I understand that PHPExcel should be initiated either by grid or jquery

Answer 1

A general idea of what you could do:

Create a button e.g.

<a href="#" id="export">export to Excel</a>

Then in jquery you have to create something like:

var grid = $(".grid.digital_edit").loadGrid({...}); //or similar - what you did to load the data into the grid

$('#export').click(function() {
    $.ajax({
        url: "export_to_excel.php", // the url of the php file that will generate the excel file
        data: grid.getData(), //or similar - based on the grid's API
        success: function(response){
            window.location.href = response.url;
        }
    })

});

The file export_to_excel.php will contain the code that generates the excel file:

1. This is where you'll initiate the PHPExcel class and create a file e.g. new_excel.xls
2. In your response array the $response['url'] will contain the absolute url to the newly created file. (http://www.example.com/files/new_excel.xls)

It may sound too complex, but try to separate your goals and achieve one at a time. E.g.

1. Create the button.
2. Then try to make a simple AJAX call when hitting the button.
3. Then create your export_to_excel.php file and try to work with the PHPExcel class.
4. Create a sample excel file based on the tutorials found.
5. Create an excel file based on your own data, but hard-coded in the php file.
6. Create the correct AJAX call that sends the wanted data to a php file.
7. Catch the correct AJAX call.
8. Pass the data from the AJAX call to the PHPExcel class.
9. Create the excel file.
10. Send back the url to the excel file.
11. Redirect the user to the url of the excel file.

EDIT

To help you a bit more: You need only one PHP script/file. The same one will receive the AJAX call from the javascript file, will generate the excel file and will return/respond the file url to the javascript file(in that order). A simplified example would be:

<?php
//export_to_excel.php

$data = $_POST['data']; // get the data from the AJAX call - it's the "data: grid.getData()" line from above

//... format the received data properly for the PHPExcel class

// later on in the same file:
$xls = new PHPExcel();
$xls->loadData($formattedData); //I assume that there is a similar loadData() method
$xls->exportToFile('/vaw/www/example.com/public/files/new_excel.xls'); // I assume that there is an exportToFile() method

$response = array(
    'success' => true,
    'url' => 'http://www.example.com/files/new_excel.xls'
);

header('Content-type: application/json');

// and in the end you respond back to javascript the file location
echo json_encode($response);

And then in javascript you display the file with this line

window.location.href = response.url; //response.url is $response['url'] from the PHP script