SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)

    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:
    Code:
     
    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
    Code:
     ����������������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��������������������������������������������������� �������!�������"�������#�������$�������%�������&�����
     ���  <!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.

    Code:
     $('#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:
    Code:
     <?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.
    Code:
     <?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.
    Code:
     <?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
    ictus==""

  2. #2
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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!

    Steve
    ictus==""


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •