PHPExcel导出表格

「爱情、让人受尽委屈。」 2022-03-26 08:48 320阅读 0赞

public function exportExcel($keyword=''){
       
        //定义一个数组来存查询到的值
        $all_data_list = 查询需要导出的数据。
        //导入插件
        vendor('PHPExcel.Classes.PHPExcel');
        $objExecl = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExecl, 'Excel5');
        ob_end_clean();    //擦除缓冲区
        $file_name = '科目代码基础表'.'.xls';
        $indexKey = $this->indexKey();
        //构造表头字段,添加在list数组之前,组成完整数组
        $all_data_list = $this->addHeadToData($all_data_list);
        $heard_arr = 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');
        //写入数据到表格中
        $objActSheet = $objExecl->getActiveSheet();
        $this->addDataToExcel($all_data_list,$objActSheet,$indexKey,$heard_arr);
        // 下载这个表格，在浏览器输出
        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='.$file_name.'');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    
    /**
     * 得到需要导出的数据  返回所有数据
     * @param string $year
     * @param string $month
     * @param string $keyword
     * @param $type
     * @return mixed
     */
    public function getExportData($keyword=''){
    
        $logicSub = Loader::model('Subjectcode','logic');
        $resMsg= $logicSub->getSubjectList($keyword);
        return $resMsg;
    }
    
    /**
     * 构建表中所有数据
     * @param $data
     */
    public function createExcelData($data){
        //导入插件
        vendor('PHPExcel.Classes.PHPExcel');
        $objExecl = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExecl, 'Excel5');
        ob_end_clean();    //擦除缓冲区
        $file_name = time().'.xls';
    
        $heard =array('subject_code'=>'科目代码','subject_name'=>'科目名称');
        $indexKey = array('subject_code','subject_name');    //$indexKey $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值)
        array_unshift($data,$heard);
        return $data;
    }
    
    /**
     * 返回 对应表中 的字段名
     * @param $type
     * @return array
     */
    public function indexKey(){
        $indexKey = array('subject_code','subject_name');    //$indexKey $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值)
        return $indexKey;
    }
    
    /**\
     * 添加表头信息
     * @param $data
     * @param $type
     * @return int
     */
    public function addHeadToData($data){
        $heard =array('subject_code'=>'科目代码','subject_name'=>'科目名称');
        array_unshift($data,$heard);
        return $data;
    }
    
    public function addDataToExcel($data,$objActSheet,$indexKey,$heard_arr){
        $startRow = 1;
        foreach ($data as $key=>$value){
            foreach ($indexKey as $k=>$v){
                $objActSheet->setCellValue($heard_arr[$k].$startRow,$data[$key][$v]);
                $objActSheet->getStyle($heard_arr[$k].$startRow)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }
            $startRow++;
        }
        $objActSheet->getColumnDimension('A')->setWidth(10);
        $objActSheet->getColumnDimension('B')->setWidth(20);
        $objActSheet->getColumnDimension('C')->setWidth(20);
        $objActSheet->getColumnDimension('D')->setWidth(20);
        $objActSheet->getColumnDimension('E')->setWidth(30);
        $objActSheet->getColumnDimension('F')->setWidth(20);
        $objActSheet->getColumnDimension('G')->setWidth(30);
    }