How to Edit and Update Excel Files in PHP

zeroexp.dev ZeroonLeave a Comment on How to Edit and Update Excel Files in PHP
How to Edit and Update Excel Files in PHP

Learn how to edit and update Excel files (.xls, .xlsx) in PHP using PhpSpreadsheet. Modify existing data, add new rows, update cells, and save changes dynamically.

Introduction

Modifying existing Excel files in PHP is essential for applications that require data updates, automated report modifications, and bulk data processing. Instead of manually changing values, PhpSpreadsheet allows PHP scripts to open, modify, and update Excel files dynamically.

This guide covers:

  • Loading and editing existing Excel files
  • Modifying specific rows and columns
  • Inserting new data dynamically
  • Updating formulas and formatting
  • Saving changes without overwriting important data

1. Installing PhpSpreadsheet for Excel Editing

To edit and update Excel files, install PhpSpreadsheet via Composer:

composer require phpoffice/phpspreadsheet

Include PhpSpreadsheet in your PHP script:

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

Once installed, your PHP application is ready to edit Excel files.

2. Loading an Existing Excel File in PHP

Before making modifications, load an existing Excel file.

$file = 'existing_data.xlsx';
$spreadsheet = IOFactory::load($file);
$sheet = $spreadsheet->getActiveSheet();

This allows modifications to be made without creating a new file.

3. Updating a Specific Cell in Excel

To modify an existing value in a specific cell:

$sheet->setCellValue('B2', 'Updated Name');
$sheet->setCellValue('C2', 35); // Update age

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('updated_data.xlsx');

echo "Excel file updated successfully.";

This changes the value of cell B2 (Name) and C2 (Age) while keeping other data intact.

4. Updating a Row in an Excel File

To modify an entire row dynamically, fetch and update it.

$row = 3; // Row to update
$sheet->setCellValue("A$row", 102);
$sheet->setCellValue("B$row", 'Michael Johnson');
$sheet->setCellValue("C$row", 'Marketing');
$sheet->setCellValue("D$row", 60000);

$writer->save('updated_data.xlsx');

This updates row 3 while maintaining the structure of other rows.

5. Appending New Data to an Existing Excel File

To add new data without overwriting existing content:

$lastRow = $sheet->getHighestRow() + 1;

$sheet->setCellValue("A$lastRow", 104);
$sheet->setCellValue("B$lastRow", 'Emma White');
$sheet->setCellValue("C$lastRow", 'Finance');
$sheet->setCellValue("D$lastRow", 75000);

$writer->save('updated_data.xlsx');

This finds the next available row and inserts new data.

6. Searching and Replacing Values in Excel

To find a specific value and replace it across the sheet:

foreach ($sheet->getRowIterator() as $row) {
    foreach ($row->getCellIterator() as $cell) {
        if ($cell->getValue() == 'John Doe') {
            $cell->setValue('John Smith');
        }
    }
}

$writer->save('updated_data.xlsx');

This searches for "John Doe" and replaces it with "John Smith" across the document.

7. Updating Excel Formulas Dynamically

To update a formula in an Excel file:

$sheet->setCellValue('E2', '=SUM(D2:D10)'); // Sum of Salary column

$writer->save('updated_data.xlsx');

This recalculates totals dynamically whenever the file is modified.

8. Applying Formatting to Updated Excel Files

To apply styles such as bold text and colors after updating data:

use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Font;

// Apply bold text
$sheet->getStyle('A1:D1')->getFont()->setBold(true);

// Change font color
$sheet->getStyle('B2')->getFont()->getColor()->setARGB(Color::COLOR_RED);

$writer->save('updated_data.xlsx');

This improves readability and formatting after data updates.

9. Protecting and Locking Excel Sheets

To prevent unauthorized modifications, protect the sheet:

$sheet->getProtection()->setSheet(true);
$sheet->getProtection()->setPassword('securepass');

$writer->save('protected_data.xlsx');

This locks the Excel file with password protection.

10. Automating Excel Updates Using Cron Jobs

For automated updates, set up a cron job that modifies Excel files periodically.

0 2 * * * php /var/www/html/update_excel.php

This runs the script every night at 2 AM to update the Excel file.

Best Practices for Editing Excel Files in PHP

  • Always create a backup before modifying an Excel file.
  • Use cell references carefully to avoid overwriting critical data.
  • Apply formatting after updating values for better presentation.
  • Lock sheets when necessary to prevent unauthorized modifications.
  • Use cron jobs for automating repetitive data updates.

Conclusion

With PhpSpreadsheet, PHP can edit and update Excel files dynamically, making it easy to modify values, insert new data, update formulas, and automate Excel-based tasks.

This guide covered:

  • Loading and editing existing Excel files
  • Modifying specific rows and columns dynamically
  • Appending new data without overwriting existing content
  • Updating formulas and applying styles to reports
  • Locking sheets and protecting Excel files

By implementing these techniques, Excel file management becomes seamless and automated in PHP applications.

Leave a Reply