Export Error after accessing Cell by name

[Gerdo488]

I am using the access by name to write content to fields. With older versions like PHPExcel 1.7.4. this was no problem.

$objReader	= IOFactory::createReader("Excel2007");
$objPHPExcel	= $objReader->load("/var/www/Test.xlsx");

$key = 'REFERENT';
$value = "test";

$objPHPExcel->getSheetByName('TNL')->setCellValue($key, `$value);

$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$tempfile = tempnam(ini_get("upload_tmp_dir") . "/", "xlsx");
$writer->save($tempfile);

Now you will get this error:

Uncaught exception 'PhpOffice\PhpSpreadsheet\Exception' with message 'Column string index can not be longer than 3 characters' in
\PhpSpreadsheet\PhpSpreadsheet\Cell.php:819 Stack trace:
 #0 \PhpSpreadsheet\PhpSpreadsheet\Worksheet.php(2652): PhpOffice\PhpSpreadsheet\Cell::columnIndexFromString('REFERENT') 
#1 \PhpSpreadsheet\PhpSpreadsheet\Spreadsheet.php(1134): PhpOffice\PhpSpreadsheet\Worksheet->garbageCollect() 
#2 \PhpSpreadsheet\PhpSpreadsheet\Writer\Excel2007.php(176): PhpOffice\PhpSpreadsheet\Spreadsheet->garbageCollect()

[PowerKiKi]

Please provide a Minimal, Complete, and Verifiable example of code that exhibits this issue without relying on an external Excel file.

[Gerdo488]

I try to create a example without an externale sheet. But I think the problem is the writing of named areas. I PHPSpreadsheet it is not possible to write them in lower case. In a normale Excel Sheet it is possible.

<?php
require_once 'global/thirdparty/PhpSpreadsheet/Autoloader.php';

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;
use \PhpOffice\PhpSpreadsheet\NamedRange;

\PhpOffice\PhpSpreadsheet\Autoloader::register();

// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
$spreadsheet->setActiveSheetIndex(0);

// Define named ranges
$spreadsheet->addNamedRange( new NamedRange('Referent', $spreadsheet->getActiveSheet(), 'B1') );

// Add some data
$spreadsheet->getActiveSheet()->setCellValue('Referent', 'Hello');
		
// Save
$writer = IOFactory::createWriter($spreadsheet, "Excel2007");
$tempfile = tempnam(ini_get("upload_tmp_dir") . "/", "xlsx");
$writer->save($tempfile);
    	
// Return Spreadsheet		
ob_clean();
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="test.xlsx"');
header('Content-Length: ' . filesize($tempfile));
    	 
readfile($tempfile);
unlink($tempfile);
die();   	 

[PowerKiKi]

Cannot reproduce with latest version of develop branch. The following code will write and reload correctly a named ranged with CamelCase:

<?php

require __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\IOFactory;

// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Define named ranges
$spreadsheet->addNamedRange(new NamedRange('Referent', $spreadsheet->getActiveSheet(), 'B1'));

// Add some data
$spreadsheet->getActiveSheet()->setCellValue('Referent', 'Hello');

// Save
$filename = 'test.xlsx';
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save($filename);

// Reload and check result
$reloadedSpreadsheet = IOFactory::load($filename);
var_dump($reloadedSpreadsheet->getActiveSheet()->getCell('B1')->getCalculatedValue());
var_dump($reloadedSpreadsheet->getActiveSheet()->getCell('Referent')->getCalculatedValue());

The output will correctly be:

string(5) "Hello"
string(5) "Hello"