Create Xlsx Files With Hidden Group Column Settings

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.

composer.json

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.3"
    }
}

Step 2.

Install phpspreadsheet.

command line

$ composer install

Step 3.

Create a new PHP file, and start coding.

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

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

Test.

Run the following codes.

command line

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

Result.

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

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

PHP

/**
 * 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 PhpSpreadsheetOn this site

Create Xlsx Files With Column Width SettingsOn this site

Creating a spreadsheetphpspreadsheet.readthedocs.io

Reading and writing to filephpspreadsheet.readthedocs.io

PhpSpreadsheet Recipesphpspreadsheet.readthedocs.io