Create Xlsx Files With Auto Filter Settings

Whenever there is a need to group column values or to reorder column contents of a worksheet, or even search a specific column data, coding with the setAutoFilter() function in PhpSpreadsheet comes in handy when creating an xlsx file in Php.

Requirements:

• Composer
• PHP 7.2 or newer

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 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 "Filter Column A" string value
$sheet->setCellValue('A1', 'Filter Column A');

/**
 * Randomly fill cell A2 to A20 with
 * values ranging from 1 to 20.
 */
for ($i=2; $i<=20; $i++) {
    $sheet->setCellValue('A' . $i, rand(1, 20));
}

// Set cell B1 with the "Filter Column B" string value
$sheet->setCellValue('B1', 'Filter Column B');

/**
 * Randomly fill cell B2 to B20 with
 * values ranging from A to Z.
 */
for ($i=2; $i<=20; $i++) {
    $sheet->setCellValue('B' . $i, chr(rand(65,90)));
}

// Set auto filter to column A and B
$sheet->setAutoFilter('A1:B1');

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

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

create-xlsx-files-with-auto-filter-settings.php

Test.

Run the following codes.

$ php create-xlsx-files-with-auto-filter-settings.php

command line

Result.

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

// Set cell A1 with the "Filter Column A" string value
$sheet->setCellValue('A1', 'Filter Column A');

/**
 * Randomly fill cell A2 to A20 with
 * values ranging from 1 to 20.
 */
for ($i=2; $i<=20; $i++) {
    $sheet->setCellValue('A' . $i, rand(1, 20));
}

// Set auto filter to column A and B
$sheet->setAutoFilter('A1:B1');

// Set cell B1 with the "Filter Column B" string value
$sheet->setCellValue('B1', 'Filter Column B');

/**
 * Randomly fill cell B2 to B20 with
 * values ranging from A to Z.
 */
for ($i=2; $i<=20; $i++) {
    $sheet->setCellValue('B' . $i, chr(rand(65,90)));
}

// Set auto filter to column A and B
$sheet->setAutoFilter('A1:B1');

Test Again.

Click the arrow down icon beside ‘Filter Column A‘ to show the filter settings.

Then click Sort Largest to Smallest.

Result.

Test Again.

Click the arrow down icon beside ‘Filter Column B‘ to show the filter settings.

Then click Sort A to Z.

Result.

References:

• How to install PhpSpreadsheet
• Creating a spreadsheet
• Reading and writing to file
• PhpSpreadsheet Recipes