Create Xlsx Files With Visible Group Column Settings

| September 13, 2020 | 1,501 views | PhpSpreadsheet

Automatically gather and bracket a list of column data to easily hide or show each of them, giving more focus to work on a specific group of data more precisely. This outline function is obtainable when creating an xlsx file with PhpSpreadsheet.

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.

getActiveSheet();

/**
 * Group and display column A - D.
 */
foreach (range('A', 'D') as $letter) {
    $sheet->setCellValue($letter . '1', 'Column ' . $letter);
    $sheet->getColumnDimension($letter)->setOutlineLevel(1)->setVisible(true)->setCollapsed(false);
}

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

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

Test.

Run the following codes.

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

Result.

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

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

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

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

Hide grouped columns.

Click on the ‘minus’ sign covering the group.

‘Column A – D’ are now hidden.

Click the ‘plus sign’ to show ‘Column A – D’ again.

Manually ungroup ‘Column A – D’.

  • 1. Select/highlight ‘Column A – D’.
  • 2. Click ‘Data’.
  • 3. Click ‘Ungroup’.
  • 4. Select ‘Columns’ and click ‘OK’.

Result.

References:

0 Comments

Leave a Reply

Your email address will not be published.