EasyExcel导入导出功能-数据过滤及样式设置

 

这篇文章主要讲如何使用阿里的开源组件easyexcel实现导入导出以及数据过滤和样式设定

1.首先我们引入组件包，使用最新版本

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.2.6</version>
</dependency>

2.简单测试写个实体类，当然也可以不用实体类进行导入，如果有大量的表格需要导入，进行数据存储，后续还需要对数据进行统计，我们可以根据excel类型设定模板，根据模板的描述来读取excel进行数据存储

package com.cn.xhwnag.login.export.bean;

import java.sql.Timestamp;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentFontStyle;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.cn.xhwnag.login.export.util.LocalDataTimeConverter;


@HeadRowHeight(25)//设置标题头行高
@HeadFontStyle(fontHeightInPoints = 9)//设置标题头字体大小
@ColumnWidth(20)//设置单元格宽度
@ContentRowHeight(25)//设置行高
@ContentFontStyle(fontHeightInPoints = 8)//设置excel文件内容字体大小
@ContentStyle(wrapped = true) //设置是否自动换行
public class ExportExcel{
	@ExcelProperty(value = "主键")
	private String id;
	@ExcelProperty(value = "名称")
	private String name;
	@ExcelProperty(value = "年龄")
	private String age;
	@ExcelProperty(value = "时间",converter=LocalDataTimeConverter.class)//时间格式转化
	private Timestamp date;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAge() {
		return age;
	}
	public void setAge(String age) {
		this.age = age;
	}
	public Timestamp getDate() {
		return date;
	}
	public void setDate(Timestamp date) {
		this.date = date;
	}
}

3.写两个数据转化类，导入时候使用，做数据转化，因为我们平常excel俩面展示的是男，女，数据库可能存储的是0,1,，所以针对这一类数据我们要进行转化

package com.cn.xhwnag.login.export.util;

import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;


@SuppressWarnings("rawtypes")
public class CustomStringConverter  implements Converter<String> {
	
	//初始化需要转化的数据，也可以放在配置文件中读取
	private static Map<String,Object> defaultMap = new ConcurrentHashMap<String,Object>();
	static{
		defaultMap.put("男", 1);
		defaultMap.put("女", 0);
		defaultMap.put("是", 1);
		defaultMap.put("否", 0);
	}
	@Override
	public CellData convertToExcelData(String arg0, ExcelContentProperty arg1, GlobalConfiguration arg2)
			throws Exception {
		return new CellData(arg0);
	}
	
	
	@Override
	public String convertToJavaData(CellData arg0, ExcelContentProperty arg1, GlobalConfiguration arg2)throws Exception {
	    return defaultMap.get(arg0.getStringValue()) == null ? arg0.getStringValue():defaultMap.get(arg0.getStringValue()).toString();
	}
	
	@Override
	public CellDataTypeEnum supportExcelTypeKey() {
		return CellDataTypeEnum.STRING;
	}

	@Override
	public Class supportJavaTypeKey() {
		return String.class;
	}
}

时间转化器

package com.cn.xhwnag.login.export.util;

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
@SuppressWarnings("rawtypes")
public class LocalDataTimeConverter implements  Converter<Timestamp>  {
	  DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	@Override
	public CellData convertToExcelData(Timestamp arg0, ExcelContentProperty arg1, GlobalConfiguration arg2)
			throws Exception {
		return new CellData(sdf.format(arg0));
	}

	@Override
	public Timestamp convertToJavaData(CellData arg0, ExcelContentProperty arg1, GlobalConfiguration arg2)
			throws Exception {
		return Timestamp.valueOf(arg0.getStringValue());
	}

	@Override
	public CellDataTypeEnum supportExcelTypeKey() {
		return CellDataTypeEnum.STRING;
	}

	@Override
	public Class<Timestamp> supportJavaTypeKey() {
		return Timestamp.class;
	}
}

4.接下来是导入导出的工具类

package com.cn.xhwnag.login.export.util;

import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Component;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;

@Component
public class EasyExcelUtil {
	    private EasyExcelUtil() {
	    }
	   
