phpoffice/phpspreadsheet

phpoffice/phpspreadsheet is a successor of phpExcel. We recently developed an automated solution to pull all the backup success rate reports from Dell EMC Data Protection Advisor (DPA). The portal which displays the report was good but not enough to use that information further like sharing the reports, pivoting etc..

After trying several methods, found this library to be very useful. There aren’t many examples available on the internet to retrieve data from MySQL and write to XLSX file. Hope this blog might be helpful who would like to use this library for creating XLSX files. Fear not, it doesn’t throw errors while opening this file in MS Office Excel

Here is the PHP code

<?php
//install phpoffice/phpspreadsheet using composer
// 'composer require phpoffice/phpspreadsheet'  run this command in the root directory
require 'vendor/autoload.php';
//Connect to DB using below credentials
    $servername = "localhost";
    $username = "your db username";
    $password = "your db password";
    $dbname = "DB Name";
    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
//Query to fetch the data from the dB
    $query="SELECT * FROM `your table` where  DATE(date) =  CURDATE() ORDER BY your_column1 ASC, your_column2 DESC";
    $result = mysqli_query($conn, $query);
//Import the php office phpspreadsheet libraries
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
//create new spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//set the stye for the header
$styleArray = [
    'font' => [
        'bold' => true,
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'inside' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => ['argb' => 'FF000000'],
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        'rotation' => 90,
        'startColor' => [
            'argb' => 'FFA0A0A0',
        ],
        'endColor' => [
            'argb' => 'FFFFFFFF',
        ],
    ],
];
//Apply the above defined style
$spreadsheet->getActiveSheet()->getStyle('A1:M1')->applyFromArray($styleArray);
//Create header
$sheet->setCellValue('A1', 'SL No');
$sheet->setCellValue('B1', 'DATE');
$sheet->setCellValue('C1', 'REGION');
$sheet->setCellValue('D1', 'DATACENTER');
$sheet->setCellValue('E1', 'RID');
$sheet->setCellValue('F1', 'CUSTOMER');
$sheet->setCellValue('G1', 'COMPLETED');
$sheet->setCellValue('H1', 'SUCCEEDED');
$sheet->setCellValue('I1', 'FAILED');
$sheet->setCellValue('J1', 'ACTIVE');
$sheet->setCellValue('K1', 'SUCCESS RATE');
$sheet->setCellValue('L1', 'STRIKE');
$sheet->setCellValue('M1', 'SERVER');
//string to iterate rows
$str = 'A';
//i to increment the rows
$i = 1;
//Fetch rows from the array $result
while ($row = mysqli_fetch_row($result)){
//increment i to goto 2nd row
++$i;
//echo $str.$i;
$sheet->fromArray(
    $row,
    NULL,
    $str.$i
);
}
//Define outline border to the cells
$styleArray = [
    'borders' => [
        'outline' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => ['argb' => 'FF000000'],
        ],
    ],
];
//Apply border style to the cells
$spreadsheet->getActiveSheet()->getStyle('A1:M'.$i)->applyFromArray($styleArray);
//create a new xlsx file from the spreadsheet created as above
$writer = new Xlsx($spreadsheet);
//$writer->save('example.xlsx');
//Set the HTML headers to download the file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="consolidated_report.xlsx"');
header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
?>

If you enjoyed this post, I’d be very grateful if you’d help it spread by emailing it to a friend or sharing it on Twitter or Facebook. Thank you!

Here is the official phpoffice/phpspreadsheet documentation

https://phpspreadsheet.readthedocs.io/en/develop/

In the next blog, I’ll post about generating consolidated DPA reports from multiple servers for each data center.

Advertisements

Written by Vinay

Proud and Tolerant Indian

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s