Easyexcel学习总结

 

 

1、概述

Easyexcel是阿里开源的基于java一个操作excel的组件,两个主要的特点：操作简单、节省内存

导出不需要提供模板，会根据实体类动态生成。

2、快速上手，下面是一个简单的导出案列

// 实体类
@HeadRowHeight(25)
@ColumnWidth(15)// 设置全局列宽，也可以加在字段上，设置每一列的列宽
public class User implements Serializable {

	private static final long serialVersionUID = 1L;
	
	@ExcelProperty(value = "编码", index = 0)
	private Long id;
	
	@ExcelProperty(value = "姓名", index = 1)
	private String name;
	
	@ColumnWidth(20)
	@ExcelProperty(value = "手机号", index = 2)
	private String phone;
	
	@ExcelProperty(value = "性別", index = 3)
	private String sex;
	
	@ExcelProperty(value = "所属组织", index = 4, converter = OrgConverter.class)
	private String orgCode;
	
	@ExcelIgnore // 导入导出时忽略当前字段
	private String field1;

    // setter getter constructor  略

}	

// 数据准备类，实际项目里数据从数据库取

public class UserData implements Serializable {

	private static final long serialVersionUID = 1L;
	
	public static List<User> list = new ArrayList<>();
	
	static {
		list.add(new User(1L, "Tom", "13934576854", "100", "男"));
		list.add(new User(2L, "Jone", "13934576855", "100", "女"));
		list.add(new User(3L, "Bob", "13934576856", "200", "男"));
	}

}

// Controller 代码

@RestController
public class EasyExcelController {
	
	@RequestMapping("/exportUser")
	public String exportUser(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
		response.setContentType("application/vnd.ms-excel");
		response.setCharacterEncoding("UTF-8");
		String fileName = URLEncoder.encode("用户模板", "UTF-8");
		response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
		try {
            Map<Integer, String[]> map = new HashMap<>();
			String[] args = {"男","女"};
			map.put(3, args);
			EasyExcel.write(response.getOutputStream(), User.class).sheet("用户信息").registerWriteHandler(new TitleHandler(map)).doWrite(UserData.list);

		} catch (IOException e) {
			e.printStackTrace();
			return "ERROR";
		}
		return "SUCCESS";
	}
}

// 组织编码枚举
public enum OrgCodeEnums {
	
	AL("100", "阿里"),
	BD("200", "百度");
	
	private String code;
	
	private String name;

	private OrgCodeEnums(String code, String name) {
		this.code = code;
		this.name = name;
	}

	public String getCode() {
		return code;
	}

	public String getName() {
		return name;
	}
	
}

// 字典转换类

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;
import com.easydatalink.tech.enums.OrgCodeEnums;

// 在实体类的注解上使用
@SuppressWarnings("rawtypes")
public class OrgConverter implements Converter<String> {

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

	@Override
	public CellDataTypeEnum supportExcelTypeKey() {
		return CellDataTypeEnum.STRING;
	}
	
	// 将导入的Excel中name转换成code
	@Override
	public String convertToJavaData(CellData cellData,
			ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
		if (OrgCodeEnums.BD.getName().equals(cellData.getStringValue())) {
			return OrgCodeEnums.BD.getCode();
		}
		if (OrgCodeEnums.AL.getName().equals(cellData.getStringValue())) {
			return OrgCodeEnums.AL.getCode();
		}
		return null;
	}
	
	// 将导出的Excel中code转换成name
	@Override
	public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
			GlobalConfiguration globalConfiguration) throws Exception {
		if (OrgCodeEnums.AL.getCode().equals(value)) {
			return new CellData(OrgCodeEnums.AL.getName());
		}
		if (OrgCodeEnums.BD.getCode().equals(value)) {
			return new CellData(OrgCodeEnums.BD.getName());
		}
		return null;
	}

}

// 设置样式

import java.util.List;
import java.util.Map;

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.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.util.CellRangeAddressList;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;

// 导出的时候需要注册一下这个handler,设置样式类
public class TitleHandler implements CellWriteHandler {
	
	// 设置样式的行数
	private static int STYLE_COUNT = 100;
	
	private Map<Integer, String[]> dropDownMap; 
	
	@Override
	public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
			Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

	}

	@Override
	public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
			Head head, Integer relativeRowIndex, Boolean isHead) {

	}

	@Override
	public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
			CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

	}

	@Override
	public void afterCellDispose(WriteSheetHolder sheet, WriteTableHolder writeTableHolder,
			List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
		Sheet curSheet = sheet.getSheet();
		if (isHead) {
			// 设置下来框
			if (null != dropDownMap && !dropDownMap.isEmpty() && dropDownMap.containsKey(cell.getColumnIndex())) {
				String[] datas = dropDownMap.get(cell.getColumnIndex());
				DataValidationHelper helper = curSheet.getDataValidationHelper();
				DataValidationConstraint con = helper.createExplicitListConstraint(datas);
				CellRangeAddressList addressList = null;
                DataValidation validation = null;
                for (int i = 1; i < STYLE_COUNT; i++) {
                    addressList = new CellRangeAddressList(i, i, cell.getColumnIndex(), cell.getColumnIndex());
                    validation = helper.createValidation(con, addressList);
                    curSheet.addValidationData(validation);
                }
			}
		} else {
			 WriteCellStyle headCellStyle = new WriteCellStyle();
	         headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 设置字体居中
	         headCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 设置背景色为白色
	         CellStyle cellStyle = StyleUtil.buildHeadCellStyle(curSheet.getWorkbook(), headCellStyle);
	         cell.setCellStyle(cellStyle);
		}
		
	}

	public TitleHandler(Map<Integer, String[]> dropDownMap) {
		this.dropDownMap = dropDownMap;
	}
	
}

以上代码导出效果：