PHPExcel export HTML table to xlsx

Question

I need to export HTML table with data from database to xlsx file as easily as possible.

I've tried PHPExcel and some JS plugins, but unsuccessfully.

Is there any new solution?

Everything I found was almost 10 years old.

This is what I created:

My HTML form:

<form action="download.php">
    <input type="submit" value="Export" />
</form>

My PHPExcel code (file: download.php):

<?php

require_once "../Classes/PHPExcel.php";

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'hello world!');
$objPHPExcel->getActiveSheet()->setTitle('Chesse1');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="helloworld.xlsx"');
header('Cache-Control: max-age=0');

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

?>

When I click the button, it downloads the file, but I can't open it, because of "wrong format".

I also tried this video tutorial: https://www.youtube.com/watch?v=4dc3a4isHNE&t

Exporting Excel file worked but another problem appeared.

The problem I had was wrong charset. I need to use characters like: ě,š,č,ř,ž,ý,á,í etc. and these characters were totally messed up.

Answer 1

Updated answer:

<?php  
$conn = new mysqli('localhost', 'root', '');  
mysqli_select_db($conn, 'xlsexport');  
$setSql = "SELECT `ur_Id`,`ur_username`,`ur_password` FROM `tbl_user`";  
$setRec = mysqli_query($conn, $setSql);  
$columnHeader = '';  
$columnHeader = "Sr NO" . "\t" . "User Name" . "\t" . "Password" . "\t";  
$setData = '';  
while ($rec = mysqli_fetch_row($setRec)) {  
    $rowData = '';  
    foreach ($rec as $value) {  
        $value = '"' . $value['id'] . '"' . "\t".'"' . $value['ur_username'] . '"'."\t".'"' . $value['ur_password'] . '"';  
        $rowData .= $value;  
    }  
    $setData .= trim($rowData) . "\n";  
}  
header("Content-type: application/octet-stream");  
header("Content-Disposition: attachment; filename = User_Detail_Reoprt.xlsx");  
header("Pragma: no-cache");  
header("Expires: 0");  
echo ucwords($columnHeader) . "\n" . $setData . "\n";  
?>

Answer 2

I have changed the format and added buffer clean in your code.

<?php

require_once "../Classes/PHPExcel.php";

$objPHPExcel = new PHPExcel();

header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment;filename="helloworld.xls"');

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getCell('A20')->setValue("TEST PHPEXCEL");
$objPHPExcel->getActiveSheet()->setTitle('Chesse1');

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

?>

Sometimes Excel2007 gets conflict with Excel5. And it depends on the library file available in the PHPExcel. I had same problem of file not opening after download. So I changed to Excel5

Sample data has been inserted to check whether data arise in the excelsheet or not. You can replace it with the database variable.

Also ob_end_clean() helps to avoid the buffering of cache.

Hope this helps.

Answer 3

<?php
if (function_exists('mb_internal_encoding')) {
    $oldEncoding=mb_internal_encoding();
    mb_internal_encoding('latin1');
}

require_once 'addons/PHPExcel/PHPExcel/IOFactory.php'; //Update your Path for PHPExcel Class
$objPHPExcel = new PHPExcel();

$sheet = $objPHPExcel->getActiveSheet();
$sheet->setTitle("Template Title");

$sheet->getColumnDimension('A')->setWidth(30);

$sheet->SetCellValue('A1', 'hello world!');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

if (function_exists('mb_internal_encoding')){
    mb_internal_encoding($oldEncoding); 
}

header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=helloworld.xlsx");
header('Cache-Control: max-age=0');

$objWriter->save('php://output');
?>