Delete Columns In Xlsx Files

Posted

July 2, 2022

admin

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"
    }
}

composer.json

Step 2.

Install phpspreadsheet.

$ composer install

command line

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);

delete-columns-in-xlsx-files.php

Test.

Run the following codes.

$ php delete-columns-in-xlsx-files.php

command line

Result.

Open the updated file create-xlsx-files.xlsx

References: