【项目实战】Java POI之Excel导出经典案例三

来源：互联网发布：php 编译 with zlib 编辑：程序博客网时间：2024/05/22 17:37

该案例的实现和【项目实战】Java POI之Excel导出经典案例一类似，点击【导出Excel】按钮进行操作。

1、创建Excel模板文档，如下：

2、在JSP中添加按钮代码：

<a href="javascript:void(0);" id="toGuestListExcelId" target="_blank" onclick="toGuestListExcel()" class="button button-primary button-small">导出到Excel</a>

3、在JS中实现点击事件：

/* 导出到Excel */function toGuestListExcel(){var curpagenum = $('#contentGroupOrderTable').getGridParam('page');$("#page").val(curpagenum);$("#toGuestListExcelId").attr("href","toSaleGuestListExcel.do?startTime="+$("#startTime").val()+"&endTime="+$("#endTime").val()+"&receiveMode="+$("#receiveMode").val()+"&groupCode="+$("#groupCode").val()+"&orgIds="+$("#supplierName").val()+"&orgIds="+$("#orgIds").val()+"&orgNames="+$("#orgNames").val()+"&operType="+$("#operType").val()+"&saleOperatorIds="+$("#saleOperatorIds").val()+"&saleOperatorName="+$("#saleOperatorName").val()+"&orderMode="+$("#dicIds").val()+"&remark="+$("#remark").val()+"&page="+$("#page").val()+"&pageSize="+$("#pageSize").val()+"&userRightType="+$("#userRightType").val()+"&guestName="+$("#guestName").val()+"&gender="+$("#gender").val()+"&ageFirst="+$("#ageFirst").val()+"&ageSecond="+$("#ageSecond").val()+"&nativePlace="+$("#nativePlace").val());}

4、Java后台Controller中请求方法的实现如下:

