【项目实战】Java POI之Excel导出经典案例二
来源:互联网 发布:oracle连接数据库很慢 编辑:程序博客网 时间:2024/05/22 12:44
该案例的实现和【项目实战】Java POI之Excel导出经典案例一类似,点击【导出Excel】按钮进行操作。
1、创建Excel模板文档,如下:
2、在JSP页面添加按钮代码:
<button type="button" onclick="toPickUpExcel()" class="button button-primary button-small">导出到Excel</button>3、JS方法点击事件如下:
/* 导出到Excel */function toPickUpExcel(){var curpagenum = $('#contentGroupOrderTable').getGridParam('page');$("#page").val(curpagenum);var startTime=$('#startTime').val();var endTime=$('#endTime').val();var receiveMode=$('#receiveMode').val();var groupCode=$('#groupCode').val();var supplierName=$('#supplierName').val();var orgIds=$('#orgIds').val();var orgNames=$('#orgNames').val();var operType=$('#operType').val();var saleOperatorIds=$('#saleOperatorIds').val();var saleOperatorName=$('#saleOperatorName').val();var orderMode=$('#dicIds').val();var remark=$('#remark').val();var page=$('#page').val();var pageSize=$('#pageSize').val();var userRightType=$('#userRightType').val();var guestName=$('#guestName').val();var gender=$('#gender').val()var ageFirst=$('#ageFirst').val()var ageSecond=$('#ageSecond').val()var nativePlace=$('#nativePlace').val()window.location ='../taobao/toGroupOrderGuesExport.do?startTime='+startTime+"&endTime="+endTime+"&receiveMode="+receiveMode+"&groupCode="+groupCode+"&supplierName="+supplierName+"&orgIds="+orgIds+"&orgNames="+orgNames+"&operType="+operType+"&saleOperatorIds="+saleOperatorIds+"&saleOperatorName="+saleOperatorName+"&orderMode="+orderMode+"&remark="+remark+"&page="+page+"&pageSize="+pageSize+"&userRightType="+userRightType+"&guestName="+guestName+"&gender="+gender+"&ageFirst="+ageFirst+"&ageSecond="+ageSecond+"&nativePlace="+nativePlace;}一部分是获取查询的条件参数;另一部分是将参数通过请求传递到Controller方法中。
// 导出Export @RequestMapping(value = "/toGroupOrderGuesExport.do") @ResponseBody public void toGroupOrderGuesExport(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); if (StringUtils.isBlank(vo.getSaleOperatorIds()) && StringUtils.isNotBlank(vo.getOrgIds())) { Set<Integer> set = new HashSet<Integer>(); String[] orgIdArr = vo.getOrgIds().split(","); for (String orgIdStr : orgIdArr) { set.add(Integer.valueOf(orgIdStr)); } set = platformEmployeeService.getUserIdListByOrgIdList(WebUtils.getCurBizId(request), set); String salesOperatorIds = ""; for (Integer usrId : set) { salesOperatorIds += usrId + ","; } if (!salesOperatorIds.equals("")) { vo.setSaleOperatorIds(salesOperatorIds.substring(0, salesOperatorIds.length() - 1)); } } PageBean pageBean = new PageBean(); if (page == null) { pageBean.setPage(1); } else { pageBean.setPage(page); } if (pageSize == null) { pageBean.setPageSize(Constants.PAGESIZE); } else { pageBean.setPageSize(pageSize); } pageBean.setParameter(vo); pageBean.setPage(page); pageBean = groupOrderService.selectGroupOrderGuestListPage(pageBean, WebUtils.getCurBizId(request), WebUtils.getDataUserIdSet(request),userRightType); String path = ""; try { String url = request.getSession().getServletContext().getRealPath("/template/excel/groupGuestContact.xlsx"); FileInputStream input = new FileInputStream(new File(url)); // 读取的文件路径 XSSFWorkbook wb = new XSSFWorkbook(new BufferedInputStream(input)); XSSFFont createFont = wb.createFont(); createFont.setFontName("微软雅黑"); createFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示 createFont.setFontHeightInPoints((short) 12); XSSFFont tableIndex = wb.createFont(); tableIndex.setFontName("宋体"); tableIndex.setFontHeightInPoints((short) 11); 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 styleFontCenter = wb.createCellStyle(); styleFontCenter.setBorderBottom(CellStyle.BORDER_THIN); // 下边框 styleFontCenter.setBorderLeft(CellStyle.BORDER_THIN);// 左边框 styleFontCenter.setBorderTop(CellStyle.BORDER_THIN);// 上边框 styleFontCenter.setBorderRight(CellStyle.BORDER_THIN);// 右边框 styleFontCenter.setAlignment(CellStyle.ALIGN_CENTER); // 居中 styleFontCenter.setFont(createFont); CellStyle styleFontTable = wb.createCellStyle(); styleFontTable.setBorderBottom(CellStyle.BORDER_THIN); // 下边框 styleFontTable.setBorderLeft(CellStyle.BORDER_THIN);// 左边框 styleFontTable.setBorderTop(CellStyle.BORDER_THIN);// 上边框 styleFontTable.setBorderRight(CellStyle.BORDER_THIN);// 右边框 styleFontTable.setAlignment(CellStyle.ALIGN_CENTER); // 居中 styleFontTable.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleFontTable.setFillPattern(CellStyle.SOLID_FOREGROUND); 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); // 居左 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); // 居右 Sheet sheet = wb.getSheetAt(0); // 获取到第一个sheet Row row = null; Cell cc = null; // 遍历集合数据,产生数据行 int index = 0; Map map = null; if (pageBean.getResult() != null && pageBean.getResult().size() > 0) { for (int i = 0; i < pageBean.getResult().size(); i++) { map = (HashMap) pageBean.getResult().get(i); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); row = sheet.createRow(index + 7); addRow(row, cc, index, cellStyle, map, styleLeft, sdf); index++; } } if (pageBean.getTotalPage() > 1) { for (int m = 2; m <= pageBean.getTotalPage(); m++) { pageBean.setPage(m); pageBean = groupOrderService.selectGroupOrderGuestListPage(pageBean, WebUtils.getCurBizId(request), WebUtils.getDataUserIdSet(request),userRightType); List list = pageBean.getResult(); if (list != null && list.size() > 0) { for (int j = 0; j < list.size(); j++) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); map = (HashMap) pageBean.getResult().get(j); row = sheet.createRow(index + 7); addRow(row, cc, index, cellStyle, map, styleLeft, sdf); index++; } } } } /* * Row to_v_row = sheet.createRow(2); Cell to_v_cc = * to_v_row.createCell(1); to_v_cc.setCellValue("11"); * to_v_cc.setCellStyle(styleLeft); */ row = sheet.createRow(5); cc = row.createCell(2); cc.setCellValue("成员信息:" + "" + "共:" + (int) pageBean.getTotalCount() + "人"); CellRangeAddress groupInfo = new CellRangeAddress((int) pageBean.getTotalCount() + 11, (int) pageBean.getTotalCount() + 12, 0, 4); sheet.addMergedRegion(groupInfo); row = sheet.createRow((int) pageBean.getTotalCount() + 11); 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); }其中的download下载方法实现代码如下:
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(); } }
KO,搞定。看看效果:
0 0
- 【项目实战】Java POI之Excel导出经典案例二
- 【项目实战】Java POI之Excel导出经典案例一
- 【项目实战】Java POI之Excel导出经典案例三
- 【项目实战】Java POI之Excel导出经典案例四
- 【项目实战】Java POI之Word导出经典案例一
- POI之经典案例--按列名导出excel
- Java中使用POI导出Excel 之 项目实战详细教程
- poi导出经典案例
- JAVA 之 使用 POI 导出 Excel (二)
- 【JAVA】POI导出EXCEL经典实现
- Java POI导出EXCEL经典实现
- java poi 导出excel经典demo
- Java POI导出EXCEL经典实现
- Java POI 导出EXCEL经典实现
- java POI导出EXCEL经典实现
- Java POI 导出EXCEL经典实现 Java导出Excel
- Java web项目利用POI导出excel
- java 项目利用POI导出excel总结
- 利用junit对springMVC的Controller进行测试(转)
- 11g中的分区表及分区索引
- 银行不良资产活动相关对象大起底
- 程序员怎样高效学习英语?
- View Controller 生命周期的各个方法的用法
- 【项目实战】Java POI之Excel导出经典案例二
- Android IPC进程间通信(从应用层源码分)《二》
- jersey设置编码方式
- 十年工龄的程序员为你揭示最危害程序员职业生涯的三大观念
- 基频提取算法-YIN算法解析
- Android-- 封装Dialog,即自定义Dialog,使其满足各种dialog的样式要求
- tcp三次握手
- bash参数补全功能 -- complete
- 第16周项目1-验证算法(7)归并排序