Post Table -> SELF -> Export to Excel

Hi,

As the subject suggests I am trying to do something that seems to be quite difficult.

I have a php page that has AJAX that calls a php based model page that queries the database and returns a result set to the php page as a table. This works really well. As the table is dynamically created in the DOM, I need a way to export the data (that is displayed in the table) to a Export to Excel class when a download button is pressed. To do this I again used an AJAX .live() event to parse the table into arrays that I can feed to the Export to Excel Object. As the Export to Excel script need to run ‘header’ commands to save an .xls document, the script need to run the Export prior to output, also the AJAX can not run asynchronously as it needs to refresh the page to let the Export work.

All aspects of what I describe above seem to work. I can trace each stage using firebug. When I click download the headers show:

 
Cache-Control    must-revalidate, post-check=0, pre-check=0
Connection    Keep-Alive
Content-Disposition    attachment;filename=companies_2012-03-23.xls
Content-Transfer-Encoding    binary
Content-Type    application/download
Date    Fri, 23 Mar 2012 19:58:14 GMT
Expires    0
Keep-Alive    timeout=15, max=97
Pragma    public
Server    Apache/2.2.9 (Debian) PHP/5.2.6-1+lenny13 with Suhosin-Patch mod_ssl/2.2.9 OpenSSL/0.9.8g mod_perl/2.0.4 Perl/v5.10.0
Transfer-Encoding    chunked
X-Powered-By    PHP/5.2.6-1+lenny13
Request Headers
Accept    */*
Accept-Encoding    gzip, deflate
Accept-Language    en-us,en;q=0.5
Connection    keep-alive
Content-Length    904
Content-Type    application/x-www-form-urlencoded; charset=UTF-8
Cookie    Xpr3ssle%40dId=97809c435b204231c1274530a6fb890c
Host    www.test.net
Referer    http://www.test.net/administration_companies.php
User-Agent    Mozilla/5.0 (X11; Linux x86_64; rv:10.0.2) Gecko/20100101 Firefox/10.0.2
X-Requested-With    XMLHttpRequest
  

This appears to work, the only thing is that the file never appears and it seems as if the AJAX has not run asynchronously; although I can’t be sure. There are no errors and again in firebug the response shows

 ����������������Companies���������������Load�����
�Company Id������Company Legal Name������Company������Phone Number�����
�Fax Number������Address�����    �Apt/Unit#������City��    ����State/Province��
����Postal Code������Country��������������������?)�����!�Saffron Corporation International������SFCI
������--
������--������1929 Allen Parkway
������--������Houston
��    ����TX��
����������@�����
�United States���������
�����������������@ ������North Memorial Group�����    �NMG
������--
������--������1900 St. James Place�����    �Suite 300������Houston
������TX������������@�����
�United States��������������������������������������������������� �������!�������"�������#�������$�������%�������&�����
 &#65533;&#65533;&#65533;  <!doctype html>

So it appears and this shows the data encoded for excel that it does push it in the header.

Here are the relevant parts of code that make this work:

JQuery AND AJAX

When the download button is selected this inacts this function, gets the contents of the companies table and posts it using synchronous AJAX.


 $('#submit_download').live('click', function(e) {
       //alert($('#country').val());
       if($('#country').val() === '0'){
          alert('Please choose the country to load the companies to download');
          return;
       } else {
             var $table = $("#search_table"),
             $headerCells = $table.find("thead th"),
             $rows = $table.find("tbody tr");
             var head = [],
             rows = [],
             rows_of_rows = [];
          
            $headerCells.each(function(k,v) {
              head[head.length] = $(this).text();
            });
          
            $rows.each(function(row,v) {
                 $(this).find("td").each(function(cell,v) {
                      if (typeof rows[cell] === 'undefined') rows[cell] = []; 
                      rows[cell][row] = $(this).text();
                 });
            });
          $.ajax({
               async:   false
               , type: "POST"
               , url:  location.href
               , data: { head: head, rows: rows}
          });
         e.preventDefault();
    }
    }); 

Then in the export_companies_to_excel.php we have this:

 <?php
#include the export-xls.class.php file
require_once('libs/common/export-xls.class.php');
$date = date('Y-m-d');
$filename = "companies_$date.xls";// The file name you want any resulting file to be called.
#create an instance of the class
//echo '$results: '; echo var_dump($results) . '<br /><br />';
if(!$head){
$o_Redirect = new Redirect();
$o_Redirect->setRedirect('libs/common/security/auth/sign_in.php');
$o_Redirect->go();
}
$xls = new ExportXLS($filename, $rows);
#lets set some headers for top of the spreadsheet
$hdr = "Companies";
$xls->addHeader($hdr);
#add blank line
$hdr = null;
$xls->addHeader($hdr);
# header 
$xls->addRow($head);//add header to xls body
 $row = array();
 //var_dump($rows);
 $nums_of_rows = null;
 $nums_of_rows = count($rows[0]);
 $index_count = null;
 $index_count = count($rows);
 for($i=0;$i <= $nums_of_rows;$i++){
   for($y=0;$y <= $index_count; $y++){
     $row[] = $rows[$y][$i];
   }
 }
//var_dump($row);
$head = null;
$xls->addRow($row);//add data to xls body
$row = null;
$xls->sendFile();
?> 

The export-xls.class.php. I use this class in other parts of my application without any problems but in those cases I am not using AJAX post.

 <?php
class ExportXLS {
   private $filename;    //Filename which the excel file will be returned as
   private $headerArray;    // Array which contains header information
   private $bodyArray;    // Array with the spreadsheet body
   private $rowNo = 0;    // Keep track of the row numbers

   #Class constructor
   function ExportXLS($filename) { 
         $this->filename = $filename;
   }

   /* -------------------------
    START OF PUBLIC FUNCTIONS
     -------------------------*/
   public function addHeader($header) {
     #Accepts an array or var which gets added to the top of the spreadsheet as a header.
        if(is_array($header)){
               $this->headerArray[] = $header;
        }else{
               $this->headerArray[][0] = $header;
        }
   }

    public function addRow($row) {
    #Accepts an array or var which gets added to the spreadsheet body

          if(is_array($row)) {
                 #check for multi dim array
                 if(is_array($row[0])) {
                         foreach($row as $key=>$array) {
                                 $this->bodyArray[] = $array;
                         } 
                  }else{
                            $this->bodyArray[] = $row;
                  }         
          }else{
                  $this->bodyArray[][0] = $row;
          }
    }
    
   public function returnSheet() {
      # returns the spreadsheet as a variable
         #build the xls
         return $this->buildXLS();
   }

   public function sendFile() {
        #build the xls
        $xls = $this->buildXLS();
        #send headers
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header("Content-Disposition: attachment;filename=".$this->filename);
        header("Content-Transfer-Encoding: binary ");

        echo $xls;
        return 'Should have printed but didnt';
        exit;
   }


 /*--------------------------
    START OF PRIVATE FUNCTIONS
 --------------------------*/

   private function buildXLS() {
   # build and return the xls 
    
       #Excel BOF
       $xls = pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);

       #build headers
       if(is_array($this->headerArray)) {
             $xls .= $this->build($this->headerArray);
       }
       #build body
       if(is_array($this->bodyArray)) {
              $xls .= $this->build($this->bodyArray);
       }
       $xls .= pack("ss", 0x0A, 0x00);
       return $xls;
   }

   private function build($array) {
   #build and return the headers 

       foreach($array as $key=>$row) {
              $colNo = 0;
               foreach($row as $key2=>$field) {
                     if(is_numeric($field)) {
                             $build .= $this->numFormat($this->rowNo, $colNo, $field);
                     }else{
                             $build .= $this->textFormat($this->rowNo, $colNo, $field);
                     }
                     $colNo++;
              }
              $this->rowNo++;
        }
       return $build;
   }

   private function textFormat($row, $col, $data) {
   # format and return the field as a header
         $data = utf8_decode($data);
         $length = strlen($data);
         $field = pack("ssssss", 0x204, 8 + $length, $row, $col, 0x0, $length);
         $field .= $data;
         return $field; 
   }
        
   private function numFormat($row, $col, $data) {
   # format and return the field as a header
                $field = pack("sssss", 0x203, 14, $row, $col, 0x0);
                $field .= pack("d", $data); 
         return $field; 
   }
}
?>  

And Finally the top of the Companies.php script that makes use of the AJAX SELF posted data. When I mean SELF posted the AJAX originally posts the data from this same Companies.php script.

 <?php 
require_once('./libs/page_queries/pdo_db.php');
require_once('libs/page_queries/pq_admin_companies.php');
require_once('libs/includes/utilities.php');
require_once('./libs/common/security/auth/Redirect.php');//include redirect class
function __autoload($class_name){
    require_once './libs/common/security/auth/' .$class_name . '.php';
}
if($_POST['head'] and $_POST['rows']){
   $head = $_POST['head'];
   $rows = $_POST['rows'];
    require_once('libs/includes/export_companies_to_excel.php');
} 

I have been troubleshooting this for quite a while and hope that one of you can give me a suggestion. I really would like NOT TO CHANGE the AJAX post of the table contents so can you suggest how I might get the AJAX and PHP to reload the page so the header methods can run?

Steve

I solved this, but not in a particularly pretty way. I use jQuery Ajax to SELF post then I grab the data, serialize it, insert it into the database and write the serialized_data id into a session then I re-direct to the export page and it does its’ thing.

GRRRRRRRR! :slight_smile:

Steve