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.
<?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(); /** * 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.
