PDA

View Full Version : Create Excel file



nicobarten
5 Jul 2010, 12:13 AM
Hello!

I'm using a 3rd party class to create an xls file in php. The php file is called using AJAX with POST params.
See code:

js code:


var dataObject = new Object();
for (var i = 0; i < tabpanelStatistieken.items.items.length; i++) {
var grid = tabpanelStatistieken.items.items[i];
var title = grid.title; // naam werkblad
var sort = new Object(); // bevat rows array en fields array
var rows = new Array();
var fields = new Array();

var gridStore = grid.getStore();

gridStore.each(function (record) {
rows.push(record.json);
});

var columns = grid.getColumnModel().columns;
for (var n = 0; n < columns.length; n++) {
fields.push(columns[n].header);
}

sort['rows'] = rows;
sort['fields'] = fields;

dataObject[title] = sort;
}

var data = Ext.util.JSON.encode(dataObject);

Ext.Ajax.request({
url: 'data/excel.php',
params: {
sort: 'statistieken',
action: 'export',
data: data
}, success: function (response) {

}
});
excel.php:


<?php

/** PHPExcel */
require_once './excel/PHPExcel.php';

$columns = array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z");

if($_POST['sort'] == 'statistieken' && $_POST['action'] == 'export' && isset($_POST['data']))
{
$encoded_data = $_POST['data'];
$data_array = json_decode($encoded_data, true);

$sheets = array_keys($data_array);

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// door elke sheet heenlopen
for($i = 0; $i < count($sheets); $i++)
{
$sort = $data_array[$sheets[$i]];

$rows = $sort["rows"];
$fields = $sort["fields"];

// Set active sheet
$objPHPExcel->setActiveSheetIndex(0);

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle($sheets[$i]);

// Create headers
for($h = 0; $h < count($fields); $h++)
{
$objPHPExcel->getActiveSheet()->setCellValue($columns[$h] . "1", $fields[$h]);
}

// Add data
for($j = 0; $j < count($rows); $j++)
{
$row = $rows[$j];

for($k = 0; $k < count($row); $k++)
{
$item = $row[$k];

$objPHPExcel->getActiveSheet()->setCellValue($columns[$k] . ($j + 2), $item);
}
}
}

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Test.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}

?>


Excel.php does its work fine. However, normally it should create a new xls file and ask the user to save or open it. The problem is, the script doesn't do this, but returns the data to my js file as response. I guess that's because it's an AJAX call. How can i make it so (synchronous call?) that it don't return the data, but use it to ask the user to save the file?

mankz
5 Jul 2010, 12:22 AM
You can't post files using ajax, you could use an iframe though.

Animal
5 Jul 2010, 12:24 AM
As repeated many times here, you can't download a file using Ajax.

Otherwise, some malicious Javascript could steal your files.

You have to click a link who's URL is the URL of the server resource, or submit a form.

Both techniques have been widely discussed and used here.

Condor
5 Jul 2010, 12:33 AM
You forgot the 3rd option, setting the src of a hidden IFRAME (example here (http://www.sencha.com/forum/showthread.php?80036-SOLVED-Download-data-from-variable-with-a-browser-prompt&p=385087#post385087)).

Animal
5 Jul 2010, 12:54 AM
I don't really like non-accessible solutions.

Ideally, I'd offer an <a href="http://you-are-downloading-something">

That way, the user knows what is happening.

Or, if the data needed to be passed is very large, then offer a submit button with a (possibly hidden) form targetted at a hidden iframe.

I try to avoid using document.location programatically.

nicobarten
5 Jul 2010, 1:04 AM
The problem is that i use POST, not GET. So i guess i need an invicible form, but i'm not sure how to do this?

Animal
5 Jul 2010, 1:13 AM
Create a hidden form with some inputs in it, targeted on a hidden iframe.

nicobarten
5 Jul 2010, 1:51 AM
I tried it this way:



var body = Ext.getBody();
var frame = body.createChild({
tag: 'iframe',
cls: 'x-hidden',
id: 'hidden_iframe',
name: 'hidden_iframe'
});

var form = body.createChild({
tag: 'form',
cls: 'x-hidden',
id: 'hidden_form',
method: 'post',
action: 'data/excel.php',
target: '_blank'
});

var input = form.createChild({
tag: 'input',
cls: 'x-hidden',
id: 'hidden_input',
name: 'data',
type: 'hidden',
value: data
});

form.dom.submit();


The php file is called, it sends the data also, but somehow the data (string) received in the php file is only the first character of the whole string. The 'data' variable is a JSON encoded array / object. So the only data it receives is '{'.

Is there a maximal length for hidden input or something? Weird.

nicobarten
5 Jul 2010, 2:06 AM
Ah, found it.

I needed to escape the JSON encoded string.

pimo
11 Oct 2010, 7:07 AM
Could you write your final code?