15

Using PHPExcel, is it possible to get the name of a column located X number of columns to the left or right?

Example, given column BZ, I'd like to return column name CB or BX. (2 to the right or left)

Thanks

raidzero
  • 299
  • 1
  • 4
  • 13
  • One quick hack would be to calculate the 'value' of `CB` as a number, add 2 and then lookup that value. You'd have an array `$lettervalues {0 => null, 1 => 'A', ... 'Z' => 26}` and do some math in PHP. – Philip Whitehouse Feb 28 '13 at 22:47

1 Answers1

36

There are functions already built into PHPExcel to help you do this

$adjustment = -2;
$currentColumn = 'BZ';

$columnIndex = PHPExcel_Cell::columnIndexFromString($currentColumn);
$adjustedColumnIndex = $columnIndex + $adjustment;
$adjustedColumn = PHPExcel_Cell::stringFromColumnIndex($adjustedColumnIndex - 1);

Note the (historic) discrepancy that columnIndexFromString() will return a 1 for column A, but that stringFromColumnIndex expects a 0 to correspond to column A

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • This is just what I was hoping for. Thanks! – raidzero Mar 01 '13 at 19:21
  • 1
    PHPExcel has now been superceded by PhpSpreadsheet and `stringFromColumnIndex` was moved to a new class called `\PhpOffice\PhpSpreadsheet\Cell\Coordinate` See docs [here](https://phpspreadsheet.readthedocs.io/en/latest/topics/migration-from-PHPExcel/) – David Jan 06 '20 at 08:42