Creating an Excel Report from MySql using PHP_XLSXWriter

We’ll look at how to use PHP_XLSXWriter to generate an excel report from a MySQL database. The PHP_XLSXWriter library is intended to be lightweight and consume less memory.

Why Report using Excel?

Because the majority of individuals in your business are likely acquainted with Excel, provide a short (or no) learning curve throughout installation.

This allows you to mix data using formulae.

It provides easy-to-use charting functionality and several handy built-in features when compared to competing tools.

It’s also convenient because you can clip and paste into reports. Because most people create reports in Word, it’s simple to copy and paste data or charts from Excel into Word.

Using PHP_XLSXWriter, we will construct a web application that will generate an excel report from a MySQL database.

We will construct a table in the MySQL database, get entries from it, and display them in table format on a web page. We will send a link that will allow you to build an excel file from the table data. You will be prompted to save the created excel file. Then you may store the file at a convenient location.

Precondition

Mastering PHP
Knowledge of MySQL or other databases

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `productid` varchar(255) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(16,2) DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `products` (`id`, `productid`, `product_name`, `description`, `price`) VALUES
(1,	'P00001',	'Telepon',	'',	5000.00),
(2,	'P00002',	'Elektronik',	'',	600.00),
(3,	'P00003',	'Televisi',	'',	500.00),
(4,	'P00004',	'Router',	NULL,	5000.00),
(5,	'P00005',	'Iphone',	'Apple',	9999.00),
(6,	'P00006',	'Laptop',	NULL,	4000.00);

dbcon.php

<?php
$mysqli = mysqli_connect("localhost", "", "", "");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

report.php

<?php

include_once("dbcon.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');

// GETING PARAMETER POST / GET
// $json = file_get_contents('php://input');
// // decoding the received JSON and store into $obj variable.
// $obj  = json_decode($json, true);

// $paramname = $obj['paramname'];  


// start excel pointer
$writer = new XLSXWriter();
$writer->setAuthor('Some Author'); 

// Normal Style
$styles1 = array(
    'font' => 'Arial',
    'font-size' => 8,
    'valign' => 'center',
    'halign' => 'center', 'border' => 'left,right,top,bottom'
);

// BOLD Style for header
$styles2 = array(
    'font' => 'Arial',
    'valign' => 'center',
    'font-style' => 'bold',
    'halign' => 'center', 'border' => 'left,right,top,bottom'
);


//==============================================================
//===========TITLE ============================================
//==============================================================

$writer->writeSheetRow(
    'Sheet1',
    array('CONTOH LAPORAN'),
    $styles2
);

$writer->writeSheetRow(
    'Sheet1',
    array('')
);

$writer->writeSheetRow(
    'Sheet1',
    array('')
);


// header
$emparray = array();
$emparray[] = "No";
$emparray[] = "ProductId";
$emparray[] = "Product";
$emparray[] = "Description";
$emparray[] = "Price";
$writer->writeSheetRow(
    'Sheet1',
    $emparray,
    $styles2
);

$query = sprintf(
    "SELECT *
    FROM `products` 
    ORDER BY productid",
//    add param for filtering
//    $mysqli->real_escape_string($param), 
);

$result = $mysqli->query($query);

if (!$result) {

    $writer->writeSheetRow(
        'Sheet1',
        array('No data found'),
        $styles2
    );

} else {

    //create an array
    $emparray = array();
    $no = 1;

    while ($row = $result->fetch_assoc()) {

        $dataarray = array();
        $dataarray[] = $no;
        $dataarray[] = $row['productid'];
        $dataarray[] = $row['product_name'];
        $dataarray[] = $row['description'];
        $dataarray[] = number_format($row["price"]);


        $writer->writeSheetRow(
            'Sheet1',
            $dataarray,
            $styles1
        );

        $no += 1;

    }
}

$writer->writeSheetRow(
    'Sheet1',
    array('')
);
$writer->writeSheetRow(
    'Sheet1',
    array('')
);

$writer->writeSheetRow(
    'Sheet1',
    array('')
);

$writer->writeSheetRow(
    'Sheet1',
    array('Tutorial Laporan Excel')
);

$writer->writeSheetRow(
    'Sheet1',
    array('Website: bagi2info.com')
);

$writer->markMergedCell('Sheet1', $start_row = 0, $start_col = 0, $end_row = 0, $end_col = 2);

$writer->writeToStdOut();

// export to file and add date
// $filename = "laporan" . "-" . date('dmY') . ".xlsx";

// $writer->writeToFile($filename);

exit(0);

Referensi

https://github.com/mk-j/PHP_XLSXWriter