Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to export html table to excel using PHPExcel?

Because it is hard to deal with different standards among different browsers, I give up trying to export html table using js or jQuery. I wonder if I can POST the table in html back to server and generate an .xls file on the server for user to download.

Now on the server side using PHPExcel, my code is like this:

$filename = "DownloadReport";
$table = $_POST['table'];

ini_set('zlib.output_compression','Off'); 
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");
//the folowing two lines make sure it is saved as a xls file
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename='.$filename);

$objReader = PHPExcel_IOFactory::createReader('HTML');
$objPHPExcel = $objReader->load($table);

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

exit;

The problem is I cannot load html table directly. How can I deal with that?

And one more question is that as I set the headers in php, the file is not downloading when I click the button. Actually I can view all the attributes of Header of the POST response, and also the content of response(in FireBug), which are all correct.

like image 848
Yongyiw Avatar asked Jun 04 '14 21:06

Yongyiw


1 Answers

To put contents directly into $objPHPExcel you would need to create a worksheet and then set values cell by cell, which is not what you want.

To insert a whole HTML table, you must read it from an HTMLReader

In the code below the Writer will be used to output the content to file

$filename = "DownloadReport";
$table    = $_POST['table'];

// save $table inside temporary file that will be deleted later
$tmpfile = tempnam(sys_get_temp_dir(), 'html');
file_put_contents($tmpfile, $table);

// insert $table into $objPHPExcel's Active Sheet through $excelHTMLReader
$objPHPExcel     = new PHPExcel();
$excelHTMLReader = PHPExcel_IOFactory::createReader('HTML');
$excelHTMLReader->loadIntoExisting($tmpfile, $objPHPExcel);
$objPHPExcel->getActiveSheet()->setTitle('any name you want'); // Change sheet's title if you want

unlink($tmpfile); // delete temporary file because it isn't needed anymore

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // header for .xlxs file
header('Content-Disposition: attachment;filename='.$filename); // specify the download file name
header('Cache-Control: max-age=0');

// Creates a writer to output the $objPHPExcel's content
$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$writer->save('php://output');
exit;
like image 114
Edson Horacio Junior Avatar answered Oct 30 '22 08:10

Edson Horacio Junior