Uncaught exception 'Exception' with message 'Column string index can not be longer than 3 characters.'

Topics: Developer Forum, User Forum
Jun 16, 2009 at 2:06 PM

So, using latest changeset (27946) I get that error when trying to convert a file to CSV using the following code:

load('test.xls');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->setDelimiter(';');
$objWriter->setEnclosure('');
$objWriter->save('test.csv');?>

Only included all code for completeness, as far as I can see the exception occurs already in the call to load(), stack trace below:

#0 phpexcel/Classes/PHPExcel/Cell.php(678): PHPExcel_Cell::columnIndexFromString('CRXP')
#1 phpexcel/Classes/PHPExcel/Reader/Excel5.php(3227): PHPExcel_Cell::extractAllCellReferencesInRange('CRXP10:IV10')
#2 phpexcel/Classes/PHPExcel/Reader/Excel5.php(688): PHPExcel_Reader_Excel5->_readHyperLink()
#3 test.php(9): PHPExcel_Reader_Excel5->load('test.xls')
#4 {main}
  thrown in /gandalf-home/jensadne/projects/dss/trunk/xlshack/phpexcel/Classes/PHPExcel/Cell.php on line 610

However, as far as I can see the file only has 19 columns, so there shouldn't be a problem with this.

Now, the problem is that the file that's causing trouble contains data owned by one of our customers, so posting it here is out of the question. And if I try to anonymize the file to be able to put it online the problem disappears. So, what can I do?

Developer
Jun 16, 2009 at 3:03 PM

Can you send me the file? (erik at phpexcel dot net)

Jun 16, 2009 at 3:36 PM

Sent.

Developer
Jun 16, 2009 at 3:54 PM

Thanks, I verified the error. Will take a look at this and post back here.

Developer
Jun 16, 2009 at 4:53 PM

Fixed here:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10142

You may download patch or wait for next source code release tomorrow.

Jun 17, 2009 at 10:30 AM

Yes, using today's source code release it works fine. Thanks. :-)

Jan 19, 2010 at 1:26 PM

I'm with problem on relation for index, when this are longer. I'm using array with data.

In the data, we index above 4 digits.

Stacktrace:

( ! ) Exception: Column string index can not be longer than 3 characters. in /var/www/fapes/maestro/utils/PHPExcelLib/PHPExcelEngine/Cell.php on line 640
Call Stack
#    Time    Memory    Function    Location
1    0.0008    70208    {main}( )    ../GeraExcel.php:0
2    3.1063    42111424    PHPExcel_Worksheet->setCellValueByColumnAndRow( )    ../GeraExcel.php:45
3    3.1064    42111704    PHPExcel_Worksheet->setCellValue( )    ../Worksheet.php:909
4    3.1064    42111908    PHPExcel_Worksheet->getCell( )    ../Worksheet.php:894
5    3.1065    42115012    PHPExcel_Cell::columnIndexFromString( )    ../Worksheet.php:996

Developer
Jan 19, 2010 at 1:35 PM
silasrm wrote:

I'm with problem on relation for index, when this are longer. I'm using array with data.

In the data, we index above 4 digits.

Stacktrace:

( ! ) Exception: Column string index can not be longer than 3 characters. in /var/www/fapes/maestro/utils/PHPExcelLib/PHPExcelEngine/Cell.php on line 640
Call Stack
#    Time    Memory    Function    Location
1    0.0008    70208    {main}( )    ../GeraExcel.php:0
2    3.1063    42111424    PHPExcel_Worksheet->setCellValueByColumnAndRow( )    ../GeraExcel.php:45
3    3.1064    42111704    PHPExcel_Worksheet->setCellValue( )    ../Worksheet.php:909
4    3.1064    42111908    PHPExcel_Worksheet->getCell( )    ../Worksheet.php:894
5    3.1065    42115012    PHPExcel_Cell::columnIndexFromString( )    ../Worksheet.php:996

Do you have some more information how we can reproduce this error? Are you reading or writing? Any sample code?