	    /**
	     * @auther: leng
	     * @date :2021年1月6日
	     * @description:fileName:文件名称,sheetName:sheet页名称,needHead:是否需要表头,listdata:需要导出的数据,dataModelClazz:数据模型,response:响应流,lockColumns需要锁定的列,hiddenColumns需要隐藏的列,dropdownBox:往excel中写入下拉框(integer,列索引,string[],需要写入的值)
	     */
	    public static void export(String fileName, String sheetName, Boolean needHead,  List<?> listdata, Class<?> dataModelClazz, HttpServletResponse response,List<Integer> lockColumns,List<Integer> hiddenColumns,Map<Integer,String[]> dropdownBox){
	    	EasyExcelParams params = new EasyExcelParams(fileName, sheetName, true, listdata, dataModelClazz, response);
	    	 params.setStyleConfig(new ExcelStyleConfig(lockColumns, hiddenColumns,dropdownBox));
	    	 try {
				exportExcel(params);
			} catch (IOException e) {
				e.printStackTrace();
			}
	    }
	    
	    public static void export(String fileName, String sheetName, Boolean needHead,  List<?> list, Class<?> dataModelClazz, HttpServletResponse response){
	    	EasyExcelParams params = new EasyExcelParams(fileName, sheetName, true, list, dataModelClazz, response);
	    	 params.setStyleConfig(new ExcelStyleConfig());
	    	 try {
				exportExcel(params);
			} catch (IOException e) {
				e.printStackTrace();
			}
	    }
	    
	    public static void export(String fileName, List<?> list, HttpServletResponse response){
	    	 EasyExcelParams params = new EasyExcelParams(fileName, fileName, true, list, response);
	    	 params.setStyleConfig(new ExcelStyleConfig());
	    	 try {
				exportExcel(params);
			} catch (IOException e) {
				e.printStackTrace();
			}
	    }
	    
	    
	    public static void export(String fileName, String sheetName, Boolean needHead,  List<?> listdata, Class<?> dataModelClazz, HttpServletResponse response,List<Integer> lockColumns,List<Integer> hiddenColumns){
	    	EasyExcelParams params = new EasyExcelParams(fileName, sheetName, true, listdata, dataModelClazz, response);
	    	 params.setStyleConfig(new ExcelStyleConfig(lockColumns, hiddenColumns));
	    	 try {
				exportExcel(params);
			} catch (IOException e) {
				e.printStackTrace();
			}
	    }
	    /**
	     * 导出Excel实现
	     */
	    @SuppressWarnings("deprecation")
		private static void exportExcel(EasyExcelParams params) throws IOException {
	        prepareResponds(params.getFileName(), params.getResponse());
	        ServletOutputStream outputStream = params.getResponse().getOutputStream();
	        ExcelWriterBuilder builder = new ExcelWriterBuilder();
	        builder.sheet(params.getSheetName());
	        builder.head(params.getDataModelClazz());
	        builder.file(outputStream);
	        builder.excelType(ExcelTypeEnum.XLSX);
	        builder.needHead(true);
	        builder.registerWriteHandler(params.getStyleConfig());
	        WriteSheet sheet = new WriteSheet();
	        sheet.setSheetName(params.getSheetName());
	        sheet.setSheetNo(1);
	        ExcelWriter writer = builder.build();
	        try {
				writer.write(params.getData(), sheet);
				if (!CollectionUtils.isEmpty(params.getMergeCellIndices())) {
				    for (EasyExcelParams.MergeCellIndex mergeCellIndex : params.getMergeCellIndices()) {
				        writer.merge(mergeCellIndex.getFirstRowIndex(), mergeCellIndex.getLastRowIndex(), mergeCellIndex.getFirstColumnIndex(), mergeCellIndex.getLastColumnIndex());
				    }
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				writer.finish();
				outputStream.close();
			}
	    }
	    /**
	     * 将文件输出到浏览器(导出)
	     */
	    private static void prepareResponds(String fileName, HttpServletResponse response) throws IOException {
	        response.setContentType("application/vnd.ms-excel");
	        response.setCharacterEncoding("utf-8");
	        fileName = URLEncoder.encode(fileName, "UTF-8");
	        response.setHeader("Content-disposition", "attachment;filename*=utf-8'zh_cn'" + fileName + ExcelTypeEnum.XLSX.getValue());
	    }
}

package com.cn.xhwnag.login.export.util;

import java.util.List;
import javax.servlet.http.HttpServletResponse;


@SuppressWarnings("rawtypes")
public class EasyExcelParams {
	  /**
     * 文件名
     */
    private String fileName;
    /**
     * sheet名
     */
    private String sheetName;
    /**
     * 是否需要表头
     */
    private Boolean needHead;
    /**
     * 导出数据
     */
    private List data;
    /**
     * 数据模型类型
     */
    private Class dataModelClazz;
    /**
     * 响应
     */
    private HttpServletResponse response;
    /**
     * 单元格样式
     */
    private ExcelStyleConfig styleConfig;
    /**
     * 合并索引数
     */
    private List<MergeCellIndex> mergeCellIndices;
    public static class MergeCellIndex {
        /**
         * 开始行
         */
        private Integer firstRowIndex;
        /**
         * 结束行
         */
        private Integer lastRowIndex;
        /**
         * 开始列
         */
        private Integer firstColumnIndex;
        /**
         * 结束列
         */
        private Integer lastColumnIndex;
		public Integer getFirstRowIndex() {
			return firstRowIndex;
		}
		public void setFirstRowIndex(Integer firstRowIndex) {
			this.firstRowIndex = firstRowIndex;
		}
		public Integer getLastRowIndex() {
			return lastRowIndex;
		}
		public void setLastRowIndex(Integer lastRowIndex) {
			this.lastRowIndex = lastRowIndex;
		}
		public Integer getFirstColumnIndex() {
			return firstColumnIndex;
		}
		public void setFirstColumnIndex(Integer firstColumnIndex) {
			this.firstColumnIndex = firstColumnIndex;
		}
		public Integer getLastColumnIndex() {
			return lastColumnIndex;
		}
		public void setLastColumnIndex(Integer lastColumnIndex) {
			this.lastColumnIndex = lastColumnIndex;
		}
        
        
    }
    /**
     * 不合并和不锁定构造
     */
  
