Easyexcel学习总结

Dear 丶 2022-10-23 14:30 158阅读 0赞

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

以上代码导出效果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbnlvdXhpbnphbw_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbnlvdXhpbnphbw_size_16_color_FFFFFF_t_70]: /images/20221023/629daf406e604137ad015d7774875b65.png