JavaPOI报表反射+自定义注解优化

一.应用背景

Java目前用来处理报表功能一般选用的都是POI,项目中需要报表的
地方越来越多之后,往往会遇到这样一个问题,每次涉及到报表时,写的代码都是一模一样的,无非是封装的数据类型发生了变化,虽然大家都知道ctrl+c,ctrl+v,但这种扩展性极差的代码毫无疑问是我们必须要去解决的问题,于是慢慢的,大家想到了反射,通过反射去获取你要填入表格的值,可以说是很大程度上解决了代码的冗余问题,也极高地增强了代码的可扩展性.

但是,这样还是会面对一些问题,对于表格的标题行还是需要人为的去手动设置,报表每变动一次,就需要去改一下代码,这当然不是我们愿意看到的,久而久之,有些聪明但是懒惰的程序员又想出了一个解决之道,通过注解,我标注属性对应的是哪个标题,在反射的时候读取注解中的标题,在构建第一行时,读取属性的标题,填充进去,不管你怎么改怎么动,我只需要对我的entity类的属性以及注解内容做修改就行了,完美的解决了扩展性的问题.

下面.我就从代码量,原理上做一些解答[PS:下面的代码除了工具类之外,都是不完整的,想完全复制了去测试的大佬们还是算了吧]

二.代码量对比

1.没有优化的controller核心代码

		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		String filename = "入库报表";
		List<TbWmsreportPurchase> list = null;
		list = (List<TbWmsreportPurchase>) JSONArray.parseArray(data,
				TbWmsreportPurchase.class);
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = workbook.createSheet("入库报表");
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(20);
		
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		// // 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setFontName("仿宋_GB2312");
		font.setBold(true);
		font.setFontHeightInPoints((short) 16);
		style.setFont(font);
		
		HSSFRow row0 = sheet.createRow(0);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
		HSSFCell cell_0_0 = row0.createCell(0);
		cell_0_0.setCellValue("入库报表");
		cell_0_0.setCellStyle(style);
		
		HSSFRow row1 = sheet.createRow(1);
		HSSFCell cell_1_0 = row1.createCell(0);
		cell_1_0.setCellValue("时间");
		cell_1_0.setCellStyle(style);
		
		HSSFCell cell_1_1 = row1.createCell(1);
		cell_1_1.setCellValue("员工姓名");
		cell_1_1.setCellStyle(style);
		
		HSSFCell cell_1_2 = row1.createCell(2);
		cell_1_2.setCellValue("签收包裹量");
		cell_1_2.setCellStyle(style);
		
		HSSFCell cell_1_3 = row1.createCell(3);
		cell_1_3.setCellValue("贴标商品数量");
		cell_1_3.setCellStyle(style);
		
		HSSFCell cell_1_4 = row1.createCell(4);
		cell_1_4.setCellValue("贴标商品种类");
		cell_1_4.setCellStyle(style);
		
		HSSFCell cell_1_5 = row1.createCell(5);
		cell_1_5.setCellValue("上架商品数量");
		cell_1_5.setCellStyle(style);
		
		HSSFCell cell_1_6 = row1.createCell(6);
		cell_1_6.setCellValue("上架商品种类");
		cell_1_6.setCellStyle(style);
		
		if(list != null && list.size() > 0) {
			int k = 2;
			for(TbWmsreportPurchase pp:list) {
				HSSFRow row = sheet.createRow(k);
				HSSFCell cell0 = row.createCell(0);
				cell0.setCellValue(pp.getCountTimeStr());
				cell0.setCellStyle(style);
				
				HSSFCell cell1 = row.createCell(1);
				cell1.setCellValue(pp.getEmployeeName());
				cell1.setCellStyle(style);
				
				HSSFCell cell2 = row.createCell(2);
				cell2.setCellValue(pp.getSingleCount());
				cell2.setCellStyle(style);
				
				HSSFCell cell3 = row.createCell(3);
				cell3.setCellValue(pp.getLableQuantity());
				cell3.setCellStyle(style);
				
				HSSFCell cell4 = row.createCell(4);
				cell4.setCellValue(pp.getLableCount());
				cell4.setCellStyle(style);
				
				HSSFCell cell5 = row.createCell(5);
				cell5.setCellValue(pp.getPutawayQuantity());
				cell5.setCellStyle(style);
				
				HSSFCell cell6 = row.createCell(6);
				cell6.setCellValue(pp.getPutawayCount());
				cell6.setCellStyle(style);
				k++;
			}
		}
		workbook.write(outStream);
		filename = "入库报表" + sdf.format(new Date()) + ".xls";
		byte[] bytes = outStream.toByteArray();
		response.setContentType("application/vnd.ms-excel");
		response.setContentLength(bytes.length);
		response.setHeader("Content-Disposition",
				"attachment;filename=" + new String(filename.getBytes("GB2312"), "8859_1"));
		response.getOutputStream().write(bytes);

