封装EasyExcel读写Excel

简介

 

读取Excel的几种方案jxl.jar、jcom.jar、poi.jar、EasyExcel，其中poi用得最广泛，但是其api很复杂，一个读取、写出操作要写很多行代码。

本文介绍一个阿里巴巴开源方案：EasyExcel。

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

github地址点这里。

官方网址点这里。

相比POI，确实代码量上要少很多，而且还可以自动将从Excel文件中读取到的数据赋值到对应的实体类对象里，或从对象写出到Excel文件。

看两个个最简单的官方例子：

    /**
     * 最简单的读
     * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
     * <p>3. 直接读即可
     */
    @Test
    public void simpleRead() {
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

    /**
     * 最简单的写
     * <p>1. 创建excel对应的实体对象 参照{@link com.alibaba.easyexcel.test.demo.write.DemoData}
     * <p>2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }

很简单是吧，这是最简单的读写，但是在实际应用中，总感觉没有那么Easy，也不够那么通用，下面开始，自己再封装一下，争取api更通用，代码量更少。

封装

所有Excel对应实体对象都继承BaseExcelData，这个类定义了Excel的通用基本样式，包括表头背景颜色、宽度、字体等。

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

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.alibaba.excel.annotation.write.style.HeadStyle;

/**
 * Excel基本设置
 * @author test
 */
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 30,
		horizontalAlignment = HorizontalAlignment.CENTER, verticalAlignment = VerticalAlignment.CENTER,
		wrapped = true, shrinkToFit = true,
		borderTop = BorderStyle.NONE, borderBottom = BorderStyle.NONE, borderLeft = BorderStyle.NONE, borderRight = BorderStyle.NONE)
@HeadRowHeight(24)
@HeadFontStyle(bold = true, color = 9, fontName = "Arial", fontHeightInPoints = 10)

@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, verticalAlignment = VerticalAlignment.CENTER)
@ContentRowHeight(19)
@ContentFontStyle(fontName = "Arial", fontHeightInPoints = 10)
public abstract class BaseExcelData {

}

测试实体类： 

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

public class DemoData extends BaseExcelData{
    //设置excel表头名称
    @ExcelProperty(value = "学生编号",index = 0)
    @ColumnWidth(13)
    private Integer sno;
    
    @ExcelProperty(value = "学生姓名",index = 1)
    @ColumnWidth(13)
    private String sname;
    
    @ExcelIgnore
    private String group;
       
	// 此处省略 get set 方法
}

一个Excel的读写，通常需要几个参数：表头类型、要写入的数据、文件名、sheet名、输入输出位置、读取监听器等，这些参数可以统一封装到一个对象中。

sheet参数封装：

import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

import com.alibaba.excel.event.AnalysisEventListener;

/**
 * Excel读写参数类
 * @author test
 */
public class ExcelParams<T> {

	/**
	 * Excel文件名
	 */
	private String fileName;

	/**
	 * Excel工作表名
	 */
	private String sheetName;
	
	/**
	 * 表头Class
	 */
	private Class<T> dataClass;
	
	/**
	 * 数据
	 */
	private List<T> dataList;
	
	/**
	 * 文件输出源
	 */
	private OutputStream fileDest;
	
	/**
	 * 文件读取源
	 */
	private InputStream fileSrc;
	
	/**
	 * Excel读取监听器
	 */
	private AnalysisEventListener<T> listener;
	
	/**
	 * 表格冻结
	 */
	private FreezePane freezePane;
	
	public ExcelParams() {
		
	}
	
	public ExcelParams(String fileName,OutputStream fileDest, String sheetName, Class<T> dataClass,List<T> dataList) {
		this.fileName = fileName;
		this.fileDest = fileDest;
		this.sheetName = sheetName;
		this.dataClass = dataClass;
		this.dataList = dataList;
	}
	
	public ExcelParams(Class<T> dataClass,List<T> dataList) {
		this.dataClass = dataClass;
		this.dataList = dataList;
	}
	
