基于thinkPHP框架使用PHPExcel导出数据

来源：互联网发布：java web php 编辑：程序博客网时间：2024/06/06 16:57
        import("Org.Util.PHPExcel");        import("Org.Util.PHPExcel.IOFactory.php");        import("Org.Util.PHPExcel.Style.NumberFormat.php");        //PHPExcel_Style_NumberFormat        //导出        header('Content-Description: File Transfer');        header('Cache-Control: private, must-revalidate,post-check=0, pre-check=0, max-age=1');//这句兼容低版本ie        header('Content-Transfer-Encoding: binary');        Header("Content-type: application/octet-stream");        $filename = "财务流水数据表.xls";//导出的文件名        $encoded_filename = urlencode($filename);        $encoded_filename = str_replace("+", "%20",$encoded_filename);        $ua = $_SERVER["HTTP_USER_AGENT"];        if (preg_match("/MSIE/", $ua)) {            header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');        } else if (preg_match("/Firefox/", $ua)) {            header('Content-Disposition: attachment; filename*="utf8\'\'' . $filename . '"');        } else {            header('Content-Disposition: attachment; filename="' . $filename . '"');        }        //导入PHPExcel类库        //创建PHPExcel对象        $objPHPExcel=new \PHPExcel();        //设置粗体        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('K1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('L1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('M1')->getFont()->setBold(true);        $objPHPExcel->setActiveSheetIndex(0)            ->setCellValue('A1','流水号')            ->setCellValue ( 'B1','订单号/包销号' )            ->setCellValue ( 'C1','财务类型' )            ->setCellValue ( 'D1','金额' )            ->setCellValue ( 'E1','产品' )            ->setCellValue ( 'F1','产品版本' )            ->setCellValue ( 'G1','款项类型' )            ->setCellValue ( 'H1','流水时间' )            ->setCellValue ( 'I1','所属用户' )            ->setCellValue ( 'J1','跟踪销售' )            ->setCellValue ( 'K1','申请人' )            ->setCellValue ( 'L1','审核人' )            ->setCellValue ( 'M1','状态' );        //设置宽度        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);        //写入文件        //取得内容        $recordRow=$this->FinR->downRecordList();        $i = 2; // 定义一个i变量，目的是在循环输出数据是控制行数        $finance_type=C(FINRECORD_TYPE);//财务类型        $kx_type=C(FINRECORD_KX_TYPE);//款项类型        $sta_type=array("未审核","已通过","已作废");        $objPHPExcel->getActiveSheet()->getStyle('H')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);//日期格式化        foreach($recordRow as $value){            $objvalue=$objPHPExcel->setActiveSheetIndex ( 0 );            $objvalue->setCellValue ( "A" . $i, " ".$value['id']." " );//第三个参数中前后加“ ”是为了使Excel格式为文本格式显示比较公证，如果需要对导出的Excel进行计算最好是前后分号去电            $objvalue->setCellValue ( "B" . $i, " ".$value['order_code']." " );            $objvalue->setCellValue ( "C" . $i, " ".$finance_type[$value['finance_type_id']]." " );            $objvalue->setCellValue ( "D" . $i, " ".$value['total_money']." " );            $objvalue->setCellValue ( "E" . $i, " ".$value['prod_name']." " );            $objvalue->setCellValue ( "F" . $i, " ".$value['prod_version_name']." " );            $objvalue->setCellValue ( "G" . $i, " ".$kx_type[$value['kx_type']]." " );            $objvalue->setCellValue ( "H" . $i, " ".date("Y/m/d H:i:s",$value['confirm_date'])." " );            $objvalue->setCellValue ( "I" . $i, " ".$value ['own_user']." " );            $objvalue->setCellValue ( "J" . $i, " ".$value ['salesman_user']." " );            $objvalue->setCellValue ( "K" . $i, " ".$value ['op_user']." " );            $objvalue->setCellValue ( "L" . $i, " ".$value ['confirm_user']." " );            $objvalue->setCellValue ( "M" . $i, " ".$sta_type [$value['status']]." " );            $i++;        };        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来        $objWriter->save("php://output");//设置以什么格式保存，及保存位置
0 0