/** * 导出 */ @RequestMapping(value = "/exlMonthlyList") @ResponseBody public String exlMonthlyList(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException { //选择列名 String columnName = request.getParameter("columnName");//导出的表头 columnName = URLDecoder.decode(URLDecoder.decode(columnName, "UTF-8"), "UTF-8"); List<MonthlyCustom> monthlyCustomList = JSONArray.parseArray(columnName, MonthlyCustom.class); //单位 String unitId = request.getParameter("unitId");//单位分sheet，一个单位一个sheet，空则所有的数据导入一个sheet String unitName = request.getParameter("unitName"); // //项目类别 // String projectType = request.getParameter("projectType"); //导出月份 String reportMonth = request.getParameter("reportMonth"); String reportingType = request.getParameter("reportingType"); Map<String, Object> map = new HashMap<String, Object>(); map.put("columnName", monthlyCustomList); map.put("unitId", unitId); map.put("unitName", unitName); map.put("reportingType",reportingType); map.put("reportMonth", reportMonth); String filename = reportMonth + "月报汇总表.xls"; try { //创建excle表格 HSSFWorkbook workbook = new HSSFWorkbook(); workbook = projectBaseInfoService.exlMonthlyList(workbook, map); response.reset(); response.setContentType("application/octet-stream"); response.addHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(filename, "UTF-8")); OutputStream os = new BufferedOutputStream(response.getOutputStream()); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { logger.error("exlMonthlyList()出错", e); e.printStackTrace(); } return null; }
/** * 导出 */ @Override public HSSFWorkbook exlMonthlyList(HSSFWorkbook workbook, Map<String,Object> map) { String unitId= map.get("unitId")==null?"":map.get("unitId").toString(); String unitName =map.get("unitName")==null?"":map.get("unitName").toString(); String reportingType = map.get("reportingType")==null?"":map.get("reportingType").toString(); String reportMonth = map.get("reportMonth")==null?"":map.get("reportMonth").toString(); List <MonthlyCustom> monthlyCustomList = (List <MonthlyCustom>)map.get("columnName"); String [] unitIds=null; String [] unitNames=null; if(!"".equals(unitId)){ unitIds = unitId.split(","); } if(!"".equals(unitName)){ unitNames = unitName.split(","); } // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.WHITE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 9); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style2.setWrapText(true); // 生成并设置另一个样式 HSSFCellStyle style3 = workbook.createCellStyle(); style3.setFillForegroundColor(HSSFColor.WHITE.index); style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font3 = workbook.createFont(); font3.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font3.setFontName("楷体"); font3.setFontHeightInPoints((short) 12); style3.setFont(font3); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); HSSFSheet sheet; HSSFRow firstHeaderRow; HSSFRow secondHeaderRow; HSSFRow thirdHeaderRow; HSSFCell yearCell; HSSFCellStyle titleStyle; HSSFFont titleFont; HSSFRow row; HSSFCell cell; List dateList; //合并单元格 int maxLength; if( unitIds!=null && unitIds.length>0) { maxLength =(monthlyCustomList==null?0:monthlyCustomList.size())+2; //生成各个单位的表格 for (int ti = 0; ti < unitIds.length; ti++) { map.put("unitId", unitIds[ti]); map.put("unitName", unitNames[ti]); dateList = projectInfoDao.querymonthlyList(map); // 生成一个表格 sheet名称 汇总数据 sheet = workbook.createSheet(unitNames[ti] + "月报汇总"); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); //生成title firstHeaderRow = sheet.createRow(0);// 第一行 secondHeaderRow = sheet.createRow(1);// 第二行 thirdHeaderRow = sheet.createRow(2);// 第三行 //合并单元格 sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, maxLength-1)); yearCell = firstHeaderRow.createCell(0); yearCell.setCellValue(reportMonth + "月报汇总表"); titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleFont.setFontName("楷体"); titleStyle.setFont(titleFont); yearCell.setCellStyle(titleStyle); //循环遍历列名 HSSFCellStyle style5 = workbook.createCellStyle(); style5.setFillForegroundColor(HSSFColor.WHITE.index); style5.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style5.setBorderBottom(HSSFCellStyle.BORDER_THIN); style5.setBorderLeft(HSSFCellStyle.BORDER_THIN); style5.setBorderRight(HSSFCellStyle.BORDER_THIN); style5.setBorderTop(HSSFCellStyle.BORDER_THIN); style5.setAlignment(HSSFCellStyle.ALIGN_LEFT); row = sheet.createRow(3); for (int i = 0; i < maxLength; i++) {//i是headers的索引，n是Excel的索引 HSSFCell cell1 = row.createCell(i); cell1.setCellStyle(style); sheet.setColumnWidth(i, 10000); HSSFRichTextString text = null; if (i == 0) { sheet.setColumnWidth(0,1500); text = new HSSFRichTextString("序号"); }else if(i == 1){ sheet.setColumnWidth(1,10000); text = new HSSFRichTextString("项目名称"); } else { MonthlyCustom mc = monthlyCustomList.get(i - 2); text = new HSSFRichTextString(mc.getColumnName()); } cell1.setCellValue(text); } for(int j = 0;j<maxLength;j++){ HSSFCell cell1 = row.getCell(j); if("项目状态".equals(cell1.getStringCellValue())){ sheet.setColumnWidth(j,3000); } } dateList = projectInfoDao.querymonthlyList(map); // 遍历集合数据，产生数据行 // 遍历集合数据，产生数据行 for (int i = 0, index = 4; i < dateList.size(); i++, index++) { row = sheet.createRow(index); Object[] obj = (Object[]) dateList.get(i); for (int x = 0; x < maxLength; x++) { HSSFCell cell1 = row.createCell((short) x); cell1.setCellStyle(style2); HSSFRichTextString richString = null; if (x == 0) { richString = new HSSFRichTextString(String.valueOf(i + 1)); }else if(x == 1){ cell1.setCellStyle(style5); richString = new HSSFRichTextString(obj[0] == null ? "" : obj[0].toString()); } else { cell1.setCellStyle(style5); richString = new HSSFRichTextString(obj[x] == null ? "" : obj[x].toString()); } cell1.setCellValue(richString); } } } }else{ maxLength =(monthlyCustomList==null?0:monthlyCustomList.size())+3; // 生成一个表格 sheet名称 汇总数据 sheet = workbook.createSheet("月报汇总"); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); //生成title firstHeaderRow = sheet.createRow(0);// 第一行 sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, maxLength-1)); yearCell = firstHeaderRow.createCell(0); yearCell.setCellValue(reportMonth+"月报汇总表"); titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleFont.setFontName("楷体"); titleStyle.setFont(titleFont); yearCell.setCellStyle(titleStyle); //循环遍历列名 HSSFCellStyle style4 = workbook.createCellStyle(); style4.setFillForegroundColor(HSSFColor.WHITE.index); style4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style4.setBorderBottom(HSSFCellStyle.BORDER_THIN); style4.setBorderLeft(HSSFCellStyle.BORDER_THIN); style4.setBorderRight(HSSFCellStyle.BORDER_THIN); style4.setBorderTop(HSSFCellStyle.BORDER_THIN); style4.setAlignment(HSSFCellStyle.ALIGN_LEFT); row = sheet.createRow(3); for (int i = 0; i < maxLength; i++) {//i是headers的索引，n是Excel的索引 HSSFCell cell1 = row.createCell(i); cell1.setCellStyle(style); //sheet.setColumnWidth(i, 10000); HSSFRichTextString text = null; if(i<3){ if(i==0){ sheet.setColumnWidth(0,1500); text=new HSSFRichTextString("序号"); }else if(i==1){ sheet.setColumnWidth(1,10000); text=new HSSFRichTextString("项目名称"); }else{ sheet.setColumnWidth(2,3000); text=new HSSFRichTextString("合作单位"); } }else { sheet.setColumnWidth(i,10000); MonthlyCustom mc = monthlyCustomList.get(i - 3); text = new HSSFRichTextString(mc.getColumnName()); } cell1.setCellValue(text); } for(int j = 0;j<maxLength;j++){ HSSFCell cell1 = row.getCell(j); if("项目状态".equals(cell1.getStringCellValue())){ sheet.setColumnWidth(j,3000); } } dateList = projectInfoDao.querymonthlyList(map); // 遍历集合数据，产生数据行 for (int i = 0, index = 4; i < dateList.size(); i++, index++) { row = sheet.createRow(index); Object [] obj = (Object [])dateList.get(i); for (int x=0;x<maxLength;x++){ HSSFCell cell1 = row.createCell((short) x); cell1.setCellStyle(style2); HSSFRichTextString richString=null; if(x==0){ richString = new HSSFRichTextString(String.valueOf(i+1)); }else if(x == 1){ cell1.setCellStyle(style4); richString = new HSSFRichTextString(obj[x-1]==null?"":obj[x-1].toString()); }else{ richString = new HSSFRichTextString(obj[x-1]==null?"":obj[x-1].toString()); } cell1.setCellValue(richString); } } } return workbook; }效果：
1249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