2.反射优化后的controller和工具类

controller核心代码

	HSSFWorkbook workbook = null;
	ByteArrayOutputStream outStream =  new ByteArrayOutputStream();
	String title = "出库日志报表";
	int code = 0;
		RetCode rt = getTaskHistoryList(request, session);
		if(rt.getAck() == 0) {
			@SuppressWarnings("unchecked")
			List<TaskOrderAndItemHistory> list = (List<TaskOrderAndItemHistory>) rt.getData();
			if(list != null && list.size() > 0) {
				LinkedHashMap<String, String> headers = new LinkedHashMap<String, String>();
				headers.put("platformOrderId", "订单编号");
				headers.put("groupid", "批次");
				headers.put("pickBasket", "配货篮子编号");
				headers.put("basketNum", "分解篮子编号");
				headers.put("orderWeight", "称重重量");
				headers.put("stockWarehouse", "仓库");
				headers.put("itemCount", "订单下商品种类");
				headers.put("itemQuantity", "订单下商品的累计总个数");
				headers.put("orderTime", "订单时间");
				headers.put("pickOper", "配货人");
				headers.put("pickTime", "配货时间");
				headers.put("resolveOper", "分解员");
				headers.put("resolveTime", "分解时间");
				headers.put("shipOper", "发货员");
				headers.put("shipTime", "发货时间");
				headers.put("trackNumber", "物流单号");
				headers.put("myLogisticsChannel", "物流渠道");
				headers.put("myLogistics","物流公司");
				RetCode rts = excelUtil.exportExcel(title, headers, list, outStream);
				if(rts.getAck()==0){
					title = "taskHistoryList.xls";
					byte[] bytes = outStream.toByteArray();
					response.setContentType("application/vnd.ms-excel");
					response.setContentLength(bytes.length);
					response.setHeader("Content-Disposition", "attachment;filename=" + title);
					response.getOutputStream().write(bytes);
					code = 1;
				}else{
					workbook = new HSSFWorkbook();
					HSSFSheet sheet = workbook.createSheet(title);
					HSSFRow titleRow = sheet.createRow(0);
					titleRow.createCell(0).setCellValue("无需要导出的记录！");
				}
				headers.clear();headers = null;
			}else {
				workbook = new HSSFWorkbook();
				HSSFSheet sheet = workbook.createSheet(title);
				HSSFRow titleRow = sheet.createRow(0);
				titleRow.createCell(0).setCellValue("没有订单数据!");
			}
		}else {
			workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet(title);
			HSSFRow titleRow = sheet.createRow(0);
			titleRow.createCell(0).setCellValue(rt.getMsg());
		}

工具类

