PHP操作Excel表格浅析

来源:互联网 发布:淘宝陶瓷店知乎 编辑:程序博客网 时间:2022/07/04 22:54

题目的要求:把订单中的信息放到Excel表格中并且下载到本地,并且循环输出订单中的所有商品。
以下图片是 效果:
这里写图片描述
主要的代码段:

 //获取服务器的绝对路径  $root1 = I('server.DOCUMENT_ROOT');   //这里是引用PHPExcel的类,相当于第三方的插件,如果没有,可以百度下载,然后再放到ThinkPHP的库目录中   import("Org.Excel.PHPExcel"); //__PUBLIC__/PHPExcel/   $order=D("OrderView");   //获取orderID   $orderID =I('get.order_code');   if ($orderID!="") {       $where['order_code'] =$orderID;       $objReader =\PHPExcel_IOFactory::createReader('Excel5');            $objPHPExcel = $objReader->load($root1.__ROOT__.'/Public/export.xls');  //导入模板打印        }else{             $objPHPExcel = new \PHPExcel();        }        $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");        $rs=$order->where($where)->select();        $order_details =getProductDetails($orderID);  //订单下的所有产品信息        dump($order_details);        //判断是否有选择的订单,如果没有就打印出所有订单信息,有的话就根据模板输出。        if ($orderID =="") {           $i=2;            $objPHPExcel->setActiveSheetIndex(0)                        ->setCellValue('A1', '订单编号')                        ->setCellValue('B1', '交易编号')                        ->setCellValue('C1', '产品编号')                        ->setCellValue('D1', '客户名称')                        ->setCellValue('E1', '交易数量')                        ->setCellValue('F1', '交易时间')                        ->setCellValue('G1', '交易状态')                        ->setCellValue('H1', '登记人')                        ->setCellValue('I1', '备注信息');            $objPHPExcel->setActiveSheetIndex(0);            foreach($rs as $k=>$v){                $objPHPExcel->setActiveSheetIndex(0)                            ->setCellValue('A'.$i, $v['order_code'])                            ->setCellValue('B'.$i, $v['trade_code'])                            ->setCellValue('C'.$i, $v['product_code'])                            ->setCellValue('D'.$i, $v['username'])                            ->setCellValue('E'.$i, $v['trade_num'])                            ->setCellValue('F'.$i, $v['createtime'])                            ->setCellValue('G'.$i, $v['order_type'])                            ->setCellValue('H'.$i, $v['input_user'])                            ->setCellValue('I'.$i, $v['remark']);                $i++;            }            // $objActSheet->setCellValue('A1', iconv('gbk', 'utf-8', '中文Hello'));    //插入数据        }else{/*       $objReader = PHPExcel_IOFactory::createReader('Excel5');       $objWriter = $objReader->load($root1.__ROOT__.'/Public/export.xls');       $objWriter = PHPExcel_IOFactory::createWriter($objWriter, 'Excel5'); */       foreach($rs as $k=>$v){            $time1 =strtotime($v['createtime']);            $last_time =date("Y年m月d日",$time1+(21*24*3600));            $create_time =date("Y-m-d");            $objPHPExcel->setActiveSheetIndex(0)                            ->setCellValue('C5', $v['username'])                            ->setCellValue('F5', $v['order_code'])                                                ->setCellValue('F12', $v['total_price'])                            ->setCellValue('F16', $create_time)                            ->setCellValue('D14', $last_time)                            ->setCellValue('I16', $v['input_user']);            }           }        foreach ($order_details as $k => $v) {            $objPHPExcel->setActiveSheetIndex(0)                    ->insertNewRowBefore(11,1)                    ->setCellValue('C11', $v['product_name'])                    ->setCellValue('D11', $v['price'])                    ->setCellValue('E11', $v['product_num'])                    ->setCellValue('F11', $v['total_price']);        }        $objPHPExcel->getActiveSheet()->setTitle('order');//设置sheet标签的名称        $objPHPExcel->setActiveSheetIndex(0);        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');             ob_end_clean();  //清空缓存         //设置header的相关参数        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='.date("YmdHis").'.xls');//设置文件的名称        header("Content-Transfer-Encoding:binary");        $objWriter->save('php://output');   //下载Excel表格        exit;

以上就是Excel表打印的核心代码,如果继续深入研究,可以查看PHPExcel插件中函数,都有例子说明。

0 0
原创粉丝点击