我在 CodeIgniter 中扩展了一个处理 PHPExcel 的 Controller 。到目前为止，我还没有接近 Z 列，直到现在。到达后，迎接我的是:

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 没有它。

$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 查询中添加更多列之前，一切都非常顺利。

PHPExcel 对我来说有点神秘，所以任何帮助都会很棒。

编辑 6 月 11 日，格林威治标准时间上午 10:05

我对 for() 循环做了一些更改:

$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 列问题，但我在电子表格中看到了一些奇怪的事情:

• 在第 1 行的列名称之后，我有 10 行空白单元格。
• 包含 8 行数据的 block 。
• 13 行空单元格。
• 包含多个单元格的行，其中包含 SUM() 公式。
• 将近 100 行空单元格。
• 包含 23 行数据的 block 。

8 + 23 = 31 行数据，这是正确的，但至于为什么公式介于两者之间，并且有差距？不知道。

因此，前进 2 步，后退 1 步。

最佳答案

从堆栈转储中，您试图获取单元格“AA”，这不是有效的 Excel 单元格引用...它缺少行号:

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

请注意，有一个内置函数可以将列号转换为列名，PHPExcel_Cell::stringFromColumnIndex() 或者您可以利用 PHP 的 PERL 样式递增字符串的能力，或者(如果您更喜欢使用列的数字)使用工作表的 setCellValueByColumnAndRow() 方法

关于PHPExcel:无效的单元格坐标错误，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/24147526/