填充数据合并单元格并导出excel代码实现

冷不防 2023-01-12 11:48 178阅读 0赞

以下代码是动态填充数据，这些数据是从数据库取得的批量的，并合并单元格，最终导出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文件中的sheet
    			HSSFSheet 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的行数列数有限制short
    			HSSFRow 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);
    				// 这里可以判断是否为null
    				size = 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 (mergeCount.compareTo(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);
    				// 这里可以判断是否为null
    				size = fList.size();
    				// 记录写入列的位置y
    				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);
    								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 (mergeCount.compareTo(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;
    	}
    
    	/**
    	 * 底部文本
    	 */
    	@Data
    	public static class FootDto {
    		private String cellType;
    		private String cellText;
    		private String align;
    		private String width;
    		private String col;
    	}
    
    	/**
    	 * 中部文本
    	 */
    	@Data
    	public static class MiddleDto {
    		private String cellType;
    		private String align;
    		private String width;
    		private String cellText;
    		private String key;
    		private String _index;
    		private String _rowKey;
    	}
    
    	/**
    	 * 头部文本
    	 */
    	@Data
    	public static class HeaderDto {
    		private String cellType;
    		private String cellText;
    		private String align;
    		private String width;
    		private String col;
    
    	}
    
    	/**
    	 * 全局文本设置
    	 */
    	@Data
    	public 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;
    	}