phpexcel中PHPExcel_Exception Invalid cell coordinate 解决办法

推荐 源码巴士 722浏览

如果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;

}

转载请注明:源码巴士 » phpexcel中PHPExcel_Exception Invalid cell coordinate 解决办法