How to generate an .xlsx using php

Question

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?

Sergey Shuchkin · Accepted Answer · 2022-05-12T12:34:22.523

36

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

edited May 12 '22 at 12:34

answered May 19 '20 at 20:59

Sergey Shuchkin

2,037
19
9

3

Need a quick and easy solution to write an array out to XLSX and this fits the bill. Perfect. Thanks Sergey! – Todd Hammer Jul 15 '20 at 15:45
1

I sadly get only 0 byte xlsx files using that class – rockZ Aug 26 '20 at 09:45
syntax error in SimpleXLSXGen line 17 $this->sheets = [ ['name' => 'Sheet1', 'rows' => [] ] ]; I coundn't find newer version – Ben Nov 04 '20 at 14:59
1

Works great ... just add `require_once __DIR__.'/simplexlsxgen/src/SimpleXLSXGen.php';` to the example given above. – TinaFrieda Dec 15 '22 at 14:21

score 11 · Answer 2 · answered Oct 23 '20 at 11:25

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

But those new buttons though.. · Answer 3 · 2020-09-18T00:35:20.173

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

score 1 · Answer 4 · answered Jun 24 '20 at 07:59

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

score 0 · Answer 5 · answered Jun 22 '16 at 04:06

0

May be the file is not compatible with the version of Excel that is used. Try with change your file extension 'xlsx' to 'xls and check it's working or not. if it's working then this file extension(.xlsx) is not compatible with the version of Excel that, you used.

answered Jun 22 '16 at 04:06

HHG

100
1
7

Yes, I've tried this and the .xls worked. I'll find out if it's on version. thanks – angel enanod Jun 22 '16 at 05:19
I don't think it's on version, cause I tried to open .xlsx file and it opened. – angel enanod Jun 22 '16 at 06:14

score 0 · Answer 6 · answered Jun 22 '16 at 04:09

0

The content is not a valid xlsx content. Try sending your content as a comma separated value content and use xls to open that

As a suggestion, If u can change your content, May be you could try this?

$file = "test.csv";
 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
 header('Content-Disposition: attachment; filename='.$file);
 $content = "Col1,Col2,Col3\n";
 $content .= "test1,test1,test3\n";
 $content .= "testtest,ttesttest2,testtest3\n";
 echo $content;

answered Jun 22 '16 at 04:09

Trudeau Fernandes

171
5

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. – Drew Jun 22 '16 at 04:22
I totally agree.. Just that i found the content very basic and a good candidate to be of a CSV format. – Trudeau Fernandes Jun 22 '16 at 04:27
@TrudeauFernandes thanks for the suggestion but I need xlsx format. – angel enanod Jun 22 '16 at 05:20

score -2 · Answer 7 · edited Feb 23 '18 at 18:34

-2

See my code, here I have made simple xlsx file

<?php

include 'PHPExcel/PHPExcel.php';
include 'PHPExcel/PHPExcel/Writer/Excel2007.php';

$objPHPExcel = new PHPExcel();


$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Trudeau');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Fernandes');

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

echo " Click here for gerate xlsx file <a href='test.xlsx'>clicking me</a>";
?>

and for me, it's working fine.

edited Feb 23 '18 at 18:34

Jason

15,017
23
85
116

answered Jun 22 '16 at 07:33

HHG

100
1
7

4

There is no reference to the include files, where do they come from? – JDuarteDJ Aug 10 '18 at 15:35
1

according to [this](https://stackoverflow.com/a/37958384/8112776) (deleted) answer, PHPExcel is deprecated. – ashleedawg Oct 23 '20 at 10:10

How to generate an .xlsx using php

7 Answers7

More Information:

Linked

Related