public <T> RetCode exportExcel(String title,
			LinkedHashMap<String, String> headers, Collection<T> dataset,
			ByteArrayOutputStream outStream) {
		RetCode rt = new RetCode();
		if (headers == null || dataset == null) {
			rt.setAck(1000);
			rt.setMsg("导出的数据不能为空！");
			return rt;
		}
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(20);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		// // 生成一个字体
		HSSFFont font = workbook.createFont();
//		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);;//HSSFFont.BOLDWEIGHT_BOLD
		// // 把字体应用到当前的样式
		style.setFont(font);
		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		int ii = 0;
		for (Map.Entry<String, String> entry : headers.entrySet()) {
			HSSFCell cell = row.createCell(ii);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(entry.getValue());
			cell.setCellValue(text);
			ii++;
		}
		// 生成一个样式
		style = workbook.createCellStyle();
		// 设置这些样式
		style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		// // 生成一个字体
		font = workbook.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);
		/** 数字小于0的样式 */
		HSSFCellStyle style2 = workbook.createCellStyle();
		// 设置这些样式
		style2.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		// // 生成一个字体
		HSSFFont font2 = workbook.createFont();
		font2.setFontHeightInPoints((short) 14);
		font2.setBold(true);
		font2.setColor(HSSFColor.RED.index);
		style2.setFont(font2);
		/** 数字大于0的样式 */
		HSSFCellStyle style3 = workbook.createCellStyle();
		// 设置这些样式
		style3.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		// // 生成一个字体
		HSSFFont font3 = workbook.createFont();
		font3.setFontHeightInPoints((short) 14);
		font3.setBold(true);
		font3.setColor(HSSFColor.GREEN.index);
		style3.setFont(font3);
		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			Field[] fields = t.getClass().getDeclaredFields();
			int j = 0;
			for (Map.Entry<String, String> entry : headers.entrySet()) {
				for (int i = 0; i < fields.length; i++) {
					Field field = fields[i];
					String fieldName = field.getName();
					if (!Sys.isCheckNull(fieldName).equals(entry.getKey())) {
						continue;
					}
					HSSFCell cell = row.createCell(j);
					cell.setCellStyle(style);
					String getMethodName = "get"
							+ fieldName.substring(0, 1).toUpperCase()
							+ fieldName.substring(1);
					try {
						@SuppressWarnings("rawtypes")
						Class tCls = t.getClass();
						@SuppressWarnings("unchecked")
						Method getMethod = tCls.getMethod(getMethodName,
								new Class[] {});
						Object value = getMethod.invoke(t, new Object[] {});
						Object valueType = getMethod.getReturnType();
						// 判断值的类型后进行强制类型转换
						String textValueType = valueType == null ? ""
								: valueType.toString();
						String textValue = value == null ? "" : value
								.toString();
						if (textValueType.indexOf(".Integer") > 0
								|| textValueType.indexOf(".int") > 0
								|| textValueType.indexOf("Integer") >= 0
								|| textValueType.indexOf("int") >= 0) {
							if (Sys.isCheckInt(textValue) < 0) {
								cell.setCellStyle(style2);
							} else {
								cell.setCellStyle(style3);
							}
							cell.setCellValue(Sys.isCheckInt(textValue));
						} else if (textValueType.indexOf(".Long") > 0
								|| textValueType.indexOf(".long") > 0
								|| textValueType.indexOf("Long") >= 0
								|| textValueType.indexOf("long") >= 0) {
							if (Sys.isCheckLong(textValue) < 0) {
								cell.setCellStyle(style2);
							} else {
								cell.setCellStyle(style3);
							}
							cell.setCellValue(Sys.isCheckLong(textValue));
						} else if (textValueType.indexOf(".Double") > 0
								|| textValueType.indexOf(".double") > 0
								|| textValueType.indexOf("Double") >= 0
								|| textValueType.indexOf("double") >= 0) {
							if (Sys.isCheckDouble(textValue) < 0) {
								cell.setCellStyle(style2);
							} else {
								cell.setCellStyle(style3);
							}
							cell.setCellValue(Sys.isCheckDouble(textValue));
						} else if (textValueType.indexOf(".Date") > 0) {
							Date dateval = (Date) getMethod.invoke(t, new Object[] {});
							if(dateval != null) {
								cell.setCellValue(sim.format(dateval));
							}else {
								cell.setCellValue("");
							}
						} else {
							cell.setCellValue(textValue);
						}
						textValueType = null;
						textValue = null;
					} catch (NoSuchMethodException e) {
						e.printStackTrace();
						rt.setAck(1000);
						rt.setMsg(e.getMessage());
					} catch (IllegalAccessException e) {
						e.printStackTrace();
						rt.setAck(1000);
						rt.setMsg(e.getMessage());
					} catch (InvocationTargetException e) {
						e.printStackTrace();
						rt.setAck(1000);
						rt.setMsg(e.getMessage());
					}
				}
				j++;
			}
		}
		try {
			workbook.write(outStream);
			rt.setAck(0);
			rt.setMsg("导出成功!");
		} catch (IOException e) {
			System.out.println("IOException错误:" + e.getMessage());
			rt.setAck(1000);
			rt.setMsg(e.getMessage());
		} catch (Exception e) {
			System.out.println("Exception错误:" + e.getMessage());
			rt.setAck(1000);
			rt.setMsg(e.getMessage());
		}
		return rt;
	}


	public String getCellValue(Cell cell) {
		String cellValue = "";
		if (cell == null) {
			return cellValue;
		}
		// 把数字当成String来读，避免出现1读成1.0的情况
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}
		// 判断数据的类型
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC: // 数字
			cellValue = String.valueOf(cell.getNumericCellValue());
			break;
		case Cell.CELL_TYPE_STRING: // 字符串
			cellValue = String.valueOf(cell.getStringCellValue());
			break;
		case Cell.CELL_TYPE_BOOLEAN: // Boolean
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA: // 公式
			cellValue = String.valueOf(cell.getCellFormula());
			break;
		case Cell.CELL_TYPE_BLANK: // 空值
			cellValue = "";
			break;
		case Cell.CELL_TYPE_ERROR: // 故障
			cellValue = "非法字符";
			break;
		default:
			cellValue = "未知类型";
			break;
		}
		return cellValue;
	}

