Automatically group and hide columns when creating xlsx files using PhpSpreadsheet, usable for generating wide xlsx sheets that needs to show only important data by default.

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

/**
 * Loop through 'Column A - D' to
 * set data and hide grouped columns.
 */
foreach (range('A', 'D') as $letter) {
    // Set some data to 'Column A - D'
    $sheet->setCellValue($letter . '1', 'Column ' . $letter);

    // Group and hide 'Column A - D'
    $sheet->getColumnDimension($letter)->setOutlineLevel(1)->setVisible(false)->setCollapsed(true);
}

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

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

create-xlsx-files-with-hidden-group-column-settings.php

Test.

Run the following codes.

$ php create-xlsx-files-with-hidden-group-column-settings.php

command line

Result.

Open the generated file create-xlsx-files-with-hidden-group-column-settings.xlsx.

Excel 2007 collapse requires the ‘visibility’ function, setVisible(true/false).

/**
 * Loop through 'Column A - D' to
 * set data and hide grouped columns.
 */
foreach (range('A', 'D') as $letter) {
    // Set some data to 'Column A - D'
    $sheet->setCellValue($letter . '1', 'Column ' . $letter);

    // Group and hide 'Column A - D'
    $sheet->getColumnDimension($letter)->setOutlineLevel(1)->setVisible(false)->setCollapsed(true);
}

Show grouped columns.

Click on the ‘plus’ sign covering the group.

‘Column A – D’ are now visible.

Click the ‘minus sign’ to hide ‘Column A – D’ again.

Manually hide ‘Column A – D’.

• 1. Select/highlight ‘Column A – D’.
• 2. Click ‘Data’.
• 3. Click ‘Group’.
• 4. Select ‘Columns’ and click ‘OK’.
• 5. Click the ‘minus sign’.

Result.

Column A – D are now hidden.

References:

• How to install PhpSpreadsheet
• Create Xlsx Files With Column Width Settings
• Creating a spreadsheet
• Reading and writing to file
• PhpSpreadsheet Recipes