PHPExcel导出数据时字段超过26列出错Invalid cell coordinate [1

http://blog.csdn.net/dl425134845/article/details/46650961

以下是解决方案函数

 

/**
*   方法名:    getExcel
*   作用  :将数据导出excel表格
*   @date   2015/03/26
*   @author dingling
*   @param1 文件名
*   @param2 字段名
*   @param3 数据
*   @return excel文件
*/
function getExcel($fileName,$headArr,$data){
    //对数据进行检验
    if(empty($data) || !is_array($data)){
        die("数据必须为数组");
    }
    //检查文件名
    if(empty($fileName)){
        exit;
    }
    //组装文件名
    $date = date("Y_m_d",time());
    $fileName .= "_{$date}.xls";

    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    date_default_timezone_set('PRC');

    if (PHP_SAPI == 'cli')
        die('只能通过浏览器运行');

    //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
    import("Org.Util.PHPExcel");
    import("Org.Util.PHPExcel.Writer.Excel5");
    import("Org.Util.PHPExcel.IOFactory.php");

    //创建PHPExcel对象，注意，不能少了\
    $objPHPExcel = new \PHPExcel();
    $objProps = $objPHPExcel->getProperties();

    //设置表头
    <span style="color:#FF0000;">$key = ord("A");//A--65
    $key2 = ord("@");//@--64</span>
    foreach($headArr as $v){
        <span style="color:#FF0000;">if($key>ord("Z")){
            $key2 += 1;
            $key = ord("A");
            $colum = chr($key2).chr($key);//超过26个字母时才会启用  dingling 20150626
        }else{
            if($key2>=ord("A")){
                $colum = chr($key2).chr($key);
            }else{
                $colum = chr($key);
            }
        }</span>
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', L($v['COMMENT']));
        $key += 1;
    }

    $column = 2;
    $objActSheet = $objPHPExcel->getActiveSheet();

    foreach($data as $key => $rows){ //行写入
        <span style="color:#FF0000;">$span = ord("A");
        $span2 = ord("@");</span>
        foreach($headArr as $k=>$v){
            <span style="color:#FF0000;">if($span>ord("Z")){
                $span2 += 1;
                $span = ord("A");
                $j = chr($span2).chr($span);//超过26个字母时才会启用  dingling 20150626
            }else{
                if($span2>=ord("A")){
                    $j = chr($span2).chr($span);
                }else{
                    $j = chr($span);
                }
            }</span>
            //$j = chr($span);
            $objActSheet->setCellValue($j.$column, strip_tags($rows[$v['FIELD']]));
            $span++;
        }
        $column++;
    }

    $fileName = iconv("utf-8", "gb2312", $fileName);

    // $objPHPExcel->getActiveSheet()->setTitle('CNLINK');// 重命名表
    $objPHPExcel->setActiveSheetIndex(0);// 设置活动单指数到第一个表,所以Excel打开这是第一个表


    // Redirect output to a client’s web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment;filename=\"$fileName\"");
    header('Cache-Control: max-age=0');

    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');
    // If you're serving to IE over SSL, 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, 'Excel5');
    // $objWriter->save('1.xls');
    // echo str_replace('.php', '.xls', __FILE__);
    $objWriter->save('php://output'); //文件通过浏览器下载

    exit;
}

红色部分是解决问题的关键