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;
}
}
以上代码导出效果: