How to generate an .xlsx using php

php excel export spreadsheet xlsx

129,852

Solution 1

$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 = Shuchkin\SimpleXLSXGen::fromArray( $books );
$xlsx->saveAs('books.xlsx');
//  $xlsx->downloadAs('books.xlsx');

Solution 2

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 an Excel-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):

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

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

Solution 3

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);

Solution 4

You can still use previous versions of PHPSpreadsheet if you are using old versions of PHP.

You have to include the version number of PHPSpreadsheet while installing it in your project directory.

This worked for me:

composer require phpoffice/phpspreadsheet:1.8.0

View more solutions

129,852

angel enanod

Updated on May 13, 2022

Comments

angel enanod about 2 years

I need to generate an Excel file with extension .xlsx.

Here is my simple code:

 $file = "test.xlsx";
 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
 header('Content-Disposition: attachment; filename='.$file);
 $content = "Col1\tCol2\tCol3\t\n";
 $content .= "test1\ttest1\ttest3\t\n";
 $content .= "testtest1\ttesttest2\ttesttest3\t\n";
 echo $content;

But I get this error when I open the generated file:

Excel cannot open the file 'test.xlsx' because the file format or file extension is not valid.

Any ideas?

Drew almost 8 years

That would effectivly create a CSV though. A .xls and .xlsx are different. If he were to create a CSV then fputcsv() would be better than what he is trying to use.
Trudeau Fernandes almost 8 years

I totally agree.. Just that i found the content very basic and a good candidate to be of a CSV format.
angel enanod almost 8 years

Yes, I've tried this and the .xls worked. I'll find out if it's on version. thanks
angel enanod almost 8 years

@TrudeauFernandes thanks for the suggestion but I need xlsx format.
angel enanod almost 8 years

I don't think it's on version, cause I tried to open .xlsx file and it opened.
JDuarteDJ almost 6 years

There is no reference to the include files, where do they come from?
Todd Hammer almost 4 years

Need a quick and easy solution to write an array out to XLSX and this fits the bill. Perfect. Thanks Sergey!
rockZ over 3 years

I sadly get only 0 byte xlsx files using that class
ashleedawg over 3 years

according to this (deleted) answer, PHPExcel is deprecated.
Ben over 3 years

syntax error in SimpleXLSXGen line 17 $this->sheets = [ ['name' => 'Sheet1', 'rows' => [] ] ]; I coundn't find newer version