PHP Add Rows To Excel File (Append Prepend Insert)

Welcome to a tutorial on how to add rows to an existing Excel file in PHP. So you need to append, prepend, or insert rows into an Excel file? Read on for the examples!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

QUICK SETUP

• Download and install a package manager called Composer first.
• Open the terminal and navigate to your project folder – cd MY/PROJECT/FOLDER
• Run composer require phpoffice/phpspreadsheet to download PHPSpreadsheet, Composer will automatically save the latest version into the vendor/ folder.

 

1) APPEND NEW ROW

<?php
// (A) LOAD PHPSPREADSHEET
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 
// (B) OPEN WORKSHEET
$spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load("x-dummy.xlsx");
$worksheet = $spreadsheet->getActiveSheet();
 
// (C) APPEND ROW
$row = $worksheet->getHighestRow()+1;
$worksheet->setCellValue("A$row", "NEW");
$worksheet->setCellValue("B$row", "ROW");
 
// (D) ALTERNATIVELY
$data = ["ANOTHER", "ROW"];
$row++;
$char = 65; // "A"
foreach ($data as $d) {
  $worksheet->setCellValue(chr($char) . $row, $d);
  $char++;
}
 
// (E) OUTPUT
$writer = new Xlsx($spreadsheet);
$writer->save("demoA.xlsx");
echo "OK!";

This may seem a little long-winded, but it’s straightforward nonetheless:

1. Load the PHPSpreadsheet library.
2. Open the Excel file that you want to update, and select the worksheet.
3. Append to the last row and set the individual cell values.
4. A “small extra example” if you want to append an array of data.
   • Loop through the array values, and use the same setCellValue() to set the cells values.
   • But take note of how the “cell coordinates” are variables – chr($char) . $row.
   • The ASCII values for A-Z are 65-90, so “A” is chr(65), “B” is chr(66), and so on. Do some research on “ASCII values” if you are interested to learn more.
5. Lastly, save the updated Excel file.

 

 

2) PREPEND NEW ROW

<?php
// (A) LOAD PHPSPREADSHEET
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) OPEN WORKSHEET
$spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load("x-dummy.xlsx");
$worksheet = $spreadsheet->getActiveSheet();

// (C) PREPEND NEW ROW
$worksheet->insertNewRowBefore(1);
$worksheet->setCellValue("A1", "NEW");
$worksheet->setCellValue("B1", "ROW");

// (D) OUTPUT
$writer = new Xlsx($spreadsheet);
$writer->save("demoB.xlsx");
echo "OK!";

To prepend a row at the top of the worksheet:

• We insert an empty row at the very top – insertNewRowBefore(1)
• Set the cell values accordingly – setCellValue("A1", "VALUE")

 

 

3) INSERT NEW ROW

<?php
// (A) LOAD PHPSPREADSHEET
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) OPEN WORKSHEET
$spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load("x-dummy.xlsx");
$worksheet = $spreadsheet->getActiveSheet();

// (C) INSERT AT ROW 3
$at = 3;
$worksheet->insertNewRowBefore($at);
$worksheet->setCellValue("A$at", "NEW");
$worksheet->setCellValue("B$at", "ROW");

// (D) SEARCH
$at = null;
foreach ($worksheet->getRowIterator() as $row) {
  foreach ($row->getCellIterator() as $cell) {
    if ($cell->getValue() == "Jon Doe") { $at = $cell->getRow(); break; }
  }
}
if ($at != null) {
  $worksheet->insertNewRowBefore($at);
  $worksheet->setCellValue("A$at", "ANOTHER");
  $worksheet->setCellValue("B$at", "ROW");
}

// (E) OUTPUT
$writer = new Xlsx($spreadsheet);
$writer->save("demoC.xlsx");
echo "OK!";

• (C) To insert a new row “somewhere in the middle”, it’s the same old insert empty row insertNewRowBefore() and setCellValue().
• (D) But if you do not know the exact location to insert, the only way is to search through the entire worksheet to find the “insert location”.

 

 

 

LINKS & REFERENCES

• PHP Display Excel In HTML Table – Code Boxx
• PHPSpreadsheeet Documentation – Code Boxx