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!

 

 

TLDR – QUICK SLIDES

Fullscreen Mode – Click Here

 

TABLE OF CONTENTS

 

 

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.

 

 

 

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

 

EXAMPLE CODE DOWNLOAD

Click here for the 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.

 

 

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

 

TUTORIAL VIDEO

 

INFOGRAPHIC CHEAT SHEET