平时在业务系统开发中，通常要进行业务数据导出到excel中，如果只是一个两个业务功能的导出我们直接写代码实现即可，但是若是有很多不同的业务数据需要导出时，如果都通过代码去构建workbook、sheet、row、cell的话，会造成大量的代码重复，同时也不利于后期的代码维护，所以这里针对POI excel进行封装，封装之后，开发者只需要配置xml文件，然后编写业务数据查询的代码即可，无需再关注POI代码编写。
一、分析
在excel导出过程中，主要有两个步骤：
1、读取业务数据
2、构建excel，其中构建excel又包括构建表头和构建数据行
以上过程中跟业务强相关的有读取业务数据和数据列，跟业务无关的时excel的构建过程，因此：
1、将跟业务相关的通过开放接口和配置文件的方式开放出来由具体开发人员操作
2、将跟业务无关的excel构建过程封装起来
二、设计
1、模板采用XML文件配置，在应用启动的时候进行加载。模板主要包含以下内容：数据提供类、数据包装类、导出列及其与数据包装类的映射。
2、应用启动的时候加载模板配置到内存，导出的时候根据模板id找到对应的模板，然后根据模板配置的数据提供类查询对应的数据，结合模板配置的列构建excel。
三、实现
- xml模板文件。文件名以.excel结尾。
文件根节点为template，属性包括id和batchSize，batchSize为每批查询的数据量，因为考虑到有时数据量比较大的时候，不可能一次性都查出来，而是分页查询，一批一批处理到excel中。
sheet节点对应excel中的sheet页，其属性有：name为excel中sheet页名称；dataProvider为数据查询提供类；dataEntity为数据实体类；支持多个sheet页的导出配置。
field节点对应导出列，比较重要的属性有nameCN，nameEN，fieldName，其中nameCN和nameEN对应excel中的列头，fieldName对应dataEntity中的属性。
<?xml version="1.0" encoding="UTF-8"?>
<template id="compare.result.export" batchSize="10">
<sheet name="sheet1" dataProvider="com.xx.xx.compare.service.impl.xxService" dataEntity=" com.xx.xx.compare.vo.CompareResultVO">
<field id="batchNo" nameCn="比对批次" nameEn="Batch No" fieldName="batchNo" ></field>
<field id="tableName" nameCn="表名" nameEn="Table Name" fieldName="tableName" ></field>
<field id="primaryKey" nameCn="主键" nameEn="Primary Key" fieldName="primaryKey" ></field>
<field id="compareRange" nameCn="比对范围" nameEn="Compare Range" fieldName="compareRange" ></field>
<field id="rsType" nameCn="比对结果" nameEn="Compare Result" fieldName="rsType" ></field>
<field id="rsKey" nameCn="数据id" nameEn="Result Data Id" fieldName="rsKey" ></field>
<field id="rsDataStandard" nameCn="更新前数据" nameEn="Old Data" fieldName="rsDataStandard" ></field>
<field id="rsDataNew" nameCn="更新后数据" nameEn="New Data" fieldName="rsDataNew" ></field>
<field id="remark" nameCn="备注" nameEn="Remark" fieldName="remark" ></field>
</sheet>
</template>
- 模板加载类，使用@Configuration注解，使得应用启动的时候即可加载配置文件。模板文件解析成对应的template实体，放入map中，key为模板id。
模板解析类TemplateReader：
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
@Configuration
public class TemplateReader {
private static final Logger logger = LoggerFactory.getLogger(TemplateReader.class);
private static Map<String,ExcelTemplate> tempalteMap = new HashMap<String,ExcelTemplate>();
public TemplateReader() {
logger.info("excel导入导出模板加载......");
try {
readConfig();
} catch (IOException | DocumentException e) {
e.printStackTrace();
logger.error("excel导入导出模板加载异常......",e);
}
}
public static ExcelTemplate getTemplate(String templateId) {
if(tempalteMap.containsKey(templateId)) {
return tempalteMap.get(templateId);
}
return null;
}
public void readConfig() throws IOException, DocumentException {
SAXReader saxReader = new SAXReader();
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
Resource[] resources = resolver.getResources("classpath*:excel/*.excel.xml");
for (Resource r : resources) {
// 读取文件
Document read = saxReader.read(r.getInputStream());
// 获取根节点
Element rootElement = read.getRootElement();
ExcelTemplate template = this.readTemplate(rootElement);
tempalteMap.put(template.getId(), template);
}
}
public ExcelTemplate readTemplate(Element rootElement) {
ExcelTemplate template = null;
String rootElementName = rootElement.getName();
if("template".equals(rootElementName)) {
template = new ExcelTemplate();
List<Attribute> attributes = rootElement.attributes();
for (Attribute attribute : attributes) {
if("id".equals(attribute.getName())) {
template.setId(attribute.getText());
}else if("batchSize".equals(attribute.getName())) {
String batchSizeStr = attribute.getText();
if(StringUtils.isNotEmpty(batchSizeStr)) {
template.setBatchSize(Integer.parseInt(batchSizeStr));
}
}
}
readSheet(template,rootElement);
}
return template;
}
public void readSheet(ExcelTemplate template,Element templateElement) {
List<ExcelTemplateSheet> sheets = new ArrayList<ExcelTemplateSheet>();
Iterator<Element> elementIterator = templateElement.elementIterator();
while (elementIterator.hasNext()) {
Element next = elementIterator.next();
if("sheet".equals(next.getName())) {
ExcelTemplateSheet sheet = new ExcelTemplateSheet();
List<Attribute> attributes = next.attributes();
for (Attribute attribute : attributes) {
if("name".equals(attribute.getName())) {
sheet.setName(attribute.getText());
}else if("dataProvider".equals(attribute.getName())) {
sheet.setDataProvider(attribute.getText());
}else if("dataEntity".equals(attribute.getName())) {
sheet.setDataEntity(attribute.getText());
}
}
sheet.setBatchSize(template.getBatchSize());
readField(sheet,next);
sheets.add(sheet);
}
}
template.setTemplateSheet(sheets);
}
public void readField(ExcelTemplateSheet sheet,Element templateElement) {
List<ExcelTemplateSheetField> fields = new ArrayList<ExcelTemplateSheetField>();
Iterator<Element> elementIterator = templateElement.elementIterator();
while (elementIterator.hasNext()) {
Element next = elementIterator.next();
if("field".equals(next.getName())) {
ExcelTemplateSheetField field = new ExcelTemplateSheetField();
List<Attribute> attributes = next.attributes();
for (Attribute attribute : attributes) {
if("id".equals(attribute.getName())) {
field.setId(attribute.getText());
}else if("nameCn".equals(attribute.getName())) {
field.setNameCn(attribute.getText());
}else if("nameEn".equals(attribute.getName())) {
field.setNameEn(attribute.getText());
}else if("fieldName".equals(attribute.getName())) {
field.setFieldName(attribute.getText());
}
}
fields.add(field);
}
}
sheet.setSheetField(fields);
}
}
模板对应的entity类，包括模板类ExcelTemplate，sheet类ExcelTemplateSheet，field类ExcelTemplateSheetField：
ExcelTemplate类：
import java.io.Serializable;
import java.util.List;
public class ExcelTemplate implements Serializable{
private static final long serialVersionUID = 1L;
private String id;
private int batchSize=5000;
private List<ExcelTemplateSheet> templateSheet;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getBatchSize() {
return batchSize;
}
public void setBatchSize(int batchSize) {
if(batchSize>0) {
this.batchSize = batchSize;
}
}
public List<ExcelTemplateSheet> getTemplateSheet() {
return templateSheet;
}
public void setTemplateSheet(List<ExcelTemplateSheet> templateSheet) {
this.templateSheet = templateSheet;
}
}
ExcelTemplateSheet类：
import java.io.Serializable;
import java.util.List;
public class ExcelTemplateSheet implements Serializable{
private static final long serialVersionUID = 1L;
private String name;
private String dataProvider;
private String dataEntity;
private int batchSize;
private List<ExcelTemplateSheetField> sheetField;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDataProvider() {
return dataProvider;
}
public void setDataProvider(String dataProvider) {
this.dataProvider = dataProvider;
}
public String getDataEntity() {
return dataEntity;
}
public void setDataEntity(String dataEntity) {
this.dataEntity = dataEntity;
}
public List<ExcelTemplateSheetField> getSheetField() {
return sheetField;
}
public void setSheetField(List<ExcelTemplateSheetField> sheetField) {
this.sheetField = sheetField;
}
public int getBatchSize() {
return batchSize;
}
public void setBatchSize(int batchSize) {
this.batchSize = batchSize;
}
}
ExcelTemplateSheetField类：
import java.io.Serializable;
public class ExcelTemplateSheetField implements Serializable{
private static final long serialVersionUID = 1L;
private String id;
private String nameCn;
private String nameEn;
private String fieldName;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getNameCn() {
return nameCn;
}
public void setNameCn(String nameCn) {
this.nameCn = nameCn;
}
public String getNameEn() {
return nameEn;
}
public void setNameEn(String nameEn) {
this.nameEn = nameEn;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
}
- 数据提供抽象类，所有的导出查询数据的类都要继承该类，并实现getBatchData方法。
ExportDataProvider类：
import java.util.List;
public abstract class ExportDataProvider<T> {
public abstract List<T> getBatchData(Object condition,PageVO vo);
}
PageVO 类：
import java.io.Serializable;
public class PageVO implements Serializable{
private static final long serialVersionUID = 1L;
private int page=1; //当前页数
private int limit; //每页数量
private long totalNum; //数据总量
private long beginIndex;//开始
private long endIndex; //结束
public long getTotalNum() {
return totalNum;
}
public void setTotalNum(long totalNum) {
this.totalNum = totalNum;
}
public long getBeginIndex() {
return beginIndex;
}
public void setBeginIndex(long beginIndex) {
this.beginIndex = beginIndex;
}
public long getEndIndex() {
return endIndex;
}
public void setEndIndex(long endIndex) {
this.endIndex = endIndex;
}
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
this.countIndex();
}
private void countIndex() {
if(page!=0 && limit!=0) {
beginIndex = (page-1)*limit;
endIndex = page*limit;
}
}
public int getLimit() {
return limit;
}
public void setLimit(int limit) {
this.limit = limit;
this.countIndex();
}
}
- 构建excel的executor类
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
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.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Component;
@Component
public class ExcelExportExecutor {
private static Logger logger = LoggerFactory.getLogger(ExcelExportExecutor.class);
public File execute(String templateId,Object obj,OutputStream os) throws FileNotFoundException, IOException {
ExcelTemplate template = TemplateReader.getTemplate(templateId);
if(CollectionUtils.isNotEmpty(template.getTemplateSheet())) {
Workbook wb = new XSSFWorkbook();
for(ExcelTemplateSheet templateSheet:template.getTemplateSheet()) {
if(CollectionUtils.isNotEmpty(templateSheet.getSheetField())) {
Sheet sheet = wb.createSheet();
createHeader(wb,sheet,templateSheet);
createData(wb,sheet,templateSheet,obj);
}
}
if(os!=null) {
wb.write(os);
}
}
return null;
}
private void createHeader(Workbook wb,Sheet sheet,ExcelTemplateSheet templateSheet) {
List<ExcelTemplateSheetField> fields = templateSheet.getSheetField();
if(CollectionUtils.isNotEmpty(fields)) {
XSSFCellStyle style = this.initHeadStyle(wb);
Row row = sheet.createRow(0);
for(int i=0;i<fields.size();i++) {
ExcelTemplateSheetField field = fields.get(i);
Cell cell = row.createCell(i);
String value = field.getNameCn();
cell.setCellValue(value);
cell.setCellStyle(style);
sheet.setColumnWidth(i,value.length()*256*3);
}
}
}
private void createData(Workbook wb,Sheet sheet,ExcelTemplateSheet templateSheet,Object condition) {
List<ExcelTemplateSheetField> fields = templateSheet.getSheetField();
if(CollectionUtils.isNotEmpty(fields)) {
try {
Class provideClz = Class.forName(templateSheet.getDataProvider());
ExportDataProvider dataProvider = (ExportDataProvider) SpringUtil.getBean(provideClz);
PageVO page = new PageVO();
page.setLimit(templateSheet.getBatchSize());
List<Object> batchData = dataProvider.getBatchData(condition, page);
Map<String,Method> methodMap = new HashMap();
int rowIndex=1;
XSSFCellStyle style = this.initDataStyle(wb);
while(CollectionUtils.isNotEmpty(batchData)) {
for(Object data:batchData){
Row row = sheet.createRow(rowIndex);
for(int i=0;i<fields.size();i++) {
ExcelTemplateSheetField field = fields.get(i);
String fieldName = field.getFieldName();
String methodName = "get" + fieldName.replaceFirst(fieldName.substring(0,1), fieldName.substring(0,1).toUpperCase());
Method method;
if(methodMap.containsKey(methodName)) {
method = methodMap.get(methodName);
}else {
method = BeanUtils.findDeclaredMethodWithMinimalParameters(data.getClass(), methodName);
}
try {
Object value = method.invoke(data);
Cell cell = row.createCell(i);
cell.setCellValue(value==null?"":String.valueOf(value));
cell.setCellStyle(style);
} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
e.printStackTrace();
}
}
rowIndex++;
};
page.setPage(page.getPage()+1);
batchData = dataProvider.getBatchData(condition, page);
}
} catch (ClassNotFoundException e) {
logger.error("找不到类"+templateSheet.getDataProvider()+",请检查excel模板配置.",e);
}
}
}
private XSSFCellStyle initHeadStyle(Workbook wb) {
XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
XSSFFont font = (XSSFFont) wb.createFont();
font.setBold(true);
font.setFontHeightInPoints((short)12);
style.setFont(font);
style.setBorderBottom(BorderStyle.MEDIUM);
style.setBorderLeft(BorderStyle.MEDIUM);
style.setBorderRight(BorderStyle.MEDIUM);
style.setBorderTop(BorderStyle.MEDIUM);
return style;
}
private XSSFCellStyle initDataStyle(Workbook wb) {
XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
style.setBorderBottom(BorderStyle.MEDIUM);
style.setBorderLeft(BorderStyle.MEDIUM);
style.setBorderRight(BorderStyle.MEDIUM);
style.setBorderTop(BorderStyle.MEDIUM);
return style;
}
}
辅助类SpringUtil：
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
@Component
public class SpringUtil implements ApplicationContextAware {
private static ApplicationContext applicationContext;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
if(SpringUtil.applicationContext == null) {
SpringUtil.applicationContext = applicationContext;
}
}
//获取applicationContext
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
//通过name获取 Bean.
public static Object getBean(String name){
return getApplicationContext().getBean(name);
}
//通过class获取Bean.
public static <T> T getBean(Class<T> clazz){
return getApplicationContext().getBean(clazz);
}
//通过name,以及Clazz返回指定的Bean
public static <T> T getBean(String name,Class<T> clazz){
return getApplicationContext().getBean(name, clazz);
}
}
总结
实际上这里只是简单的封装了一下，只是将excel构建功能封装了起来，而且目前这种封装算是比较低程度的封装。大家可以从以下方面考虑更高层级的封装：
1、针对数据量比较大导出，比如说几十万上百万的数据导出，可以在这个基础上实现多线程异步导出。
2、有些业务场景对excel样式有要求，可以考虑开放接口出来以供自定义样式。
840
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
