Master Excel File Manipulation in PHP with PhpSpreadsheet: Quick Start Guide

Overview

PhpSpreadsheet is a pure‑PHP library that lets you read and write many spreadsheet file formats, offering a rich API for cell values, styles, images, dates, functions, and more.

Official documentation:

https://phpspreadsheet.readthedocs.io

Supported Formats

Open Document Format/OASIS (.ods) – read/write

Office Open XML (.xlsx) – read/write

BIFF 8 (.xls) – read/write

BIFF 5 (.xls) – read only

SpreadsheetML (.xml) – read only

Gnumeric – read only

HTML – read/write

SYLK – read only

CSV – read/write

PDF (via TCPDF, Dompdf or mPDF) – write only

Installation

Install via Composer: composer require phpoffice/phpspreadsheet To include source for documentation and examples:

composer require phpoffice/phpspreadsheet --prefer-source

If the development machine uses a different PHP version than the target server, add a platform config to composer.json:

{
  "require": {
    "phpoffice/phpspreadsheet": "^1.23"
  },
  "config": {
    "platform": {
      "php": "7.4"
    }
  }
}

Then run:

composer install

Getting Started

Basic Example

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$activeWorksheet = $spreadsheet->getActiveSheet();
$activeWorksheet->setCellValue('A1', 'Hello World !');
$writer = new Xlsx($spreadsheet);
$writer->save('Helloorld.xlsx');

Reading an Excel File

The following code loads an XLSX file and prints each cell value:

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'example.xlsx';
$spreadsheet = IOFactory::load($inputFileName);
$sheet = $spreadsheet->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

for ($row = 1; $row <= $highestRow; ++$row) {
    for ($col = 1; $col <= $highestColumnIndex; ++$col) {
        $cellValue = $sheet->getCellByColumnAndRow($col, $row)->getValue();
        echo $cellValue . "\t";
    }
    echo "
";
}

Creating and Writing an Excel File

This example creates a new spreadsheet, writes some data, applies simple styling, and saves it as an XLSX file:

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Fill;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', '姓名');
$sheet->setCellValue('B1', '年龄');
$sheet->setCellValue('A2', '张三');
$sheet->setCellValue('B2', 25);

$style = $sheet->getStyle('A1');
$font = $style->getFont();
$font->setName('Arial');
$font->setSize(16);
$font->setBold(true);
$font->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE));

$fill = $style->getFill();
$fill->setFillType(Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('FF0000');

$writer = new Xlsx($spreadsheet);
$outputFileName = 'styled_example.xlsx';
$writer->save($outputFileName);
echo "文件已保存为: $outputFileName";