根据Excel模板填充优先推荐阿里的EasyExcel，github地址：https://github.com/alibaba/easyexcel。
由于目前老系统poi版本过低，高版本EasyExcel不兼容，因此自己实现了根据模板填充的工具类：如下：
package com.openplatform.system.util; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.util.CollectionUtils; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.Modifier; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.function.Consumer; /** * 由于poi版本过低，很多工具类不能用，因此自定义低版本poi的excel导出工具类 * @author 刘亚楼 * @date 2020/3/28 */ public class ExcelExportUtils { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportUtils.class); /** * 忽略序列化id */ private static final String INGORED_FIELD = "serialVersionUID"; private static final String REPLACED_REGIX = "\\{|\\}"; public static <T> void fillWithTemplate(List<T> dataList, String templatePath, String dest) throws Exception { fillWithTemplate(dataList, templatePath, new FileOutputStream(dest)); } /** * 根据Excel模板填充，忽略第一行，即头行 * @param dataList 数据列表 * @param templatePath 模板路径 * @param dest 目标路径 * @param <T> 可以是JavaBean也可以是Map<String,Object> * @throws Exception */ public static <T> void fillWithTemplate(List<T> dataList, String templatePath, OutputStream dest) throws Exception { File file = new File(templatePath); Workbook workbook = WorkbookFactory.create(file); Sheet sheet = workbook.getSheetAt(0); Row dataRow = sheet.getRow(1); // 导空数据 if (CollectionUtils.isEmpty(dataList)) { exportOnEmptyData(dataRow, workbook, dest); return; } export(dataList, dataRow, workbook, dest); } private static void exportOnEmptyData(Row dataRow, Workbook workbook, OutputStream dest) throws IOException { doLoop(cellIndex -> { Cell cell = dataRow.getCell(cellIndex); cell.setCellValue(""); }, dataRow.getPhysicalNumberOfCells()); workbook.write(dest); } private static <T> void export(List<T> dataList, Row dataRow, Workbook workbook, OutputStream dest) throws Exception { T firstRowData = dataList.get(0); boolean isMapInstance = Map.class.isAssignableFrom(firstRowData.getClass()); Map<Integer, String> templateVariableMap = parseTemplateVariable(dataRow); if (isMapInstance) { fillFirstDataRow(firstRowData, dataRow, templateVariableMap); dataList.remove(0); fillRemainingDataRow(dataList, dataRow, templateVariableMap); } else { Map<String, Method> methodMap = getAllGetterMethods(dataList.get(0).getClass()); fillFirstDataRow(firstRowData, dataRow, methodMap, templateVariableMap); dataList.remove(0); fillRemainingDataRow(dataList, dataRow, methodMap, templateVariableMap); } workbook.write(dest); } private static <T> void fillFirstDataRow(T t, Row dataRow, Map<String, Method> methodMap, Map<Integer, String> templaVariableMap) { doLoop(cellIndex -> { try { Method getterMethod = methodMap.get(templaVariableMap.get(cellIndex)); setCellValueByType(dataRow.getCell(cellIndex), getterMethod.invoke(t)); } catch (Exception e) { LOGGER.error(e.getMessage(), e); } }, dataRow.getPhysicalNumberOfCells()); } private static <T> void fillFirstDataRow(T t, Row dataRow, Map<Integer, String> templaVariableMap) { Map<String, Object> result = (Map<String, Object>) t; doLoop(cellIndex -> { setCellValueByType(dataRow.getCell(cellIndex), result.get(templaVariableMap.get(cellIndex))); }, dataRow.getPhysicalNumberOfCells()); } private static <T> void fillRemainingDataRow(List<T> dataList, Row dataRow, Map<Integer, String> templaVariableMap) { int currentRowNum = dataRow.getRowNum() + 1; for (T data : dataList) { Map<String, Object> result = (Map<String, Object>) data; Row currentRow = dataRow.getSheet().createRow(currentRowNum++); doLoop(cellIndex -> { Cell currentCell = currentRow.createCell(cellIndex); currentCell.setCellStyle(dataRow.getCell(cellIndex).getCellStyle()); setCellValueByType(currentCell, result.get(templaVariableMap.get(cellIndex))); }, dataRow.getPhysicalNumberOfCells()); } } private static <T> void fillRemainingDataRow(List<T> dataList, Row dataRow, Map<String, Method> methodMap, Map<Integer, String> templaVariableMap) { int currentRowNum = dataRow.getRowNum() + 1; for (T data : dataList) { Row currentRow = dataRow.getSheet().createRow(currentRowNum++); doLoop(cellIndex -> { try { Method getterMethod = methodMap.get(templaVariableMap.get(cellIndex)); Cell currentCell = currentRow.createCell(cellIndex); currentCell.setCellStyle(dataRow.getCell(cellIndex).getCellStyle()); setCellValueByType(currentCell, getterMethod.invoke(data)); } catch (Exception e) { LOGGER.error(e.getMessage(), e); } }, dataRow.getPhysicalNumberOfCells()); } } private static void setCellValueByType(Cell dataCell, Object obj) { if (obj instanceof Integer || obj instanceof Float || obj instanceof Long) { dataCell.setCellValue(String.valueOf(obj)); } else if (obj instanceof Double) { dataCell.setCellValue((Double) obj); } else if (obj instanceof Boolean) { dataCell.setCellValue((Boolean) obj); } else if (obj instanceof Date) { dataCell.setCellValue((Date) obj); } else if (obj instanceof Calendar) { dataCell.setCellValue((Calendar) obj); } else { dataCell.setCellValue(String.valueOf(obj)); } } /** * 反射获取运行时对象所有的get方法 * @param clazz * @return * @throws Exception */ private static Map<String, Method> getAllGetterMethods(Class<?> clazz) throws Exception { Map<String, Method> methodMap = new HashMap<>(); Field[] fields = clazz.getDeclaredFields(); String fieldName = null; String methodName = null; for (Field field : fields) { // 忽略类变量 if (Modifier.isStatic(field.getModifiers())) { continue; } fieldName = field.getName(); methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); methodMap.put(fieldName, clazz.getDeclaredMethod(methodName)); } return methodMap; } /** * 从占位符中获取模板变量，如：{variable}中的模板变量为variable * @param dataRow * @return */ private static Map<Integer, String> parseTemplateVariable(Row dataRow) { Map<Integer, String> templateVariableMap = new HashMap<>(); doLoop(cellIndex -> { Cell currentCell = dataRow.getCell(cellIndex); String cellValue = currentCell.getStringCellValue(); if (StringUtils.isNotBlank(cellValue) && cellValue.contains("{") && cellValue.contains("}")) { String fieldName = cellValue.replaceAll(REPLACED_REGIX, ""); templateVariableMap.put(cellIndex, fieldName); } }, dataRow.getPhysicalNumberOfCells()); return templateVariableMap; } private static void doLoop(Consumer<Integer> consumer, int totalColumns) { for (int cellIndex = 0; cellIndex < totalColumns; cellIndex++) { consumer.accept(cellIndex); } } }
拓展使用：
package com.iboxpay.jdk8.streamOp; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; /** * <b> 导出数据的帮助类，用于将数据导出至Excel表格中，可调整导出数据的样式，如：行(分为标题行和数据行)的背景颜色，字体颜色，字体高度， * 字体是否加粗， 是否条纹展示数据<b> * * @author 刘亚楼 * */ public class ExportHelper { // 列宽 private int columWidth; // 标题行背景颜色 private short headerBackground; // 标题行字体颜色 private short headerFontColor; // 标题行字体是否加粗 private boolean isHeaderFontBold; // 标题行字体高度 private short headerFontWeight; // 数据行背景颜色 private short dataRowBackground; // 数据行另一种背景色，用于显示条纹效果 private short dataRowBackground2; // 数据行字体颜色 private short dataRowFontColor; // 数据行字体是否加粗 private boolean isDataRowFontBold; // 数据行字体高度 private short dataRowFontWeight; // 是否给数据行加条纹 private boolean isStriped; public ExportHelper() { this.columWidth = 12; this.headerBackground = HSSFColor.WHITE.index; this.headerFontColor = HSSFColor.BLACK.index; this.isHeaderFontBold = false; this.headerFontWeight = 0; this.dataRowBackground = HSSFColor.WHITE.index; this.dataRowBackground2 = HSSFColor.GREY_25_PERCENT.index; this.dataRowFontColor = HSSFColor.BLACK.index; this.isDataRowFontBold = false; this.dataRowFontWeight = 0; this.isStriped = false; } /** * 设定列宽 * * @param columWidth */ public void setColumWidth(int columWidth) { this.columWidth = columWidth; } /** * 设置标题行字体颜色，可通过HSSFColor.colorClass.index设置 * * @param headerFontColor */ public void setHeaderFontColor(short headerFontColor) { this.headerFontColor = headerFontColor; } /** * 设置数据行字体颜色，可通过HSSFColor.colorClass.index设置 * * @param dataRowFontColor */ public void setDataRowFontColor(short dataRowFontColor) { this.dataRowFontColor = dataRowFontColor; } /** * 设置标题行的背景颜色，可通过HSSFColor.colorClass.index设置 * * @param headerBackground */ public void setHeaderBackground(short headerBackground) { this.headerBackground = headerBackground; } /** * 设置数据行的背景颜色，可通过HSSFColor.colorClass.index设置 * * @param dataRowBackground */ public void setDataRowBackground(short dataRowBackground) { this.dataRowBackground = dataRowBackground; } /** * 设置标题行字体粗度，默认为false * * @param isHeaderFontBold */ public void setHeaderFontBold(boolean isHeaderFontBold) { this.isHeaderFontBold = isHeaderFontBold; } /** * 设置数据行字体粗度，默认为false * * @param isDataRowFontBold */ public void setDataRowFontBold(boolean isDataRowFontBold) { this.isDataRowFontBold = isDataRowFontBold; } /** * 设置标题行字体高度 * * @param headerFontWeight */ public void setHeaderFontWeight(short headerFontWeight) { this.headerFontWeight = headerFontWeight; } /** * 设置数据行字体高度 * * @param dataRowFontWeight */ public void setDataRowFontWeight(short dataRowFontWeight) { this.dataRowFontWeight = dataRowFontWeight; } /** * 设置数据行是否条纹展示 * * @param isStriped */ public void setStriped(boolean isStriped) { this.isStriped = isStriped; } /** * 数据行另一种背景颜色，用于显示条纹效果 * * @param dataRowBackground2 */ public void setDataRowBackground2(short dataRowBackground2) { this.dataRowBackground2 = dataRowBackground2; } /** * 将list中的对象转换为Workbook，并且将Workbook写到输出流中 * * @param sheetName * 表单的名字 * @param headers * 标题 * @param list * 包含对象的list集合 * @param os * 输出流 * @throws NoSuchMethodException * @throws SecurityException * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws IOException */ public <T> void exportToExcel(String sheetName, String[] headers, List<T> list, OutputStream os) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IOException { // 声明一个工作簿，HSSFWorkbook为2007以上的版本 HSSFWorkbook workbook = new HSSFWorkbook(); // 声明一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 设置默认的列宽 sheet.setDefaultColumnWidth(columWidth); // 创建标题行样式 HSSFCellStyle headerStyle = createRowStyle(workbook, headerBackground, headerFontColor, headerFontWeight, isHeaderFontBold); // 创建数据样式 HSSFCellStyle rowStyle = createRowStyle(workbook, dataRowBackground, dataRowFontColor, dataRowFontWeight, isDataRowFontBold); HSSFCellStyle rowStyle2 = null; if (isStriped) { // 有条纹展示，创建另一种数据行样式 rowStyle2 = createRowStyle(workbook, dataRowBackground2, dataRowFontColor, dataRowFontWeight, isDataRowFontBold); } // 为表格填充内容 fillContentsForSheet(headers, list, sheet, headerStyle, rowStyle, rowStyle2); // 将表格中的内容写入输出流中 workbook.write(os); os.flush(); } /** * 创建行样式 * * @param workbook * 工作簿对象 * @param background * 行背景颜色 * @param fontColor * 字体颜色 * @param fontWeight * 字体高度 * @param isFontBold * 字体是否加粗 * @return */ private HSSFCellStyle createRowStyle(HSSFWorkbook workbook, short background, short fontColor, short fontWeight, boolean isFontBold) { // 创建样式 HSSFCellStyle rowStyle = workbook.createCellStyle(); // 设置填充颜色 rowStyle.setFillForegroundColor(background); // 设置填充样式 rowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置边框 rowStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); rowStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); rowStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); rowStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置水平对齐方式 rowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置垂直对齐方式 rowStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建字体 HSSFFont rowFont = workbook.createFont(); rowFont.setColor(fontColor); if (fontWeight != 0) { rowFont.setFontHeightInPoints(fontWeight); } // 默认设为normal rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); if (isFontBold) { rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } // 把字体应用到当前的样式 rowStyle.setFont(rowFont); return rowStyle; } // 为表格填充内容 private <T> void fillContentsForSheet(String[] headers, List<T> list, HSSFSheet sheet, HSSFCellStyle headerStyle, HSSFCellStyle rowStyle, HSSFCellStyle rowStyle2) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException { // 是否存在标题行 boolean isHeaderRowExisted = false; if (headers != null && headers.length > 0) { isHeaderRowExisted = true; // 创建标题行 HSSFRow headerRow = sheet.createRow(0); for (int i = 0; i < headers.length; i++) { HSSFCell headerCell = headerRow.createCell(i); headerCell.setCellStyle(headerStyle); headerCell.setCellValue(headers[i]); } } if (list != null && list.size() > 0) { // 获得list中对象的运行时类 Class<T> clazz = (Class<T>) list.get(0).getClass(); List<Method> methodList = getAllGetMethods(clazz); int rowNum = 0; if (isHeaderRowExisted) { rowNum = 1;// 如果有标题行行号则从1开始，没有从0开始 } HSSFCellStyle style = null; for (int row = rowNum, index = 0; index < list.size(); row++, index++) { T t = list.get(index); // 如果有条纹展示，奇偶行样式各不同 if (rowStyle2 != null && index % 2 == 0) { style = rowStyle2; } else { style = rowStyle; } // 创建数据行 HSSFRow dataRow = sheet.createRow(row); // 一个getXXX方法代表一个字段值，根据字段值生成列的个数 for (int cell = 0; cell < methodList.size(); cell++) { HSSFCell dataCell = dataRow.createCell(cell); dataCell.setCellStyle(style); // 通过反射调用对应的getXXX方法 Object obj = methodList.get(cell).invoke(t); // 判断数据类型，设置单元格的值 if (obj instanceof Integer) { Integer value = (Integer) obj; dataCell.setCellValue(String.valueOf(value)); } else if (obj instanceof Float) { Float value = (Float) obj; double doubleValue = Double.parseDouble(String.valueOf(value)); dataCell.setCellValue(doubleValue); } else if (obj instanceof Long) { Long value = (Long) obj; dataCell.setCellValue(String.valueOf(value)); } else if (obj instanceof Double) { Double value = (Double) obj; dataCell.setCellValue(value); } else if (obj instanceof Boolean) { Boolean value = (Boolean) obj; dataCell.setCellValue(value); } else if (obj instanceof Date) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); dataCell.setCellValue(sdf.format(obj)); } else if (obj instanceof Calendar) { Calendar c = (Calendar) obj; Date date = c.getTime(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); dataCell.setCellValue(sdf.format(date)); } else { String value = (String) obj; dataCell.setCellValue(value); } } } } } // 获取类中每个成员变量对应的get方法，存入集合中 private List<Method> getAllGetMethods(Class<?> clazz) throws NoSuchMethodException, SecurityException { List<Method> methodList = new ArrayList<Method>(); Field[] fields = clazz.getDeclaredFields(); for (Field field : fields) { // 如果名字为serialVersionUID，代表字段值为序列化编号，应该跳过 if (field.getName().equals("serialVersionUID")) { continue; } String fieldName = field.getName(); String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); Method getMethod = clazz.getDeclaredMethod(methodName); methodList.add(getMethod); } return methodList; } }
