Update an existing xlsx file and remove a specific column dynamically using PhpSpreadsheet, this is a convenient way of editing numerous xlsx files without opening the files one by one. Also a very useful method to auto-generate an xlsx file for downloads using a premade template.
Requirements:
- Composer
- PHP 7.2 or newer
- Existing xlsx file – Create Xlsx Files
Current content of the xlsx file that will be updated (create-xlsx-files.xlsx).
Step 1.
Setup dependencies.
{
"require": {
"phpoffice/phpspreadsheet": "^1.3"
}
}Step 2.
Install phpspreadsheet.
$ composer install
Step 3.
Create a new PHP file, and start coding.
<?php // Autoload dependencies require 'vendor/autoload.php'; // Import the Xlsx reader class use PhpOffice\PhpSpreadsheet\Reader\Xlsx; // Import the Xlsx writer class use PhpOffice\PhpSpreadsheet\Writer\Xlsx as Writer; // Full path of the file to read $inputFileName = 'create-xlsx-files.xlsx'; // Create the reader object $reader = new Xlsx(); // Load the file to read $spreadsheet = $reader->load($inputFileName); // Get the active sheet $sheet = $spreadsheet->getActiveSheet(); // Remove Column 'A1' $spreadsheet->getActiveSheet()->removeColumn('A', 1); // Prepare the writer $writer = new Writer($spreadsheet); // Update the .xlsx file $writer->save($inputFileName);
Test.
Run the following codes.
$ php delete-columns-in-xlsx-files.php
Result.
Open the updated file create-xlsx-files.xlsx
