导出Excel表用到的组件POI,动态的获取每个列数,然后将每个行数的值累加求和。代码如下
controller层代码
try { XSSFCell cell = null; String sheetName="督查统计数据总和"; String fileName =new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+sheetName + ".xlsx"; // 第一步,创建一个webbook,对应一个Excel文件 XSSFWorkbook xb = new XSSFWorkbook(); // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet XSSFSheet sheet = xb.createSheet(sheetName); // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short XSSFRow row = sheet.createRow(0); // 第四步,创建单元格,并设置值表头 设置表头居中 XSSFCellStyle style = xb.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 List<SupdataSend> list=supdataSendService.getSupdatasendid(supdataSend.getId()); List<String> list1= new ArrayList<String>(); list1.add("部门名称"); list1.add("上报次数"); for (SupdataSend supdataSend1:list){ list1.add(supdataSend1.getDataName()); } for (int i = 0; i < list1.size(); i++) { cell = row.createCell(i); cell.setCellValue(list1.get(i)); cell.setCellStyle(style); } List<SupdataSend> sendList=supdataSendService.getListImport(supdataSend.getId()); for (int i=0;i<sendList.size();i++){ row = sheet.createRow(i+1); if (sendList.get(i).getReceiveId()!=null){ row.createCell(0).setCellValue(sendList.get(i).getReceiveId()); }else { row.createCell(0).setCellValue(""); } if (sendList.get(i).getReportNum()!=null){ row.createCell(1).setCellValue(sendList.get(i).getReportNum()); }else { row.createCell(1).setCellValue("0"); } for (int j=0;j<list.size();j++){
//根据行和列统计总数
Double sumvalue= supdataValSonService.getBydataName(list.get(j).getDataName(),sendList.get(i).getRemark());
if (sumvalue!=null){
row.createCell(j+2).setCellValue(sumvalue);
}else {
row.createCell(j+2).setCellValue("0");
}
}
}
try { this.setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); xb.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } } catch (Exception e) { addMessage(redirectAttributes, "导出督查统计记录失败!失败信息:" + e.getMessage()); }
public void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(), "ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setContentType("application/octet-stream;charset=ISO8859-1"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } }service层代码
public List<SupdataSend> getListImport(String id){ //List<SupdataField> fieldList=supdataFieldDao.getSupdataSendid(id); List<SupdataSend> list1= new ArrayList<SupdataSend>(); List<SupdataReceive> list=supdataReceiveDao.getSupdatasendId(id); for (SupdataReceive supdataReceive:list){ SupdataSend supdataSend= new SupdataSend(); supdataSend.setReceiveId(supdataReceive.getOfficeName()); supdataSend.setReportNum(supdataReceive.getReportNum()); //封装单位的ID supdataSend.setRemark(supdataReceive.getRemark()); //supdataSend.setList(list2); list1.add(supdataSend); } return list1; }