How to use mysql loop with xlsxwriter php to export to Excel sheet in PHP

Question

I am trying to create a mysql query loop to implement it in the xlsxwriter php excel exporter but i am having hard time making it work, it generates the excel sheet but it says it is corrupted and not working.

This is the original file source code:

<?php
include_once("xlsxwriter.class.php");
ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

$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');

$rows = array(
    array('test','1','-50.5','2010-01-01 23:00:00','2012-12-31 23:00:00'),
    array('2003','=B1', '23.5','2010-01-01 00:00:00','2012-12-31 00:00:00'),
);

$writer = new XLSXWriter();
$writer->setAuthor('Some Author'); 
foreach($rows as $row)
    $writer->writeSheetRow('Sheet1', $row);
$writer->writeToStdOut();
//$writer->writeToFile('example.xlsx');
//echo $writer->writeToString();
exit(0);

And this is the code i have created, and you will notice that in the "WriteSheetRow" line, i have made some dummy data, just to check if it is working, and it is not!:

<?php
include "session.php";
include_once("files/excel/xlsxwriter.class.php");
ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

$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');

$writer = new XLSXWriter();

//define column headers
$headers = array('Product Id' => 'integer', 'Price' => 'price', 'Sale Price' => 'price', 'Sales Count' => 'integer', 'Sale Date' => 'string');
//write headers
$writer->writeSheetHeader('Sheet1', $headers);

            $sql = "SELECT * FROM customeracct WHERE tourid=1005 AND status=1";
            $result = $db->query($sql);
            if ($result->num_rows > 0) { while($customeracct = $result->fetch_assoc()) { 

                $contactid = $customeracct["contactid"];
                $contactsql = "SELECT * FROM contacts WHERE id=$contactid";
                $contactresult = $db->query($contactsql);
                $contactcustomer = $contactresult->fetch_assoc();

                //write rows to sheet1
                $writer->writeSheetRow('Sheet1',array('test','1','-50.5','2010-01-01 23:00:00','2012-12-31 23:00:00'));
            }}


$writer->setAuthor('Good Shepherd Travels'); 
exit(0);

Can you please help me check if it is correct, or if there is a better way to do it? I tried to make the loop inside the array, but it doesn't work to have a php conditional code inside an array.

Thank you.

score 1 · Accepted Answer · answered Oct 22 '18 at 10:22

After trying several methods with xlsxwriter, and by trying to tweak the answer in this question: how to export xlsx from mysql with php xlsxwriter

I was able to find a solution for my problem, and this is the code for people who are facing a similar problem like me, and hopefully it will benefit them:

<?php
include "session.php";
include_once("files/excel/xlsxwriter.class.php");
ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

    $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="SELECT * FROM customeracct WHERE tourid=1005 AND status=1";
    $result = $db->query($query); 
    //$rows = $result->fetch_assoc(); 
    $header = array(
      'ID'=>'integer',
      'Subject'=>'string',
      'Content'=>'string',
    );
    $writer = new XLSXWriter();
    $writer->writeSheetHeader('Sheet1', $header);
    $array = array();
    while ($row=$result->fetch_assoc())
    {
        $array[1] = $row['contactid'];
        $array[2] = $row['tourid'];
        $array[3] = $row['status'];
        $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);

The above answer is using mysqli instead of mysql, and also the arrays aren't variables $i, instead they are fixed, specially if you want customized report as i wanted. — Khader Handal, Oct 22 '18 at 10:23

score 0 · Answer 2 · answered Oct 22 '18 at 10:18

0

This link may be usefull. There are lot of way to do this. just search the google.

https://www.sitepoint.com/generate-excel-files-charts-phpexcel/

answered Oct 22 '18 at 10:18

Rumaiz Mohomed

23
6

Thank you for your link, It speaks about phpexcel, and i am using xlsxwriter. But thankfully i was able to find the solution and i just shared it with anyone would face a similar problem like mine. – Khader Handal Oct 22 '18 at 10:30

How to use mysql loop with xlsxwriter php to export to Excel sheet in PHP

2 Answers2