poi单个导出Excel（klx的比较完整的例子）
/****************************** * 用途说明: * 作者姓名: Administrator * 创建时间: 2022-10-14 19:53 ******************************/ public class T { //创建Excel @RequestMapping(value = {"listExportCal", ""}) public String listExport(FormBean formBean, HttpServletRequest request, HttpServletResponse response) throws IOException { Map<String, Object> searchParams = formBean.getBean(); String year = MapUtils.getString(searchParams, "ND", "") + "年"; Page<Map<String, Object>> page = calendarService.list( new Page<Map<String, Object>>(request, response), formBean); //创建HSSFWorkbook对象(excel的文档对象) HSSFWorkbook wb = new HSSFWorkbook(); //建立新的sheet对象（excel的表单） HSSFSheet sheet = wb.createSheet("中山大学党委组织部部历"); //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个 //================样式设置start====================== //样式1 HSSFCellStyle setBorder = wb.createCellStyle(); //二、设置边框: setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 // 三、设置居中: setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 四、设置字体: HSSFFont font = wb.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 16);//设置字体大小 /* HSSFFont font2 = wb.createFont(); font2.setFontName("仿宋_GB2312"); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 font2.setFontHeightInPoints((short) 12);*/ setBorder.setFont(font);//选择需要用到的字体格式 //样式2 HSSFCellStyle setBorder1 = wb.createCellStyle(); //一、设置背景色： setBorder1.setFillForegroundColor(IndexedColors.TAN.getIndex());// 设置背景色 setBorder1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //二、设置边框: setBorder1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 // 三、设置居中: setBorder1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 四、设置字体: HSSFFont font1 = wb.createFont(); font1.setFontName("宋体"); font1.setFontHeightInPoints((short) 16);//设置字体大小 /* HSSFFont font2 = wb.createFont(); font2.setFontName("仿宋_GB2312"); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 font2.setFontHeightInPoints((short) 12);*/ setBorder1.setFont(font1);//选择需要用到的字体格式 //样式4 HSSFCellStyle setBorder3 = wb.createCellStyle(); //一、设置背景色： setBorder3.setFillForegroundColor(IndexedColors.TAN.getIndex());// 设置背景色 setBorder3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //二、设置边框: setBorder3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 // 三、设置居中: setBorder3.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 指定单元格垂直居中对齐 setBorder3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 四、设置字体: HSSFFont font3 = wb.createFont(); font3.setFontName("宋体"); font3.setFontHeightInPoints((short) 14);//设置字体大小 /* HSSFFont font2 = wb.createFont(); font2.setFontName("仿宋_GB2312"); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 font2.setFontHeightInPoints((short) 12);*/ setBorder3.setFont(font3);//选择需要用到的字体格式 //样式5 HSSFCellStyle setBorder4 = wb.createCellStyle(); //二、设置边框: setBorder4.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder4.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder4.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder4.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 // 三、设置居中: setBorder4.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 指定单元格垂直居中对齐 setBorder4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 四、设置字体: HSSFFont font4 = wb.createFont(); font4.setFontName("宋体"); font4.setFontHeightInPoints((short) 12);//设置字体大小 /* HSSFFont font2 = wb.createFont(); font2.setFontName("仿宋_GB2312"); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 font2.setFontHeightInPoints((short) 12);*/ setBorder4.setFont(font4);//选择需要用到的字体格式 //样式6 HSSFCellStyle setBorder5 = wb.createCellStyle(); setBorder5.setWrapText(true); //二、设置边框: setBorder5.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder5.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder5.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder5.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 // 三、设置居中: setBorder5.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居zuo // 四、设置字体: HSSFFont font5 = wb.createFont(); font5.setFontName("宋体"); font5.setFontHeightInPoints((short) 12);//设置字体大小 /* HSSFFont font2 = wb.createFont(); font2.setFontName("仿宋_GB2312"); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 font2.setFontHeightInPoints((short) 12);*/ setBorder5.setFont(font5);//选择需要用到的字体格式 //================样式设置end======================== HSSFRow row1 = sheet.createRow(0); //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个 HSSFCell cell = row1.createCell(0); HSSFCell cell01 = row1.createCell(0); cell01.setCellStyle(setBorder); HSSFCell cell111 = row1.createCell(1); cell111.setCellStyle(setBorder); HSSFCell cell21 = row1.createCell(2); cell21.setCellStyle(setBorder); HSSFCell cell31 = row1.createCell(3); cell31.setCellStyle(setBorder); HSSFCell cell41 = row1.createCell(4); cell41.setCellStyle(setBorder); HSSFCell cell51 = row1.createCell(5); cell51.setCellStyle(setBorder); HSSFCell cell61 = row1.createCell(6); cell61.setCellStyle(setBorder); HSSFCell cell71 = row1.createCell(7); cell71.setCellStyle(setBorder); HSSFCell cell81 = row1.createCell(8); cell81.setCellStyle(setBorder); HSSFCell cell91 = row1.createCell(9); cell91.setCellStyle(setBorder); HSSFCell cell101 = row1.createCell(10); cell101.setCellStyle(setBorder); HSSFCell cell0111 = row1.createCell(11); cell0111.setCellStyle(setBorder); HSSFCell cell121 = row1.createCell(12); cell121.setCellStyle(setBorder); HSSFCell cell131 = row1.createCell(13); cell131.setCellStyle(setBorder); HSSFCell cell141 = row1.createCell(14); cell141.setCellStyle(setBorder); //设置单元格内容 cell.setCellValue("中山大学党委组织部部历"); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14)); cell.setCellStyle(setBorder); //在sheet里创建第二行 HSSFRow row2 = sheet.createRow(1); //创建单元格并设置单元格内容 HSSFCell cell0 = row2.createCell(0); cell0.setCellValue(year); cell0.setCellStyle(setBorder1); HSSFCell cell1 = row2.createCell(1); cell1.setCellValue("周次"); cell1.setCellStyle(setBorder1); HSSFCell cell2 = row2.createCell(2); cell2.setCellValue("日"); cell2.setCellStyle(setBorder1); HSSFCell cell3 = row2.createCell(3); cell3.setCellValue("一"); cell3.setCellStyle(setBorder1); HSSFCell cell4 = row2.createCell(4); cell4.setCellValue("二"); cell4.setCellStyle(setBorder1); HSSFCell cell5 = row2.createCell(5); cell5.setCellValue("三"); cell5.setCellStyle(setBorder1); HSSFCell cell6 = row2.createCell(6); cell6.setCellValue("四"); cell6.setCellStyle(setBorder1); HSSFCell cell7 = row2.createCell(7); cell7.setCellValue("五"); cell7.setCellStyle(setBorder1); HSSFCell cell8 = row2.createCell(8); cell8.setCellValue("六"); cell8.setCellStyle(setBorder1); HSSFCell cell9 = row2.createCell(9); cell9.setCellValue("工作事项"); cell9.setCellStyle(setBorder1); HSSFCell cell10 = row2.createCell(10); cell10.setCellValue(""); cell10.setCellStyle(setBorder1); HSSFCell cell11 = row2.createCell(11); cell11.setCellValue(""); cell11.setCellStyle(setBorder1); HSSFCell cell12 = row2.createCell(12); cell12.setCellValue(""); cell12.setCellStyle(setBorder1); HSSFCell cell13 = row2.createCell(13); cell13.setCellValue(""); cell13.setCellStyle(setBorder1); HSSFCell cell15 = row2.createCell(14); cell15.setCellValue("备注"); cell15.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region2 = new CellRangeAddress(1, 1, 9, (short) 13); //给定要合并的单元格范围 sheet.addMergedRegion(region2); //在sheet里创建第三行 HSSFRow row3 = sheet.createRow(2); //创建单元格并设置单元格内容 HSSFCell cell30 = row3.createCell(0); cell30.setCellValue(year); cell30.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region3 = new CellRangeAddress(1, 2, 0, (short) 0); //给定要合并的单元格范围 sheet.addMergedRegion(region3); HSSFCell cell311 = row3.createCell(1); cell311.setCellValue("周次"); cell311.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region4 = new CellRangeAddress(1, 2, 1, (short) 1); //给定要合并的单元格范围 sheet.addMergedRegion(region4); HSSFCell cell32 = row3.createCell(2); cell32.setCellValue("日"); cell32.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region5 = new CellRangeAddress(1, 2, 2, (short) 2); //给定要合并的单元格范围 sheet.addMergedRegion(region5); HSSFCell cell33 = row3.createCell(3); cell33.setCellValue("一"); cell33.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region6 = new CellRangeAddress(1, 2, 3, (short) 3); //给定要合并的单元格范围 sheet.addMergedRegion(region6); HSSFCell cell34 = row3.createCell(4); cell34.setCellValue("二"); cell34.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region61 = new CellRangeAddress(1, 2, 4, (short) 4); //给定要合并的单元格范围 sheet.addMergedRegion(region61); HSSFCell cell35 = row3.createCell(5); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region7 = new CellRangeAddress(1, 2, 5, (short) 5); //给定要合并的单元格范围 sheet.addMergedRegion(region7); cell35.setCellValue("三"); cell35.setCellStyle(setBorder1); HSSFCell cell36 = row3.createCell(6); cell36.setCellValue("四"); cell36.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region8 = new CellRangeAddress(1, 2, 6, (short) 6); //给定要合并的单元格范围 sheet.addMergedRegion(region8); HSSFCell cell37 = row3.createCell(7); cell37.setCellValue("五"); cell37.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region9 = new CellRangeAddress(1, 2, 7, (short) 7); //给定要合并的单元格范围 sheet.addMergedRegion(region9); HSSFCell cell38 = row3.createCell(8); cell38.setCellValue("六"); cell38.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列 CellRangeAddress region10 = new CellRangeAddress(1, 2, 8, (short) 8); //给定要合并的单元格范围 sheet.addMergedRegion(region10); HSSFCell cell310 = row3.createCell(9); cell310.setCellValue("组织工作"); cell310.setCellStyle(setBorder1); //设置单元格的宽度 sheet.setColumnWidth(2, 5 * 256); sheet.setColumnWidth(3, 5 * 256); sheet.setColumnWidth(4, 5 * 256); sheet.setColumnWidth(5, 5 * 256); sheet.setColumnWidth(6, 5 * 256); sheet.setColumnWidth(7, 5 * 256); sheet.setColumnWidth(8, 5 * 256); sheet.setColumnWidth(9, 25 * 256); sheet.setColumnWidth(10, 25 * 256); sheet.setColumnWidth(11, 25 * 256); sheet.setColumnWidth(12, 25 * 256); sheet.setColumnWidth(13, 25 * 256); sheet.setColumnWidth(14, 25 * 256); HSSFCell cell3111 = row3.createCell(10); cell3111.setCellValue("干部选任"); cell3111.setCellStyle(setBorder1); HSSFCell cell312 = row3.createCell(11); cell312.setCellValue("干部监督"); cell312.setCellStyle(setBorder1); HSSFCell cell313 = row3.createCell(12); cell313.setCellValue("教育培训"); cell313.setCellStyle(setBorder1); HSSFCell cell314 = row3.createCell(13); cell314.setCellValue("综合管理"); cell314.setCellStyle(setBorder1); HSSFCell cell315 = row3.createCell(14); cell315.setCellValue("备注"); cell315.setCellStyle(setBorder1); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列（行合并） CellRangeAddress region11 = new CellRangeAddress(1, 2, 14, (short) 14); //给定要合并的单元格范围 sheet.addMergedRegion(region11); //定义行号 int index = 3; //循环赋值 if (page != null && page.getList() != null && page.getList().size() > 0) { Map<String, Object> resMap = page.getList().get(0);// 取出的结果 List<Map<String, Object>> resList = getListByMap(resMap); if (null != resList && resList.size() > 0) { for (int i = 0; i < resList.size(); i++) { ++index; //在sheet里创建第三行 HSSFRow row5 = sheet.createRow(3 + i); //创建单元格并设置单元格内容 HSSFCell cell50 = row5.createCell(0); if (resList.get(i) != null && resList.get(i).containsKey("month") && null != resList.get(i).get("month")) { cell50.setCellValue(resList.get(i).get("month").toString() + "月"); } else { cell50.setCellValue(""); } cell50.setCellStyle(setBorder3); HSSFCell cell511 = row5.createCell(1); if (resList.get(i) != null && resList.get(i).containsKey("weekNum") && null != resList.get(i).get("weekNum")) { cell511.setCellValue("第" + resList.get(i).get("weekNum").toString() + "周"); } else { cell511.setCellValue(""); } cell511.setCellStyle(setBorder4); HSSFCell cell52 = row5.createCell(2); if (resList.get(i) != null && resList.get(i).containsKey("week7")) { cell52.setCellValue(resList.get(i).get("week7").toString()); } else { cell52.setCellValue(""); } cell52.setCellStyle(setBorder4); HSSFCell cell53 = row5.createCell(3); if (resList.get(i) != null && resList.get(i).containsKey("week1")) { cell53.setCellValue(resList.get(i).get("week1").toString()); } else { cell53.setCellValue(""); } cell53.setCellStyle(setBorder4); HSSFCell cell54 = row5.createCell(4); if (resList.get(i) != null && resList.get(i).containsKey("week2")) { cell54.setCellValue(resList.get(i).get("week2").toString()); } else { cell54.setCellValue(""); } cell54.setCellStyle(setBorder4); HSSFCell cell55 = row5.createCell(5); if (resList.get(i) != null && resList.get(i).containsKey("week3")) { cell55.setCellValue(resList.get(i).get("week3").toString()); } else { cell55.setCellValue(""); } cell55.setCellStyle(setBorder4); HSSFCell cell56 = row5.createCell(6); if (resList.get(i) != null && resList.get(i).containsKey("week4")) { cell56.setCellValue(resList.get(i).get("week4").toString()); } else { cell56.setCellValue(""); } cell56.setCellStyle(setBorder4); HSSFCell cell57 = row5.createCell(7); if (resList.get(i) != null && resList.get(i).containsKey("week5")) { cell57.setCellValue(resList.get(i).get("week5").toString()); } else { cell57.setCellValue(""); } cell57.setCellStyle(setBorder4); HSSFCell cell58 = row5.createCell(8); if (resList.get(i) != null && resList.get(i).containsKey("week6")) { cell58.setCellValue(resList.get(i).get("week6").toString()); } else { cell58.setCellValue(""); } cell58.setCellStyle(setBorder4); HSSFCell cell510 = row5.createCell(9); if (resList.get(i) != null && resList.get(i).containsKey("strZZGZList")) { cell510.setCellValue(resList.get(i).get("strZZGZList").toString()); } else { cell510.setCellValue(""); } cell510.setCellStyle(setBorder5); //设置单元格的宽度 sheet.setColumnWidth(2, 5 * 256); sheet.setColumnWidth(3, 5 * 256); sheet.setColumnWidth(4, 5 * 256); sheet.setColumnWidth(5, 5 * 256); sheet.setColumnWidth(6, 5 * 256); sheet.setColumnWidth(7, 5 * 256); sheet.setColumnWidth(8, 5 * 256); sheet.setColumnWidth(9, 25 * 256); sheet.setColumnWidth(10, 25 * 256); sheet.setColumnWidth(11, 25 * 256); sheet.setColumnWidth(12, 25 * 256); sheet.setColumnWidth(13, 25 * 256); sheet.setColumnWidth(14, 25 * 256); HSSFCell cell5111 = row5.createCell(10); if (resList.get(i) != null && resList.get(i).containsKey("strGBXRList")) { cell5111.setCellValue(resList.get(i).get("strGBXRList").toString()); } else { cell5111.setCellValue(""); } cell5111.setCellStyle(setBorder5); HSSFCell cell512 = row5.createCell(11); if (resList.get(i) != null && resList.get(i).containsKey("strGBJDList")) { cell512.setCellValue(resList.get(i).get("strGBJDList").toString()); } else { cell512.setCellValue(""); } cell512.setCellStyle(setBorder5); HSSFCell cell513 = row5.createCell(12); if (resList.get(i) != null && resList.get(i).containsKey("strJYPXList")) { cell513.setCellValue(resList.get(i).get("strJYPXList").toString()); } else { cell513.setCellValue(""); } cell513.setCellStyle(setBorder5); HSSFCell cell514 = row5.createCell(13); if (resList.get(i) != null && resList.get(i).containsKey("strZHGLList")) { cell514.setCellValue(resList.get(i).get("strZHGLList").toString()); } else { cell514.setCellValue(""); } cell514.setCellStyle(setBorder5); HSSFCell cell515 = row5.createCell(14); if (resList.get(i) != null && resList.get(i).containsKey("strZHGLList")) { cell515.setCellValue(resList.get(i).get("strZHGLList").toString()); } else { cell515.setCellValue(""); } cell515.setCellStyle(setBorder5); } } } //在sheet里创建第index行 HSSFRow row6 = sheet.createRow(index); HSSFCell cell601 = row6.createCell(0); cell601.setCellStyle(setBorder5); String mark = "填表说明："; mark = mark + "\n" + " 1.请按工作类别填写。"; mark = mark + "\n" + " 2.仅填写重点工作；常委会、干部小组会议、跨部门会议等重要会议；全体人员参加的活动；其它重要事项。"; mark = mark + "\n" + ""; mark = mark + "\n" + ""; cell601.setCellValue(mark); //设置单元格高度 row6.setHeight((short) 1500); HSSFCell cell6111 = row6.createCell(1); cell6111.setCellStyle(setBorder5); HSSFCell cell621 = row6.createCell(2); cell621.setCellStyle(setBorder5); HSSFCell cell631 = row6.createCell(3); cell631.setCellStyle(setBorder5); HSSFCell cell641 = row6.createCell(4); cell641.setCellStyle(setBorder5); HSSFCell cell651 = row6.createCell(5); cell651.setCellStyle(setBorder5); HSSFCell cell661 = row6.createCell(6); cell661.setCellStyle(setBorder5); HSSFCell cell671 = row6.createCell(7); cell671.setCellStyle(setBorder5); HSSFCell cell681 = row6.createCell(8); cell681.setCellStyle(setBorder5); HSSFCell cell691 = row6.createCell(9); cell691.setCellStyle(setBorder5); HSSFCell cell6101 = row6.createCell(10); cell6101.setCellStyle(setBorder5); HSSFCell cell60111 = row6.createCell(11); cell60111.setCellStyle(setBorder5); HSSFCell cell6121 = row6.createCell(12); cell6121.setCellStyle(setBorder5); HSSFCell cell6131 = row6.createCell(13); cell6131.setCellStyle(setBorder5); HSSFCell cell6141 = row6.createCell(14); cell6141.setCellStyle(setBorder5); //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列（行合并） CellRangeAddress region66 = new CellRangeAddress(index, index, 0, (short) 14); //给定要合并的单元格范围 sheet.addMergedRegion(region66); //输出Excel文件 OutputStream output = response.getOutputStream(); response.reset(); String fileName = "中山大学党委组织部部历"; String agent = request.getHeader("USER-AGENT").toLowerCase(); response.setContentType("application/vnd.ms-excel"); String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8"); if (agent.contains("firefox")) { response.setCharacterEncoding("utf-8"); response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls"); } else { response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls"); } response.setContentType("application/msexcel"); wb.write(output); output.close(); return null; } }
==============Excel样式图片================
