How to create Excel file in PHP

3Oct

Generating Excel files from PHP is a common requirement for web applications. Fortunately, there are libraries like PhpSpreadsheet that simplify this task. PhpSpreadsheet is a popular library for creating and managing Excel files in PHP. Here’s a step-by-step guide on how to create a basic Excel file using PhpSpreadsheet:

Table of Contents

Include PhpSpreadsheet Library

First, you need to include the PhpSpreadsheet library in your PHP file. Download it from the PhpSpreadsheet website, and include it in your project like this:

require ‘vendor/autoload.php’; // Assuming Composer autoload
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

Create PhpSpreadsheet Instance

Now, create an instance of the PhpSpreadsheet class:

$spreadsheet = new Spreadsheet();

Get the Active Worksheet

To work with worksheets in your Excel file, you need to get the active worksheet:

$sheet = $spreadsheet->getActiveSheet();

Set Cell Values

Set values for the cells in your worksheet:

$sheet->setCellValue(‘A1’, ‘Name’);
$sheet->setCellValue(‘B1’, ‘Age’);
$sheet->setCellValue(‘A2’, ‘John Doe’);
$sheet->setCellValue(‘B2′, ’30’);

In this example, we set values for cells A1 and B1 as “Name” and “Age,” respectively. Cells A2 and B2 are populated with “John Doe” and “30”.

Save as Excel File

To save the generated Excel file, create a writer object and configure the headers to prompt the user for download:

$writer = new Xlsx($spreadsheet);
header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition: attachment;filename=”sample.xlsx”‘);
header(‘Cache-Control: max-age=0’);
$writer->save(‘php://output’);

Here’s what’s happening:

We use the createWriter method to create a writer object for our PhpSpreadsheet instance.

Headers are set to specify the content type (application/vnd.ms-excel), indicate attachment (Content-Disposition), and provide the filename (sample.xlsx).
We disable caching to ensure the file is not stored by the browser.
Finally, we use the writer object to save the file to the output stream (php://output).

That’s the basic process of creating an Excel file in PHP using PhpSpreadsheet. You can further customize your Excel file by setting font styles, background colors, borders, adding formulas, charts, and images to the worksheet.

Advanced Excel File Manipulation with PhpSpreadsheet

While we’ve covered the basics of creating Excel files with PhpSpreadsheet, there are many advanced features and tasks you can perform with this powerful library. Let’s explore some advanced Excel file manipulation techniques:

Formulas

You can add formulas to Excel cells, which can reference other cells in the worksheet. For example:

$sheet->setCellValue(‘C1’, ‘=SUM(A2:B2)’);

Multiple Worksheets

You can create and manage multiple worksheets within a single Excel file:

$spreadsheet->createSheet();
$newSheet = $spreadsheet->getSheet(1);
$newSheet->setTitle(‘Sheet 2’);

Reading Excel Files

PhpSpreadsheet can not only create but also read Excel files. You can extract data, formulas, and styles from existing Excel files.

use PhpOffice\PhpSpreadsheet\IOFactory;
$spreadsheet = IOFactory::load(‘existing.xlsx’);

Styling and Formatting

You can apply various styles and formatting options to your Excel file, such as changing font styles, cell colors, borders, and alignment. Here’s an example of how to set cell formatting:

use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

$style = $sheet->getStyle(‘A1:B2’);
$style->getFont()->setBold(true);
$style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB(‘FFFF00’);
$style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);