Jan 19, 2010 at 2:27 PM

I'm writing in Excel 2007.

My PHP generator:

-------------

    require_once 'PHPExcel/PHPExcel.php';
    require_once 'PHPExcel/PHPExcel/Writer/Excel2007.php';
    
    $dados = (array)json_decode( file_get_contents(urldecode($_GET['arquivo']) ) );
    $workbook = new PHPExcel;
    
    $sheet = $workbook->getActiveSheet();
    $sheet->setCellValue('A1', $dados['cabecalho'][0]);
    
    $lineStartVaga = 4;
    foreach( $dados['vagas'] as $vaga )
    {
        $sheet->setCellValue('A3', $vaga->descricao);
        
        $sheet->setCellValueByColumnAndRow( 0, $lineStartVaga, 'N. Inscrição');
        $sheet->setCellValueByColumnAndRow( 1, $lineStartVaga, 'Nome');
        $sheet->setCellValueByColumnAndRow( 2, $lineStartVaga, 'Nota Objetiva');
        
        if( $vaga->temTitulo == 1)
        {
            $sheet->setCellValueByColumnAndRow( 3, $lineStartVaga, 'Nota Título');
            $sheet->setCellValueByColumnAndRow( 4, $lineStartVaga, 'Nota Final');
            $sheet->setCellValueByColumnAndRow( 5, $lineStartVaga, 'Qtd. Acertos');
        }
        else
        {
            $sheet->setCellValueByColumnAndRow( 3, $lineStartVaga, 'Nota Final');
            $sheet->setCellValueByColumnAndRow( 4, $lineStartVaga, 'Qtd. Acertos');
        }
        
        $lineStart = $lineStartVaga + 1;
        foreach( $vaga->inscricoes as $inscricao )
        {
            $sheet->setCellValueByColumnAndRow( 0, $lineStart, $inscricao->nInscricao);
            $sheet->setCellValueByColumnAndRow( 1, $lineStart, $inscricao->nome);
            $sheet->setCellValueByColumnAndRow( 2, $lineStart, $inscricao->notaObjetiva);
            
            if( $vaga->temTitulo == 1)
            {
                $sheet->setCellValueByColumnAndRow( 3, $lineStart, $inscricao->notaTitulo);
                $sheet->setCellValueByColumnAndRow( $lineStart, $lineStart, $inscricao->notaFinal);
                $sheet->setCellValueByColumnAndRow( 5, $lineStart, $inscricao->qtdAcertos);
            }
            else
            {
                $sheet->setCellValueByColumnAndRow( 3, $lineStart, $inscricao->notaFinal);
                $sheet->setCellValueByColumnAndRow( 4, $lineStart, qtdAcertos);
            }
            
            $lineStart++;
        }
        
        $lineStartVaga += $lineStart + 3;
    }
    
    $writer = new PHPExcel_Writer_Excel2007($workbook);
    $writer->setOffice2003Compatibility(true);
    
    $records = '/var/www/fapes/arquivos/' . $_GET['concurso'] . '/resultados/resultado.xlsx';
    
    $writer->save($records);

-----

My data:

 

------

Array
(
[cabecalho] => Array
(
[0] => Resultado Parcial
)

[vagas] => Array
(
[0] => stdClass Object
(
[descricao] => AUXILIAR DE ENSINO - BANDIAÇU
[temTitulo] => 0
[inscricoes] => Array
(
[0] => stdClass Object
(
[nInscricao] => 2600019
[nome] => GRACIELA DE 0LIVEIRA MOTA
[notaObjetiva] => 0.00
[notatitulo] => 0.00
[notaFinal] => 0.00
[qtdAcertos] =>
)

[1] => stdClass Object
(
[nInscricao] => 2600406
[nome] => ROMILSON COSTA MOTA
[notaObjetiva] => 0.00
[notatitulo] => 0.00
[notaFinal] => 0.00
[qtdAcertos] =>
)

[2] => stdClass Object
(
[nInscricao] => 2600525
[nome] => MAIRA DA SILVA CEDRAZ CARNEIR0
[notaObjetiva] => 0.00
[notatitulo] => 0.00
[notaFinal] => 0.00
[qtdAcertos] =>
)
[...]
                            [1808] => stdClass Object
(
[nInscricao] => 2604387
[nome] => R0SANIA DA ANUNCIACA0 SILVA
[notaObjetiva] => 0.00
[notatitulo] => 0.00
[notaFinal] => 0.00
[qtdAcertos] =>
)

)

)

)

)

