How to Write Data to an Excel File Using PHP

A detailed guide on how to write data to an Excel file using PHP with the help of the PHPExcel library or the more modern PhpSpreadsheet library.

We will use the PhpSpreadsheet library, a popular and powerful tool for manipulating Excel files in PHP. This guide will show you how to install and use the library to create and write data to an Excel file.

Step 1: Install the PhpSpreadsheet library

First, install the PhpSpreadsheet library using Composer:

composer require phpoffice/phpspreadsheet

Step 2: Write data to an Excel file

Here is a simple example of how to write data to an Excel file using PHP:

<?php
require 'vendor/autoload.php';

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

// Create a new Excel file
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Write data to cells
$sheet->setCellValue('A1', 'Name');
$sheet->setCellValue('B1', 'Age');
$sheet->setCellValue('A2', 'John Doe');
$sheet->setCellValue('B2', '25');
$sheet->setCellValue('A3', 'Jane Smith');
$sheet->setCellValue('B3', '30');

// Save the Excel file
$writer = new Xlsx($spreadsheet);
$writer->save('list.xlsx');

echo "Data has been written to the Excel file successfully!";
?>

Detailed explanation

1. use PhpOffice\PhpSpreadsheet\Spreadsheet;: Uses the Spreadsheet class from the PhpSpreadsheet library.
2. $spreadsheet = new Spreadsheet();: Creates a new Spreadsheet object.
3. $sheet->setCellValue('A1', 'Name');: Writes data to cell A1 with the value "Name".
4. $writer = new Xlsx($spreadsheet);: Creates a Writer object to save the Excel file in .xlsx format.
5. $writer->save('list.xlsx');: Saves the Excel file as "list.xlsx" in the current directory.

System Requirements

• PHP version 7.3 or later
• Composer to manage PHP libraries
• PhpSpreadsheet library

How to install libraries to run the above PHP code

1. Install Composer if you haven't: https://getcomposer.org/download/
2. Use the command composer require phpoffice/phpspreadsheet to install the PhpSpreadsheet library.
3. Make sure your PHP file includes require 'vendor/autoload.php'; to automatically load the libraries installed via Composer.

Tips

• Always validate your data before writing to the Excel file to avoid potential issues.
• Ensure the target directory has write permissions to save the Excel file successfully.