3.反射+自定义注解优化后的controller和工具类

controller核心代码

    HSSFWorkbook workbook = null;
    ByteArrayOutputStream outputStream =  new ByteArrayOutputStream();
    String title = "出库订单商品报表";
    RetCode rc = getTaskHistoryList(request, session);
    String fileName = "taskAndItemHistoryList.xls";
    if(rc.getAck()==0 && !StringUtils.isEmpty(rc.getData())) {
    @SuppressWarnings("unchecked")
    List<TaskOrderAndItemHistory> list = (List<TaskOrderAndItemHistory>)rc.getData();
	//导出的订单数据集合
	List<TaskOrderAndItemHistoryExcel> orderList = new ArrayList<>();
	for(TaskOrderAndItemHistory tat : list) {
    	TaskOrderAndItemHistoryExcel tate = new TaskOrderAndItemHistoryExcel();
    	//商品详情
    	List<TbTaskItemHistoryExcel> itemList = new ArrayList<>();
		if(tat.getItemList()!=null && tat.getItemList().size()>0) {
    			for(TbTaskItemHistory ttih : tat.getItemList()) {
    				TbTaskItemHistoryExcel t = new TbTaskItemHistoryExcel();
    				BeanCopier copier =BeanCopier.create(ttih.getClass(), t.getClass(), false);
    				copier.copy(ttih, t, null);
    				itemList.add(t);
    			}
    		}
    		if(!itemList.isEmpty()) {
    			tate.setItems(itemList);
    		}
    		BeanCopier copier =BeanCopier.create(tat.getClass(), tate.getClass(), false);
    		copier.copy(tat, tate, null);
    		orderList.add(tate);
    	}
    	createExcel.exportExcel(orderList, TaskOrderAndItemHistoryExcel.class,null, outputStream, title,0);
    	outputStream.flush();
    	byte[] bytes = outputStream.toByteArray();
    	response.setContentType("application/vnd.ms-excel");
    	response.setContentLength(bytes.length);
    	response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    	response.getOutputStream().write(bytes);
    	return ;

工具类

@Component
public class CreateExcel2 {
	/**
	 * 反射递归报表(嵌套List<T>)
	 * @param dataset 反射的数据集
	 * @param clazz 反射的数据类型
	 * @param workbook 工作簿
	 * @param outStream 字节数组输出流
	 * @param sheetName 表名
	 * @param currentRow 当前行
	 * @throws IOException
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	@SuppressWarnings({ "deprecation" })
	public <T> void exportExcel(List<T> dataset,Class<T> clazz,HSSFWorkbook workbook,
			ByteArrayOutputStream outStream,String sheetName,int currentRow) throws IOException, IllegalArgumentException, IllegalAccessException {
		//每次执行时重置流,否则递归写入的文件内容会被覆盖掉
		outStream.reset();
		if(workbook==null) {
			workbook = new HSSFWorkbook();
		}
		if(dataset == null || dataset.size() <= 0) {
			HSSFSheet sheet = workbook.createSheet(sheetName);
			HSSFRow titleRow = sheet.createRow(currentRow);
			titleRow.createCell(0).setCellValue("没有要导出的记录！");
			workbook.write(outStream);
			return ;
		}
		HSSFSheet sheet = null;
		if(workbook.getSheet(sheetName)==null) {
			sheet = workbook.createSheet(sheetName);
		}else {
			sheet = workbook.getSheet(sheetName);
		}
		
		sheet.setDefaultColumnWidth(20);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		// // 生成一个字体
		HSSFFont font = workbook.createFont();
//		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);;//HSSFFont.BOLDWEIGHT_BOLD
		// // 把字体应用到当前的样式
		style.setFont(font);
		
		// 生成一个样式
		HSSFCellStyle style1 = workbook.createCellStyle();
		// 设置这些样式
		style1.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		// // 生成一个字体
		HSSFFont font1 = workbook.createFont();
		font1.setFontHeightInPoints((short) 12);
		font1.setBold(true);
		style1.setFont(font1);
		style1.setAlignment(HorizontalAlignment.CENTER);
		//垂直居中
		style1.setVerticalAlignment(VerticalAlignment.CENTER);
		/** 数字小于0的样式 */
		HSSFCellStyle style2 = workbook.createCellStyle();
		// 设置这些样式
		style2.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		//垂直居中
		style2.setVerticalAlignment(VerticalAlignment.CENTER);
		// // 生成一个字体
		HSSFFont font2 = workbook.createFont();
		font2.setFontHeightInPoints((short) 14);
		font2.setBold(true);
		font2.setColor(HSSFColor.RED.index);
		style2.setFont(font2);
		/** 数字大于0的样式 */
		HSSFCellStyle style3 = workbook.createCellStyle();
		// 设置这些样式
		//垂直居中
		style3.setVerticalAlignment(VerticalAlignment.CENTER);
		//水平居中
		style3.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		// 生成一个字体
		HSSFFont font3 = workbook.createFont();
		font3.setFontHeightInPoints((short) 14);
		font3.setBold(true);
		font3.setColor(HSSFColor.GREEN.index);
		style3.setFont(font3);
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 
		SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Field[] fields  = clazz.getDeclaredFields();
		HSSFRow row = null;
		// 只产生表格前两行标题
		//第一次写入
		if(currentRow==0) {
			row = sheet.createRow(0);
			for(Field f:fields) {
				f.setAccessible(true);
				ExcelFilter meta = f.getAnnotation(ExcelFilter.class);
				HSSFCell cell = row.createCell(meta.sort());
				cell.setCellStyle(style);
				HSSFRichTextString text = new HSSFRichTextString(meta.titleName());
				cell.setCellValue(text);
			}
			//空出第二行用于写入商品标题
			currentRow=2;
		}else {
			//第二次递归调用,写入商品标题,会多次写,但是可以覆盖
			row = sheet.createRow(1);
			for(Field f:fields) {
				f.setAccessible(true);
				ExcelFilter meta = f.getAnnotation(ExcelFilter.class);
				HSSFCell cell = row.createCell(meta.sort());
				cell.setCellStyle(style);
				HSSFRichTextString text = new HSSFRichTextString(meta.titleName());
				cell.setCellValue(text);
			}
		}
		int k=0;//list索引
		int rowNo = currentRow;//定位到当前行的下一行
		for(T o:dataset) {
			row = sheet.createRow(rowNo);
			for(Field f:fields) {
				f.setAccessible(true);
				ExcelFilter meta = f.getAnnotation(ExcelFilter.class);
				HSSFCell cell = row.createCell(meta.sort());
				cell.setCellStyle(style);
				if(meta.isImage()) {//图片
					if(f.get(o) != null) {
						try {
							System.out.println("正在写入第"+rowNo+"行数据");
							row.setHeight((short) (80*20));
							BufferedImage input = ImageIO.read(new URL(f.get(o).toString()));
							ByteArrayOutputStream baos = new ByteArrayOutputStream();
							ImageIO.write(input, "jpg", baos);
							//anchor主要用于设置图片的属性
				            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 600, 240,(short) meta.sort(), rowNo, (short) meta.sort(), rowNo);
				            patriarch.createPicture(anchor, workbook.addPicture(baos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
						}catch (Exception e) {
							cell.setCellValue("图片生成失败");
							row.setHeight((short) (20*20));
						}
						
					}else {
						cell.setCellValue("无图片");
						row.setHeight((short) (20*20));
					}
					
					continue;
				}
				if(meta.dataType().equals(CustomDataType.INTEGER)) {//INTEGER类型
					if(f.get(o) != null) {
						if(f.getInt(dataset.get(k)) >= 0) {
							cell.setCellStyle(style3);
							cell.setCellValue(f.getInt(o));
						}else {
							cell.setCellStyle(style2);
							cell.setCellValue(f.getInt(o));
						}
					}else {
						cell.setCellStyle(style3);
						cell.setCellValue(f.getInt(o));
					}
				}
				if(meta.dataType().equals(CustomDataType.LONG)) {//INTEGER类型
					if(f.get(o) != null) {
						if(f.getLong(dataset.get(k)) >= 0) {
							cell.setCellStyle(style3);
							cell.setCellValue(f.getLong(o));
						}else {
							cell.setCellStyle(style2);
							cell.setCellValue(f.getLong(o));
						}
					}else {
						cell.setCellStyle(style3);
						cell.setCellValue(f.getLong(o));
					}
				}
				if(meta.dataType().equals(CustomDataType.DOUBLE)) {//DOUBLE类型
					if(f.get(o) != null) {
						if(f.getDouble(dataset.get(k)) >= 0) {
							cell.setCellStyle(style3);
							cell.setCellValue(f.getDouble(o));
						}else {
							cell.setCellStyle(style2);
							cell.setCellValue(f.getDouble(o));
						}
					}else {
						cell.setCellStyle(style3);
						cell.setCellValue(f.getDouble(o));
					}
				}
				if(meta.dataType().equals(CustomDataType.STRING)) {//string类型
					cell.setCellStyle(style1);
					if(f.get(o) != null) {
						cell.setCellValue(f.get(o).toString());
					}else {
						cell.setCellValue("");
					}
				}
				if(meta.dataType().equals(CustomDataType.DATE)) {//DATE类型
					cell.setCellStyle(style1);
					if(f.get(o) != null) {
						cell.setCellValue(sim.format(f.get(o)));
					}else {
						cell.setCellValue("");
					}
				}
				if(meta.dataType().equals(CustomDataType.LIST)) {//List<T>类型
					rowNo++;
					if(f.get(o)!=null) {
						rowNo = writeListData(rowNo,workbook,outStream,f,o,sheetName);
					}
				}
			}
			k++;
			rowNo++;
		}
		workbook.write(outStream);
	}
	
	@SuppressWarnings("unchecked")
	public <T> int writeListData(int rowNo,HSSFWorkbook Wworkbook,ByteArrayOutputStream outStream,Field f,T o,String sheetName) {
		Class<T> genericClazz= null;
		//获取list集合递归写数据
		Type genericType = f.getGenericType();
		if(genericType!=null && (genericType instanceof ParameterizedType)) {
			//带泛型的list集合
			ParameterizedType pt = (ParameterizedType) genericType;
			//获取到泛型的class
			genericClazz = (Class<T>)pt.getActualTypeArguments()[0];
		}
		List<T> l = null;
		try {
			l = (List<T>)f.get(o);
			exportExcel(l,genericClazz,Wworkbook,outStream,sheetName,rowNo);
//			return rowNo+l.size();
			return rowNo+l.size()-1;
		} catch (Exception e) {
			return rowNo;
		}
	}
}

自定义注解及其应用

//注解类
@Target({ElementType.METHOD,ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelFilter {
	String titleName() default "";//标题名
	int sort() default 0;//顺序
	boolean isImage() default false;//是否是图片
	CustomDataType dataType() default CustomDataType.STRING;//属性的类型
}
//枚举类
public enum CustomDataType {
	DOUBLE("double"),INTEGER("int"),STRING("string"),DATE("date"),LONG("long"),LIST("list");
	// 成员变量  
    private String index;
	// 构造方法  
    private CustomDataType(String index) {  
        this.index = index;  
    }  
	public String getIndex() {
		return index;
	}
	public void setIndex(String index) {
		this.index = index;
	}  
}
//数据集合类应用
public class TaskOrderAndItemHistoryExcel {
	@ExcelFilter(titleName="订单编号",sort=0,dataType=CustomDataType.STRING,isImage=false)
	private String platformOrderId;
	
	@ExcelFilter(titleName="仓库",sort=5,dataType=CustomDataType.STRING,isImage=false)
	private String stockWarehouse;
	
	@ExcelFilter(titleName="订单时间",sort=8,dataType=CustomDataType.DATE,isImage=false)
	private Date orderTime;
	
	@ExcelFilter(titleName="物流公司",sort=17,dataType=CustomDataType.STRING,isImage=false)
	private String myLogistics;
	
	@ExcelFilter(titleName="",sort=18,dataType=CustomDataType.LIST,isImage=false)
	private List<TbTaskItemHistoryExcel> items;//商品集合
	
	//还有很多属性,这里出于商业原因不展示所有......
}

三.原理

原理啥的其实也没啥高深的东西,就是最基础的反射嘛;通过反射我们可以获取目标对象属性的注解,注解包括sort(这个属性对应的单元格的第几列),dataType(属性的数据类型),之所以对属性的数据类型写一个枚举,是为了防止因为出现int和Integer这种类型,因为不知道程序员会用哪个,所以干脆用枚举把他给定死;titleName(属性对应的标题行);isIamge(对于需要写入的图片需要做特殊的处理,这里单独列出来作为boolean判断).

这里比较特殊的是,我们的数据类型里多了一个List类型,考虑到我们反射时,嵌套List做的操作其实都是一样的,逐个字段去反射,然后循环直达结束,所以,很容易就会想到通过递归算法来进行反射.

递归算法反射时,需要注意的几点问题:

[1]为了使我们嵌套List中的内容和原List中的内容写入同一张表中,并且是连续写入,我们需要传入的参数就有workbook,sheetName,保证递归调用时操作的是同一个工作簿里的同一张表

[2]没次执行递归调用结束后,会调用write将outputStream中的内容写入表格,再次递归写入时如果不重置流,会导致之前写入的内容被覆盖,所以我们这里在进入方法时便会重置输出流 outStream.reset()

想分享的到这里就结束了,其他方面的问题,大部分我都在代码中注释说明了,所以认真看完的话应该是完全没有问题了的.

也许这种方法已经有很多大佬用过了,第一次发博客,我只能说小弟不才,我只是把自己接触到的这些比较好的东西拉出来分享总结一下,并没有打算说是要教大家怎样怎样,暂时还没那个本领,也欢迎大佬们一起在评论区讨论更多更高明的解决方案,秉着学习交流的目的,一起进步,一起成长,谢谢!!