PhpExcel’s successor, PhpSpreadsheet, is a pure PHP library that provides a set of classes that allow you to read from and write to a variety of different spreadsheet file formats including Excel and Open Document Format/OASIS (.ods). In the Making the Switch from PhpExcel to PhpSpreadsheet tutorial, we learned how PhpSpreadsheet has evolved since its original incarnation. Today’s follow-up will explore PhpSpreadsheet’s formatting capabilities by recreating the following web page as an Excel spreadsheet:
Specifically, this tutorial will build on the work started in Export Data from Your Website as an Excel Spreadsheet, by duplicating the fonts, colors, and borders of the original web page.
Updating the download.php Code for the New PhpSpreadsheet Library
Here is the start of the download.php script updated for the PhpSpreadsheet library. It includes a number of namespaces that we’ll be needing, as well as a reference to the autoload.php library entry-point file:
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
require_once 'vendor/autoload.php';
$webstats = array(
array(
"Domain"=>"robgravelle.com",
"Status"=>"200 OK",
"Speed"=>0.57,
"Last Backup"=>"2017-10-27",
"SSL Certificate?"=>"No"
),
array(
"Domain"=>"buysci-fi.com",
"Status"=>"301 redirect detected",
"Speed"=>1.08,
"Last Backup"=>"2017-10-27",
"SSL Certificate?"=>"Yes"
),
array(
"Domain"=>"captains-blog.com",
"Status"=>"500 Server Error!",
"Speed"=>0.52,
"Last Backup"=>"2017-09-27",
"SSL Certificate?"=>"Yes"
)
);
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
$activeSheet = $spreadsheet->getActiveSheet();
$activeSheet->setTitle('Website Stats Page');
$activeSheet->getDefaultStyle()->getFont()->setName('Times New Roman');
$activeSheet->setCellValue('A1', 'Website Stats Page');
$activeSheet->getStyle("A1")->getFont()->setSize(16);
//output headers
$activeSheet->fromArray(array_keys($webstats[0]), NULL, 'A3');
Styling Cells using the Style::applyFromArray() Method
There are a few ways to modify a cell’s appearance; I find that the easiest is to get the existing style using getStyle() and then setting it via one of its many setter methods. We’ve already seen this technique in action when we set the page title font to 16 points using the the Font’s setSize() method. That’s great for setting a specific property, but sometimes we have many properties to set. In those instances, we can use the Style’s applyFromArray() method. It accepts an associative array where each key corresponds to an object property. For example, the first HTML table headers use a bold font and feature a solid gray background. We can set both properties as follows:
//output headers
$activeSheet->fromArray(array_keys($webstats[0]), NULL, 'A3');
//getStyle accepts a range of cells as well!
$activeSheet->getStyle('A3:E3')->applyFromArray(
array(
'fill' => array(
'type' => Fill::FILL_SOLID,
'color' => array('rgb' => 'E5E4E2' )
),
'font' => array(
'bold' => true
)
)
);
Setting the Columns and Rows Dynamically
Columns may be accessed either by their letter or numeric index, making them easy to iterate over. We won’t be doing that because we’re going to apply some column-specific formatting. Instead, we’ll iterate over the webstats array and increment a variable to keep track of the current row. The $row variable adds 5 to the numeric array key so that the first data row is inserted on the 5th row of the worksheet. We can then reference a cell by concatenating the $row to the column letter:
foreach($webstats as $key => $domain) {
$row = (int)$key+5;
$activeSheet->setCellValue('B'.$row, $domain['Domain']);
//...
}
Adding Hyperlinks
Excel spreadsheets support hyperlinks, just as web documents do. Setting a hyperlink follows a similar convention to styles: get the cell (or cells), get the hyperlink object, then set its URL:
$activeSheet->getCell('B'.$row)
->getHyperlink()
->setUrl('https://www.'.$domain['Domain'])
->setTooltip('Click here to visit site');
You can set other properties – like the tooltip – using method chaining.
Setting Colors based on the Domain Status
Take a look at the HTMl table in the Export Data from Your Website as an Excel Spreadsheet tutorial and notice the Domain Status column. It has three background colors based on the domain status: green for “200 OK”, orange for “301 redirect detected”, and red for “500 server error.” Rather than use an if statement, I mapped each status code’s first character – 2, 3, or 5 – to its RGB color as elements of an associative array. Then in the setter code I can capitalize on the fact that, in PHP, strings are actually stored as an array of characters. As such, the zero [0] index returns the first character:
//at top of file
$colors = array(
'2'=>'00FF00',
'3'=>'FFA500',
'5'=>'8B0000'
);
//in for loop
$activeSheet->setCellValue('B'.$row , $domain['Status']);
$activeSheet->getStyle('B'.$row)->applyFromArray(
array(
'fill' => array(
'type' => Fill::FILL_SOLID,
'color' => array('rgb' => $colors[$domain['Status'][0]] )
),
'font' => array(
'color' => array('rgb' => 'FFFFFF')
)
)
);
Auto-sizing Column Widths
PhpSpreadsheet supports iterating over a range of cells, which allows for things like column auto-sizing:
foreach (range('B','E') as $col) {
$activeSheet->getColumnDimension($col)->setAutoSize(true);
}
Configuring Cell Borders
Borders can be complicated to work with. Luckily the borders property has a sub-property called “allborders” that lets you apply border properties to every cell border within a given range:
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => Border::BORDER_MEDIUM,
'color' => array('argb' => '000000'),
),
),
);
$activeSheet->getStyle('A3:E'.$row)->applyFromArray($styleArray);
Here is the entire source code for the file that we worked on here today:
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
require_once 'vendor/autoload.php';
$colors = array(
'2'=>'00FF00',
'3'=>'FFA500',
'5'=>'8B0000'
);
$webstats = array(
array(
"Domain"=>"robgravelle.com",
"Status"=>"200 OK",
"Speed"=>0.57,
"Last Backup"=>"2017-10-27",
"SSL Certificate?"=>"No"
),
array(
"Domain"=>"buysci-fi.com",
"Status"=>"301 redirect detected",
"Speed"=>1.08,
"Last Backup"=>"2017-10-27",
"SSL Certificate?"=>"Yes"
),
array(
"Domain"=>"captains-blog.com",
"Status"=>"500 Server Error!",
"Speed"=>0.52,
"Last Backup"=>"2017-09-27",
"SSL Certificate?"=>"Yes"
)
);
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
$activeSheet = $spreadsheet->getActiveSheet();
$activeSheet->setTitle('Website Stats Page');
$activeSheet->getDefaultStyle()->getFont()->setName('Times New Roman');
$activeSheet->setCellValue('A1', 'Website Stats Page');
$activeSheet->getStyle("A1")->getFont()->setSize(16);
//output headers
$activeSheet->fromArray(array_keys($webstats[0]), NULL, 'A3');
$activeSheet->getStyle('A3:E3')->applyFromArray(
array(
'fill' => array(
'type' => Fill::FILL_SOLID,
'color' => array('rgb' => 'E5E4E2' )
),
'font' => array(
'bold' => true
)
)
);
foreach($webstats as $key => $domain) {
$row = (int)$key+5;
$activeSheet->setCellValue('B'.$row, $domain['Domain']);
$activeSheet->getCell('B'.$row)
->getHyperlink()
->setUrl('https://www.'.$domain['Domain'])
->setTooltip('Click here to visit site');
$activeSheet->setCellValue('B'.$row , $domain['Status']);
$activeSheet->getStyle('B'.$row)->applyFromArray(
array(
'fill' => array(
'type' => Fill::FILL_SOLID,
'color' => array('rgb' => $colors[$domain['Status'][0]] )
),
'font' => array(
'color' => array('rgb' => 'FFFFFF')
)
)
);
$activeSheet->setCellValue('C'.$row , $domain['Speed']);
$activeSheet->setCellValue('D'.$row , $domain['Last Backup']);
$activeSheet->setCellValue('E'.$row , $domain['SSL Certificate?']);
}
foreach (range('B','E') as $col) {
$activeSheet->getColumnDimension($col)->setAutoSize(true);
}
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => Border::BORDER_MEDIUM,
'color' => array('argb' => '000000'),
),
),
);
$activeSheet->getStyle('A3:E'.$row)->applyFromArray($styleArray);
// Redirect output to a client's web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="webstats.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;
This is a screenshot of the webstats.xlsx spreadsheet on Windows 10 (Note that the A column’s width was manually reduced in order to fit the image within this page):
