PHPExcel之将获取到的数据导出excel

最新推荐文章于 2021-03-11 04:22:08 发布

原创最新推荐文章于 2021-03-11 04:22:08 发布 · 283 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#PHPExcel

PHPexcel 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种使用TP3.2框架从数据库中获取管理员及关联员工的年假信息，并将其整理成Excel文件的方法。具体包括数据查询、计算年假余额、创建Excel文件并设置属性、填充数据、调整文件格式以及最终的文件导出流程。

tp3.2(注意：project\ThinkPHP\Library\Org\Util\PHPExcel 引用方法： use Org\Util; )

public function export_nj()
    {
        $data = M('admin')->alias('a')
            ->join('right join oa_ren_newwork n on n.uid=a.id')
            ->field('n.id,n.uid,n.username,n.tztime,n.njtime')->where(['a.status'=>1])->select();
        foreach($data as $k=>$v){
            $wherek['user_id']         =$v['uid'];
            $wherek['renshi_build_id'] =3;//请假类型
            $wherek['save_sub_statue'] =3;//请假批准
            $wherek['leave_type']     =1; //年假类型
            $njyytime=M('oa_ren_work')->where($wherek)->sum('leavetime');
            $data[$k]['njyytime']=round($njyytime/8, 2);
            $data[$k]['njsytime']=$data[$k]['njtime']-$data[$k]['njyytime'];
            if($data[$k]['njyytime']==''){
                $data[$k]['njyytime']=" ";
            }
            unset($data[$k]['njyytime']);
            unset($data[$k]['id']);
        }
//        dump($data);die;
//        $title=array('用户','花名','调休剩余时间','年假时间','年假剩余天数');
//      if(!empty($result)) {
        $objPHPExcel = new Util\PHPExcel();
        // Set document properties
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");

        // Add some data
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '用户id')
            ->setCellValue('B1', '花名')
            ->setCellValue('C1', '调休剩余时间/h')
            ->setCellValue('D1', '年假总天数')
            ->setCellValue('E1', '年假剩余天数/天');
        $rowIndex=2;

        foreach ($data as $dataRow) {
            $objPHPExcel->getActiveSheet()
                ->setCellValue('A'.$rowIndex, $dataRow['uid'])
                ->setCellValue('B'.$rowIndex, $dataRow['username'])
                ->setCellValue('C'.$rowIndex, $dataRow['tztime'])
                ->setCellValue('D'.$rowIndex, $dataRow['njtime'])
                ->setCellValue('E'.$rowIndex,$dataRow['njsytime']);

            $rowIndex++;
        }

        $urlMonth = I('get.month');
        $tableTitle = "oa年假";
        $objPHPExcel->getActiveSheet()->setTitle($tableTitle);
        $objPHPExcel->setActiveSheetIndex(0);

        $fileName = "{$tableTitle}.xlsx";

        //清空缓冲区，否则导出的文件可能打不开
        ob_end_clean();
        ob_start();

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        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;
    }