How to create new or modify existing excel file using PHP

So, you wanna create an excel file using PHP or have already a excel file and want to modify that using PHP.

Let’s dive into it

What you will learn from this article?

1. Create a new excel file using PHP.
2. Insert data to the excel file using PHP.
3. Update existing excel file using PHP.

PHPSpreadsheet is a PHP library to read and write excel files. It allows us to work on various spreadsheet file formats like Excel and LibreOffice Calc. It is very easy to use and well documented. PHPSpreadsheet is an open source PHP library and available on github.

You can install this library via composer. PHP version should be more than 7.2 or latest. Run the following command in the root of your project directory.

composer require phpoffice/phpspreadsheet

Read also : Begin with Laravel step by step guide post

Create a new excel file using PHP

To create the excel file using PHP just do the following,

<?php

require 'vendor/autoload.php';

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

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

The autoload file from the vendor folder actually connects your project with the vendor folder, So if you are already using any PHP framework you don’t need to do it and now just need to use required classes from the library.

As we need spreadsheets in our excel file just use the spreadsheet class here from the PhpSpreadsheet library. To create the excel file using PHP we need to use Xlsx class also.

Then first prepare the spreadsheet and then add that spreadsheet to the excel file using Xlsx class. Last step is just to save the file. 

Insert data to the excel file

Congratulations! You have successfully created the excel file using PHP. Now let’s insert some data to the excel file using PHP Spreadsheet library.

// Set cell A1 with a string value
$spreadsheet->getActiveSheet()->setCellValue('A1', 'PhpSpreadsheet');

// Set cell A2 with a numeric value
$spreadsheet->getActiveSheet()->setCellValue('A2', 12345.6789);

// Set cell A3 with a boolean value
$spreadsheet->getActiveSheet()->setCellValue('A3', TRUE);

// Set cell A4 with a formula
$spreadsheet->getActiveSheet()->setCellValue(
    'A4',
    '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
);

You can set the cell value by using a cell address like A21 where A is a column and 21st is a row. You also can set the formula to the cell. One another method to insert data to the excel file using PHP is below.

$spreadsheet->getActiveSheet()
    ->getCell('B8')
    ->setValue('Some value');

Here getCell() will get the cell with given address and then set the given value to it by using setValue(). But if the cell does not exist with this address then it will create it. If you want to avoid this default behavior then you can pass FALSE as the second parameter to the getCell()  function. It will return null if the cell does not exist instead of creating it.

Modify existing excel file using PHP

If you want to keep updating your excel file with newly added records to the database, then we can use something like below.

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");
    $spreadsheet = $reader->load('Hostel_records.xlsx');
 
    $sheet = $spreadsheet->getActiveSheet();
    $last_row = (int) $sheet->getHighestRow();
    $new_row = $last_row+1;
 
    $sheet->setCellValue('A'.$new_row, “14”);
    $sheet->setCellValue('B'.$new_row, “Alina”);
    $sheet->setCellValue('C'.$new_row, “PG”);
    $sheet->setCellValue('D'.$new_row, “$32”);
    $sheet->setCellValue('E'.$new_row, “Pending”);
 
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx");
    $writer->save('Hostel_records.xlsx');

First step is to create the spreadsheet reader then load the file using this reader. The getHighestRow helps us to get the last added row, so that you can start adding new records from the next empty row.

Second step is to create the writer and pass the above modified spreadsheet as a first parameter. Then just save the file with this writer.

Please leave a comment if you have any query.