zjutsxj
2/1/2018 - 9:26 AM

phpexcel 导出excel

将数据库的数据导出为excel表格 需要导入PHPExcel扩展

/**
 * [downexcel 数据导出成excel]
 * @param  [type] $filename [文件名]
 * @param  [type] $header   [表头]
 * @param  [type] $data     [数据]
 * @return [type]           [无返回]
 */
function downexcel($filename, $header, $data){
    vendor("PHPExcel.PHPExcel");
    vendor("PHPExcel.PHPExcel.Writer.Excel5");
    vendor("PHPExcel.PHPExcel.IOFactory");

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

    $date = date("Ymd",time());
    $filename = "{$date}_$filename.xls";
    $filename = iconv("utf-8", "gb2312", $filename);

    //设置表头
    $key = ord("A");
    //print_r($headArr);exit;
    foreach($header as $v){
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
        $key += 1;
    }

    $column = 2;
    $objActSheet = $objPHPExcel->getActiveSheet();
    ob_clean();
    //print_r($data);exit;
    foreach($data as $key => $rows){ //行写入
        $span = ord("A");
        foreach($rows as $keyName=>$value){// 列写入
            $j = chr($span);
            $objActSheet->setCellValue($j.$column, $value);
            $span++;
        }
        $column++;
    }

    //重命名表
    //$objPHPExcel->getActiveSheet()->setTitle('test');
    //设置活动单指数到第一个表,所以Excel打开这是第一个表
    $objPHPExcel->setActiveSheetIndex(0);
    header('Content-Type: application/vnd.ms-excel;charset=gbk');
    header("Content-Disposition: attachment;filename=\"$filename\"");
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output'); //文件通过浏览器下载
    exit;
}