配置化的excel生成解决方案

主要思想：

依赖于apache POI 和 springEL

通过自定义excel 配置文件，定义excel 中每一列的数据来源。 用springEL从数据模型中解析出数据值。

优点是：

只要定义好 excel 配置文件， 然后提供数据源 就可以自动生成excel文件。无需每张导出都写一个特殊的导出。

关键支撑工具类：

public class ExcelReportConfig {
    private String name;
    private String templateName;
    private boolean needGenerateHeader;
    private int startRow;
    private List<ExcelConfig> details;
}

public class ConfigurablePoiExcelGenerator {

    private String defaultTitle = "Sheet1";

    private final ExpressionParser parser = new SpelExpressionParser();

    private Logger logger = LoggerFactory.getLogger(ConfigurablePoiExcelGenerator.class);

    private Map<String, ExcelReportConfig> configMap = new ConcurrentHashMap<>();


    public ConfigurablePoiExcelGenerator() {
        super();
    }

    public File generateXlsFile(List<Object> models, File file, String name) throws IOException {
        ExcelReportConfig config = loadExcelReportConfig(name);
        return generateXlsFile(models, file, config);
    }


    private ExcelReportConfig loadExcelReportConfig(String name) {
        ExcelReportConfig config = configMap.get(name);
        if (config != null) {
            return config;
        }
        Yaml yaml = new Yaml();
        try (InputStream inputStream =
                     ConfigurablePoiExcelGenerator.class.getClassLoader()
                             .getResourceAsStream(String.format("excel-config/%s.yml", name));) {
            config = yaml.loadAs(inputStream, ExcelReportConfig.class);
            configMap.put(name, config);
        } catch (IOException e) {
            logger.info("error", e);
        }
        return config;
    }


    public File generateXlsFile(List<Object> models, File file, ExcelReportConfig excelConfig) throws IOException {

        List<ExcelConfig> configList = excelConfig.getDetails();

        Workbook wb = null;
        Sheet sheet = null;
        if (StringUtils.isEmpty(excelConfig.getTemplateName())) {
            wb = new HSSFWorkbook();
            //第二步创建sheet
            sheet = ((HSSFWorkbook) wb).createSheet(defaultTitle);
        } else {
            InputStream inputStream = ConfigurablePoiExcelGenerator.class.getClassLoader()
                    .getResourceAsStream(String.format("templates/%s", excelConfig.getTemplateName()));
            //
            wb = new SXSSFWorkbook(new XSSFWorkbook(inputStream), 10240);
            sheet = wb.getSheetAt(0);

        }
        if (excelConfig.isNeedGenerateHeader()) {
            createHeaders(sheet, wb, configList);
        }

        List<Expression> expressions = configList.stream().map(config -> {
            //logger.info(config.getValueExp());
            try {
                return parser.parseExpression(config.getValueExp());
            } catch (Exception e) {
                logger.warn(config.getValueExp(), e);
                throw new RuntimeException(e);
            }
        }).collect(Collectors.toList());
        if (models != null) {
            int excelStartRow = excelConfig.getStartRow();
            for (int i = 0; i < models.size(); i++) {
                Object record = models.get(i);
                //创建行
                Row row = sheet.createRow(i + excelStartRow);
                for (int j = 0; j < expressions.size(); j++) {
                    //创建单元格并且添加数据
                    Object value = null;
                    try {
                        value = expressions.get(j).getValue(record);
                    } catch (Exception e) {
                        Cat.logError(e);
                        value = "ERR";
                    }
                    Cell cell = row.createCell(j);
                    CellStyle style = cell.getCellStyle();
                    style.setAlignment(HorizontalAlignment.CENTER);
                    style.setShrinkToFit(false);
                    style.setWrapText(true);

                    if (value == null) {
                        cell.setCellValue("");
                        continue;
                    }
                    if (value instanceof String) {
                        cell.setCellValue((String) value);
                    } else {
                        cell.setCellValue(value.toString());
                    }
                }
            }
        }
        //第六步将生成excel文件保存到指定路径下
        try (FileOutputStream fout = new FileOutputStream(file);) {
            wb.write(fout);
        } catch (IOException e) {
            logger.error("", e);
        }
        return file;
    }


    private void createHeaders(Sheet sheet, Workbook wb, List<ExcelConfig> configList) {
        Row row = sheet.createRow(0);
        //第四步创建单元格
        for (int i = 0; i < configList.size(); i++) {
            ExcelConfig config = configList.get(i);
            Cell cell = row.createCell(i);         //第一个单元格
            cell.setCellValue(config.getTitle());                  //设定值

            CellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);  //居中
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, config.getWidth() * 256);
        }
    }
}