如果excel文件是xlsx格式,请考虑使用这个库:https://github.com/shuchkin/simplexlsx
1、当导出的首行字段超过26的字符的时候,就会报出错误,错误信息如下:
PHPExcel_Exception Invalid cell coordinate [1
原因:
这个不是条数多了出问题,而是列超过26列出的问题。
仔细看看excel表格,z后面是AA、AB、AC,因此输出的时候需要判断。
解决办法:
public function actionExcel()
{
/** Include PHPExcel */
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'); //65
$key2 = ord("@"); //64
$objPHPExcel->setActiveSheetIndex(0); //表示使用的是第一个sheet
foreach ($first_row_name as $key => $val)
{
if($ordA > ord("Z"))
{
$colum = chr(ord("A")).chr(++$key2);//超过26个字母 AA1,AB1,AC1,AD1...BA1,BB1...
}
else
{
$colum = chr($ordA++);
}
$objPHPExcel->getActiveSheet()->SetCellValue($colum.'1', $val);
}
$index_row = 2; //从第二行开始插入数据
$ii = 0;
foreach ($excel_data as $kk => $rows)
{
$ordA = ord('A');//重新从A开始
$key2 = ord("@"); //64
foreach($rows as $key => $val)
{
if($ordA > ord("Z"))
{
$colum = chr(ord("A")).chr(++$key2);//超过26个字母 AA1,AB1,AC1,AD1...BA1,BB1...
}
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');// If you're serving to IE 9, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');//文件通过浏览器下载
exit;
}