Generating Excel sheet with dynamic column names

Forget about naming Excel columns using letters and use this technique instead.

Posted on Jan 18, 2021 · 3 min read

This is actually my first technical post since I've started the blog as I've been using it more for writing other kind of stuff.

Here goes.

The problem

When we export an Excel sheet from PHP, or other language, the most common use case is to export a table from the database, and some of the times it also contains some information in the header, and a footer with some totals.

Bottom line, we have a table, and some columns.

This is how you'd go normally, by placing manually each column letter in the different parts of the document, such as:

  • Header
  • Rows
  • Styling
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Normal way of naming Excel columns');

$i=3;

// Header
$sheet->setCellValue('A'.$i, 'Name');
$sheet->setCellValue('B'.$i, 'Phone');
$sheet->setCellValue('C'.$i, 'City');

// Rows
foreach ($rows as $row) {
  $i++;
  $sheet->setCellValue('A'.$i, $row['name']);
  $sheet->setCellValue('B'.$i, $row['phone']);
  $sheet->setCellValue('C'.$i, $row['city']);
}

// Styling
$lastRowIndex=$i;

$sheet->getStyle('A3:C'.$lastRowIndex)->applyFromArray([
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ],
    ],
]);

$sheet->getStyle('A3:C3')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFA0A0A0');

$sheet->getColumnDimension('A')->setWidth(12);
$sheet->getColumnDimension('B')->setWidth(25);
$sheet->getColumnDimension('C')->setWidth(25);

$writer = new Xlsx($spreadsheet);
$writer->save('normal.xlsx');

The problem with this approach is that if you want to add new columns, you'll have to spend some time renaming all the letters. And you'll also have to remember which letter you have assigned to each column when you apply styling.

So if you want to add the column Company after Phone, you'll have to put it on the letter C, and rename the next column C to D, and do this in renaming everywhere, not just in the header, also in the rows and in the styling, etc.

You'd say it's a quick task, but..

  • What if you had 10-20 columns or more?
  • What if you wanted to have some cases when you show a column and other cases when you want to hide it based on user preference when exporting?

The solution

Okay it might not be the best solution, I'm sure others might think of better ways to do it but this is one way to avoid spending lots of time renaming the columns.

// Load the column names helper
require 'ExCol.php';

ExCol::reset(); // reset mapping before using

$show_company_column=true;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Dynamic way of naming Excel columns');

$i=3;

// Header
$sheet->setCellValue(ExCol::get('name', $i), 'Name');
$sheet->setCellValue(ExCol::get('phone', $i), 'Phone');
if ($show_company_column) {
  $sheet->setCellValue(ExCol::get('company', $i), 'Company');
}
$sheet->setCellValue(ExCol::get('city', $i), 'City');

// Rows
foreach ($rows as $row) {
  $i++;
  $sheet->setCellValue(ExCol::get('name', $i), $row['name']);
  $sheet->setCellValue(ExCol::get('phone', $i), $row['phone']);
  if ($show_company_column) {
    $sheet->setCellValue(ExCol::get('company', $i), $row['company']);
  }
  $sheet->setCellValue(ExCol::get('city', $i), $row['city']);
}

$lastColLetter=ExCol::getLast();

// Styling
$lastRowIndex=$i;

$sheet->getStyle('A3:'.$lastColLetter.$lastRowIndex)->applyFromArray([
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ],
    ],
]);

$sheet->getStyle("A3:{$lastColLetter}3")->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFA0A0A0');

$sheet->getColumnDimension(ExCol::get('name'))->setWidth(12);
if ($show_company_column) {
  $sheet->getColumnDimension(ExCol::get('company'))->setWidth(35);
}
$sheet->getColumnDimension(ExCol::get('phone'))->setWidth(25);
$sheet->getColumnDimension(ExCol::get('city'))->setWidth(25);

$writer = new Xlsx($spreadsheet);
$writer->save('dynamic.xlsx');

So how does it work?

Well, we have created a small helper class named ExCol.

I know, I could have found a better name but at least it is short.

<?php

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class ExCol {

  static private $_map=array();

  static public function get($col, $row=null) {
    if (!in_array($col, self::$_map)) {
      self::$_map[]=$col;
    }
    $index = array_search($col, self::$_map);
    $columnLetter = Coordinate::stringFromColumnIndex($index + 1);
    return $columnLetter.($row?$row:null);
  }

  static public function getLast() {
    return Coordinate::stringFromColumnIndex(count(self::$_map));
  }

  static public function reset() {
    self::$_map=array();
  }

}

With this class, every time we need a letter for a column, we just call the method ExCol::get().

Examples: 

ExCol::get('my_first_column'); // Returns A
ExCol::get('my_second_column'); // Returns B
ExCol::get('my_third_column'); // Returns C
ExCol::get('my_third_column', 2); // Returns C2

Optionally we can also pass a $row number so instead of getting back C, we'll get C2 and avoid having to use concatenation.

So the class stores statically an index of the columns requested in the order they were requested. And based on the index assigned to each column, it will return its letter, by using PhpSpreadsheet's Coordinate::stringFromColumnIndex() method.

So let's go over the new parts in the code.

If we use the helper on a script that is used to generate multiple Excel files, we might want to call the reset() method to clear the index just in case so it won't contain the columns of the previous file.

ExCol::reset(); // reset mapping before using

We then define a variable for showing or hiding one of the columns: 

$show_company_column=true;

And when printing the header, we only include that column if required: 

// Header
$sheet->setCellValue(ExCol::get('name', $i), 'Name');
$sheet->setCellValue(ExCol::get('phone', $i), 'Phone');
if ($show_company_column) {
  $sheet->setCellValue(ExCol::get('company', $i), 'Company');
}
$sheet->setCellValue(ExCol::get('city', $i), 'City');

Same happens when looping the rows and with the styling: 

// Rows
foreach ($rows as $row) {
  $i++;
  $sheet->setCellValue(ExCol::get('name', $i), $row['name']);
  $sheet->setCellValue(ExCol::get('phone', $i), $row['phone']);
  if ($show_company_column) {
    $sheet->setCellValue(ExCol::get('company', $i), $row['company']);
  }
  $sheet->setCellValue(ExCol::get('city', $i), $row['city']);
}

So you can see we're dynamically including one column, so if we only put 3 columns we'll the helper will give us A, B and C, but if we put 4, we'll get A, B, C, D.

If you want to control dynamically the columns to show, or you need to add more columns or remove some, there will be no need for renaming anything as it will all happen automatically.

Full source code here:

https://github.com/atrandafir/excel-dynamic-columns

Credits

Thanks to https://www.generatedata.com/ for generating the demo data, and to https://highlightjs.org/ for the code blocks highlight.