Easily Create Excel Sheets in PHP: A Step-by-Step Guide

Creating Excel spreadsheets programmatically is a highly sought-after skill in the realm of web development, especially when handling large datasets or creating automated reports. In this blog post, we'll delve into how you can leverage PHP, a popular server-side scripting language, to generate Excel files. Whether you're working on automating business processes, generating financial reports, or just need to manage data efficiently, PHP's capabilities can be your go-to solution. Here's how you can easily create and manipulate Excel sheets using PHP:

Why Use PHP for Excel Sheet Creation?

PHP has numerous libraries that support the creation of Excel files. Here’s why you should consider using PHP:

• Open Source: PHP is freely available, which means you won’t incur additional costs for the software itself.
• Web Integration: As PHP primarily runs on a web server, it’s perfect for generating Excel files on-the-fly and serving them to users via web applications.
• Extensibility: PHP’s ecosystem includes libraries like PHPExcel (deprecated) and PhpSpreadsheet which provide extensive features for Excel operations.

Prerequisites

• PHP installed on your system or server.
• An IDE or text editor for writing PHP code.
• Basic knowledge of PHP scripting.

Setting Up Your Environment

Before we dive into the code:

1. Install PHP if it’s not already on your server.
2. Choose a library:
   • PhpSpreadsheet: The modern, well-maintained library for Excel manipulation in PHP. Install it via Composer:

     composer require phpoffice/phpspreadsheet

Creating an Excel File with PhpSpreadsheet

Now, let’s walk through the process of creating a simple Excel file:

Initializing the Spreadsheet

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

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

// Create a new Spreadsheet object
spreadsheet = new Spreadsheet();
// Get the currently active sheet
sheet = $spreadsheet->getActiveSheet();
?>

Adding Data to the Sheet

// Set cell A1 with header “ID”
sheet->setCellValue('A1', 'ID');
// Set cell B1 with header "Name"
sheet->setCellValue(‘B1’, ‘Name’);
// Set cell A2 with ID value
sheet->setCellValue('A2', 1);
// Set cell B2 with Name value
sheet->setCellValue(‘B2’, ‘John Doe’);

Formatting Cells

Let’s apply some formatting to enhance the visual appeal:

// Get the default style object to clone cell style
$styleArray = [
    ‘font’ => [
        ‘bold’ => true,
        ‘size’  => 16,
    ],
    ‘fill’ => [
        ‘fillType’ => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        ‘startColor’ => [
            ‘argb’ => ‘FFA0A0A0’,
        ],
    ],
];

// Apply style to the header cells
sheet->getStyle('A1:B1')->applyFromArray(styleArray);

Saving the File

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

Advanced Techniques

• Using Formulas: You can add Excel formulas:

sheet->setCellValue('C1', 'Total');
sheet->setCellValue(‘C2’, ‘=A2+B2’);
  

• Merging Cells: Merge cells for headers or titles:

sheet->mergeCells('A3:C3');
sheet->setCellValue(‘A3’, ‘This is a merged cell’);
  

• Data Validation: Implement data validation rules:

use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

validation = sheet->getCell(‘A2’)->getDataValidation();
$validation->setType(DataValidation::TYPE_LIST)
           ->setFormula1(‘“John,Jane,Jim,Jack”’);
  

Handling Large Datasets

For big data:

• Batch Insertion: Insert data in chunks rather than loading the entire dataset at once.
• Memory Management: Use memory-saving techniques when dealing with large files.

💡 Note: Ensure you handle large data sets with care to prevent memory exhaustion on your server.

Summary

In this comprehensive guide, we’ve explored the fundamental steps of creating and manipulating Excel spreadsheets using PHP. From initializing spreadsheets, adding data, formatting cells, to implementing advanced features like formulas and data validation, PHP offers a robust solution for Excel file generation. By leveraging libraries like PhpSpreadsheet, you can automate the creation of complex reports, export database information, or provide downloadable data sets to users directly through a web interface. Remember to consider performance when dealing with large datasets and ensure your PHP environment is adequately configured to support such tasks.

Can PHP create Excel files without additional libraries?

+

No, PHP itself does not have built-in functionality to directly create Excel files. Libraries like PhpSpreadsheet are necessary to handle Excel-specific formats and functionalities.

What are the alternatives to PhpSpreadsheet?

+

Although deprecated, PHPExcel was a popular choice in the past. For real-time Excel generation, you might also consider server-side solutions like Excel Services in SharePoint or cloud-based services like Google Sheets API.

Is PhpSpreadsheet compatible with all versions of Excel?

+

PhpSpreadsheet aims to support the latest Excel formats like .xlsx (Excel 2007 and later), but older file formats might not be fully supported or have limitations in functionality.

Related Terms:

• PHP create XLS
• PHP export to Excel xlsx
• Export to Excel in PHP
• PHP edit excel file
• PHP to excel
• PhpSpreadsheet create multiple sheets