2 Steps To Create Excel Spreadsheet In PHP – Simple Examples

Welcome to a quick tutorial and example on how to create Excel spreadsheets with PHP. Need to generate a report in an Excel Spreadsheet? Yes, we can actually do that quite easily.

In order to generate Excel files in PHP, we need to use a third-party library. PHPSpreadsheet is a good recommendation, and the easy way to get it is to use Composercomposer require phpoffice/phpspreadsheet. Thereafter, a code snippet to generate an Excel Spreadsheet:

  • require "vendor/autoload.php";
  • use PhpOffice\PhpSpreadsheet\Spreadsheet;
  • use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
  • $spreadsheet = new Spreadsheet();
  • $sheet = $spreadsheet->getActiveSheet();
  • $sheet->setCellValue("A1", "Hello World !");
  • $writer = new Xlsx($spreadsheet);
  • $writer->save("demo.xlsx");

That covers the essentials, but let us walk through a few more examples in this guide – Read on!

ⓘ I have included a zip file with all the sample code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes PHP Spreadsheet Useful Bits & Links
Tutorial Video The End

 

 

DOWNLOAD & NOTES

First, here is the download link to the example source code as promised.

 

QUICK NOTES

  • A copy of PHPSpreadsheet is not included in the zip file. Please download the latest version from their official website.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

CREATING XLSX SPREADSHEETS IN PHP

All right, let us now get into the example of creating an Excel spreadsheet in PHP.

 

STEP 1) DOWNLOAD PHPSPREADSHEET

  • PHP cannot generate Excel files natively, we need to use a third-party library called PHPSpreadsheet.
  • One of the easier ways to get PHPSpreadsheet is to use a package manager called Composer. A hassle to download and install that, but it’s a one-time effort… Plus, Composer does offer a ton of other packages.
  • Once Composer is installed – Fire up the command line, navigate to your project folder, and run composer require phpoffice/phpspreadsheet. Composer will automatically download the latest version into the vendor/ folder.
  • Check out the Official PHPSpreadsheet documentation for more.

 

 

STEP 2) PHP SIMPLE SPREADSHEET

2-create-spreadsheet.php
<?php
// (A) LOAD PHPSPREADSHEET
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET
$spreadsheet = new Spreadsheet();
 
// (C) GET WORKSHEET
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("Basic");
$sheet->setCellValue("A1", "Hello World !");
$sheet->setCellValue("A2", "Goodbye World !");
 
// (D) ADD NEW WORKSHEET + YOU CAN ALSO USE FORMULAS!
$spreadsheet->createSheet();
$sheet = $spreadsheet->getSheet(1);
$sheet->setTitle("Formula");
$sheet->setCellValue("A1", "5");
$sheet->setCellValue("A2", "6");
$sheet->setCellValue("A3", "=SUM(A1:A2)");
 
// (E) OUTPUT
$writer = new Xlsx($spreadsheet);

// (E1) SAVE TO A FILE ON THE SERVER
$writer->save("demoA.xlsx");
echo "OK!";

/* (E2) OR FORCE DOWNLOAD
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=\"demoA.xlsx\"");
header("Cache-Control: max-age=0");
header("Expires: Fri, 11 Nov 2011 11:11:11 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
$writer->save("php://output");
*/

Yep, the basic usage shouldn’t be too difficult to understand –

  • (A) Load and use the PHPSpreadsheet library.
  • (B) Create a new spreadsheet.
  • (C & D) Add your worksheets and populate the cells.
  • (E) Save or output the spreadsheet.

 

 

EXTRA) ARRAY TO SPREADSHEET

3-array-spreadsheet.php
<?php
// (A) LOAD PHPSPREADSHEET
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET
$spreadsheet = new Spreadsheet();

// (C) GET WORKSHEET
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("Arr");

// (D) DATA ARRAY TO WORKSHEET
$data = [
  ["Name", "Email", "Tel"],
  ["Jon Doe", "jon@doe.com", "123456"],
  ["Joe Doe", "joe@doe.com", "234567"],
  ["Joy Doe", "joy@doe.com", "345678"],
];
$cRow = 0; $cCol = 0;
foreach ($data as $row) {
  $cRow ++; // NEXT ROW
  $cCol = 65; // RESET COLUMN "A"
  foreach ($row as $cell) {
    $sheet->setCellValue(chr($cCol) . $cRow, $cell);
    $cCol++;
  }
}

// (E) OUTPUT
$writer = new Xlsx($spreadsheet);
$writer->save("demoB.xlsx");
echo "OK!"; 

As for writing an array to a Spreadsheet, this is really subjective to how the array is formatted. This is just a quick example of how a 2-dimension array can be “converted” into an Excel Spreadsheet.

 

ALTERNATIVE) CSV FILES

4-csv.php
<?php
// (A) DATA TO WRITE
$data = [
  ["Apple", "Orange", "Pear"],
  ["Grape", "Durian", "Papaya"]
];
 
// (B) WRITE TO CSV FILE
$file = fopen("fruits.csv", "w");
foreach ($data as $line) { fputcsv($file, $line); }
fclose($file);
echo "OK";

If all the above is too much, a quick alternative is to generate CSV files instead. It is heck a lot easier, with just 3 “main functions” to know:

  • fopen(FILE, MODE) to create a new CSV file.
  • fputcsv(FILE, LINE) to put a line into the CSV file. Take note of that LINE is an array.
  • fclose(FILE) to properly close and write the CSV file.

But of course, CSV is pure data – No formulas, no setting of cell colors, no column dimensions, and all the funky stuff.

 

 

USEFUL BITS & LINKS

Finally, here are some small extras that may be useful to you.

 

USEFUL LINKS & REFERENCES

 

TUTORIAL VIDEO

 

INFOGRAPHIC CHEAT SHEET

Create Excel Spreadsheet With PHP (Click To Enlarge)

 

THE END

Thank you for reading, and we have come to the end of this short tutorial. I hope this has helped you to create Excel files in PHP for your project, and if you have anything to share with this guide, please feel free to comment below. Good luck and happy coding!

8 thoughts on “2 Steps To Create Excel Spreadsheet In PHP – Simple Examples”

  1. When running 2-create-spreadsheet.php without any changes after using “composer require phpoffice/phpspreadsheet”. My system is a linux server on the google cloud.
    I get the error: PHP Fatal error: Uncaught PhpOffice\\PhpSpreadsheet\\Writer\\Exception: Could not open file “demoA.xlsx” for writing.
    Any help, I as I do need to get it to wotk.

    1. Found it was a permitting problem run the following command to fix the issue and the 2-create-spreadsheet.php script worked:
      sudo usermod -a -G www-data my_user_name
      sudo chgrp -R www-data /var/www
      sudo chmod -R g+w /var/www

  2. Hi, I encountered this problem while trying to run the codes,
    Warning: require(vendor/autoload.php): failed to open stream: No such file or directory in C:\wamp64\www\php\2-generate-xlsx.php on line 3
    ( ! ) Fatal error: require(): Failed opening required ‘vendor/autoload.php’ (include_path=’.;C:\php\pear’) in C:\wamp64\www\php\2-generate-xlsx.php on line 3

    Where can I obtain the vendor/autoload.php file? Thanks in advanced!

Leave a Comment

Your email address will not be published.