PHPSpreadsheet Beginner's Tutorial

0-PHPSPREADSHEET.MD

PHPSPREADSHEET BEGINNER EXAMPLES

https://code-boxx.com/phpspreadsheet-beginner-tutorial/

PHPSPREADSHEET

https://github.com/PHPOffice/PhpSpreadsheet

NOTES

A copy of PHPSpreadsheet is not included. Download Composer, navigate to your project in the command line, and run composer require phpoffice/phpspreadsheet.

LICENSE

Copyright by Code Boxx

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

1-hello-spreadsheet.php

<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// (C) SET CELL VALUE
$sheet->setCellValue("A1", "Hello World!");

// (D) SAVE TO FILE
$writer = new Xlsx($spreadsheet);
$writer->save("1-hello.xlsx");

2-force-download.php

<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// (C) SET CELL VALUE
$sheet->setCellValue("A1", "Hello World!");

// (D) SEND DOWNLOAD HEADERS
// ob_clean();
// ob_start();
$writer = new Xlsx($spreadsheet);
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=\"2-download.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");
// ob_end_flush();

3-load.php

<?php
// (A) LOAD PHPSPREADSHEET LIBRARY
require "vendor/autoload.php";

// (B) READ FILE
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load("1-hello.xlsx");

// (C) READ CELLS
$sheet = $spreadsheet->getSheet(0);
$cell = $sheet->getCell("A1");
$value = $cell->getValue();
echo $value;

4-worksheets.php

<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) FIRST WORKSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("First Sheet");
$sheet->setCellValue("A1", "Hello World!");

// (C) ADD WORKSHEET
$spreadsheet->createSheet();

// (C1) WORKSHEETS ARE IN RUNNING SEQUENCE NUMBER - 0, 1, 2, ...
$sheet = $spreadsheet->getSheet(1);

// (C2) ALTERNATIVELY, WE CAN GET BY NAME (AFTER WE SET THE TITLE)
//$sheet = $spreadsheet->getSheetByName("TITLE");

// (C3) SET WORKSHEET TITLE + CELL VALUE
$sheet->setTitle("Second Sheet");
$sheet->setCellValue("A1", "Foo Bar!");

// (D) COPY WORKSHEET
$evilClone = clone $spreadsheet->getSheet(0);
$evilClone->setTitle("Evil Clone");
$spreadsheet->addSheet($evilClone);

// (E) DELETE WORKSHEET
// $spreadsheet->removeSheetByIndex(0);

// (F) GET TOTAL NUMBER OF WORKSHEETS
// $total = $spreadsheet->getSheetCount();

// (G) SAVE TO SERVER
$writer = new Xlsx($spreadsheet);
$writer->save("4-worksheets.xlsx");

5-cells.php

<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE WORKSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("First Sheet");

// (C) SINGLE CELL
// (C1) GET SINGLE CELL THEN SET VALUE
$cell = $sheet->getCell("A1");
$cell->setValue("Hello");

// (C2) MORE WAYS SET VALUE
$sheet->setCellValue("A2", "World!");
$sheet->setCellValueByColumnAndRow(1, 3, "FOO!");

// (C3) GET VALUE
$cell = $sheet->getCellByColumnAndRow(1, 2);
$value = $cell->getValue();

// (C4) GET HIGHEST ROW + COL
$highestRow = $sheet->getHighestRow();
$highestCol = $sheet->getHighestColumn();

// TIP - You can use $highestRow $highestCol to loop through the cells.
// for ($i=0; i<$highest; i++) { ... }

// (D) RANGE OF CELLS
// (D1) GET SELECTED RANGE INTO AN ARRAY
$data = $sheet->rangeToArray("A1:A3");

// (D2) SET DATA FROM ARRAY INTO CELLS
$data = [100, 53, 86];
$data = array_chunk($data, 1);
$sheet->fromArray($data, null, "B1");

// (E) FORMULAS ACCEPTED - JUST AS IN EXCEL
$sheet->setCellValue("B4", "=SUM(B1:B3)");

// (F) SAVE TO SERVER
$writer = new Xlsx($spreadsheet);
$writer->save("5-cells.xlsx");

6-structure.php

