EasyExcel导出工具类

目录

工具类

头部实体类（要和工具类在同一个module或项目下）

日期转换器

---

工具类

/*** 导出Excel工具类*/
public class EasyExcelUtil<T> {/*** 单sheet（Map写入）* @param response 响应对象* @param headList 头部集合* @param dataList 数据集合*/public static void write(HttpServletResponse response, List<ExcelHead> headList, List<Map<String, Object>> dataList) throws IOException {ExcelWriterBuilder writerBuilder = EasyExcel.write();writerBuilder.file(response.getOutputStream());writerBuilder.excelType(ExcelTypeEnum.XLSX);//日期转换器TimestampStringConverter converter = new TimestampStringConverter();writerBuilder.registerConverter(converter).registerWriteHandler(new ColumnWidthStyleStrategy()).head(convertHead(headList)).sheet("sheet1").doWrite(convertData(headList, dataList));}/*** 多sheet（Map写入）* @param response 响应对象* @param headMap 头部Map数据* @param dataMap 数据Map数据* @param sheetMap sheet Map数据*/public static void multipleWrite(HttpServletResponse response, Map<String,List<ExcelHead>> headMap, Map<String,List<Map<String, Object>>> dataMap, Map<String,String> sheetMap) throws IOException {//日期转换器TimestampStringConverter converter = new TimestampStringConverter();ExcelWriter excelWriter = EasyExcel.write().registerConverter(converter).registerWriteHandler(new ColumnWidthStyleStrategy()).file(response.getOutputStream()).excelType(ExcelTypeEnum.XLSX).autoCloseStream(true).build();int i = 0;for (Map.Entry<String,List<ExcelHead>> entry : headMap.entrySet()) {WriteSheet writeSheet = EasyExcel.writerSheet(i++, sheetMap.get(entry.getKey())).head(convertHead(entry.getValue())).build();excelWriter.write(convertData(entry.getValue(), dataMap.get(entry.getKey())), writeSheet);}excelWriter.finish();}/*** 实体写入* @param response 响应对象* @param sheetName sheet名称* @param c 实体类* @param list 实体数据*/public static <T> void writeSheet(HttpServletResponse response, String sheetName, Class<T> c, List<T> list) throws IOException {EasyExcel.write(response.getOutputStream(), c).sheet(sheetName).doWrite(list);}/*** 读取并存储到实体* @param fileName 路径地址* @param sheetName sheet名称* @param c 实体类*/public static <T> List<T> read(String fileName, String sheetName, Class c) {List<T> list = new ArrayList();EasyExcel.read(fileName, c, new ReadListener<T>() {@Overridepublic void invoke(T o, AnalysisContext analysisContext) {list.add(o);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}).sheet(sheetName).doRead();return list;}/*** 读取并存储到实体* @param fileName 路径地址* @param sheetNo 指定sheet*/public static Map<String,Object> readToMap(String fileName, Integer sheetNo) {Map<String,Object> result = new HashMap<>();List<Map<String,Object>> dataList = new ArrayList();//头部mapMap<String,String> headMap = new HashMap<>();//头部拼音mapMap<String,String> pinyinMap = new HashMap<>();EasyExcel.read(fileName,new AnalysisEventListener<Map<Integer, Object>>() {@Overridepublic void invoke(Map<Integer, Object> data, AnalysisContext context) {Map<String,Object> map = new HashMap<>();for (Integer key : data.keySet()) {if(key!=null && data.get(key)!=null) {map.put("field_" + key.toString(), data.get(key));}}dataList.add(map);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}@Overridepublic void invokeHead(Map<Integer, ReadCellData<?>> head, AnalysisContext context) {for (Integer key : head.keySet()) {if(key!=null && head.get(key)!=null && StringUtils.isNotBlank(head.get(key).getStringValue())) {headMap.put("field_" + key.toString(), head.get(key).getStringValue());pinyinMap.put("field_" + key.toString(), Pinyin4jUtils.getPinYinHeadChar(head.get(key).getStringValue()));}}}}).sheet(sheetNo).headRowNumber(1).doRead();result.put("headMap",headMap);result.put("pinyinMap",pinyinMap);result.put("dataList",dataList);result.put("count",dataList.size());return result;}/*** 读取表头并存储到实体* @param fileName 路径地址* @param sheetNo 指定sheet*/public static Map<String,Object> readToMapHead(String fileName, Integer sheetNo) {Map<String,Object> result = new HashMap<>();//头部mapMap<String,String> headMap = new HashMap<>();//头部拼音mapMap<String,String> pinyinMap = new HashMap<>();EasyExcel.read(fileName,new AnalysisEventListener<Map<Integer, Object>>() {@Overridepublic void invoke(Map<Integer, Object> data, AnalysisContext context) {}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}@Overridepublic void invokeHead(Map<Integer, ReadCellData<?>> head, AnalysisContext context) {for (Integer key : head.keySet()) {if(key!=null && head.get(key)!=null && StringUtils.isNotBlank(head.get(key).getStringValue())) {headMap.put("field_" + key.toString(), head.get(key).getStringValue());pinyinMap.put("field_" + key.toString(), Pinyin4jUtils.getPinYinHeadChar(head.get(key).getStringValue()));}}}}).sheet(sheetNo).headRowNumber(1).doRead();result.put("headMap",headMap);result.put("pinyinMap",pinyinMap);return result;}/*** 头部转换* @param headList 头部集合*/private static List<List<String>> convertHead(List<ExcelHead> headList) {List<List<String>> list = new ArrayList<>();for (ExcelHead head : headList) {list.add(Lists.newArrayList(head.getTitle()));}//沒有搞清楚head的参数为List<List<String>>,用List<String>就OK了return list;}/*** 数据转换* @param headList 头部集合* @param dataList 数据集合*/private static List<List<Object>> convertData(List<ExcelHead> headList, List<Map<String, Object>> dataList) {List<List<Object>> result = new ArrayList();//对dataList转为easyExcel的数据格式for (Map<String, Object> data : dataList) {List<Object> row = new ArrayList();for (ExcelHead h : headList) {Object o = data.get(h.getFieldName());//需要对null的处理，比如age的null，要转为-1row.add(handler(o, h.getNullValue()));}result.add(row);}return result;}/*** 空值处理* @param o 数值* @param nullValue 空值置换*/private static Object handler(Object o, Object nullValue) {return o != null ? o : nullValue;}
}

头部实体类（要和工具类在同一个module或项目下）

/*** Excel头部实体*/
public class ExcelHead<T> {private String fieldName;private String title;private T nullValue;public ExcelHead(String fieldName, String title) {this.fieldName = fieldName;this.title = title;}public ExcelHead(String fieldName, String title, T nullValue) {this.fieldName = fieldName;this.title = title;this.nullValue = nullValue;}public String getFieldName() {return fieldName;}public void setFieldName(String fieldName) {this.fieldName = fieldName;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public T getNullValue() {return nullValue;}public void setNullValue(T nullValue) {this.nullValue = nullValue;}
}

注意：真正导出表格的是ExcelWriterSheetBuilder类中的方法，前面只是封装，这个是真正导出用到的；这个类是EasyExcel自带的。

日期转换器

/*** 日期转换器*/
public class TimestampStringConverter implements Converter<Timestamp> {@Overridepublic Class<?> supportJavaTypeKey() {return Timestamp.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic WriteCellData<?> convertToExcelData(Timestamp value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {WriteCellData cellData = new WriteCellData();String cellValue;if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {cellValue = DateUtils.format(value.toLocalDateTime(), null, globalConfiguration.getLocale());} else {cellValue = DateUtils.format(value.toLocalDateTime(), contentProperty.getDateTimeFormatProperty().getFormat(),globalConfiguration.getLocale());}cellData.setType(CellDataTypeEnum.STRING);cellData.setStringValue(cellValue);cellData.setData(cellValue);return cellData;}
}