主要思想：
依赖于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 details;
}
public class ConfigurablePoiExcelGenerator {
private String defaultTitle = "Sheet1";
private final expressionParser parser = new SpelexpressionParser();
private Logger logger = LoggerFactory.getLogger(ConfigurablePoiExcelGenerator.class);
private Map configMap = new ConcurrentHashMap<>();
public ConfigurablePoiExcelGenerator() {
super();
}
public File generateXlsFile(List 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 models, File file, ExcelReportConfig excelConfig) throws IOException {
List 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 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 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);
}
}
}
