PHPExcel: Invalid cell coordinate error

php mysql codeigniter phpexcel

32,592

Solution 1

From the stack dump, you're trying to get cell 'AA', which isn't a valid Excel cell reference... it's missing a row number:

PHPExcel_Worksheet->setCellValue('AA', 'Purchase Order ...')

Note that there is a built-in function that can convert a column number to a column name, PHPExcel_Cell::stringFromColumnIndex() or you can take advantage of PHP's ability to increment strings PERL-style, or (if you prefer to work with numbers for columns) use the worksheet's setCellValueByColumnAndRow() method

Solution 2

In the end, what I had was close, but required a few tweaks:

$columns = ( ... count(of columns) ... );
$iterator_for_column_range=1;
// Assign the letters of the alphabet to the column ranges.
for ($x = 65; $x <= 90; $x++):
    for ($y = 65; $y <= 90; $y++):
        if (chr($x) == "A"):
            $array_column_indices[$iterator_for_column_range] = chr($y);
        else:
            $array_column_indices[$iterator_for_column_range] = chr($x-1) . chr($y);
        endif;
        if ($iterator_for_column_range == $columns) break;
        $iterator_for_column_range++;
    endfor;
endfor;

Here, the $columns variable stops the columns from running on.

Also, made changes in the foreach loops to the iterator variables, so that they only iterate once each pass:

$objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[**$index_of_columns_for_column_name**] . **$index_of_range**, $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['bookings'][$keys]);

Done!

32,592

Author by

Wayne Smallman

Updated on July 05, 2022

Comments

Wayne Smallman almost 2 years

I'm extending a controller in CodeIgniter that handles PHPExcel. So far, I've not gotten anywhere near column Z, until now. Having arrived, I'm greeted with:

Fatal error: Uncaught exception 'Exception' with message 'Invalid cell coordinate AA' in /PATH/phpexcel/PHPExcel/Cell.php:513 Stack trace: #0 /PATH/phpexcel/PHPExcel/Worksheet.php(1119): PHPExcel_Cell::coordinateFromString('AA') #1 /PATH/phpexcel/PHPExcel/Worksheet.php(1022): PHPExcel_Worksheet->getCell('AA') #2 /PATH/application/modules/bookings/controllers/export.php(138): PHPExcel_Worksheet->setCellValue('AA', 'Purchase Order ...') #3 [internal function]: Export->index() #4 /PATH/system/core/CodeIgniter.php(297): call_user_func_array(Array, Array) #5 /PATH/index.php(163): require_once('/PATH...') #6 {main} thrown in /PATH/phpexcel/PHPExcel/Cell.php on line 513

I've written some code to generate AB-AZ BB-BZ columns, but — as you can see above — PHPExcel isn't having it.

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_sqlite;
$cacheSettings = array('memoryCacheSize' => '32MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setTitle($arrayAttr['form']['title']);
$objPHPExcel->getProperties()->setDescription("Export of Advanced Search");
$objPHPExcel->getProperties()->setCreator("Me!");
$objPHPExcel->getProperties()->setLastModifiedBy("Me!");
// Assign cell values
$objPHPExcel->setActiveSheetIndex(0);
$a=1;
$p=1;
for ($s=65; $s<=90; $s++):
    // Assign the letters of the alphabet.
    for ($i=65; $i<=90; $i++):
        if (chr($s) == "A"):
            $array_column_indices[$a] = chr($i) . $p;
        else:
            $array_column_indices[$a] = chr($s-1) . chr($i);
        endif;
        $a++;
    endfor;
    $p++;
endfor;
$b=2;
foreach ($arrayAttr['results']['data_search']['results'] as $index_search_advanced => $array_value_search_advanced):
    $x=1;
    foreach ($arrayAttr['results']['data_search']['results'][0] as $keys => $values):
        if (array_key_exists($keys, $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['bookings'])
            && ($keys != 'clients_options')):
            $objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$x],
            $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['bookings'][$keys]);
            $x++;
        elseif (array_key_exists($keys, $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['bookings_attendees'])
            && ($keys != 'clients_options')):
            $objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$x],
            $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['bookings_attendees'][$keys]);
            $x++;
        elseif (array_key_exists($keys, $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['venues'])
            && ($keys != 'clients_options')):
            $objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$x],
            $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['venues'][$keys]);
            $x++;
        elseif (array_key_exists($keys, $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['values'])
            && ($keys != 'clients_options')):
            $objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$x],
            $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['values'][$keys]);
            $x++;
        elseif ($keys == 'clients_options'):
            $array_options_for_booking = explode(chr(0x1D), $values);
            $a_=0;
            $array_options_sorted = array();
            foreach ($array_options_for_booking as $option):
                if (strstr($option, chr(0x1F))):
                    $array_options_sorted[] = implode('', array_slice(explode(chr(0x1F), $option), $a_, 1));
                else:
                    $array_options_sorted[] = $option;
                endif;
                $a_++;
            endforeach;
            // Write the custom column values.
            foreach ($array_options_fields as $index_options => $array_values_options):
                $objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$x], $array_values_options['label']);
                $x++;
            endforeach;
        endif;
    endforeach;
    // Keep a count of the actual columns within the results, rather than just the columns used when displaying the results.
    $c=1;
    foreach ($array_value_search_advanced as $value_search_advanced):
        // "U" is the column where the custom values first occur.
        if (strstr($array_column_indices[$c], "U")):
            $array_options_for_booking = explode(chr(0x1D), $value_search_advanced);
            $a_=0;
            $array_options_sorted = array();
            foreach ($array_options_for_booking as $option):
                if (strstr($option, chr(0x1F))):
                    $array_options_sorted[] = implode('', array_slice(explode(chr(0x1F), $option), $a_, 1));
                else:
                    $array_options_sorted[] = $option;
                endif;
                $a_++;
            endforeach;
            $d=$c;
            $e=1;
            // Write the custom column values.
            foreach ($array_options_sorted as $index_options => $array_values_options):
                $objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$d] . $b, $array_values_options);
                $d++;
                $e++;
            endforeach;
            $c=($d-1);
        // Else, if the columns are Venue Rate...
        elseif (strstr($array_column_indices[$c], "E")):
            // ... apply the number format to the cells.
            $objPHPExcel->getActiveSheet()->getStyle($array_column_indices[$c] . $b)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            // Write the regular column values.
            $objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$c] . $b, $value_search_advanced);
        // Else, if the Number of Nights...
        elseif (strstr($array_column_indices[$c], "O")):
            // ... apply the number format to the cells.
            $objPHPExcel->getActiveSheet()->getStyle($array_column_indices[$c] . $b)->getNumberFormat()->setFormatCode();
            // Write the regular column values.
            $objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$c] . $b, $value_search_advanced);
        else:
            // Write the regular column values.
            $objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$c] . $b, $value_search_advanced);
        endif;
        $c++;
    endforeach;
    $b++;
