Apache POI 和ExportExcelByJXLS (JXLS)两种方法
一、Apache POI
-
Apache POI是一种流行的API,它允许程序员使用Java程序创建,修改和显示MS Office文件。这由Apache软件基金会开发使用Java分布式设计或修改Microsoft Office文件的开源库。它包含类和方法对用户输入数据或文件到MS Office文档进行解码。
-
/** * 导出excel列表 */ @RequestMapping("/exportposts") @ResponseBody public void exportPosts(HttpServletRequest request, HttpServletResponse response, Long projectId, Integer totalnum, String page) { // Long projectId=Long.valueOf(request.getParameter("projectId")); // Integer totalnum=Integer.valueOf(request.getParameter("totalnum")); try { Map<String, Object> map = new HashMap<String, Object>(); Map<String, Object> searchMap = new HashMap<String, Object>(); String str = request.getParameter("searchMap"); if (str != null) { JSONObject json = JSONObject.parseObject(str); for (String key : json.keySet()) { searchMap.put(key, json.get(key)); } } searchMap.put("items", projectId); PageInfo pageInfo = new PageInfo(); Integer currentPage = Integer.valueOf("".equals(page) || page == null ? "0" : page); pageInfo.setCurrentPage(currentPage); pageInfo.setRowPerPage(totalnum); //String ordermapstr = request.getParameter("orderMap"); List<String> orderTypeList = new ArrayList<>(); orderTypeList.add("{\"createdAt\":\"desc\"}"); orderTypeList.add("{\"cntReplied\":\"desc\"}"); orderTypeList.add("{\"cntForwarded\":\"desc\"}"); orderTypeList.add("{\"cntLikes\":\"desc\"}"); ArrayList<String> nameList = new ArrayList(); nameList.add("_第一页"); nameList.add("_第二页"); nameList.add("_第三页"); nameList.add("_第四页"); HSSFWorkbook wb = new HSSFWorkbook(); for (int count = 0; count<orderTypeList.size();count++){ Map<String, String> orderMap = new HashMap<String, String>(); String ordermapstr = orderTypeList.get(count); if (ordermapstr != null) { JSONObject json1 = JSONObject.parseObject(ordermapstr); for (String key : json1.keySet()) { orderMap.put(key, String.valueOf(json1.get(key))); } } pageInfo.setCurrentPage(0); pageInfo.setRowPerPage(0); map = communityService.selectPostByES(searchMap, pageInfo, orderMap); if (map == null) { log.info("没有数据需要导出."); return; } PageInfo totalInfo = (PageInfo) map.get("pageInfo"); PageInfo selectInfo = new PageInfo(); selectInfo.setRowPerPage(500); selectInfo.setTotalNum(totalInfo.getTotalNum()); Sheet sheet = addExcelHead(wb,nameList.get(count)); int index = 0; for (int i = 0; i < selectInfo.getPageNum(); i++) { selectInfo.setCurrentPage(i); Map<String, Object> map1 = communityService.selectPostByES(searchMap, selectInfo, orderMap); List<PostInfo> payload = new ArrayList<PostInfo>((Collection<? extends PostInfo>) map1.get("postData")); List<PostInfo> _payload = new ArrayList(); for(int j = 0; j < 5&&j<payload.size(); j++){ _payload.add(payload.get(j)); } addExcelData(sheet, _payload, index); index = index + _payload.size(); _payload.clear(); } } OutputStream os; os = response.getOutputStream(); response.reset(); response.setContentType("application/msexcel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode("帖子数据.xls", "UTF8") + "\"");//指定文件名,h5页面的a标签download属性和target属性冲突,不能指定 wb.write(os); response.getOutputStream().flush(); response.getOutputStream().close(); } catch (Exception e) { log.error("导出帖子数据ERROR.", e); }
}
二、ExportExcelByJXLS (JXLS)
-
Jxls是一个小型的Java库,使Excel生成报表变得容易。Jxls在Excel模板中使用特殊标记来定义输出格式和数据布局。
-
/** * Excel 下载方法 * @param taskId * @param chartCode * @param request * @param response */ @RequestMapping("{taskId:.+}/{chartCode:.+}") @ResponseBody public void downLoadByChartCode(@PathVariable("taskId") int taskId, @PathVariable("chartCode")String chartCode, HttpServletRequest request, HttpServletResponse response){ BvcUser adminUser = getAdminUser(); InsightList insightList = insightListService.findOne(taskId); if(null!=insightList&&adminUser.getUserId().equals(new Long((long)insightList.getCreateId()))){ String realPath=request.getServletContext().getRealPath(TYPE_COMPARISON_MODELPATH),//包含路径 toPath= insightList.getReportName()+"-"+Constants.CHART_NAME_MAP.get(chartCode)+"-"+ DateFormatUtils.format(new Date(),"yyyyMMdd")+suffix,//文件名 modelName="chartCode_"+chartCode,//模板名 path; Map beans=new HashMap(); OwnerInsightChartEntity chartsData=null; Map items; List list = new ArrayList(); String [] xDatas; String [] yDatas; JSONArray dataArr; switch (chartCode){ case Constants.CHARTCODE_TLCXSL: chartsData=ownerInsightChartService.getDataByTaskIdAndChartCode(taskId, chartCode); if(null!=chartsData){ xDatas = chartsData.getXData().split(","); yDatas = chartsData.getYData().split(","); List<String> carNamesByIds = bvcCarService.getCarNamesByIds(Arrays.asList(chartsData.getXData().replace(" ",""))); for (int i = 0; i < xDatas.length; i++) { items = new HashMap(); items.put("type", carNamesByIds.get(i)); items.put("num", yDatas[i]); list.add(items); } if(list.size() > 0){ beans.put("datas", list); } } if(beans.size()==0){ realPath=request.getServletContext().getRealPath(modelPath); modelName="NODATA"; } path= ExportExcelByJXLS.doExcel(realPath, modelName+suffix, toPath, beans); break; case Constants.CHARTCODE_TLCXXHD: chartsData=ownerInsightChartService.getDataByTaskIdAndChartCode(taskId, chartCode); if(null!=chartsData){ xDatas = chartsData.getXData().split(","); yDatas = chartsData.getYData().split(","); List<String> carNamesByIds = bvcCarService.getCarNamesByIds(Arrays.asList(chartsData.getXData().replace(" ",""))); for (int i = 0; i < xDatas.length; i++) { NumberFormat numberFormat = NumberFormat.getInstance(); // 设置精确到小数点后2位 numberFormat.setMaximumFractionDigits(2); numberFormat.setMinimumFractionDigits(2); items = new HashMap(); items.put("type",carNamesByIds.get(i)); items.put("num", numberFormat.format(Float.parseFloat(yDatas[i]) * 100F)); list.add(items); } if(list.size() > 0){ beans.put("vehiclePreferencesDatas", list); } } if(beans.size()==0){ realPath=request.getServletContext().getRealPath(modelPath); modelName="NODATA"; } path= ExportExcelByJXLS.doExcel(realPath, modelName+suffix, toPath, beans); break; case Constants.CHARTCODE_TLSX: chartsData=ownerInsightChartService.getDataByTaskIdAndChartCode(taskId, chartCode); List<String> sheetNames = new ArrayList<>(); List sheetData =new ArrayList(); String sheetName; List<String> carAttributeId = new ArrayList<String>(); if(null!=chartsData){ xDatas = chartsData.getXData().split(","); dataArr = JSONArray.parseArray(chartsData.getYData()); for (int k = 0; k < dataArr.size(); k++) { Map m = (Map)dataArr.get(k); carAttributeId.add(m.get("name").toString()); } List<String> carAttributeNames = bvcCarAttributeService.findAttrNamesByIds(carAttributeId, BvcCarAttributeEntity.attrLev.lev1); for (int i = 0; i < xDatas.length; i++) { list =new ArrayList(); sheetName = xDatas[i]; sheetNames.add(sheetName); for (int j = 0; j < dataArr.size(); j++) { items = new HashMap(); Map m = (Map)dataArr.get(j); items.put("type",carAttributeNames.get(j)); JSONArray data = JSONObject.parseArray(m.get("value").toString()); items.put("num",data.get(i)); list.add(items); } sheetData.add(list); } } if(sheetData.size()==0){ realPath=request.getServletContext().getRealPath(modelPath); modelName="NODATA"; path= ExportExcelByJXLS.doExcel(realPath, modelName+suffix, toPath, beans); }else{ path = ExportExcelByJXLS.doExcelSheets(realPath, modelName + suffix, toPath, sheetData, sheetNames); } break; default: log.info("下载未知类型图表"); return; } //下载文件 ExportExcelByJXLS.doDownLoad(path,toPath,response); //下载完,删除服务器文件 File file =new File(path); if(file.exists()&&file.isFile()){ file.delete(); } }
}