PHP操作Excel表格浅析

题目的要求：把订单中的信息放到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插件中函数，都有例子说明。