	public ExcelParams(InputStream fileSrc, Class<T> dataClass) {
		this.fileSrc = fileSrc;
		this.dataClass = dataClass;
	}
	
	public ExcelParams(String fileName,InputStream fileSrc, String sheetName, Class<T> dataClass,List<T> dataList) {
		this.fileName = fileName;
		this.fileSrc = fileSrc;
		this.sheetName = sheetName;
		this.dataClass = dataClass;
		this.dataList = dataList;
	}
	
	
	// 此处省略 get set 方法
}

多sheet参数封装类，多个sheet，可能表头都是不一样的：

import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

import com.alibaba.excel.event.AnalysisEventListener;

/**
 * Excel多sheet读写参数类
 * @author test
 */
@SuppressWarnings("rawtypes")
public class ExcelParamsMulti {

	/**
	 * Excel文件名
	 */
	private String fileName;

	/**
	 * Excel工作表名
	 */
	private String[] sheetName;
	
	/**
	 * 表头Class
	 */
	private Class[] dataClassArray;
	
	/**
	 * 数据
	 */
	private List[] dataListArray;
	
	/**
	 * 文件输出源
	 */
	private OutputStream fileDest;
	
	/**
	 * 文件读取源
	 */
	private InputStream fileSrc;
	
	/**
	 * Excel读取监听器
	 */
	private AnalysisEventListener[] listenerArray;
	
	/**
	 * 表格冻结
	 */
	private FreezePane[] freezePane;
	
	public ExcelParamsMulti() {
		
	}
	
	public ExcelParamsMulti(String fileName,OutputStream fileDest, String[] sheetName, Class[] dataClassArray,List[] dataListArray) {
		this.fileName = fileName;
		this.fileDest = fileDest;
		this.sheetName = sheetName;
		this.dataClassArray = dataClassArray;
		this.dataListArray = dataListArray;
	}

	public ExcelParamsMulti(InputStream fileSrc, Class[] dataClassArray) {
		this.fileSrc = fileSrc;
		this.dataClassArray = dataClassArray;
	}
	
	public ExcelParamsMulti(String fileName,InputStream fileSrc, String[] sheetName, Class[] dataClassArray,List[] dataListArray) {
		this.fileName = fileName;
		this.fileSrc = fileSrc;
		this.sheetName = sheetName;
		this.dataClassArray = dataClassArray;
		this.dataListArray = dataListArray;
	}
	
    // 此处省略 get set 方法
}

如果需要冻结窗口，可以使用FreezePane：

/**
 * 窗口冻结
 * @author test
 */
public class FreezePane {
	private int colSplit = 0;
	private int rowSplit = 0;
	private int leftmostColumn = 0;
	private int topRow = 0;
	
	public FreezePane(int colSplit, int rowSplit) {
		this.colSplit = colSplit;  
		this.rowSplit = rowSplit;
	}
	
	public FreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) {
		this.colSplit = colSplit;
		this.rowSplit = rowSplit;
		this.leftmostColumn = leftmostColumn;
		this.topRow = topRow;
	}

    // 此处省略 set get 方法
}

import org.apache.poi.ss.usermodel.Sheet;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;

/**
 * Excel冻结窗口Handler
 * @author test
 */
public class SheetFreezeWriteHandler implements SheetWriteHandler {
	
	private FreezePane freezePane;
	
	public SheetFreezeWriteHandler() {
		
	}
	
	public SheetFreezeWriteHandler(FreezePane freezePane) {
		this.freezePane = freezePane;
	}

	@Override
	public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		
	}

	@Override
	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		Sheet sheet = writeSheetHolder.getSheet();
		
		if (freezePane != null) {
			int colSplit = freezePane.getColSplit();
			int rowSplit = freezePane.getRowSplit();
			int leftmostColumn = freezePane.getLeftmostColumn();
			int topRow = freezePane.getTopRow();
			
			if (leftmostColumn == 0 && topRow == 0) {
				sheet.createFreezePane(colSplit, rowSplit);
			} else {
				sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
			}
		}
		
	}
	
}

