PHP Reading And Writing Excel Files Tutorial

About

In this code snippet, we’ll learn how to read and write excel files in PHP.

We will use the PHPSpreadsheet library to read/write Excel files and we’ll learn how to read cells, find a specific string in a row or column, write to cells, set cell styling and formatting(like setting the border, background color, etc) and we’ll see how to insert images.

Let’s see how to do it in the example below.

Installing PHPSpreadsheet:

Install PHPSpreadsheet using Composer by opening your project directory in cmd and running this:

composer require phpoffice/phpspreadsheet

If you don’t know what Composer is or how to use it check out this post I made.

Note:

You might have to enable the gd PHP extension for this to work. I first got this error:

Problem 1

– Root composer.json requires phpoffice/phpspreadsheet ^1.18 -> satisfiable by phpoffice/phpspreadsheet[1.18.0].

– phpoffice/phpspreadsheet 1.18.0 requires ext-gd * -> it is missing from your system. Install or enable PHP’s gd extension.

FIX: If you are using XAMPP like I am open up your php.ini file and add this: extension=gd to the bottom of it. Then save the file and restart the Apache webserver and try to install the package again.

Reading Excel Files Code:

ReadExcel.php

<?php

//Include the dependencies using Composer.
require_once(dirname(__FILE__) ."\\vendor\\autoload.php");
require_once(dirname(__FILE__) ."\\helperFunctions.php");


//Setup Excel reader///////////////////////////////////////////

//File to be read.
$inputFileName = dirname(__FILE__) . "\\readme.xlsx"; 
$sheetname = "First Sheet";

//Make a new instance of the Xlsx rader.
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
//Load the excel file to be read.
$spreadsheet = $reader->load($inputFileName);     
//Get the sheet by name.
$sheet = $spreadsheet->getSheetByName($sheetname);   

///////////////////////////////////////////////////////////////


//Reading file/////////////////////////////////////////////////

//The row number of the header.
$columnNameRow = 1;
//The row number where our data starts.
$dataRow = $columnNameRow+1;

//If you don't know the exact column number you search for it with this helper function.
$column = findColumn($sheet, "greetings", $columnNameRow);
//Or if you need to search the rows:
//$row = findRow($sheet, "text to find", $column);

//Data array that will store the read data.
$readSheetData = [];

//Read rows until an empty one is hit.
$currentCellData = "/";
for($i = $dataRow; $currentCellData != ""; $i++){
    //Get cell data.
    $currentCellData = $sheet->getCellByColumnAndRow($column, $i)->getCalculatedValue();
    //If data is present add it to the data array. 
    if($currentCellData != null)
        $readSheetData[] = $currentCellData;
}

//Display data array.
foreach($readSheetData as $item)
    echo $item . "<br>";

////////////////////////////////////////////////////////////////

HelperFunctions.php

<?php

//Helper functions//////////////////////////////////////////////

function findColumn($spreadsheet, $word, $row){
    $column = 1;
    $cellContent = $spreadsheet->getCellByColumnAndRow($column, $row)->getValue();
    
    while($cellContent != $word){
        $column++;
        $cellContent = $spreadsheet->getCellByColumnAndRow($column, $row)->getValue(); 
    }

    return $column;
}

function findRow($spreadsheet, $word, $column){
    $row = 1;
    $cellContent = $spreadsheet->getCellByColumnAndRow($column, $row)->getValue();

    while($cellContent != $word) {
        $row++;
        $cellContent = $spreadsheet->getCellByColumnAndRow($column, $row)->getValue();                 
    } 

    return $row;
}

////////////////////////////////////////////////////////////////

Resulting Output:

Writing Excel Files Code:

WriteExcel.php

<?php

//Include the dependencies using Composer.
require_once(dirname(__FILE__) ."\\vendor\\autoload.php");

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


//Setup Excel writer////////////////////////////////////////////

//File to be read.
$inputFileName = dirname(__FILE__) . "readme.xlsx"; 
$sheetname = "First Sheet";

//Make a new spreadsheet.
$spreadsheet = new Spreadsheet();
//Get active sheet.
$sheet = $spreadsheet->getActiveSheet();
//Set sheet name.
$sheet->setTitle('Data');

////////////////////////////////////////////////////////////////


//Writing file//////////////////////////////////////////////////

$dataToWrite1 = [ 15465, 532185, 2566, 54886 ];
$dataToWrite2 = [ 5694, 56964, 321789, 45623 ];

//Make header(optional).
$sheet->setCellValue('A1', "Data Set 1");
$sheet->setCellValue('B1', "Data Set 2");
//Make a bottom border(optional).
$sheet->getStyle('A1:B1')->getBorders()->getBottom()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
//Set header background color(optional).
$sheet->getStyle('A1:B1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('d2d3d1');
//Set text bold.
$sheet->getStyle("A1:B1")->getFont()->setBold(true);
//Set auto resize(optional).
$sheet->getColumnDimension('A')->setAutoSize(true);

//For more styling/formatting info. check out the official documentation: https://phpspreadsheet.readthedocs.io/en/latest/

//Write data 1.
$i = 2;
foreach($dataToWrite1 as $item){
    //Write value into cell.
    $sheet->setCellValue('A'.$i, $item);
    //Set cell alignment(optional).
    $sheet->getStyle('A'.$i)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    $i++;
}

//Write data 2.
$i = 2;
foreach($dataToWrite2 as $item){
    //Write value into cell.
    $sheet->setCellValue('B'.$i, $item);
    //Set cell alignment(optional).
    $sheet->getStyle('B'.$i)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    $i++;
}

//Adding an image.

//Create drawing.
$objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();


//Get image path.
$imgPathName = dirname(__FILE__) . "\\PHP Reading And Writing Excel Files.jpg";    

//Create gdImage from image.
$gdImage = imagecreatefromjpeg($imgPathName);
//Set gdImage as a property to drawing.
$objDrawing->setImageResource($gdImage);

//Set drawing properties.
$objDrawing->setName('Thumbnail');
$objDrawing->setDescription('PHP Reading And Writing Excel Files');
//Set file type.
$objDrawing->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);

//Set position.
$objDrawing->setCoordinates('D1');
//Set position offset.
$objDrawing->setOffsetX(50);
$objDrawing->setOffsetY(50);                
//Set width and height.
$objDrawing->setWidth(400); 
$objDrawing->setHeight(100);

$objDrawing->setWorksheet($sheet);

//Write excel file.
$savePath = dirname(__FILE__);

$writer = new Xlsx($spreadsheet);
$writer->save($savePath . "\\New File.xlsx");

////////////////////////////////////////////////////////////////