Java 使用poi jar包- excel文件订单导出

女爷i 2022-11-30 12:54 172阅读 0赞

public Object exportOrder(Map<String, Object> params) throws FuLiShareExecuteException {
    		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Map<String,Object> map = null;
            try {
                //1.创建一个webbook，对应一个Excel文件
                HSSFWorkbook wb = new HSSFWorkbook();
                //2.在webbook中添加一个sheet，对应Excel文件中的sheet
                HSSFSheet sheet = wb.createSheet("sheet1");
                //3.在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制short
                HSSFRow row = sheet.createRow(0);
                //4.创建单元格，并设置表头  这只表头居中
                HSSFCellStyle style = wb.createCellStyle();
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   //创建一个居中格式
                HSSFCell cell = row.createCell(0);
                cell.setCellValue("主订单编号");
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue("订单编号");
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("订单状态");
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellValue("商品名称");
                cell.setCellStyle(style);
                cell = row.createCell(4);
                cell.setCellValue("商品数量");
                cell.setCellStyle(style);
                cell = row.createCell(5);
                cell.setCellValue("收件人姓名");
                cell.setCellStyle(style);
                cell = row.createCell(6);
                cell.setCellValue("联系电话");
                cell.setCellStyle(style);
                cell = row.createCell(7);
                cell.setCellValue("地址");
                cell.setCellStyle(style);
                cell = row.createCell(8);
                cell.setCellValue("创建时间");
                cell.setCellStyle(style);
                cell = row.createCell(9);
                cell.setCellValue("MO订单编号");
                cell.setCellStyle(style);
                cell = row.createCell(10);
                cell.setCellValue("订单支付价格");
                cell.setCellStyle(style);
                cell = row.createCell(11);
                cell.setCellValue("所属商户名称");
                cell.setCellStyle(style);
                
                //5.获取导入的数据
                LogUtil.info("导出报表参数："+JSONObject.toJSONString(params));
                String orderIds = CommonUtils.getParam(params,"orderIds");
                List<Map<String,Object>> list=new ArrayList();
                if(orderIds==null||orderIds.equals("")){
                	String userId=CommonUtils.getParam(params, "userId");
            		String orderNumber=CommonUtils.getParam(params, "orderNumber","");//搜索框输入的值
            		String orderStatus=CommonUtils.getParam(params, "orderStatus",""); //订单状态
            		String orderType=CommonUtils.getParam(params, "orderType"); //(必传)订单类型  1.赠送订单    2.领取订单    3.收仓订单    4.自购订单    5.自己领取订单
            		String payBeginTime=CommonUtils.getParam(params, "payBeginTime","");
            		String payEndTime=CommonUtils.getParam(params, "payEndTime","");
            		String payType=CommonUtils.getParam(params, "payType","");
            		String receiver=CommonUtils.getParam(params, "receiver","");
            		String goodsType=CommonUtils.getParam(params, "goodsType","1");
            		if(payType.equals("wx")){
            			payType="'JSAPI','NATIVE','wx'";
            		}
            		if(StringUtils.isNotBlank(orderNumber)){
            			orderNumber="%"+orderNumber+"%";
            		}
            		Map<String,Object> data = new HashMap();
            		data.put("userId", userId);
            		data.put("orderType", orderType);
            		data.put("payType", payType);
            		data.put("orderStatus", orderStatus);
            		data.put("orderNumber", orderNumber);
            		data.put("payBeginTime", payBeginTime);
            		data.put("payEndTime", payEndTime);
            		data.put("receiver", receiver);
            		data.put("goodsType", goodsType);
            		/*Set<String> sets=data.keySet();
            		for(String aaa:sets){
            			LogUtil.info("========"+aaa+":============"+data.get(aaa));
            		}*/
            		list= orderMapper.findAllOrderPageByUserIdAndReasons(data);
                }else{
                	String[] orderIdList = orderIds.split(",");
                    for(String orderId:orderIdList){
                    	list.add(orderMapper.getExportOrder(orderId));
                    }
                }
                LogUtil.info("====== 导出条数 ======="+list.size()+"==============");
                //创建存储所有订单集合
                List<Map<String,Object>> allList = new ArrayList<>();
                //遍历主订单
                for(int i = 0; i<list.size(); i++) {
                    Map<String, Object> order = list.get(i);
                    //如果是送礼订单，导出订单的所有子订单
                    if(Integer.parseInt(order.get("order_type").toString())==1){
                    	LogUtil.info("=================="+"进入了子订单"+"==================");
                        //查询子订单
                        List<Map<String,Object>> sonList = orderMapper.findOrderByGrandParentId((String)order.get("id"));
                        LogUtil.info("=================="+sonList.size()+"==================");
                        //主订单放进去
                        allList.add(order);
                        //放子订单
                        for (Map<String, Object> sonMap : sonList) {
                        	sonMap.put("order_number",order.get("order_number"));
                        	sonMap.put("son_order_number",sonMap.get("order_number"));
                        	sonMap.put("order_status",sonMap.get("order_status"));
                        	sonMap.put("goods_name",sonMap.get("goods_name"));
                        	sonMap.put("total_num",sonMap.get("total_num"));
                        	sonMap.put("name",sonMap.get("name"));
                        	sonMap.put("tel",sonMap.get("tel"));
                        	sonMap.put("address",sonMap.get("address"));
                        	sonMap.put("create_time",sonMap.get("create_time"));
                        	sonMap.put("total_price",sonMap.get("total_price"));
                        	sonMap.put("company_name",sonMap.get("company_name"));
                            allList.add(sonMap);
                        }
                    }else{
                        //主订单放进去
                        allList.add(order);
                    }
                }
                LogUtil.info("====== 导出条数 ======="+allList.size()+"==============");
                //6.生成表格
                for(int i = 0; i<allList.size(); i++) {
                    row = sheet.createRow(i + 1);
                    Map<String, Object> order = allList.get(i);
                    //创建单元格，并设置值
                    String orderNumber = (String)order.get("order_number");
                    //查询广视订单号
                    String MoOrderNumber = orderMapper.queryMoOrderByOrderNumber(orderNumber);
                    //导出主订单的代码
                    row.createCell(0).setCellValue(orderNumber);
                    row.createCell(1).setCellValue((String)order.get("son_order_number"));
                    try{
                    	//row.createCell(2).setCellValue(OrderConstant.getOrderStateByCompany(order).getStrDes());
                    	row.createCell(2).setCellValue(OrderConstant.OrderState.getStateObj(order.get("order_status").toString()).getStrDes());
                    }catch(Exception e){
                    	row.createCell(2).setCellValue("");
                    	e.printStackTrace();
                    }
                    row.createCell(3).setCellValue(order.get("goods_name").toString().replaceAll(";,", ";"));
                    row.createCell(4).setCellValue(order.get("total_num").toString());
                    row.createCell(5).setCellValue((String)order.get("name"));
                    row.createCell(6).setCellValue((String)order.get("tel"));
                    row.createCell(7).setCellValue((String)order.get("address"));
                    row.createCell(8).setCellValue(format.format(order.get("create_time")));
                    row.createCell(9).setCellValue(MoOrderNumber == null ? "" : MoOrderNumber);
                    row.createCell(10).setCellValue(order.get("total_price").toString());
                    row.createCell(11).setCellValue(order.containsKey("company_name")?order.get("company_name").toString():"");
                }
                //6.上传oss
                Date d = new Date();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String filename1 = "订单详情("+sdf.format(d)+").xls";
                String path = "agent";
                // 写入
                ByteArrayOutputStream bis = new ByteArrayOutputStream();
                wb.write(bis);
                byte[] bytes = bis.toByteArray();
                InputStream by = new ByteArrayInputStream(bytes);
                map = iOssService.putObjectMethod(by, path, filename1);
            } catch (IOException e) {
                e.printStackTrace();
            }
            return map;
    	}