Encapsulate POI excel export and realize export by configuring xml template

  In business system development, business data is usually exported to excel. If only one or two business functions are exported, we can directly write code to realize it. However, if there are many different business data to be exported, if we build workbook s, sheet s, row s and cell s through code, it will cause a lot of code duplication, which is not conducive to the later stage Code maintenance, so here we encapsulate POI excel. After encapsulation, developers only need to configure xml file, and then write business data query code, without paying attention to POI code writing.

I. Analysis

In the process of excel export, there are two steps:
1. Read business data
 2. Building excel, which includes building header and building data row

In the above process, there is a strong correlation between reading business data and data columns, and the construction process of excel when it has nothing to do with business. Therefore:
1. Open the business-related interface and configuration file to specific developers
 2. Encapsulate the business independent excel construction process

Two, design

1. The template is configured as an XML file and loaded when the application starts. The template mainly includes the following contents: data providing class, data packaging class, export column and its mapping with data packaging class.

2. When the application is started, the template is loaded and configured into memory. When it is exported, the corresponding template is found according to the template id. then, the corresponding data is queried according to the data provided by the template configuration class, and excel is built based on the column configured by the template.

Three, implementation

  1. xml template file. The filename ends in. excel.
    The root node of the file is template, and the attributes include id and batchSize. batchSize is the amount of data queried in each batch. Considering that sometimes when the amount of data is large, it is impossible to find it all at once, but to query in pages, and process it in excel batch by batch.
    The sheet node corresponds to the sheet page in excel. Its attributes are: name is the sheet page name in excel; dataProvider is the data query providing class; dataEntity is the data entity class; it supports the export configuration of multiple sheet pages.
    The field node corresponds to the exported column, and the more important attributes are nameCN, nameEN, fieldName, where nameCN and nameEN correspond to the column header in excel, and fieldName corresponds to the attribute in 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="Comparison batch"  nameEn="Batch No" fieldName="batchNo" ></field>
        <field id="tableName" nameCn="Table name"  nameEn="Table Name" fieldName="tableName" ></field>
        <field id="primaryKey" nameCn="Primary key"  nameEn="Primary Key" fieldName="primaryKey" ></field>
        <field id="compareRange" nameCn="Comparison range"  nameEn="Compare Range" fieldName="compareRange" ></field>
        <field id="rsType" nameCn="Comparison result"  nameEn="Compare Result" fieldName="rsType" ></field>
        <field id="rsKey" nameCn="data id"  nameEn="Result Data Id" fieldName="rsKey" ></field>
        <field id="rsDataStandard" nameCn="Data before update"  nameEn="Old Data" fieldName="rsDataStandard" ></field>
        <field id="rsDataNew" nameCn="Updated data"  nameEn="New Data" fieldName="rsDataNew" ></field>
        <field id="remark" nameCn="Remarks"  nameEn="Remark" fieldName="remark" ></field>
    </sheet>
</template>
  1. Template loading class, use @ Configuration annotation, so that the Configuration file can be loaded when the application starts. The template file is parsed into the corresponding template entity and put into the map. The key is the template id.

Template resolution class 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 Import export template load......");
		try {
			readConfig();
		} catch (IOException | DocumentException e) {
			e.printStackTrace();
			logger.error("excel Import / export template load exception......",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) {
            // read file
            Document read = saxReader.read(r.getInputStream());
			// Get root node
	        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);
    }
}

The entity class corresponding to the template, including template class ExcelTemplate, sheet class ExcelTemplateSheet, field class ExcelTemplateSheetField:
ExcelTemplate class:

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;
	}
}

ExcelTemplateSheet class:

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 class:

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;
	}
}
  1. The data provides an abstract class. All classes exporting query data must inherit this class and implement the getBatchData method.

ExportDataProvider class:

import java.util.List;
public abstract class ExportDataProvider<T> {
	public abstract List<T> getBatchData(Object condition,PageVO vo);
}

PageVO class:

import java.io.Serializable;

public class PageVO implements Serializable{
    private static final long serialVersionUID = 1L;
    
	private int page=1;       //Current page number
	private int limit;      //Quantity per page
	private long totalNum;  //Total data
	private long beginIndex;//start
	private long endIndex;  //End
	
	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();
	}
}
  1. Building the executor class of excel
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("Class not found"+templateSheet.getDataProvider()+",Please check. excel Template configuration.",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;
	} 
}

Auxiliary class 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;
        }
    }
 
    //Get applicationContext
    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }
 
    //Get Bean through name
    public static Object getBean(String name){
        return getApplicationContext().getBean(name);
    }
 
    //Get Bean through class
    public static <T> T getBean(Class<T> clazz){
        return getApplicationContext().getBean(clazz);
    }
 
    //Return the specified Bean through name and Clazz
    public static <T> T getBean(String name,Class<T> clazz){
        return getApplicationContext().getBean(name, clazz);
    }
 
}
Published 19 original articles, won praise and 30000 visitors+
Private letter follow

Tags: Attribute Java Excel Apache

Posted on Mon, 10 Feb 2020 00:12:56 -0800 by ngu_tri