    public EasyExcelParams(String fileName, String sheetName, Boolean needHead,  List data, Class dataModelClazz, HttpServletResponse response) {
        this.fileName = fileName;
        this.sheetName = sheetName;
        this.needHead = needHead;
        this.data = data;
        this.dataModelClazz = dataModelClazz;
        this.response = response;
    }
    
    public EasyExcelParams(String fileName, String sheetName, Boolean needHead,  List data, HttpServletResponse response) {
        this.fileName = fileName;
        this.sheetName = sheetName;
        this.needHead = needHead;
        this.data = data;
        this.response = response;
    }
   
    public void setStyleConfig(ExcelStyleConfig styleConfig) {
        this.styleConfig = styleConfig;
    }
    public void setMergeCellIndices(List<MergeCellIndex> mergeCellIndices) {
        this.mergeCellIndices = mergeCellIndices;
    }

	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public Boolean getNeedHead() {
		return needHead;
	}

	public void setNeedHead(Boolean needHead) {
		this.needHead = needHead;
	}

	public List getData() {
		return data;
	}

	public void setData(List data) {
		this.data = data;
	}

	public Class getDataModelClazz() {
		return dataModelClazz;
	}

	public void setDataModelClazz(Class dataModelClazz) {
		this.dataModelClazz = dataModelClazz;
	}

	public HttpServletResponse getResponse() {
		return response;
	}

	public void setResponse(HttpServletResponse response) {
		this.response = response;
	}

	public ExcelStyleConfig getStyleConfig() {
		return styleConfig;
	}

	public List<MergeCellIndex> getMergeCellIndices() {
		return mergeCellIndices;
	}

	@Override
	public String toString() {
		return "EasyExcelParams [getFileName()=" + getFileName() + ", getSheetName()=" + getSheetName()
				+ ", getNeedHead()=" + getNeedHead() + ", getData()=" + getData() + ", getDataModelClazz()="
				+ getDataModelClazz() + ", getResponse()=" + getResponse() + ", getStyleConfig()=" + getStyleConfig()
				+ ", getMergeCellIndices()=" + getMergeCellIndices() + "]";
	}
}

我们设定单元格格式和表格锁定以及渲染下拉框之，以及导出数据的时候数据转化类的操作都在这个类里面完成

package com.cn.xhwnag.login.export.util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;


@SuppressWarnings("rawtypes")
public class ExcelStyleConfig implements CellWriteHandler{
	
