PHPExcel:无效的单元格坐标错误 [英] PHPExcel: Invalid cell coordinate error
问题描述
我正在CodeIgniter中扩展一个处理PHPExcel的控制器.到目前为止，到目前为止，我还没有到达Z列附近的任何地方.到达后，我受到了欢迎:
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:
致命错误:消息为无效单元格"的未捕获异常"Exception" 在/PATH/phpexcel/PHPExcel/Cell.php:513中协调AA':堆栈跟踪:#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'，'购买订单...')#3 [内部功能]:导出-> index()#4 /PATH/system/core/CodeIgniter.php(297):call_user_func_array(Array， 数组)#5/PATH/index.php(163):require_once('/PATH ...')#6 {main} 在第513行的/PATH/phpexcel/PHPExcel/Cell.php中抛出
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
我已经编写了一些代码来生成AB-AZ BB-BZ列，但是-如您在上面所看到的-PHPExcel没有它.
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);
第138行是:
$objPHPExcel->getActiveSheet()->setCellValue($array_column_indices[$x], $array_values_options['label']);
只需重申一下，直到我在MySQL查询中添加更多列为止，一切都像丝绸一样顺利.
Just to reiterate, everything was smooth as silk until I added more columns in the MySQL query.
PHPExcel对我来说还是个谜，因此任何帮助都是很棒的.
PHPExcel is something of a mystery to me, so any assistance would be wonderful.
格林尼治标准时间6月11日上午10:05
我对for()循环进行了一些更改:
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;
尽管已经解决了Z列问题，但我在电子表格中看到了奇怪的事情:
While this has gotten around the column Z problem, I'm seeing strange things in the spreadsheet:
- 在第1行的列名称之后，我有10行空白的空白单元格.
- 一个包含8行数据的块.
- 13行空单元格.
- 包含许多具有SUM()公式的单元格的行.
- 几乎100行的空单元格.
- 一个包含数据的23行的块.
8 + 23 = 31行数据，这是正确的，但是关于为什么公式之间存在间隙呢?不知道.
8 + 23 = 31 rows of data, which is correct, but as to why the formulas are in between, and the gaps? No idea.
所以，向前2步，向后1步.
So, 2 steps forwards, 1 step backward.
推荐答案
从堆栈转储中，您正在尝试获取单元格"AA"，这不是有效的Excel单元格引用...缺少行号:
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 ...')
请注意，有一个内置函数可以将列号转换为列名，PHPExcel_Cell::stringFromColumnIndex()，或者您可以利用PHP的PERL样式字符串递增功能，或者(如果您喜欢使用数字，对于列)，请使用工作表的setCellValueByColumnAndRow()方法
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
这篇关于PHPExcel:无效的单元格坐标错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
