Create Xlsx Files With Auto Column Width Settings

| September 5, 2020 | 5,775 views | PhpSpreadsheet

Code an xlsx file in PhpSpreadsheet to automatically adjust a column width based on the maximum number of characters from a cell within the given column. Once the xlsx file is created, the cell will automatically fit it’s value, this is similar to manually setting the column width with the function ‘Auto Fit Column Width’.

Manually setting ‘Auto Fit Column Width’

Format -> Auto Fit Column Width

Requirements:

  • Composer
  • PHP 7.2 or newer

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 core class of PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// Import the Xlsx writer class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet();

// Set cell A1 with the "Hello World !" string value
$sheet->setCellValue('A1', 'Hello World !');

// Set column A width automatically
$sheet->getColumnDimension('A')->setAutoSize(true);

// Write a new .xlsx file
$writer = new Xlsx($spreadsheet);

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-auto-column-width-settings.xlsx');

Test.

Run the following codes.

$ php create-xlsx-files-with-auto-column-width-settings.php

Result.

Open the generated file create-xlsx-files-with-auto-column-width-settings.xlsx.

// Set column A width automatically
$sheet->getColumnDimension('A')->setAutoSize(true);

Default.

References:

0 Comments

Leave a Reply

Your email address will not be published.