Getting Started with PhpSpreadsheet: Installation, Reading, Writing, and Styling Excel Files in PHP

PhpSpreadsheet is a pure‑PHP library that follows PSR standards and provides a rich API for creating, reading, and styling spreadsheet files such as XLSX, XLS, and CSV.

1. Installation

composer require phpoffice/phpspreadsheet

After a successful install the library files are located under vendor/phpoffice/phpspreadsheet .

2. Importing the library

use PhpOffice\PhpSpreadsheet\IOFactory;

Use the appropriate namespace when working with the classes.

3. Reading data

$file_name = 'E:\test1.xlsx';
$sheel = IOFactory::createReader('Xlsx'); // create reader
$spreadsheet = $sheel->load($file_name); // load file
$sheet = $spreadsheet->getSheet(0); // first worksheet
$count = $sheet->getHighestRow(); // total rows
$value = $sheet->getCell('A1')->getValue(); // read cell A1

The reader supports XLSX, XLS and CSV formats.

4. Writing data

use PhpOffice\PhpSpreadsheet\Spreadsheet;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet(); // new sheet
$sheet->setCellValue('A1', '1表格'); // set cell value
$sheel = IOFactory::createWriter($spreadsheet, 'Xlsx'); // create writer
$sheel->save('E:\test1.xlsx'); // save file

Writing also supports XLSX, XLS and CSV extensions.

5. Example: Creating a Course Schedule

The following code demonstrates merging cells, setting fonts, applying borders, background colors, and populating the sheet with a weekly schedule.

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑');
$sheet->mergeCells('A1:F1');
$sheet->setCellValue('A1', '课程表');
$styleArray_a1 = [
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical'   => Alignment::VERTICAL_CENTER,
    ],
];
$sheet->getStyle('A1:F12')->applyFromArray($styleArray_a1);
// ... additional style and data population omitted for brevity ...
$sheel = IOFactory::createWriter($spreadsheet, 'Xlsx');
$sheel->save('E:\test1.xlsx');

Note: If the target file already exists it will be overwritten.

6. Command summary

Common operations include setting cell values, retrieving values, applying font styles, formatting dates, adding hyperlinks, and batch inserting data.

// Set cell value (method 1)
$sheet->getCellByColumnAndRow(1, 2)->setValue('内容');
// Set cell value (method 2)
$sheet->getCell('A3')->setValue('张XXX');
// Set cell value (method 3)
$sheet->setCellValueByColumnAndRow(1, 3, '早自习');
// Set cell value (method 4)
$sheet->setCellValue('A1', "课程\n表");
// Enable text wrap
$sheet->getStyle('A1')->getAlignment()->setWrapText(true);

// Get cell value (method 1)
$value = $sheet->getCell('A1')->getValue();
// Get cell coordinate (method 2)
$coord = $sheet->getCell('A1')->getCoordinate();

// Apply font style
$sheet->getStyle('B2')->getFont()->setBold(true)->setName('宋体')->setSize(20);

// Apply borders, fill, alignment via array
$styleArray = [
    'font' => [
        'name' => '微软雅黑',
        'bold' => true,
        'size' => 13,
        'color' => ['argb' => Color::COLOR_WHITE],
    ],
    'borders' => [
        'outline' => ['borderStyle' => Border::BORDER_THICK, 'color' => ['argb' => '31869b']],
        'inside'  => ['borderStyle' => Border::BORDER_THIN,  'color' => ['argb' => '3ba0b9']],
    ],
];
$sheet->getStyle('A2:F2')->applyFromArray($styleArray);

// Set date format
$sheet->getStyle('A2')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2);

// Add hyperlink
$sheet->getCell('A1')->getHyperlink()->setUrl('https://www.baidu.com/');

// Batch insert data
$arrayData = [
    [NULL, '语文', '数学', '英语'],
    ['张一', 12, 15, 21],
    ['张二', 56, 73, 86],
];
$sheet->fromArray($arrayData, NULL, 'C2');

The article concludes with a reminder that the content is sourced from the internet and should be removed if it infringes copyright.