PHPExcel 导出

作者:yiqiu,最后更新时间:2020-03-24 11:47,访问:4747

原文:http://www.upwqy.com/details/235.html







   //订单导出
   public function exportsPost(){


       Loader::import('phpoffice.phpexcel.Classes.PHPExcel',EXTEND_PATH,'.php');

       $objPHPExcel = new \PHPExcel();
       $objPHPExcel->setActiveSheetIndex(0);

       $objPHPExcel->getActiveSheet()->setTitle('订单明细');



       // 设置所有单元格(列表)表格宽度
       $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);

       // 设置表格宽度
//        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->getFont()->setSize(16);
//        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);


       // 列名表头文字加粗
       $objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getFont()->setBold(true);
       // 列表头文字居中 PHPExcel_Style_Alignment
       $objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getAlignment()
           ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
       // 列名赋值
       $objPHPExcel->getActiveSheet()->setCellValue('A1', '日期');
       $objPHPExcel->getActiveSheet()->setCellValue('B1', '客户类型');
       $objPHPExcel->getActiveSheet()->setCellValue('C1', '客户名称');
       $objPHPExcel->getActiveSheet()->setCellValue('D1', '本次预存款剩余');
       $objPHPExcel->getActiveSheet()->setCellValue('E1', '付款方式');
       $objPHPExcel->getActiveSheet()->setCellValue('F1', '销售员');
       $objPHPExcel->getActiveSheet()->setCellValue('G1', '产品名称');

       $objPHPExcel->getActiveSheet()->setCellValue('H1', '备注');

       $objPHPExcel->getActiveSheet()->setCellValue('I1', '数量');
       $objPHPExcel->getActiveSheet()->setCellValue('J1', '单价');
       $objPHPExcel->getActiveSheet()->setCellValue('K1', '金额');
       $objPHPExcel->getActiveSheet()->setCellValue('L1', '业绩');
       $objPHPExcel->getActiveSheet()->setCellValue('M1', '订单实收金额');
       $objPHPExcel->getActiveSheet()->setCellValue('N1', '对账金额');
       $objPHPExcel->getActiveSheet()->setCellValue('O1', '对账时间');
       $objPHPExcel->getActiveSheet()->setCellValue('P1', '对账备注');

       $where = [];

       $user_keywords = input('user_keywords');

       $slOrderModel = new \app\common\model\SlOrder();
       $slOrderModel->with([
           'slUser',
           'slUser.agentLevel',
           'slUser.accountManages',
           'orderGoodsItem',
           'orderGoodsItem.goods'
       ])
           ->where('pay_status',1)
           ->where('order_status',2)
           ->where('cancel_time',0);

       //搜索用户
       if($user_keywords){
           $slUserModel = new \app\common\model\SlUser();
           $userInfo = $slUserModel->where('username|mobile','like',"%$user_keywords%")->find();
           if($userInfo){
               $slOrderModel->where('user_id',$userInfo->user_id);
           }else{
               $slOrderModel->where('user_id',0);
           }
       }

       $list = $slOrderModel->order('id desc')->select();



       // 数据起始行
       $row_num = 2;
       // 向每行单元格插入数据
       foreach($list as $value)
       {
           // 设置所有垂直居中
           $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'P' . $row_num)
               ->getAlignment()
               ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
               ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           // 设置价格为数字格式
//            $objPHPExcel->getActiveSheet()->getStyle('D' . $row_num)
//                ->getNumberFormat()
//                ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
           // 居中
//            $objPHPExcel->getActiveSheet()->getStyle('E' . $row_num . ':' . 'H' . $row_num)
//                ->getAlignment()
//                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           
           $count = count($value['order_goods_item']);


           $pay_type_text = '微信支付';
   
           // 设置单元格数值
           $objPHPExcel->getActiveSheet()->mergeCells("A".$row_num.":A".($row_num+$count-1))->setCellValue('A' . $row_num, date('Ymd',$value['finish_time']));
           $objPHPExcel->getActiveSheet()->mergeCells("B".$row_num.":B".($row_num+$count-1))->setCellValue('B' . $row_num, $value['sl_user']['agent_level']['title']);
           $objPHPExcel->getActiveSheet()->mergeCells("C".$row_num.":C".($row_num+$count-1))->setCellValue('C' . $row_num, $value['sl_user']['username']);
           $objPHPExcel->getActiveSheet()->mergeCells("D".$row_num.":D".($row_num+$count-1))->setCellValue('D' . $row_num, $value['surplus_payment_price']);
           $objPHPExcel->getActiveSheet()->mergeCells("E".$row_num.":E".($row_num+$count-1))->setCellValue('E' . $row_num, $pay_type_text);
           $objPHPExcel->getActiveSheet()->mergeCells("F".$row_num.":F".($row_num+$count-1))->setCellValue('F' . $row_num, $value['sl_user']['account_manages']['user_nickname'] );

           $objPHPExcel->getActiveSheet()->mergeCells("L".$row_num.":L".($row_num+$count-1))->setCellValue('L' . $row_num, $value['profit_price']);
           $objPHPExcel->getActiveSheet()->mergeCells("M".$row_num.":M".($row_num+$count-1))->setCellValue('M' . $row_num, $value['total_price']);

           $objPHPExcel->getActiveSheet()->mergeCells("N".$row_num.":N".($row_num+$count-1));
           $objPHPExcel->getActiveSheet()->mergeCells("O".$row_num.":O".($row_num+$count-1));
           $objPHPExcel->getActiveSheet()->mergeCells("P".$row_num.":P".($row_num+$count-1));
           $objPHPExcel->getActiveSheet()->mergeCells("H".$row_num.":H".($row_num+$count-1));

           foreach ($value['order_goods_item'] as $key=>$val){
               $objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $val['goods']['title'] );

//                $objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num, '');

               $objPHPExcel->getActiveSheet()->setCellValue('I' . $row_num, $val['count']);
               $objPHPExcel->getActiveSheet()->setCellValue('J' . $row_num, $val['price']);
               $objPHPExcel->getActiveSheet()->setCellValue('K' . $row_num, $val['price'] * $val['count']);
               $row_num++;
           }

//            $objPHPExcel->getActiveSheet()->getDefaultRowDimension(\PHPExcel_Cell::stringFromColumnIndex($row_num))->setRowHeight(1);
           $objPHPExcel->getActiveSheet()->mergeCells("A".$row_num.":P".$row_num);
           $row_num++;
       }

       $outputFileName =  'order_' .time(). '.xls';
       $xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
       ob_end_clean();
       header("Content-Type: application/force-download");
       header("Content-Type: application/octet-stream");
       header("Content-Type: application/download");
       header('Content-Disposition:inline;filename="' . $outputFileName . '"');
       header("Content-Transfer-Encoding: binary");
       header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
       header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
       header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
       header("Pragma: no-cache");
       $xlsWriter->save("php://output");

   }


上一篇: mac ssh 连接服务器出现的问题
下一篇: 后台打印