php导出数据库记录到excel中

记录两种方式，一种是适用于列数少于26，一种是兼容大于26列的。

方法参数均为：文件名，列名数组，行数据数组

1、适用于少于26行，其中cw::$config为配置文件的自定义的，根据需要修改即可

 //列多余26行时，不适用
   public static function exportToExcel($expTitle, $expCellName, $expTableData) {
        if (PHP_SAPI == 'cli')
            die('只能通过浏览器运行');

        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle); //文件名称
        $fileName = $xlsTitle . '_' . date('YmdHis');
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        require_once cw::$config['export']['dir'];
        $objPHPExcel = new \PHPExcel();

        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
        //$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
//        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
//         $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '1', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 2), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.' . cw::$config['export']['extension'] . '"');
        header("Content-Disposition:attachment;filename=$fileName." . cw::$config['export']['extension']); //attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, cw::$config['export']['type']); //Excel5为xls格式，excel2007为xlsx格式
        $objWriter->save('php://output');
        exit;
    }

2、列多余26行完美解决

 //列多余26行完美解决
   public static   function getExcel($fileName, $headArr, $data) {
        //组装文件名  
        $date = date("Y_m_d", time());
        $fileName .= "_{$date}.".cw::$config['export']['extension'];


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

        require_once cw::$config['export']['dir'];

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

        //设置表头  
        $key = ord("A"); //A=65  
        $key2 = ord("@"); //@=64</span>  
        foreach ($headArr as $v) {
            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);
                }
            }
           
           $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v[1]);
            $key += 1;
        }

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

        foreach ($data as $key => $rows) { //行写入  
            $span = ord("A");
            $span2 = ord("@");

            foreach ($headArr as $k => $v) {
                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);
                    }
                }
                //$j = chr($span);  
                $objActSheet->setCellValue($j . $column, $rows[$v[0]]);
                $span++;
            }
            $column++;
        }

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

        $objPHPExcel->getActiveSheet()->setTitle('Order List');// 重命名表  
        $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, cw::$config['export']['type']);
        // $objWriter->save('1.xls');  
        // echo str_replace('.php', '.xls', __FILE__);  
        $objWriter->save('php://output'); //文件通过浏览器下载  

        exit;
    }