以下代码是动态填充数据，这些数据是从数据库取得的批量的，并合并单元格，最终导出excel。但是有一点需要注意，一定是先填充数据在合并单元格，而且每个单元格的样式都是提前设置好的 。
举个例子：合并单元格，第一行的第一个单元格是靠左的，第二个单元格你想居中，第三个单元格是靠右，这样是没法实现的。
下面代码可以直接使用，分为四部分，标题，head部分，内容，foot部分。
public void export(List headerList, List middleList, List footList, List data, String title, GlobalDto globalDto, HttpServletResponse response) throws Exception {try {// 创建一个workbook，对应一个Excel文件HSSFWorkbook workbook = new HSSFWorkbook();// 在webbook中添加一个sheet,对应Excel文件中的sheetHSSFSheet hssfSheet = workbook.createSheet("sheet1");// 设置head单元格的格式HSSFCellStyle style1 = workbook.createCellStyle();// style1.setFillPattern(FillPatternType.ALT_BARS);// 设置边框的线条style1.setBorderBottom(BorderStyle.THIN);style1.setBorderLeft(BorderStyle.THIN);style1.setBorderRight(BorderStyle.THIN);style1.setBorderTop(BorderStyle.THIN);// 设置边框的颜色为白色（默认是黑色）if ("0".equals(globalDto.getHeaderBorder())) {style1.setBottomBorderColor(IndexedColors.WHITE.getIndex());style1.setLeftBorderColor(IndexedColors.WHITE.getIndex());style1.setRightBorderColor(IndexedColors.WHITE.getIndex());style1.setTopBorderColor(IndexedColors.WHITE.getIndex());}style1.setAlignment(HorizontalAlignment.CENTER);style1.setVerticalAlignment(VerticalAlignment.CENTER);style1.setWrapText(true);// 生成并设置另一个样式:文本样式 标题的格式HSSFCellStyle style2 = workbook.createCellStyle();style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());// style2.setFillPattern(FillPatternType.ALT_BARS);style2.setBorderBottom(BorderStyle.THIN);style2.setBorderLeft(BorderStyle.THIN);style2.setBorderRight(BorderStyle.THIN);style2.setBorderTop(BorderStyle.THIN);style2.setAlignment(HorizontalAlignment.CENTER);style2.setVerticalAlignment(VerticalAlignment.CENTER);style2.setWrapText(true);// 设置foot单元格的格式HSSFCellStyle style3 = workbook.createCellStyle();// 设置边框的线条style3.setBorderBottom(BorderStyle.THIN);style3.setBorderLeft(BorderStyle.THIN);style3.setBorderRight(BorderStyle.THIN);style3.setBorderTop(BorderStyle.THIN);// 设置边框的颜色为白色（默认是黑色）if ("0".equals(globalDto.getFooterBorder())) {style3.setBottomBorderColor(IndexedColors.WHITE.getIndex());style3.setLeftBorderColor(IndexedColors.WHITE.getIndex());style3.setRightBorderColor(IndexedColors.WHITE.getIndex());style3.setTopBorderColor(IndexedColors.WHITE.getIndex());}style3.setAlignment(HorizontalAlignment.CENTER);style3.setVerticalAlignment(VerticalAlignment.CENTER);// 生成一个字体// HSSFFont font = workbook.createFont();// font.setFontHeightInPoints((short) 12);// font.setBold(true);// style3.setFont(font);style3.setWrapText(true);// 设置标题的格式HSSFCellStyle style4 = workbook.createCellStyle();// 设置边框的线条style4.setBorderBottom(BorderStyle.THIN);style4.setBorderLeft(BorderStyle.THIN);style4.setBorderRight(BorderStyle.THIN);style4.setBorderTop(BorderStyle.THIN);// 设置边框的颜色为白色（默认是黑色）style4.setBottomBorderColor(IndexedColors.WHITE.getIndex());style4.setLeftBorderColor(IndexedColors.WHITE.getIndex());style4.setRightBorderColor(IndexedColors.WHITE.getIndex());style4.setTopBorderColor(IndexedColors.WHITE.getIndex());style4.setAlignment(HorizontalAlignment.CENTER);style4.setVerticalAlignment(VerticalAlignment.CENTER);// 生成一个字体HSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 12);font.setBold(true);style4.setFont(font);style4.setWrapText(true);// 获取表的最大的列int length = middleList.size();int size = 0;List<HeaderDto> hList = null;List<FootDto> fList = null;// 记录行int count = 0;// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制shortHSSFRow row = null;HSSFCell hssfCell = null;// 写标题row = hssfSheet.createRow(count++);hssfCell = row.createCell(0);setCellValue(hssfCell, title, style4);// 获取data的第一个数据 方便用来取头部和底部文本的数据源数据Map generate = (Map) data.get(0);Date now = new Date();// 1.写header部分的数据(与写foot部分的写法一样，怎么去封装)for (int i = 0; i < headerList.size(); i++) {List<Integer> mergeCountList = new ArrayList<>();row = hssfSheet.createRow(count++);hList = (List<HeaderDto>) headerList.get(i);// 这里可以判断是否为nullsize = hList.size();// 记录写入列的位置int k = 0;if (size <= (length / 2) + 1) {for (int j = 0; j < length; j++) {String cellText = "";//String align = "center";if ((j + 1) % 2 != 0) {if (k < size) {mergeCountList.add(j);HeaderDto head = hList.get(k);// 1、常量文本 2、数据源字段 3、打印信息if ("2".equals(head.getCellType())) {// 从数据源中取数据(有可能是)cellText = formatData(generate.get(head.getCellText()));} else if ("1".equals(head.getCellType())) {cellText = head.getCellText();} else if ("3".equals(head.getCellType())) {// 1、打印时间 2、打印日期if ("1".equals(head.getCellText())) {cellText = new SimpleDateFormat("HH:mm:ss").format(now);} else if ("2".equals(head.getCellText())) {cellText = new SimpleDateFormat("yyyy-MM-dd").format(now);} else {cellText = "";}} else {cellText = "";}//align = head.getAlign();k++;}}HSSFCell cell = row.createCell(j);setCellValue(cell, cellText, style1);}logger.info("i:{}, mergeCountList:{}, length:{}", i, JSON.toJSONString(mergeCountList), length);for (Integer mergeCount : mergeCountList) {if (mergeCountList.get(mergeCountList.size() - 1).equals(mergeCount)) {// 最后一个单元格firstCol<lastCol,否则异常，不能导出if (pareTo(length - 1) < 0) {// 遍历到的mergeCount就是list的最后一个元素hssfSheet.addMergedRegion(new CellRangeAddress(count - 1, count - 1, mergeCount, length - 1));}} else {hssfSheet.addMergedRegion(new CellRangeAddress(count - 1, count - 1, mergeCount, mergeCount + 1));}}} else {for (int j = 0; j < length; j++) {String cellText = "";// String align = "center";if (k < size) {HeaderDto head = hList.get(k);// 1、常量文本 2、数据源字段 3、打印信息if ("2".equals(head.getCellType())) {// 从数据源中取数据(有可能是)cellText = formatData(generate.get(head.getCellText()));} else if ("1".equals(head.getCellType())) {cellText = head.getCellText();} else if ("3".equals(head.getCellType())) {// 1、打印时间 2、打印日期if ("1".equals(head.getCellText())) {cellText = new SimpleDateFormat("HH:mm:ss").format(now);} else if ("2".equals(head.getCellText())) {cellText = new SimpleDateFormat("yyyy-MM-dd").format(now);} else {cellText = "";}} else {cellText = "";}// align = head.getAlign();k++;}HSSFCell cell = row.createCell(j);setCellValue(cell, cellText, style1);}logger.info("i:{}, size:{}, length:{}", i, size, length);if (size < length) {hssfSheet.addMergedRegion(new CellRangeAddress(count - 1, count - 1, size - 1, length - 1));}}// 删除list，防止过多的创建list导致内存溢出mergeCountList.clear();}// 2.写middle部分的数据row = hssfSheet.createRow(count++);for (int i = 0; i < middleList.size(); i++) {MiddleDto mDto = (MiddleDto) middleList.get(i);hssfCell = row.createCell(i);hssfCell.setCellValue(mDto.getCellText());hssfCell.setCellStyle(style2);}// 3.写data数据,获取map中的list集合for (int i = 0; i < data.size(); i++) {row = hssfSheet.createRow(count++);Map ddMap = (Map) data.get(i);// 写数据for (int j = 0; j < length; j++) {// 创建单元格HSSFCell cell = row.createCell(j);cell.setCellStyle(style2);MiddleDto mDto = (MiddleDto) middleList.get(j);if ("序号".equals(mDto.getCellText())) {int z = i + 1;cell.setCellValue(z + "");} else {// 设置单元格的值MiddleDto temp = (MiddleDto) middleList.get(j);// String filed = ddMap.get(temp.getKey())+"";String filed = formatData(ddMap.get(temp.getKey()));cell.setCellValue(filed);}}}// 4.写foot数据for (int i = 0; i < footList.size(); i++) {List<Integer> mergeCountList = new ArrayList<>();row = hssfSheet.createRow(count++);fList = (List<FootDto>) footList.get(i);// 这里可以判断是否为nullsize = fList.size();// 记录写入列的位置yint k = 0;if (size <= (length / 2) + 1) {for (int j = 0; j < length; j++) {String cellText = "";//String align = "center";if ((j + 1) % 2 != 0) {if (k < size) {mergeCountList.add(j);FootDto foot = fList.get(k);// 1、常量文本 2、数据源字段 3、打印信息if ("2".equals(foot.getCellType())) {// 从数据源中取数据cellText = formatData(generate.get(foot.getCellText()));} else if ("1".equals(foot.getCellType())) {cellText = foot.getCellText();} else if ("3".equals(foot.getCellType())) {// 1、打印时间 2、打印日期if ("1".equals(foot.getCellText())) {cellText = new SimpleDateFormat("HH:mm:ss").format(now);} else if ("2".equals(foot.getCellText())) {cellText = new SimpleDateFormat("yyyy-MM-dd").format(now);} else {cellText = "";}} else {cellText = "";}//align = foot.getAlign();k++;}}HSSFCell cell = row.createCell(j);setCellValue(cell, cellText, style3);}logger.info("i:{}, mergeCountList:{}, length:{}", i, JSON.toJSONString(mergeCountList), length);for (Integer mergeCount : mergeCountList) {if (mergeCountList.get(mergeCountList.size() - 1).equals(mergeCount)) {// 最后一个单元格firstCol<lastCol,否则异常，不能导出if (pareTo(length - 1) < 0) {// 遍历到的mergeCount就是list的最后一个元素hssfSheet.addMergedRegion(new CellRangeAddress(count - 1, count - 1, mergeCount, length - 1));}} else {hssfSheet.addMergedRegion(new CellRangeAddress(count - 1, count - 1, mergeCount, mergeCount + 1));}}} else {for (int j = 0; j < length; j++) {String cellText = "";//String align = "center";if (k < size) {FootDto foot = fList.get(k);// 1、常量文本 2、数据源字段 3、打印信息if ("2".equals(foot.getCellType())) {// 从数据源中取数据cellText = formatData(generate.get(foot.getCellText()));} else if ("1".equals(foot.getCellType())) {cellText = foot.getCellText();} else if ("3".equals(foot.getCellType())) {// 1、打印时间 2、打印日期if ("1".equals(foot.getCellText())) {cellText = new SimpleDateFormat("HH:mm:ss").format(now);} else if ("2".equals(foot.getCellText())) {cellText = new SimpleDateFormat("yyyy-MM-dd").format(now);} else {cellText = "";}} else {cellText = "";}//align = foot.getAlign();k++;}HSSFCell cell = row.createCell(j);setCellValue(cell, cellText, style3);}logger.info("i:{}, size:{}, length:{}", i, size, length);if (size < length) {hssfSheet.addMergedRegion(new CellRangeAddress(count - 1, count - 1, size - 1, length - 1));}}// 删除list，防止过多的创建list导致内存溢出mergeCountList.clear();}// 合并单元格hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, length - 1));for (int i = 0; i < length; i++) {// hssfSheet.setColumnWidth(i,(short)(middleList.get(i).getCellText().getBytes().length*256));hssfSheet.autoSizeColumn(i, true);}// 将文件输出到客户端浏览器response.setContentType("application/octet-stream");// 默认Excel名称response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(title + ".xls", "UTF-8"));try {response.flushBuffer();workbook.write(response.getOutputStream());} catch (Exception e) {e.printStackTrace();}} catch (Exception e) {throw new Exception("导出信息失败！失败原因:", e);}}/*** 写单元格数据* * @param cell* @param cellValue* @param style*/private void setCellValue(HSSFCell cell, String cellValue, HSSFCellStyle style, String align) {logger.info("align:{}", align);if ("left".equalsIgnoreCase(align)) {style.setAlignment(HorizontalAlignment.LEFT);} else if ("center".equalsIgnoreCase(align)) {style.setAlignment(HorizontalAlignment.CENTER);} else if ("right".equalsIgnoreCase(align)) {style.setAlignment(HorizontalAlignment.RIGHT);}cell.setCellValue(cellValue);cell.setCellStyle(style);}/*** 写单元格数据** @param cell* @param cellValue* @param style*/private void setCellValue(HSSFCell cell, String cellValue, HSSFCellStyle style) {cell.setCellValue(cellValue);cell.setCellStyle(style);}/*** 数据格式转换* * @param o* @return*/public String formatData(Object o) {String result = "";if (o == null) {return "/";}if (o instanceof Long) {// 处理日期类型的数据SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");result = dateFormat.format(new Date((long) o));} else if (o instanceof BigDecimal) {// 处理bigDecimal格式DecimalFormat decimalFormat = new DecimalFormat("#.00");result = decimalFormat.format((BigDecimal) o);} else if (o instanceof String) {result = StringUtils.isBlank((String) o) ? "/" : (String) o;} else {result = o.toString();}return result;}/*** bean转map：可以直接通过JSON来转，弃用* * @param source* @param <T>* @return* @throws IllegalAccessException*/public <T> Map<String, Object> beanToMap(T source) throws IllegalAccessException {Map<String, Object> result = new HashMap<>();Class<?> sourceClass = source.getClass();// 拿到所有的字段,不包括继承的字段Field[] sourceFiled = sourceClass.getDeclaredFields();for (Field field : sourceFiled) {field.setAccessible(true);result.put(field.getName(), field.get(source));}return result;}/*** 底部文本*/@Datapublic static class FootDto {private String cellType;private String cellText;private String align;private String width;private String col;}/*** 中部文本*/@Datapublic static class MiddleDto {private String cellType;private String align;private String width;private String cellText;private String key;private String _index;private String _rowKey;}/*** 头部文本*/@Datapublic static class HeaderDto {private String cellType;private String cellText;private String align;private String width;private String col;}/*** 全局文本设置*/@Datapublic static class GlobalDto {private String paddingTop;private String paddingLeft;private String paddingRight;private String paddingBottom;private String width;private String height;private String headerBorder;private String footerBorder;private String showBarCode;private String barCodeFormAttr;private String cellType;private String showFooter;private String groupTotal;}
