Create Multiple Excel Sheets with PHP: Step-by-Step Guide

Creating multiple Excel sheets within a single workbook using PHP can be a valuable skill for developers working on applications that require data organization or reporting functionalities. PHP provides several libraries to handle Excel files, but one of the most popular and versatile is PHPExcel, now known as PhpSpreadsheet after a significant update. This guide will walk you through the process of creating, populating, and saving multiple sheets within an Excel workbook using PhpSpreadsheet.

Why Use PhpSpreadsheet?

PhpSpreadsheet offers robust features like writing Excel files in formats like XLSX, XLS, CSV, HTML, PDF, and ODS. It supports reading from various file formats as well, providing extensive compatibility with Microsoft Office Excel. Here are some key benefits:

• Multi-platform support: Works on Windows, Linux, or macOS.
• Feature-rich: Allows complex cell styling, formula calculations, and chart creation.
• Easy to integrate: It’s a pure PHP library, meaning no external dependencies or Microsoft Office installation required.

Installation

Before diving into the coding, you’ll need to install PhpSpreadsheet:

composer require phpoffice/phpspreadsheet

Or download it directly from the GitHub repository and include it manually.

Creating the Spreadsheet

Let’s start by setting up a new PhpSpreadsheet workbook and adding multiple sheets:

<?php
require ‘vendor/autoload.php’;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

spreadsheet = new Spreadsheet();
sheet1 = spreadsheet->getActiveSheet();
sheet1->setTitle(“Sheet1”);

spreadsheet->createSheet();
sheet2 = spreadsheet->getSheet(1);
sheet2->setTitle(“Sheet2”);

Populating Data into Sheets

You can now populate these sheets with data:

// Data for Sheet1
sheet1->setCellValue('A1', "This is Sheet 1");
sheet1->setCellValue(‘B1’, “Data”);

// Data for Sheet2
sheet2->setCellValue('A1', "Welcome to Sheet 2");
sheet2->setCellValue(‘B1’, “More Data”);

Formatting Cells

To make your spreadsheet visually appealing or to highlight certain data, you can format cells:

sheet1->getStyle('A1')->getFont()->setBold(true);
sheet2->getStyle(‘B1’)->getFont()->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color(‘#FF0000’));

📘 Note: Cell formatting can significantly enhance the readability of your Excel files, but be cautious not to overdo it, which might slow down the file’s loading time.

Working with Formulas

You can also add formulas to your sheets:

$sheet1->setCellValue(‘A2’, ‘=SUM(A1:B1)’);

Saving the Workbook

Once your workbook is ready, save it to a file:

writer = new Xlsx(spreadsheet);
$writer->save(‘my-spreadsheet.xlsx’);

Advanced Features

PhpSpreadsheet allows for:

• Merging cells: mergeCells('A1:B1');
• Auto sizing columns: $sheet->getColumnDimension('A')->setAutoSize(true);
• Creating charts: Using the Chart class.

Tips for Better Performance

When dealing with large datasets, consider these tips:

• Use memory optimizations like $spreadsheet->garbageCollect();
• Avoid reading the entire workbook at once if you only need specific data
• Set up a cache mechanism to store cells that are frequently accessed.

In this guide, we’ve covered creating a PHP script to generate an Excel workbook with multiple sheets using PhpSpreadsheet. Whether you’re exporting data, generating reports, or just organizing information, the flexibility of handling Excel files programmatically can enhance your application’s functionality.

The key points we’ve discussed include:

• Installation and Setup: Installing PhpSpreadsheet with Composer or downloading it manually.
• Creating and Manipulating Sheets: Adding, naming, and populating sheets with data, including text, numbers, and formulas.
• Styling and Formatting: Enhancing data presentation with cell styling and color.
• Saving the Workbook: Saving your modified Excel file.
• Optimization: Tips for handling large datasets efficiently.

By mastering these techniques, you can significantly improve data management in your PHP applications, making them more versatile and user-friendly.

Can PhpSpreadsheet handle different Excel file formats?

+

Yes, PhpSpreadsheet supports reading and writing multiple Excel file formats, including XLSX, XLS, CSV, HTML, PDF, and ODS.

How can I include charts in my Excel sheets?

+

Charts can be added using the Chart class in PhpSpreadsheet, allowing for custom charting options based on your data.

What’s the best way to handle large datasets with PhpSpreadsheet?

+

For large datasets, use memory optimizations like garbage collection, read only parts of the workbook, and implement caching where possible.