/**
 * Excel背景颜色枚举类
 * @author test
 */
public enum ExcelColor {

	RED((short)10),YELLOW((short)53),BLUE((short)30),GREEN((short)57);
	
	public final short value;
	
	private ExcelColor(short value){
		this.value = value;
	}

	public short getValue() {
		return value;
	}
}

 读取Excel时的监听器，可有可无，可以用来做一些异步批量插入数据库等操作：

import java.util.ArrayList;
import java.util.List;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

public class ModelExcelListener<T> extends AnalysisEventListener<Object> {
    
	private List<T> dataList = new ArrayList<>();
    
	/**
	 * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
	 */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
       // System.out.println(data.toString());
    	dataList.add((T)data);
        //根据业务自行处理，可以写入数据库等等
    }

    //所以的数据解析完了调用
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("所有数据解析完成");
        for (int i = 0; i < dataList.size(); i ++) {
        	/*DemoData de = (DemoData)dataList.get(i);
        	System.out.println(de.getSname());*/
        }
    }
}

 重点来了，封装了读写方法的工具类：

import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;

/**
 * Excel读写工具类
 * @author test
 */
@SuppressWarnings("rawtypes")
public class ExcelUtils {

	private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class); 
	
	public static boolean isExcel(File file) {
		String fileName = file.getName();
		
		if (file.isFile()) {
			int idx = fileName.lastIndexOf(".");
			if (idx > 0) {
				String ext = fileName.substring(fileName.lastIndexOf("."), fileName.length());
				if (".xls".equals(ext) || ".xlsx".equals(ext)) {
					return true;
				}
			}
		}
		
		return false;
	}
	
	/**
	 * Excel写出
	 * @param params
	 */
	public static <T> void writeExcel(ExcelParams<T> params) {
		
		ExcelWriterSheetBuilder writerSheetBuilder = null;
		ExcelWriter excelWriter = null;
		try {
			
			if (params.getFileDest() == null) {
				logger.error("Excel写出失败，fileDest为空！");
				return;
			}
			
		    excelWriter = EasyExcel.write(params.getFileDest(), params.getDataClass()).build();
		    
		    FreezePane fp = params.getFreezePane();
		    
		    writerSheetBuilder = EasyExcel.writerSheet(params.getSheetName());

		    if (fp != null) {
		    	writerSheetBuilder.registerWriteHandler(new SheetFreezeWriteHandler(fp));
		    }
		    
		    WriteSheet writeSheet = writerSheetBuilder.build();
		    excelWriter.write(params.getDataList(), writeSheet);
		} finally {
		    // 千万别忘记finish 会帮忙关闭流
		    if (excelWriter != null) {
		        excelWriter.finish();
		    }
		}
	}

	/**
	 * Excel写出到response
	 * @param params
	 */
	public static <T> void writeExcel(ExcelParams<T> params, HttpServletResponse response) {
		
		response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 如果fileName为空，则使用当前时间戳为fileName
        String fileName = StringUtils.defaultIfBlank(params.getFileName(), String.valueOf(System.currentTimeMillis()));
		try {
			fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
		
		ExcelWriterSheetBuilder writerSheetBuilder = null;
		ExcelWriter excelWriter = null;
		try {
		    excelWriter = EasyExcel.write(response.getOutputStream(), params.getDataClass()).build();
		    
		    FreezePane fp = params.getFreezePane();
		    
		    writerSheetBuilder = EasyExcel.writerSheet(params.getSheetName());

		    if (fp != null) {
		    	writerSheetBuilder.registerWriteHandler(new SheetFreezeWriteHandler(fp));
		    }
		    writerSheetBuilder.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
		    
		    WriteSheet writeSheet = writerSheetBuilder.build();
		    excelWriter.write(params.getDataList(), writeSheet);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
		    // 千万别忘记finish 会帮忙关闭流
		    if (excelWriter != null) {
		        excelWriter.finish();
		    }
		}
	}	

	/**
	 * Execl异步读取
	 * @param params
	 * @return
	 */
	public static <T> void readExcel(ExcelParams<T> params) {
        ExcelReader excelReader = null;
        ReadSheet readSheet1 = null;
        try {
			if (params.getFileSrc() == null) {
				logger.error("Excel读取失败，fileSrc为空！");
				return;
			}
			
            excelReader = EasyExcel.read(params.getFileSrc()).autoTrim(true).build();
            
            // 注册监听器
            if (params.getListener() != null) {
            	readSheet1 = EasyExcel.readSheet(0).head(params.getDataClass()).registerReadListener(params.getListener()).build();
            } else {
            	readSheet1 = EasyExcel.readSheet(0).head(params.getDataClass()).build();
            }
           
            excelReader.read(readSheet1);
            
        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
	}
	
	/**
	 * Execl同步读取
	 * @param params
	 * @return
	 */
	public static <T> List<T> readExcelSync(ExcelParams<T> params) {
        ExcelReader excelReader = null;
        ReadSheet readSheet = null;
        try {
			if (params.getFileSrc() == null) {
				logger.error("Excel读取失败，fileSrc为空！");
				return new ArrayList<T>();
			}
			
            excelReader = EasyExcel.read(params.getFileSrc()).autoTrim(true).build();
            
            // 注册监听器
            SyncReadListener syncReadListener = new SyncReadListener();
            if (params.getListener() != null) {
            	readSheet = EasyExcel.readSheet(0).head(params.getDataClass())
            			.registerReadListener(syncReadListener).registerReadListener(params.getListener()).build();
            } else {
            	readSheet = EasyExcel.readSheet(0).head(params.getDataClass()).registerReadListener(syncReadListener).build();
            }
           
            excelReader.read(readSheet);
            
            return (List<T>)syncReadListener.getList();
            
        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
	}


	/**
	 * Excel多sheet导出
	 * @param params
	 */
	public static void writeExcelMulti(ExcelParamsMulti paramsMulti) {
		
		ExcelWriter excelWriter = null;
		try {
			if (paramsMulti.getFileDest() == null) {
				logger.error("Excel写出失败，fileDest为空！");
				return;
			}
			
			Class[] dataClass = paramsMulti.getDataClassArray();
			String[] sheetNames = paramsMulti.getSheetName();
			FreezePane[] fp = paramsMulti.getFreezePane();
			List[] dataListArray = paramsMulti.getDataListArray();
			
			excelWriter = EasyExcel.write(paramsMulti.getFileDest()).build();

			for (int i = 0; i < sheetNames.length; i ++) {
			
				ExcelWriterSheetBuilder writerSheetBuilder = EasyExcel.writerSheet(sheetNames[i]).head(dataClass[i]);
				if (fp != null && fp[i] != null) {
					writerSheetBuilder.registerWriteHandler(new SheetFreezeWriteHandler(fp[i]));
				}
				
				WriteSheet writeSheet = writerSheetBuilder.build();
			    excelWriter.write(dataListArray[i], writeSheet);
			}
			
		} finally {
		    // 千万别忘记finish 会帮忙关闭流
		    if (excelWriter != null) {
		        excelWriter.finish();
		    }
		}
	}	
	
	/**
	 * Execl多sheet异步读取
	 * @param params
	 * @return
	 */
	public static void readExcelMulti(ExcelParamsMulti paramsMulti) {
        
		List<ReadSheet> sheetList = new ArrayList<>();
		ExcelReader excelReader = null;
        try {
        	if (paramsMulti.getFileSrc() == null) {
				logger.error("Excel读取失败，fileSrc为空！");
				return;
			}
        	
			Class[] dataClass = paramsMulti.getDataClassArray();
			AnalysisEventListener[] listener = paramsMulti.getListenerArray();
			
			excelReader = EasyExcel.read(paramsMulti.getFileSrc()).autoTrim(true).build();

			for (int i = 0; i < dataClass.length; i ++) {
				ExcelReaderSheetBuilder readerSheetBuilder = EasyExcel.readSheet(i).head(dataClass[i]);
				
				// 注册监听器
	            if (listener != null && listener[i] != null) {
	            	readerSheetBuilder.registerReadListener(listener[i]);
	            }
	            
	            ReadSheet readSheet = readerSheetBuilder.build();
	            sheetList.add(readSheet);
			}
        	
			excelReader.read(sheetList);
            
        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
	}
	
	/**
	 * Execl多sheet同步读取
	 * @param <T>
	 * @param params
	 * @return
	 */
	public static List[] readExcelMultiSync(ExcelParamsMulti paramsMulti) {
		List[] dataListArray = null;
		List<ReadSheet> sheetList = new ArrayList<>();
        ExcelReader excelReader = null;
        try {
        	if (paramsMulti.getFileSrc() == null) {
				logger.error("Excel读取失败，fileSrc为空！");
				return new ArrayList[0];
			}
        	
			Class[] dataClass = paramsMulti.getDataClassArray();
			AnalysisEventListener[] listener = paramsMulti.getListenerArray();
			
			excelReader = EasyExcel.read(paramsMulti.getFileSrc()).autoTrim(true).build();
			
			dataListArray = new ArrayList[dataClass.length];
			SyncReadListener[] listenerArray = new SyncReadListener[dataClass.length];

			for (int i = 0; i < dataClass.length; i ++) {
				ExcelReaderSheetBuilder readerSheetBuilder = EasyExcel.readSheet(i).head(dataClass[i]);
				
				// 注册监听器
	            if (listener != null && listener[i] != null) {
	            	readerSheetBuilder.registerReadListener(listener[i]);
	            }
	            SyncReadListener syncReadListener = new SyncReadListener();
	            readerSheetBuilder.registerReadListener(syncReadListener);
	            
	            ReadSheet readSheet = readerSheetBuilder.build();
	            sheetList.add(readSheet);
	            listenerArray[i] = syncReadListener;
			}
        	
			excelReader.read(sheetList);
            
            for (int i = 0; i < listenerArray.length; i ++) {
            	dataListArray[i] = listenerArray[i].getList();
            }
			
            return dataListArray;
            
        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
	}
	
}

测试写Excel：

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 写Excel
 * @author test
 */
public class TestEasyExcelWrite {

    public static void main(String[] args) throws Exception {

        String fileName = "D:\\write1.xlsx";
        
        File file = new File(fileName);
        OutputStream os = new FileOutputStream(file);
        
		// 封装Excel参数
		ExcelParams<DemoData> params = new ExcelParams<>("学生统计",os, "学生", DemoData.class,getData(20));
		params.setFreezePane(new FreezePane(0, 1));
		// 写Excel文件
		ExcelUtils.writeExcel(params);
        
    }

    //创建方法返回list集合
    public static List<DemoData> getData(int count) {
        List<DemoData> list = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            DemoData data = new DemoData();
            data.setSno(i);
            data.setSname("张三" + i);
            list.add(data);
        }
        return list;
    }
}

生成的Excel是这样的：

测试读取Excel：

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;

/**
 * 读Excel
 * @author test
 */
public class TestEasyExcelRead {

	public static void main(String[] args) throws Exception {
		
        String fileName = "D:\\write1.xlsx";
        
        File file = new File(fileName);
        InputStream is = new FileInputStream(file);
        
		// 封装Excel参数
		ExcelParams<DemoData> params = new ExcelParams<>(is, DemoData.class);
		// 读取Excel文件
		List<DemoData> dataList = ExcelUtils.readExcelSync(params);
		
		for (int i = 0; i < dataList.size(); i ++) {
        	System.out.println(dataList.get(i).getSname());
        }
		
	}

}

怎么样？简单吧，只需要把读取或写出的Excel信息封装到ExcelParams类型的参数对象中，然后调用ExcelUtils工具类的读写方法即可。