<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET & DUMMY DATA
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue("A1", "Hello, this is a very very long string.");
$sheet->setCellValue("A2", "World!");
$sheet->setCellValue("A3", "Foo");
$sheet->setCellValue("A4", "Bar");

// (C) MERGE & UNMERGE CELLS
$sheet->mergeCells("A1:D1");
$sheet->mergeCells("A2:B2");
$sheet->unmergeCells("A2:B2");

// (D) INSERT ROW & COL
$sheet->insertNewColumnBefore("A", 1); // 1 new column before column A
$sheet->insertNewRowBefore(3, 1); // 1 new row before row 3

// (E) VISIBILITY
$sheet->getColumnDimension("A")->setVisible(false);
// $sheet->getColumnDimension("A")->setVisible(true);
$sheet->getRowDimension(4)->setVisible(false);
// $sheet->getRowDimension(4)->setVisible(true);

// (F) WIDTH & HEIGHT
$sheet->getRowDimension("4")->setRowHeight(100);
$sheet->getColumnDimension("C")->setWidth(100);

// (G) SAVE TO FILE
$writer = new Xlsx($spreadsheet);
$writer->save("6-structure.xlsx");

7-formatting.php

<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET & DUMMY DATA
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue("B1", "Hello");
$sheet->setCellValue("B2", "World!");
$sheet->setCellValue("B3", "Foo");
$sheet->setCellValue("B4", "Bar");
$sheet->getRowDimension("3")->setRowHeight(50);

// (C) SET STYLE
$styleSet = [
  // (C1) FONT
  "font" => [
    "bold" => true,
    "italic" => true,
    "underline" => true,
    "strikethrough" => true,
    "color" => ["argb" => "FFFF0000"],
    "name" => "Cooper Hewitt",
    "size" => 22
  ],

  // (C2) ALIGNMENT
  "alignment" => [
    "horizontal" => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
    // \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT
    // \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER
    "vertical" => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_BOTTOM
    // \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP
    // \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
  ],

  // (C3) BORDER
  "borders" => [
    "top" => [
      "borderStyle" => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
      "color" => ["argb" => "FFFF0000"]
    ],
    "bottom" => [
      "borderStyle" => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
      "color" => ["argb" => "FF00FF00"]
    ],
    "left" => [
      "borderStyle" => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
      "color" => ["argb" => "FF0000FF"]
    ],
    "right" => [
      "borderStyle" => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
      "color" => ["argb" => "FF0000FF"]
    ]
    /* ALTERNATIVELY, THIS WILL SET ALL
    "outline" => [
      "borderStyle" => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
      "color" => ["argb" => "FFFF0000"]
    ]*/
  ],

  // (C4) FILL
  "fill" => [
    // SOLID FILL
    "fillType" => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
    "color" => ["argb" => "FF110000"]

    /*  GRADIENT FILL
    "fillType" => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
    "rotation" => 90,
    "startColor" => [
      "argb" => "FF000000",
    ],
    "endColor" => [
      "argb" => "FFFFFFFF",
    ]*/
  ]
];
$style = $sheet->getStyle("B3");
// $style = $sheet->getStyle("B1:B4");
$style->applyFromArray($styleSet);

// (D) SAVE TO FILE
$writer = new Xlsx($spreadsheet);
$writer->save("7-formatting.xlsx");

8-headers.php

<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET & DUMMY DATA
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue("A1", "First");
$sheet->setCellValue("B1", "Second");
$sheet->setCellValue("C1", "Third");
$sheet->setCellValue("A2", "Hello");
$sheet->setCellValue("A3", "World!");
$sheet->setCellValue("A4", "Foo");
$sheet->setCellValue("B2", 12);
$sheet->setCellValue("B3", 34);
$sheet->setCellValue("B4", 56);
$sheet->setCellValue("C2", true);
$sheet->setCellValue("C3", false);
$sheet->setCellValue("C4", true);

// (C) AUTO FILTER
$sheet->setAutoFilter("A1:C4");

// (D) FREEZE PANE
$sheet->freezePane("C2");

// (E) SAVE TO FILE
$writer = new Xlsx($spreadsheet);
$writer->save("8-headers.xlsx");