@RequestMapping(value = "/toSaleGuestListExcel.do") public void toSaleGuestListExcel(HttpServletRequest request, HttpServletResponse response,         String startTime,String endTime,String receiveMode,String groupCode,String supplierName,    String orgIds,String orgNames,String operType,String saleOperatorIds,String saleOperatorName,    String orderMode,String remark,Integer page,Integer pageSize,Integer userRightType,String guestName,Integer gender,Integer ageFirst,Integer ageSecond,String nativePlace) { GroupOrder vo = new GroupOrder();        vo.setPage(page);        vo.setPageSize(pageSize);        vo.setStartTime(startTime);        vo.setEndTime(endTime);        vo.setRemark(remark);        vo.setGuestName(guestName);        vo.setOrderNo(orderMode);        vo.setGroupCode(groupCode);        vo.setSaleOperatorIds(saleOperatorIds);        vo.setOrgIds(orgIds);        vo.setOperType(Integer.valueOf(operType));        vo.setReceiveMode(receiveMode);        vo.setOrgNames(orgNames);        vo.setSaleOperatorName(saleOperatorName);        vo.setSupplierName(supplierName);        vo.setGender(gender);        vo.setAgeFirst(ageFirst);        vo.setAgeSecond(ageSecond);        vo.setNativePlace(nativePlace);                PageBean pageBean = new PageBean();        if (page == null) {            pageBean.setPage(1);        } else {            pageBean.setPage(page);        }        if (pageSize == null) {            pageBean.setPageSize(10000);        } else {            pageBean.setPageSize(10000);        }        pageBean.setParameter(vo);        pageBean.setPage(page);        pageBean = groupOrderService.selectGroupOrderGuestListPage(pageBean, WebUtils.getCurBizId(request),                WebUtils.getDataUserIdSet(request),userRightType);        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");        String path = "";        try {        String url = request.getSession().getServletContext().getRealPath("/template/excel/groupGuestList.xlsx");            FileInputStream input = new FileInputStream(new File(url)); // 读取的文件路径            XSSFWorkbook wb = new XSSFWorkbook(new BufferedInputStream(input));            CellStyle cellStyle = wb.createCellStyle();            cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框            cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框            cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框            cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框            cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 居中            cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直            cellStyle.setWrapText(true);            CellStyle styleLeft = wb.createCellStyle();            styleLeft.setBorderBottom(CellStyle.BORDER_THIN); // 下边框            styleLeft.setBorderLeft(CellStyle.BORDER_THIN);// 左边框            styleLeft.setBorderTop(CellStyle.BORDER_THIN);// 上边框            styleLeft.setBorderRight(CellStyle.BORDER_THIN);// 右边框            styleLeft.setAlignment(CellStyle.ALIGN_LEFT); // 居左            styleLeft.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直            styleLeft.setWrapText(true);            CellStyle styleRight = wb.createCellStyle();            styleRight.setBorderBottom(CellStyle.BORDER_THIN); // 下边框            styleRight.setBorderLeft(CellStyle.BORDER_THIN);// 左边框            styleRight.setBorderTop(CellStyle.BORDER_THIN);// 上边框            styleRight.setBorderRight(CellStyle.BORDER_THIN);// 右边框            styleRight.setAlignment(CellStyle.ALIGN_RIGHT); // 居右            styleRight.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直            styleRight.setWrapText(true);            Sheet sheet = wb.getSheetAt(0); // 获取到第一个sheet            Row row = null;            Cell cc = null;            // 遍历集合数据，产生数据行            //Iterator<GroupOrder> it = pageBean.getResult().iterator();            Map map = null;            int index = 0;            DecimalFormat df = new DecimalFormat("0.##");            if (pageBean.getResult() != null && pageBean.getResult().size() > 0) {                for (int i = 0; i < pageBean.getResult().size(); i++) {                    map = (HashMap) pageBean.getResult().get(i);            //GroupOrder go = it.next();                        row = sheet.createRow(index + 2);cc = row.createCell(0);cc.setCellValue(index + 1);cc.setCellStyle(cellStyle);cc = row.createCell(1);                cc.setCellValue((String)map.get("group_code"));//号                cc.setCellStyle(styleLeft);                                cc = row.createCell(2);                cc.setCellValue(sdf.format(map.get("departure_date")));//日期                cc.setCellStyle(cellStyle);                                cc = row.createCell(3);                cc.setCellValue((String)map.get("supplier_name"));//来源                cc.setCellStyle(styleLeft);                                cc = row.createCell(4);                cc.setCellValue((String)map.get("receive_mode"));//客人信息                cc.setCellStyle(styleLeft);                                cc = row.createCell(5);                cc.setCellValue((String)map.get("name"));//姓名                cc.setCellStyle(styleLeft);                                cc = row.createCell(6);                cc.setCellValue((Integer)map.get("gender")==0?"女":"男");//性别                cc.setCellStyle(cellStyle);                                cc = row.createCell(7);                cc.setCellValue(String.valueOf(map.get("age")));//年龄                cc.setCellStyle(cellStyle);                                cc = row.createCell(8);                cc.setCellValue((String)(map.get("certificate_num")));//证件号                cc.setCellStyle(cellStyle);                                cc = row.createCell(9);                cc.setCellValue((String)map.get("mobile"));//电话                cc.setCellStyle(cellStyle);                                cc = row.createCell(10);                cc.setCellValue((String)map.get("native_place"));//籍贯                cc.setCellStyle(styleLeft);                                cc = row.createCell(11);                cc.setCellValue((String)map.get("remark"));//产品备注                cc.setCellStyle(styleLeft);                                if((Integer)map.get("order_mode")==1374){                cc = row.createCell(12);                cc.setCellValue("长线");//业务                cc.setCellStyle(cellStyle);                } else if((Integer)map.get("order_mode")==1475){                cc = row.createCell(12);                cc.setCellValue("短线");//业务                cc.setCellStyle(cellStyle);                }else if((Integer)map.get("order_mode")==1476){                cc = row.createCell(12);                cc.setCellValue("签证");//业务                cc.setCellStyle(cellStyle);                }else if((Integer)map.get("order_mode")==1486){                cc = row.createCell(12);                cc.setCellValue("门票");//业务                cc.setCellStyle(cellStyle);                }else if((Integer)map.get("order_mode")==1487){                cc = row.createCell(12);                cc.setCellValue("酒店");//业务                cc.setCellStyle(cellStyle);                }else if((Integer)map.get("order_mode")==1488){                cc = row.createCell(12);                cc.setCellValue("专线");//业务                cc.setCellStyle(cellStyle);                }else if((Integer)map.get("order_mode")==1489){                cc = row.createCell(12);                cc.setCellValue("包车");//业务                cc.setCellStyle(cellStyle);                }else if((Integer)map.get("order_mode")==1490){                cc = row.createCell(12);                cc.setCellValue("组团");//业务                cc.setCellStyle(cellStyle);                }else if((Integer)map.get("order_mode")==1493){                cc = row.createCell(12);                cc.setCellValue("推广");//业务                cc.setCellStyle(cellStyle);                }else {                cc = row.createCell(12);                cc.setCellValue("XXX");//业务                cc.setCellStyle(cellStyle);                }                                cc = row.createCell(13);                cc.setCellValue((String)map.get("sale_operator_name"));//销售                cc.setCellStyle(cellStyle);                                cc = row.createCell(14);                cc.setCellValue((String)map.get("operator_name"));//计调                cc.setCellStyle(cellStyle);                                Integer sumAC = (Integer) map.get("num_adult")+(Integer) map.get("num_child");                BigDecimal total = (BigDecimal) map.get("total");                               cc = row.createCell(15);                cc.setCellValue(df.format(total.divide(new BigDecimal(sumAC),2, RoundingMode.HALF_UP)));//金额                cc.setCellStyle(cellStyle);                                index++;            }            }            CellRangeAddress region = new CellRangeAddress(pageBean.getResult().size() + 5,            pageBean.getResult().size() + 5, 0, 10);sheet.addMergedRegion(region);row = sheet.createRow(pageBean.getResult().size() + 5);cc = row.createCell(0);cc.setCellValue("打印人：" + WebUtils.getCurUser(request).getName()+ " 打印时间："+ DateUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));            path = request.getSession().getServletContext().getRealPath("/") + "/download/" + System.currentTimeMillis()                    + ".xlsx";            FileOutputStream out = new FileOutputStream(path);            wb.write(out);            out.close();            wb.close();                   } catch (Exception e) {            e.printStackTrace();        }        String fileName = "";        try {            fileName = new String("名单导出.xlsx".getBytes("UTF-8"), "iso-8859-1");        } catch (UnsupportedEncodingException e) {            e.printStackTrace();        }        download(path, fileName, request, response);    }

downLaod()为下载的方法，其代码如下：

private void download(String path, String fileName, HttpServletRequest request, HttpServletResponse response) {        try {            // path是指欲下载的文件的路径。            File file = new File(path);            // 以流的形式下载文件。            InputStream fis = new BufferedInputStream(new FileInputStream(path));            byte[] buffer = new byte[fis.available()];            fis.read(buffer);            fis.close();            // 清空response            response.reset();            /*             * //解决IE浏览器下下载文件名乱码问题 if             * (request.getHeader("USER-AGENT").indexOf("msie") > -1){ fileName             * = new URLEncoder().encode(fileName) ; }             */            // 设置response的Header            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);            response.addHeader("Content-Length", "" + file.length());            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());            response.setContentType("application/vnd.ms-excel;charset=gb2312");            toClient.write(buffer);            toClient.flush();            toClient.close();            file.delete();        } catch (IOException ex) {            ex.printStackTrace();        }    }

到此大功告成！！！

5、最终效果如下：

0 0