根据指定Excel模板填充导出数据

痛定思痛。 2022-06-01 00:52 244阅读 0赞

根据Excel模板填充优先推荐阿里的EasyExcel，github地址：[https://github.com/alibaba/easyexcel][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;
      }
    }

[https_github.com_alibaba_easyexcel]: https://github.com/alibaba/easyexcel