1. Products
  2.   Spreadsheet
  3.   PHP
  4.   PHPSpreadsheet

PHPSpreadsheet

 
 

Open Source PHP Library for Spreadsheet Files

A Powerful Open Source PHP API allows Developers to Read, Write, Manipulate and Process Microsoft Excel & LibreOffice Calc files.

What is PHPSpreadsheet?

Managing and manipulating spreadsheet data is a crucial part of many applications, especially in business and enterprise environments. From automating data entry to generating dynamic reports, the ability to handle spreadsheets programmatically adds significant value to software solutions. PHPSpreadsheet is an open-source library that includes a set of classes to allow you to interact with and utilize various spreadsheet file formats of Microsoft Excel and LibreOffice Calc. PHPSpreadsheet supports spreadsheets that contain one or more worksheets, containing cells to hold data of various types, such as numbers, formulas, image, etc.

PHPSpreadsheet is an open-source PHP library that enables developers to read, create, edit, and write spreadsheet documents. It replaces its predecessor, PHPExcel, and provides a more modern architecture with enhanced performance and additional features. The library supports various spreadsheet formats and includes functionalities for formatting cells, performing calculations, and generating complex reports, among others.

Previous Next

Getting Started with PHPSpreadsheet

First of all you need to have PHP version 7.1 or newer to develop using PhpSpreadsheet.  The recommend way to install PhpSpreadsheet into your project is by using composer. Open the terminal in your project root directory and run the command:

Here is the command

 composer require phpoffice/phpspreadsheet

Composer offers a convenient installer that you can execute directly from the command line. Feel free to download this file or review it on GitHub. There are two ways to install Composer. Install it locally as part of your project, or globally as a system wide executable.

Work with Spreadsheet Cells

PHPSpreadsheet allows you to access cell and set its value by coordinates. You can also create a new cell and set up a formula in it. You can also configure the cell to occupy various types of data, such as date, time, and number with leading zeros. PHPSpreadsheet also allows you to set a range of cells from an array, as well as looping through cells using iterators or indexes. You can also use value binders for allowing user-friendly data entry.

Create a new XLSX - PHP

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Create a new Spreasheet
$spreadsheet = new Spreadsheet();
// Get active sheet
$sheet = $spreadsheet->getActiveSheet();
// Set cell value
$sheet->setCellValue('A1', 'File Format Developer Guide !');
// Save in Xlsx format
$writer = new Xlsx($spreadsheet);
$writer->save('FileFormat.xlsx');

Create & Apply AutoFilters

Using PHPSpreadsheet, you can apply auto-filter ranges to filter out and display only those rows which match the criteria that you have defined in the auto-filter. You can apply filters to more than one column as additives.

PHPSpreadsheet allows you to set an auto-filter area on a worksheet. You can create, read and write auto-filter expressions. Furthermore, various types of filters are available, such as simple filters, matching blanks, DateGroup filters, custom filters, dynamic filters, and top ten filters.

Apply Auto Filter in XLSX - PHP

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Load existing file without filters 
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('withoutFilter.xlsx');
// Select active worksheet
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension());

// Set active filters
$autoFilter = $spreadsheet->getActiveSheet()->getAutoFilter();
// Filter the Country column on a filter value of Germany
$autoFilter->getColumn('C')
  ->setFilterType(Column::AUTOFILTER_FILTERTYPE_FILTER)
  ->createRule()
  ->setRule(
    Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
    'Germany'
  );
// Save file
$writer = new Xlsx($spreadsheet);
$writer->save('Filter.xlsx');

Formula Calculation Engine

To perform basic to advanced level calculations in your business spreadsheets, you can easily evoke PHPSpreadsheet’s formula calculation engine, and it will take care of the rest.

PHPSpreadsheet’s formula parser can automatically adjust a formula while rows/columns are being inserted/removed. Microsoft Excel formula is converted into PHP code before being executed. To boost performance, a calculation cache is used to hold the result of the formula. Similarly, each individual worksheet is maintained by a separate cache.

 English