	/**
	 * 需要渲染下拉框
	 */
	private Map<Integer,String[]> dropdownBox = new HashMap<Integer,String[]>();
	 /**
     * 需要锁定的列集合
     */
    private List<Integer> columnList = new ArrayList<Integer>();
    /**
     * 样式类
     */
    private CellStyle cellStyle = null;
    
    /**
     * 设置字体大小
     */
    //private Font font;
    /**
     * 隐藏索引数
     */
    private List<Integer> hiddenIndices = new ArrayList<Integer>();
    
    /**
     * sheet页
     */
    private  Sheet sheet;
  
    
    public ExcelStyleConfig() {
    }
    public ExcelStyleConfig(List<Integer> columnList) {
        this.columnList = columnList;
    }
    public ExcelStyleConfig(List<Integer> columnList, List<Integer> hiddenIndices) {
        this.columnList = columnList;
        this.hiddenIndices = hiddenIndices;
    }
    public ExcelStyleConfig(List<Integer> columnList, List<Integer> hiddenIndices,Map<Integer,String[]> dropdownBox) {
        this.columnList = columnList;
        this.hiddenIndices = hiddenIndices;
        this.dropdownBox = dropdownBox;
    }
    
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    	if(cellStyle==null)cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
        sheet = writeSheetHolder.getSheet();
    }
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
		
    	//设置下拉框选项
		DataValidationHelper helper = sheet.getDataValidationHelper();
		for(Map.Entry<Integer,String[]> entry :dropdownBox.entrySet()){
			CellRangeAddressList addressList = new CellRangeAddressList(1,9999,entry.getKey(),entry.getKey());
			DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
			DataValidation dataValidation = helper.createValidation(constraint, addressList);
			if(dataValidation instanceof XSSFDataValidation){
				dataValidation.setSuppressDropDownArrow(true);
				dataValidation.setShowErrorBox(true);
			}else{
				dataValidation.setSuppressDropDownArrow(false);
			}
			sheet.addValidationData(dataValidation);
		}
    	
    	//设置字体大小
    	//font.setFontHeightInPoints((short)9);
        // 下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        // 左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        // 上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        // 右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        // 水平对齐方式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
       // cellStyle.setFont(font);
        // 垂直对齐方式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setLocked(false);
        
        // 设置隐藏列
        if (hiddenIndices.size()>0 && hiddenIndices.contains(cell.getColumnIndex())) {
            writeSheetHolder.getSheet().setColumnHidden(cell.getColumnIndex(), true);
        }
        
        if (columnList.size()>0 && columnList.contains(cell.getColumnIndex())) {
            // 设置表单保护密码
            writeSheetHolder.getSheet().protectSheet("password");
            // 设置锁定单元格
            cellStyle.setLocked(true);
            //设置冻结某行某列
           // sheet.createFreezePane(cell.getColumnIndex(),0);
            //设置背景颜色
            cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        }
        // 填充单元格样式
        cell.setCellStyle(cellStyle);
    }
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,  List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }
	@Override
	public void afterCellDataConverted(WriteSheetHolder arg0, WriteTableHolder arg1, CellData arg2, Cell arg3,
			Head arg4, Integer arg5, Boolean arg6) {
		    //隐藏列中的数据不需要处理
			if(!hiddenIndices.contains(arg3.getColumnIndex())){
				//这里简单处理一下，跟CustomStringConverter类里面的处理逻辑一样，对应起来,将需要转化的数据配置在配置文件中，然后进行相应的转化处理
				if("1".equals(arg2.toString()) ){
					arg2.setStringValue("是");
				}
				if("0".equals(arg2.toString())){
					arg2.setStringValue("否");
				}
				arg2.setType(CellDataTypeEnum.STRING);
			}
	}
}

导入时的工具类，继承AnalysisEventListener监听，将读取的数据进行存储返回，用于数据库保存

package com.cn.xhwnag.login.export.util;

import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.cn.xhwnag.login.export.bean.ExportExcel;

