本文主要是介绍工作总结之----动态导出数据到Excel，多sheet页，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
/** * 导出 */ @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，空则所有的数据导入一个sheetString 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;}效果：
这篇关于工作总结之----动态导出数据到Excel，多sheet页的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
