how to export xlsx from mysql with php xlsxwriter

Question

I use PHP_XLSXWriter class to export xlsx from mysql

include_once('xlsxwriter.class.php');
$filename = "example.xlsx";
header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');   
$query="my query here";
$result = mysql_query($query); 
$rows = mysql_fetch_assoc($result); 
$header = array(
  'ID'=>'integer',
  'Subject'=>'string',
  'Content'=>'string',
);
$writer = new XLSXWriter();
$writer->writeSheetHeader('Sheet1', $header);
foreach($rows as $row)
    $writer->writeSheetRow('Sheet1', $row);
//$writer->writeSheet($rows,'Sheet1', $header);//or write the whole sheet in 1 call 
$writer->writeToStdOut();
//$writer->writeToFile('example.xlsx');
//echo $writer->writeToString();
exit(0);

the export xlsx only has header, no data, what's the problem of my code?

Problem of your code is you're using unsupported, unmaintained, insecure, and deprecated database functions. And I'm not going to look any further than that! http://stackoverflow.com/q/12859942/1255289 — miken32, Mar 31 '17 at 02:14
Though I would hope the color of your code above will give you some hint what's wrong.... — miken32, Mar 31 '17 at 02:15

score 4 · Accepted Answer · answered May 25 '17 at 01:49

finally, those codes work.

include_once('xlsxwriter.class.php');
    $filename = "example.xlsx";
    header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    header('Content-Transfer-Encoding: binary');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');   
    $query="my query here";
    $result = mysql_query($query); 
    $rows = mysql_fetch_assoc($result); 
    $header = array(
      'ID'=>'integer',
      'Subject'=>'string',
      'Content'=>'string',
    );
    $writer = new XLSXWriter();
    $writer->writeSheetHeader('Sheet1', $header);
    $array = array();
    while ($row=mysql_fetch_row($result))
    {
        for ($i=0; $i<mysql_num_fields($result); $i++ )
        {
        $array[$i] = $row[$i];
        //$array[$i] = strip_tag($row[$i],"<p> <b> <br> <a> <img>");
        }
        $writer->writeSheetRow('Sheet1', $array);
    };

    //$writer->writeSheet($array,'Sheet1', $header);//or write the whole sheet in 1 call    

    $writer->writeToStdOut();
    //$writer->writeToFile('example.xlsx');
    //echo $writer->writeToString();
    exit(0);

Nice solution. But i want insert image. How to do? – Nitin Karale Oct 16 '18 at 13:46 — Nitin Karale, Oct 16 '18 at 13:46

score 0 · Answer 2 · edited Jul 16 '21 at 00:12

0

try this one https://github.com/WeiB71/table2excel

function exportTableToExcel(tableName,fileName) {
    var table2excel = new Table2Excel();
    table2excel.export($("#" + tableName), fileName);
}

edited Jul 16 '21 at 00:12

Sven Eberth

3,057
12
24
29

answered Jul 15 '21 at 21:15

WeiB

1

Welcome to StackOverflow. While this code may answer the question, providing additional context regarding *how* and/or *why* it solves the problem would improve the answer's long-term value. – Sven Eberth Jul 16 '21 at 00:12

how to export xlsx from mysql with php xlsxwriter

2 Answers2

Linked