endforeach;
$z = ($b-1);
//print_r($objPHPExcel);
$objPHPExcel->getActiveSheet()->setCellValueExplicit("E" . $b, '=SUM(E2:E' . $z . ')');
$objPHPExcel->getActiveSheet()->setCellValueExplicit("O" . $b, '=SUM(O2:O' . $z . ')');
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Search Results.xls"');
// Save it as a Micrtosoft Excel 2003 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
$objWriter->save("library/profiles/reports/spreadsheet.xls");
$objWriter->save('php://output');
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);

Line 138 is:

$objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$x], $array_values_options['label']);

Just to reiterate, everything was smooth as silk until I added more columns in the MySQL query.

PHPExcel is something of a mystery to me, so any assistance would be wonderful.

Edit 11 June, 10:05am GMT

I've made some changes to the for() loops:

$a=1;
$p=1;
$r=0;
for ($s=65; $s<=90; $s++):
    // Assign the letters of the alphabet.
    for ($i=65; $i<=90; $i++):
        if (chr($s) == "A"):
            $array_column_indices[$a] = chr($i) . $p;
        else:
            $array_column_indices[$a] = chr($s-1) . chr($i) . $p;
        endif;
        $a++;
    endfor;
    $r++;
endfor;

While this has gotten around the column Z problem, I'm seeing strange things in the spreadsheet:

After the column names in row 1, I have 10 row gap of empty cells.
A block of 8 rows with data.
13 rows of empty cells.
A row containing a number of cells with SUM() formula in them.
Almost a 100 rows of empty cells.
A block of 23 rows with data.

8 + 23 = 31 rows of data, which is correct, but as to why the formulas are in between, and the gaps? No idea.

So, 2 steps forwards, 1 step backward.

Mr. Llama almost 10 years

What are the values of $array_column_indices[$x] and $array_values_options['label'] at the time of failure?
Wayne Smallman almost 10 years

@GigaWatt they're there, third line from the bottom.
Wayne Smallman almost 10 years

@GigaWatt I was writing while on the move earlier. In the error message, third line from the bottom, you'll see: "('AA', 'Purchase Order ...')". So I'm assuming the label values are good, because that's one of the label values.

Wayne Smallman almost 10 years

I'm confused, because when I look at Excel, after column Z, it goes AB-AZ and so on. So why would what I'm doing not be valid? I need to know what PHPExcel needs to see, or I can't write the code. I've looked, but I can't find any guidance on column range values.
Wayne Smallman almost 10 years

Okay, I see what's happening; irrespective of the column name, PHPExcel also needs the row number in there, too. I'll give that a go...
Mark Baker almost 10 years

That's right.... a cell reference in both PHPExcel and MS Excel is both column and row
Wayne Smallman almost 10 years

I'm trying to adapt the for() loops, and while it's giving me something, it's splitting the rows out into groups separated by blocks of empty rows. I don't feel confident using setCellValueByColumnAndRow() as it looks as though it'd need a re-write.