1、当导出的首行字段超过26的字符的时候,就会报出错误,错误信息如下: PHPExcel_Exception Invalid cell coordinate [1 原因: 这个不是条数多了出问题,而是列超过26列出的问题。 仔细看看excel表格,z后面是AA、AB、AC,因此输出的时候需要判断。 解决办法:
public function actionExcel()
{
require_once dirname(dirname(__FILE__)) . '/components/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$excel_data = self::getExcelData();
$objPHPExcel->getProperties()->setCreator("suv3")
->setLastModifiedBy("suv3")
->setTitle("suv3")
->setSubject("suv3")
->setDescription("suv3")
->setKeywords("suv3")
->setCategory("suv3");
$first_row_name = ['日期', '渠道', '新增账号', 'D1累计', 'D1LTV', 'D2累计', 'D2LTV', 'D3累计', 'D3LTV', 'D4累计', 'D4LTV', 'D5累计', 'D5LTV', 'D6累计', 'D6LTV', 'D7累计', 'D7LTV',
'D15累计', 'D15LTV', 'D30累计', 'D30LTV', 'D45累计', 'D45LTV', 'D60累计', 'D60LTV', 'D75累计', 'D75LTV', 'D90累计', 'D90LTV'];
$ordA = ord('A');
$key2 = ord("@");
$objPHPExcel->setActiveSheetIndex(0);
foreach ($first_row_name as $key => $val)
{
if($ordA > ord("Z"))
{
$colum = chr(ord("A")).chr(++$key2);
}
else
{
$colum = chr($ordA++);
}
$objPHPExcel->getActiveSheet()->SetCellValue($colum.'1', $val);
}
$index_row = 2;
$ii = 0;
foreach ($excel_data as $kk => $rows)
{
$ordA = ord('A');
$key2 = ord("@");
foreach($rows as $key => $val)
{
if($ordA > ord("Z"))
{
$colum = chr(ord("A")).chr(++$key2);
}
else
{
$colum = chr($ordA++);
}
$objPHPExcel->getActiveSheet()->setCellValue($colum.$index_row, $val);
}
$index_row++;
}
$pro_name = Product::find()->select(['product_name'])->andWhere(['product_id' => \Yii::$app->request->get('pro_id')])->asArray()->one()['product_name'];
$excel_name = $pro_name . '-付费分析';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $excel_name . '.xlsx"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');
header ('Cache-Control: cache, must-revalidate');
header ('Pragma: public');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}