public class ExcelZBData  extends AnalysisEventListener<ExportExcel>{
	

	private List<ExportExcel> datas = new ArrayList<ExportExcel>();
	
	private ExportExcel bean = new ExportExcel();
	public List<ExportExcel> getData() {
		return datas;
	}

	public void setData(List<ExportExcel> datas) {
		this.datas = datas;
	}

	@Override
	public void doAfterAllAnalysed(AnalysisContext arg0) {
	}

	@Override
	public void invoke(ExportExcel arg0, AnalysisContext arg1) {
		datas.add(bean);
	}

}

前面所有的铺垫工作已经完成，接下来我们写controller进行测试

package com.cn.xhwnag.login.export.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.alibaba.excel.EasyExcel;
import com.cn.xhwnag.login.export.bean.ExportExcel;
import com.cn.xhwnag.login.export.service.IExportService;
import com.cn.xhwnag.login.export.util.CustomStringConverter;
import com.cn.xhwnag.login.export.util.EasyExcelUtil;
import com.cn.xhwnag.login.export.util.ExcelZBData;
import com.cn.xhwnag.login.export.util.LocalDataTimeConverter;
import com.google.common.collect.Lists;



/**
 * @auther: leng
 * @date :2021年1月6日
 */
@RestController
@RequestMapping("/export")
public class ExportController {
	
	@Autowired
	private IExportService export;
	
	@RequestMapping(value = "/exportToExcel")
    public Object test(HttpServletResponse response,ExportExcel bean,String startDate, String endDate) {
		String[] sexString ={"是","否"};//在excel指定位置渲染下拉框，Integer，索引数，String[]需要渲染下拉框里面的值
		Map<Integer,String[]> map = new HashMap<>();
		map.put(55, sexString);
		map.put(58, sexString);
        try {
        	//第一个参数是文件名，第二个是sheet页名称，第三个是我们数据库查询出来需要导出的值，
        	//第四个是数据模型，第五个是相应，第六个是excel中需要锁定的列(参数是列的索引)，
        	//第七个是excel需要隐藏的列，第八个是指定的列进行下拉框渲染
        	
        	//像锁定列以及隐藏列和下拉框的需要的参数都可以在配置文件中进行配置，然后代码进行读取
        	//隐藏列和锁定类也可以配置成excel中列显示的A,B,AT，BD之类的字母，在ConversionUtils类中进行转化即可
			EasyExcelUtil.export("数据台账", "数据台账", true, export.getList(bean,startDate,endDate), ExportExcel.class, response,Lists.newArrayList(59), Lists.newArrayList(43,44,45,46,47,48,49,50,51,52,53,54,56,57,59),map);
		} catch (Exception e) {
		}
        return "数据导入成功!";
    }
	
	
	
	
	@RequestMapping("/importExcel")
	public Object importExcel(HttpServletRequest req){
		String path = "xxxxxxxxxx";//excel文件路径
		importData(path);
		return "数据导入成功!";
	}
	
	private void importData(String filePath){
		ExcelZBData data = new ExcelZBData();
		EasyExcel.read(filePath,ExportExcel.class,data).registerConverter(new CustomStringConverter()).registerConverter(new LocalDataTimeConverter()).sheet().headRowNumber(1).doRead();
		List<ExportExcel> list = data.getData();
		export.saveObject(list);
	}
}

文章中部分地方的数据在代码里面写死，在实际使用中我们配置在配置文件中进行读取，再提供一个转化类

package com.cn.xhwnag.login.export;

import java.util.HashMap;
import java.util.Map;

public class ConversionUtils {
	public static Map<String,Integer> columnType = new HashMap<String,Integer>();
	
	static{
		columnType.put("A", 0);
		columnType.put("B", 1);
		columnType.put("C", 2);
		columnType.put("D", 3);
		columnType.put("E", 4);
		columnType.put("F", 5);
		columnType.put("G", 6);
		columnType.put("H", 7);
		columnType.put("I", 8);
		columnType.put("J", 9);
		columnType.put("K", 10);
		columnType.put("L", 11);
		columnType.put("M", 12);
		columnType.put("N", 13);
		columnType.put("O", 14);
		columnType.put("P", 15);
		columnType.put("Q", 16);
		columnType.put("R", 17);
		columnType.put("S", 18);
		columnType.put("T", 19);
		columnType.put("U", 20);
		columnType.put("V", 21);
		columnType.put("W", 22);
		columnType.put("X", 23);
		columnType.put("Y", 24);
		columnType.put("Z", 25);
		
	}
	
