php导出excel表格

最新推荐文章于 2022-01-22 13:53:36 发布

小公子三木君

最新推荐文章于 2022-01-22 13:53:36 发布

阅读量401

点赞数

本文链接：https://blog.csdn.net/qq_21208843/article/details/80772926

版权

实例代码，前端根据情况调用函数。

/**
 * 导出数据表
 */
public function exportTable()
{
    //搜索数据表，获得需要导出的数据
    $data = M('t_op_meeting_reservation')
        ->alias('a')
        ->join('t_ma_personal b on a.perid=b.perid')
        ->field("a.id,a.is_through,a.meeting_room,a.begin_time,a.end_time,a.remark,b.cname,b.department")
        ->select();
    $res = $data;
    //表格数据导出
    header("Content-type: text/html; charset=utf-8");
    vendor("PHPExcel.PHPExcel");
    $objPHPExcel = new \PHPExcel();
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    $objProps = $objPHPExcel->getProperties();
    $objProps->setCreator("");
    $objProps->setLastModifiedBy("");
    $objProps->setTitle("会议室批准表");//标题
    $outputFileName = "会议室申请表-" . date("Ymd", time()) . ".xls";//文件名字
    $objActs = $objPHPExcel->getActiveSheet();
    //居中
    $objActs->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objActs->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    //设置填充的样式和背景色
    $objActs->getStyle('A1:Y1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
    $objActs->getStyle('A1:Y1')->getFill()->getStartColor()->setARGB('00CDCDCD'); //加背景颜色
    $objActs->getStyle('A1:Y1')->getFont()->setBold(true);  //字体加粗
    //列宽
    $objActs->getColumnDimension('A')->setWidth(30);
    $objActs->getColumnDimension('B')->setWidth(30);
    $objActs->getColumnDimension('C')->setWidth(30);
    $objActs->getColumnDimension('D')->setWidth(30);
    $objActs->getColumnDimension('E')->setWidth(50);
    $objActs->getColumnDimension('F')->setWidth(20);

    //设置标题
    $objActSheet = $objPHPExcel->setActiveSheetIndex();
    $title_arr = array('会议室', '申请人', '申请部门', '备注', '申请时间', '审批结果',);
    $objActSheet->setCellValue('A1', $title_arr[0]);
    $objActSheet->setCellValue('B1', $title_arr[1]);
    $objActSheet->setCellValue('C1', $title_arr[2]);
    $objActSheet->setCellValue('D1', $title_arr[3]);
    $objActSheet->setCellValue('E1', $title_arr[4]);
    $objActSheet->setCellValue('F1', $title_arr[5]);

    //循环数据输出到表格
    foreach ($res as $key => $value) {
        $i = $key+3;
        $objActSheet->setCellValue('A' . $i, $value['meeting_room']);
        $objActSheet->setCellValue('B' . $i, $value['cname']);
        $objActSheet->setCellValue('C' . $i, $value['department']);
        $objActSheet->setCellValue('D' . $i, $value['remark']);
        $objActSheet->setCellValue('E' . $i, substr($value['begin_time'],0,10).'-'.substr($value['end_time'],0,10));
        if($value['is_through'] == null)
            $objActSheet->setCellValue('F' . $i, "未审批");
        elseif($value['is_through'] == 1){
            $objActSheet->setCellValue('F' . $i, "已通过");
        }else{
            $objActSheet->setCellValue('F' . $i, "未通过");
        }
    }

    ob_end_clean();
    ob_start();
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");;
    header('Content-Disposition:attachment;filename="' . $outputFileName . '"');
    header("Content-Transfer-Encoding:binary");
    header('Content-Type:text/html;Charset=utf-8;');
    $objWriter->save('php://output');
}