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