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.

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

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

TUTORIAL VIDEO

 

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

<?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

<?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

<?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 of the above is too much, a quick alternative is to generate CSV files instead. It is 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.

 

 

 

LINKS & REFERENCES

• Here is a massive list of commonly used Excel formula that you might find useful.
• Display Excel As HTML In PHP – Code Boxx
• PHPSpreadsheet Quickstart Tutorial – Code Boxx
• Export Data From MYSQL To Excel In PHP – Code Boxx