In many occasions when a data table is queried from database and/or displayed on the screen of web application, there is a need to enable the user to download the table, preferably as an MS Excel file.
Traditionally, the method of doing so is to export the table as CSV (comma-separated values) or TSV (tab-separated values) text file, and to use MS Excel to import the CSV or TSV into a worksheet.
There are a lot of shortcomings using this method, including but not limited to:
- You need to open a new webpage in order to perform the file export, which looks weird to the user experience.
- The separator character might be different for different users. Some uses comma (","), some uses semicolon (";"), some uses vertical bar pipe ("|"), and so on. Libre Calc in LibreOffice is more flexible in handling the separator character during data import, whereas in MS Excel, if the separator character in the file is not the same with your computer's regional format setting, you will probably get rows of long data in single column.
- The CSV/TSV is in plain text, and no cell formatting can be made.
- You need to properly escape and handle the data to be exported, to ensure your data export is successful without error.
- Numeric fields will most probably be treated as number in the worksheet, although you might want to treat them as text string instead. Common occasions including handling of telephone numbers, batch numbers, serial numbers, etc.
Over the years, there is a trick to export a TSV as XLS (old Excel file format), by putting these 2 lines in the page header:
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
However, the exported file is actually just a TSV instead of real XLS file. Newer version of MS Excel will actually prompt out warning message when you try to open such exported XLS file.
A much better and proper method is to use the PhpSpreadsheet library to generate the real MS Excel file in xlsx (Microsoft Open XML Spreadsheet) format.
By using this method, you can export the Excel file without the need of opening a new webpage that confuse the user. You can also perform many Excel file manipulation by using the functions, classes and methods provided by the PhpSpreadsheet library.
Step 1: Install PhpSpreadsheet library using composer, if you haven't done so.
If you are using
Laragon WAMP platform, you can install the library by opening the
Terminal window (the 4th button in Laragon control panel) and type the following command:
composer require phpoffice/phpspreadsheet
Step 2: Add these 3 lines at the beginning of your PHP script.
require '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
Step 3: Prepare your dataset in an array. Normally, this is queried from SQL database server using SELECT statement. Note that in order to use the writeXLSX function with source codes in Step 4 below, the dataset needs to be stored in an array instead of an object.
$db = new ezSQL_mysqli(MY_USR, MY_PWD, MY_DB, MY_HOST);
$sql = "SELECT * FROM ".MY_TABLE;
$items =$db->get_results($sql, $output=ARRAY_A);
$keys = array_keys(current($items));
Step 4: Put the following function somewhere in your script.
function writeXLSX($filename, $rows, $keys = [], $formats = []) {
// instantiate the class
$doc = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $doc->getActiveSheet();
// $keys are for the header row. If they are supplied we start writing at row 2
if ($keys) {
$offset = 2;
} else {
$offset = 1;
}
// write the rows
$i = 0;
foreach($rows as $row) {
$doc->getActiveSheet()->fromArray($row, null, 'A' . ($i++ + $offset));
}
// write the header row from the $keys
if ($keys) {
$doc->setActiveSheetIndex(0);
$doc->getActiveSheet()->fromArray($keys, null, 'A1');
}
// get last row and column for formatting
$last_column = $doc->getActiveSheet()->getHighestColumn();
$last_row = $doc->getActiveSheet()->getHighestRow();
// if $keys, freeze the header row and make it bold
if ($keys) {
$doc->getActiveSheet()->freezePane('A2');
$doc->getActiveSheet()->getStyle('A1:' . $last_column . '1')->getFont()->setBold(true);
}
// format all columns as text
$doc->getActiveSheet()->getStyle('A2:' . $last_column . $last_row)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
if ($formats) {
foreach ($formats as $col => $format) {
$doc->getActiveSheet()->getStyle($col . $offset . ':' . $col . $last_row)->getNumberFormat()->setFormatCode($format);
}
}
// enable autofilter
$doc->getActiveSheet()->setAutoFilter($doc->getActiveSheet()->calculateWorksheetDimension());
// autosize all columns to content width
for ($i = 'A'; $i <= $last_column; $i++) {
$doc->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);
}
// write and save the file
$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($doc);
$writer->save($filename);
}
Step 5: Call the writeXLSX function to generate your MS Excel file.
writeXLSX(EXCELFILE, $items, $keys);
That's all about it. Pretty easy and straightforward, isn't it?