Developer
Jan 19, 2010 at 2:52 PM

@silasrm: I don't see any error.

In PHPExcel/Worksheet.php there is this function:

public function setCellValueByColumnAndRow($pColumn = 0, $pRow = 0, $pValue = null)
{
return $this->setCellValue(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow, $pValue);
}


Try to replace with this and post what you see on the screen. Maybe it will reveal something.

public function setCellValueByColumnAndRow($pColumn = 0, $pRow = 0, $pValue = null)
{
var_dump($pColumn, $pRow);
return $this->setCellValue(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow, $pValue);
}

 

Jan 19, 2010 at 8:23 PM

The error did not occur again, now everything is blank paga. No error or exception.

File with var_dump return: https://dl.dropbox.com/u/3963438/error_maestro_var_dump.txt

 

 

Developer
Jan 20, 2010 at 2:38 AM
silasrm wrote:

The error did not occur again, now everything is blank paga. No error or exception.

File with var_dump return: https://dl.dropbox.com/u/3963438/error_maestro_var_dump.txt

It looks like an error in your code. The problem is this line:

$sheet->setCellValueByColumnAndRow( $lineStart, $lineStart, $inscricao->notaFinal);

$lineStart becomes way too large. Can you double-check?

Jan 20, 2010 at 5:47 PM

Hi Koyama,

I correct this error of the lineStart, but the page remains blank. I checked this code again, but did not found a new errors.

I added various 'echo' for mark the zones of the code for find a last line executed. Do you have any tips to generate files with many lines?

Developer
Jan 21, 2010 at 5:12 AM
silasrm wrote:

Hi Koyama,

I correct this error of the lineStart, but the page remains blank. I checked this code again, but did not found a new errors.

I added various 'echo' for mark the zones of the code for find a last line executed. Do you have any tips to generate files with many lines?

Are you only getting a blank page when you have many rows? How many rows do you have, and what is your memory_limit set to in PHP?

Jan 21, 2010 at 11:50 AM

Yes. I getting a blank page when has many rows. Follow my settings:

max_execution_time = 480; Maximum execution time of each script, in seconds
max_input_time = 240; Maximum amount of time each script may spend parsing request data
;max_input_nesting_level = 64; Maximum input variable nesting level
memory_limit = 256M; Maximum amount of memory a script may consume (16MB)

I tested with 50 rows and all is okay. You know if exists one bug in the PHPExcel or can be configutation of the server?

Developer
Jan 21, 2010 at 11:58 AM
silasrm wrote:

Yes. I getting a blank page when has many rows. Follow my settings:

max_execution_time = 480; Maximum execution time of each script, in seconds
max_input_time = 240; Maximum amount of time each script may spend parsing request data
;max_input_nesting_level = 64; Maximum input variable nesting level
memory_limit = 256M; Maximum amount of memory a script may consume (16MB)

I tested with 50 rows and all is okay. You know if exists one bug in the PHPExcel or can be configutation of the server?

I don't know what the problem is. How long time are you waiting before you get that blank page?

Try to put this at the beginning of your script:

error_reporting(E_ALL);
ini_set('display_errors', '1');
Maybe it will reveal some warning message?

Jan 22, 2010 at 7:06 PM

Hi,

The unique error ocurred is:

Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 77 bytes) in /var/www/resultado/utils/PHPExcel/PHPExcel/Worksheet.php on line 1123

No warnings. I waiting between 2 a 3 minutes to get a blank page.

This error can be caused by circular references? It is using too much memory.