	public static Integer getM(String args){
		if(columnType.get(args)==null && args.length()==2){//极端情况下出现一行有很长的数据
			columnType.put(args, (columnType.get(String.valueOf(args.charAt(0)))+1)*26+columnType.get(String.valueOf(args.charAt(1))));
		}
		return columnType.get(args);
	}
}

当然一些样式也可以在实体类中进行设置

  @ContentFontStyle：用于设置单元格内容字体格式的注解

参数	含义
fontName	字体名称
fontHeightInPoints	字体高度
italic	是否斜体
strikeout	是否设置删除水平线
color	字体颜色
typeOffset	偏移量
underline	下划线
bold	是否加粗
charset	编码格式

  @ContentLoopMerge：用于设置合并单元格的注解

参数	含义
eachRow
columnExtend

  @ContentRowHeight：设置行高

参数	含义
value	行高，-1代表自动行高

  @ContentStyle：设置内容格式注解

参数	含义
dataFormat	日期格式
hidden	设置单元格使用此样式隐藏
locked	设置单元格使用此样式锁定
quotePrefix	在单元格前面增加`符号，数字或公式将以字符串形式展示
horizontalAlignment	设置是否水平居中
wrapped	设置文本是否应换行。将此标志设置为true通过在多行上显示使单元格中的所有内容可见
verticalAlignment	设置是否垂直居中
rotation	设置单元格中文本旋转角度。03版本的Excel旋转角度区间为-90°~90°，07版本的Excel旋转角度区间为0°~180°
indent	设置单元格中缩进文本的空格数
borderLeft	设置左边框的样式
borderRight	设置右边框样式
borderTop	设置上边框样式
borderBottom	设置下边框样式
leftBorderColor	设置左边框颜色
rightBorderColor	设置右边框颜色
topBorderColor	设置上边框颜色
bottomBorderColor	设置下边框颜色
fillPatternType	设置填充类型
fillBackgroundColor	设置背景色
fillForegroundColor	设置前景色
shrinkToFit	设置自动单元格自动大小

  @HeadFontStyle：用于定制标题字体格式

参数	含义
fontName	设置字体名称
fontHeightInPoints	设置字体高度
italic	设置字体是否斜体
strikeout	是否设置删除线
color	设置字体颜色
typeOffset	设置偏移量
underline	设置下划线
charset	设置字体编码
bold	设置字体是否家畜

  @HeadRowHeight：设置标题行行高

参数	含义
value	设置行高，-1代表自动行高

  @HeadStyle：设置标题样式

参数	含义
dataFormat	日期格式
hidden	设置单元格使用此样式隐藏
locked	设置单元格使用此样式锁定
quotePrefix	在单元格前面增加`符号，数字或公式将以字符串形式展示
horizontalAlignment	设置是否水平居中
wrapped	设置文本是否应换行。将此标志设置为true通过在多行上显示使单元格中的所有内容可见
verticalAlignment	设置是否垂直居中
rotation	设置单元格中文本旋转角度。03版本的Excel旋转角度区间为-90°~90°，07版本的Excel旋转角度区间为0°~180°
indent	设置单元格中缩进文本的空格数
borderLeft	设置左边框的样式
borderRight	设置右边框样式
borderTop	设置上边框样式
borderBottom	设置下边框样式
leftBorderColor	设置左边框颜色
rightBorderColor	设置右边框颜色
topBorderColor	设置上边框颜色
bottomBorderColor	设置下边框颜色
fillPatternType	设置填充类型
fillBackgroundColor	设置背景色
fillForegroundColor	设置前景色
shrinkToFit	设置自动单元格自动大小

  @ExcelIgnore：不将该字段转换成Excel

  @ExcelIgnoreUnannotated“没有注解的字段都不转换

 

由于时间仓促，代码没有进行优化，如有不足之处，还望不吝指出。