setlio setlio - 6 months ago 71
PHP Question

Dojo data grid to Excel file

I have a question about exporting dojo data grid to excel file. I have made it work with csv file using the dojo exporter and some php code. However, how do I make it to save as excel file. I now about pear and some other libraries, but there has to be similar solution to the one I am using for the csv. Also, when I create my own exporter in dojo, does it need to have something more specific then the code I am using for the csv exporter. Also, what do I need to change in the php code to make it save as xls. The code is below. Thanks a lot in advance.

My dojo exporter:

function exportCsv(){
var g = dijit.byId("grid");
g.exportGrid("csv",{
writerArgs: {
separator: ","
}
}, function(str){


var form = document.createElement('form');
dojo.attr(form, 'method', 'POST');
document.body.appendChild(form);
dojo.io.iframe.send({
url: "csv.php",
form: form,
method: "POST",
content: {exp: str},
timeout: 15000
});
document.body.removeChild(form);

});
}


My php code working with csv:

<?

$time = time();
header("Pragma: public");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=\"grid_$time.csv\"");
$exportedData = $_POST['exp'];


echo stripslashes($exportedData);
exit;
?>

Answer

Here is a nice PHP tool, well suited for the purpose.

http://www.phpclasses.org/package/1919-PHP-Stream-wrapper-to-read-and-write-MS-Excel-files.html

The setup is quite simple, you have most of it setup to passthrough the .csv file as download attachment allready, try the following code.

Conversion of CSV to XLS

First setup files csv-data and classes

require_once "excel.php"; 
define('_CSV_SEPARATOR_', ',');
// the excel class setsup xlsfile stream writer, point it to a tmp file
$export_file = "xlsfile://tmp/example.xls"; 
// the csv-contents must be put into an array, 
// serialized and sent to the stream
$import_file = "/path/to/CSV_FILE.csv";
$import=explode("\n", file_get_contents($import_file));
// column names should be first line
$header = array_shift($import);

Making sure, everything is looking nicely

$header = explode(_CSV_SEPARATOR_, $header);
for($i = 0; $i < count($header); $i++)
    $header[$i] = trim($header[$i]);

Looping lines in remaining contents of csv-data

// rest of text is data, split em up and list them with array indices,
// and associative names as key in the rowdata
$assocData = array();

foreach($import as $line) {
   $row = explode(_CSV_SEPARATOR_, $line);
   $rowData = array();
   $unknowncount = 0;
   for($i = 0; $i < count($row); $i++) {
       if(!empty($header[$i])) $column = $header[$i];
       else $column = 'UNK'.$unknowncount++;

       $rowData[$column] = trim($row[$i]);
   }
   $assocData[]=$rowData;
}

Now, we write data to the export tmp-file and conversion is done

$fp = fopen($export_file, "wb"); 
if (!is_resource($fp)) 
{ 
    die("Cannot open $export_file"); 
} 
fwrite($fp, serialize($assocData)); 
fclose($fp); 

Throughputting the outputted tmp-file to client

$export_file = "xlsfile://tmp/example.xls"; 
header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); 
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT"); 
header ("Cache-Control: no-cache, must-revalidate"); 
header ("Pragma: no-cache"); 
header ("Content-type: application/x-msexcel"); 
header ("Content-Disposition: attachment; filename=\"" . basename($export_file) . "\"" ); 
header ("Content-Description: PHP/INTERBASE Generated Data" ); 
readfile($export_file); 
exit; 

Good luck and enjoy :D

Comments