How to generate an .xlsx using php

SimpleXLSXGen

$books = [    ['ISBN', 'title', 'author', 'publisher', 'ctry' ],    [618260307, 'The Hobbit', 'J. R. R. Tolkien', 'Houghton Mifflin', 'USA'],    [908606664, 'Slinky Malinki', 'Lynley Dodd', 'Mallinson Rendel', 'NZ']];$xlsx = SimpleXLSXGen::fromArray( $books );$xlsx->saveAs('books.xlsx');//  $xlsx->downloadAs('books.xlsx');

As others have mentioned, PhpSpreadsheet provides a nice library for this. Assuming you have it installed via composer and the vendor/autoload.php has been included in your project, you can use the function below to generate an xlsx from an array of arrays. I've included extensive comments here to help teach beginners about how PhpSpreadsheet works and what the code is doing:

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();    // autosize all columns to content width    for ($i = 'A'; $i <= $last_column; $i++) {        $doc->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);    }    // 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) {        // if there are user supplied formats, set each column format accordingly        // $formats should be an array with column letter as key and one of the PhpOffice constants as value        // https://phpoffice.github.io/PhpSpreadsheet/1.2.1/PhpOffice/PhpSpreadsheet/Style/NumberFormat.html        // EXAMPLE:        // ['C' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00, 'D' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00]        foreach ($formats as $col => $format) {            $doc->getActiveSheet()->getStyle($col . $offset . ':' . $col . $last_row)->getNumberFormat()->setFormatCode($format);        }    }    // write and save the file    $writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($doc);    $writer->save($filename);}

EXAMPLE USAGE:

$rows = [    ['sku' => 'A123', 'price' => '99'],    ['sku' => 'B456', 'price' => '5.35'],    ['sku' => 'C789', 'price' => '17.7']];$keys = array_keys(current($rows));$formats = ['B' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00];writeXLSX('pricelist.xlsx', $rows, $keys, $formats);

After trying a few options, I found that PHP_XLSX_Writer suited my needs.

PHP_XLSX_Writer-...designed to be lightweight, minimal memory usage, generates anExcel-compatible workbook in XLSX format, with basic features supported:

 - supports PHP 5.2.1+ - takes 'UTF-8' characters (or encoded input) - multiple worksheets - supports currency/date/numeric cell formatting, simple formulas - supports basic cell styling - supports writing huge 100K+ row spreadsheets

^{(Adapted from the library's GitHub repository)}

Here's an working example demonstrating a few features on 3 worksheets (tabs):

1. First create a file called xlsxwriter.class.php containing the code found here.

2. Create another PHP file (in the same folder) containing:

    require('xlsxwriter.class.php'); $fname='my_1st_php_excel_workbook.xlsx'; $header1 = [ 'create_date' => 'date',              'quantity' => 'string',              'product_id' => 'string',              'amount' => 'money',              'description' => 'string' ]; $data1 = [ ['2021-04-20', 1, 27, '44.00', 'twig'],            ['2021-04-21', 1, '=C1', '-44.00', 'refund'] ]; $data2 = [ ['2','7','ᑌᑎIᑕᗝᗪᗴ ☋†Ϝ-➑'],            ['4','8','😁'] ]; $styles2 = array( ['font-size'=>6],['font-size'=>8],['font-size'=>10],['font-size'=>16] ); $writer = new XLSXWriter(); $writer->setAuthor('Your Name Here'); $writer->writeSheet($data1,'MySheet1', $header1);  // with headers $writer->writeSheet($data2,'MySheet2');            // no headers $writer->writeSheetRow('MySheet2', $rowdata = array(300,234,456,789), $styles2 ); $writer->writeToFile($fname);   // creates XLSX file (in current folder)  echo "Wrote $fname (".filesize($fname)." bytes)<br>"; // ...or instead of creating the XLSX you can just trigger a // download by replacing the last 2 lines with: // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // header('Content-Disposition: attachment;filename="'.$fname.'"'); // header('Cache-Control: max-age=0'); // $writer->writeToStdOut();

More Information:

• GitHub repository
• author's website
• lots more examples