Java Excel的数据导入导出

引入依赖

<!-- EasyExcel -->
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.7</version>
</dependency><!--csv文件操作-->
<dependency><groupId>net.sourceforge.javacsv</groupId><artifactId>javacsv</artifactId><version>2.0</version>
</dependency>

数据读取监听

导入数据时，程序解析和读取数据用，必须要！！！

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;import java.util.ArrayList;
import java.util.List;
import java.util.Map;/*** Excel数据解析监听器， 数据解析方法异步执行* @param <T>   Excel中数据的类型*/
@Getter
@Setter
@NoArgsConstructor
public class ExcelListener<T> extends AnalysisEventListener<T> {// 加入一个判断标签，判断数据是否已经读取完private volatile boolean retryLock = false;// 解析完成后的数据集合, 监听对象初始化之后，立即初始化集合对象private final List<T> dataList = new ArrayList<>();// 每次最多导入条数private final int batchSize = 2000;/*** 获取解析后的数据集合， 如果数据还没有被解析完成，会对读取该集合的线程进行阻塞，直到数据读取完成之后，进行解锁。* 如果一次导入数据超过batchSize条，则以抛异常的形式阻止导入数据* @return  解析后的数据集合*/public List<T> getDataList() {while (true){if (retryLock){if (dataList.size() > batchSize){// 手动清空数据内存数据，减少内存消耗dataList.clear();throw new RuntimeException("一次最多导入"+ batchSize +"条数据");} else {return dataList;}}}}/*** Excel每解析一行数据，就会调用一次该方法* @param data*            one row value. Is is same as {@link AnalysisContext#readRowHolder()}* @param context*            analysis context*/@Overridepublic void invoke(T data, AnalysisContext context) {dataList.add(data);}/*** 读取表头内容* @param headMap   表头部数据* @param context   数据解析上下文*/@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {//System.out.println("表头：" + headMap);}/*** 流中的数据解析完成之后，就会调用此方法* @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 数据解析完成，解锁retryLock = true;}/*** 解析过程如果发生异常，会调用此方法* @param exception* @param context*/@Overridepublic void onException(Exception exception, AnalysisContext context){throw new RuntimeException("Excel数据异常，请检查或联系管理员！");}
}

Excel工具类

根据EasyExcel Model 导出工具类,CSV支持easyexcel获取ExcelProperty

package net.demo.excel.common.util;import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.csvreader.CsvWriter;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;/*** @desc: 根据EasyExcel Model 导出工具类* @Author: Swift* @Date: 2019-08-26 15:18*/
public class ExportUtil {/*** 获取ExcelProperty Value* @return*/public static <T extends BaseRowModel> String[] getFieldNames(Class<T> tClass) {Field[] fields = tClass.getDeclaredFields();List<String> headers = new ArrayList<>();for (Field field: fields) {ExcelProperty property = field.getAnnotation(ExcelProperty.class);if (property != null) {String[] s = property.value();if (s.length > 0) {headers.add(s[0]);}}}String[] strings = new String[headers.size()];headers.toArray(strings);return strings;}/*** 获取filedName* @param vo* @param <T>* @return*/public static <T extends BaseRowModel>  String[] getFields(T vo) {Field[] fields = vo.getClass().getDeclaredFields();List<String> columns = new ArrayList<>();for (Field field: fields) {ExcelProperty property = field.getAnnotation(ExcelProperty.class);if (property != null) {try {field.setAccessible(true);columns.add(field.get(vo)==null ? "" : field.get(vo).toString());} catch (IllegalAccessException e) {e.printStackTrace();throw new RuntimeException("写入内容到csv失败!");}}}String[] strings = new String[columns.size()];columns.toArray(strings);return strings;}public static <T extends BaseRowModel> void export(String type, List<T> vos, HttpServletResponse response, Class<T> t) throws IOException {Boolean isCsv = "csv".equals(type);String fileName = "data" + CalendarUtils.getCurrentTime();response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");if (isCsv) {fileName += ".csv";} else {fileName += ".xlsx";}response.setHeader("Content-disposition", "attachment;filename=" + fileName);ServletOutputStream outputStream = response.getOutputStream();if (isCsv) {CsvWriter csvWriter = new CsvWriter(outputStream, ',', Charset.forName("GBK"));csvWriter.writeRecord(getFieldNames(t));for (T vo : vos) {String[] fields = getFields(vo);csvWriter.writeRecord(fields);}csvWriter.close();} else {ExcelWriter excelWriter = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);Sheet sheet = new Sheet(1, 0, t);excelWriter.write(vos, sheet);sheet.setAutoWidth(true);excelWriter.finish();}outputStream.flush();}public static <T extends BaseRowModel> void export(String type,String fileName, List<T> vos, HttpServletResponse response, Class<T> t) throws IOException {Boolean isCsv = "csv".equals(type);
//        String fileName = "data" + CalendarUtils.getCurrentTime();response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");if (isCsv) {fileName += ".csv";} else {fileName += ".xlsx";}response.setHeader("Content-disposition", "attachment;filename=" + fileName);ServletOutputStream outputStream = response.getOutputStream();if (isCsv) {CsvWriter csvWriter = new CsvWriter(outputStream, ',', Charset.forName("GBK"));csvWriter.writeRecord(getFieldNames(t));for (T vo : vos) {String[] fields = getFields(vo);csvWriter.writeRecord(fields);}csvWriter.close();} else {ExcelWriter excelWriter = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);Sheet sheet = new Sheet(1, 0, t);excelWriter.write(vos, sheet);sheet.setAutoWidth(true);excelWriter.finish();}outputStream.flush();}/*** 根据Excel模板，批量导入数据* @param file  导入的Excel* @param clazz 解析的类型* @return  解析完成的数据*/public static List<?> importExcel(MultipartFile file, Class<?> clazz){if (file == null || file.isEmpty()){throw new RuntimeException("没有文件或者文件内容为空！");}List<Object> dataList = null;BufferedInputStream ipt = null;try {InputStream is = file.getInputStream();// 用缓冲流对数据流进行包装ipt = new BufferedInputStream(is);// 数据解析监听器ExcelListener<Object> listener = new ExcelListener<>();// 读取数据EasyExcel.read(ipt, clazz,listener).sheet().doRead();// 获取去读完成之后的数据dataList = listener.getDataList();} catch (Exception e){log.error(String.valueOf(e));throw new RuntimeException("数据导入失败！" + e);}return dataList;}
}

WriterFactory

导出工厂实现了xlsx与csv 两种方式

package net.demo.excel.common.export;import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import javax.servlet.http.HttpServletResponse;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import net.demo.excel.common.constants.Constants;
import net.demo.excel.common.constants.ExportTypeEnum;
import net.demo.excel.common.export.csv.CsvExport;
import net.demo.excel.common.export.excel.ExcelExport;/*** 导出工厂*/
@Data
@Slf4j
public class WriterFactory {/*** 返回文件流方式** @param key* @param fileName* @param response* @return*/public static ExportWriter getExportWriter(String key, String fileName,HttpServletResponse response) {ExportWriter exportWriter = null;try {OutputStream os = response.getOutputStream();ExportTypeEnum exportTypeEnum = ExportTypeEnum.findByType(key);fileName = fileName + exportTypeEnum.getSuffix();exportWriter = generateExportWriter(exportTypeEnum, os);response.setContentType("application/force-download");// 设置文件名response.addHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));} catch (UnsupportedEncodingException e) {log.error("UnsupportedEncoding:", e);} catch (IOException e) {log.error("IOException:", e);}return exportWriter;}/*** 输出到文件方式** @param key* @param fileName* @return*/public static ExportWriter getExportWriter(String key, String fileName)throws FileNotFoundException {ExportTypeEnum exportTypeEnum = ExportTypeEnum.findByType(key);String filePath =Constants.EXPORT_TMP_DIR + File.separator + fileName + exportTypeEnum.getSuffix();File file = new File(filePath);OutputStream os = new FileOutputStream(file);ExportWriter exportWriter = generateExportWriter(exportTypeEnum, os);exportWriter.setFilePath(filePath);return exportWriter;}private static ExportWriter generateExportWriter(ExportTypeEnum exportTypeEnum, OutputStream os) {ExportWriter exportWriter = null;switch (exportTypeEnum) {case EXCEL:exportWriter = new ExcelExport(os);break;case CSV:exportWriter = new CsvExport(os);break;default:}return exportWriter;}
}

ExportModel抽象类

package net.demo.excel.common.export.model;import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.csvreader.CsvWriter;/*** 抽象实体*/
public abstract class ExportModel {public abstract ExcelWriter getExcelWriter();public abstract Sheet getSheet();public abstract CsvWriter getCsvWriter();
}

CsvModel 实体类 

package net.demo.excel.common.export.csv.model;import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.csvreader.CsvWriter;
import lombok.Data;
import net.demo.excel.common.export.model.ExportModel;/*** csv相关实体*/
@Data
public class CsvModel extends ExportModel {private CsvWriter csvWriter;@Overridepublic ExcelWriter getExcelWriter() {return null;}@Overridepublic Sheet getSheet() {return null;}@Overridepublic CsvWriter getCsvWriter() {return csvWriter;}public CsvModel(CsvWriter csvWriter) {this.csvWriter = csvWriter;}
}

ExcelModel 实体类 

package net.demo.excel.common.export.excel.model;import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.csvreader.CsvWriter;
import lombok.Data;
import net.demo.excel.common.export.model.ExportModel;/*** excel,csv相关实体*/
@Data
public class ExcelModel extends ExportModel {private ExcelWriter excelWriter;private Sheet sheet;@Overridepublic ExcelWriter getExcelWriter() {return excelWriter;}@Overridepublic Sheet getSheet() {return sheet;}@Overridepublic CsvWriter getCsvWriter() {return null;}public ExcelModel(ExcelWriter excelWriter, Sheet sheet) {this.excelWriter = excelWriter;this.sheet = sheet;}
}

ExportWriter接口

package net.demo.excel.common.export;import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.fastjson.JSONArray;
import java.util.List;
import net.demo.excel.common.bean.Column;/*** 导出接口*/
public interface ExportWriter {public static final int EXPORT_PAGE_SIZE = 5000;/*** 写入表头 结构为{"code":"字段名","title":"显示名"}** @param columnList*/public void writeTitle(List<Column> columnList);public <T extends BaseRowModel> void writeTitle(Class<T> t);/*** 添加内容 data的Map结构为{"字段名":"字段值"}** @param dataList* @param columnList*/public void appendContent(JSONArray dataList, List<Column> columnList);public <T extends BaseRowModel> void appendContent(List<T> vos, Class<T> t);/*** 关闭文件流*/public void close();public String getFilePath();public void setFilePath(String filePath);}

Column

package net.demo.excel.common.bean;import lombok.AllArgsConstructor;
import lombok.Data;@Data
@AllArgsConstructor
public class Column {/*** 英文名称*/private String code;/*** 中文名称*/private String title;
}

ExcelExport实现类

model.getSheet() 的sheet的初始化在ExcelExport中完成。new sheet(1, 0, ExcelProperty)第3个参数com.alibaba.excel.annotation.ExcelProperty通过注解来生成表头。

package net.demo.excel.common.export.excel;import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import net.demo.excel.common.bean.Column;
import net.demo.excel.common.export.ExportWriter;
import net.demo.excel.common.export.excel.model.ExcelModel;
import net.demo.excel.common.export.model.ExportModel;
import net.demo.excel.common.util.ExportUtil;import java.io.*;
import java.util.ArrayList;
import java.util.List;/*** excel具体实现*/
@Slf4j
@Data
public class ExcelExport implements ExportWriter {private ExportModel model;private OutputStream os;private String filePath;/*** 初始化** @param os*/public ExcelExport(OutputStream os) {ExcelWriter writer = new ExcelWriter(os, ExcelTypeEnum.XLSX);Sheet sheet = new Sheet(1, 0);sheet.setSheetName("1");this.model = new ExcelModel(writer, sheet);this.os = os;}/*** 写标题** @param columnList*/@Overridepublic void writeTitle(List<Column> columnList) {List<List<String>> dataList = new ArrayList<List<String>>();//组装标题行for (int i = 0; i < columnList.size(); i++) {List<String> titleList = new ArrayList<String>();Column column = columnList.get(i);String title = column.getTitle();titleList.add(title);dataList.add(titleList);}model.getSheet().setHead(dataList);}/*** 写标题*/@Overridepublic <T extends BaseRowModel> void writeTitle(Class<T> t) {List<List<String>> dataList = new ArrayList<List<String>>();String[] columns = ExportUtil.getFieldNames(t);//组装标题行for (int i = 0; i < columns.length; i++) {List<String> titleList = new ArrayList<String>();String title = columns[i];titleList.add(title);dataList.add(titleList);}model.getSheet().setHead(dataList);}/*** 内容追加** @param dataList* @param columnList*/@Overridepublic void appendContent(JSONArray dataList, List<Column> columnList) {List<List<String>> dataArray = new ArrayList<List<String>>();//组装数据行for (Object obj : dataList) {JSONObject json = (JSONObject) JSONObject.toJSON(obj);List<String> rowList = new ArrayList<>();for (int j = 0; j < columnList.size(); j++) {Column columnMap = columnList.get(j);String code = columnMap.getCode();Object value = json.get(code);String valueStr = value == null ? "" : value.toString();rowList.add(valueStr);}dataArray.add(rowList);}model.getExcelWriter().write1(dataArray, model.getSheet());}/*** 追加内容** @param vos* @param t* @param <T>*/@Overridepublic <T extends BaseRowModel> void appendContent(List<T> vos, Class<T> t) {//--start 修复sheet行数据为null时，列对齐错位--List<List<Object>> dataArray = new ArrayList<List<Object>>();for (T vo : vos) {Field[] fields = vo.getClass().getDeclaredFields();List<Object> rowList = new ArrayList<>();for (Field field : fields) {ExcelProperty property = field.getAnnotation(ExcelProperty.class);if (property != null) {try {field.setAccessible(true);Object fieldValue = field.get(vo) == null ? "" : field.get(vo);rowList.add(fieldValue);} catch (IllegalAccessException e) {e.printStackTrace();throw new RuntimeException("写入内容到xlsx失败!");}}}dataArray.add(rowList);}model.getExcelWriter().write(dataArray, model.getSheet());//--end 修复api行数据为null时，列对齐错位--model.getSheet().setClazz(t); //注解来生成表头//model.getExcelWriter().write(vos, model.getSheet());model.getSheet().setAutoWidth(true);}/*** 关闭流*/@Overridepublic void close() {ExcelWriter writer = model.getExcelWriter();try {if (writer != null) {writer.finish();}os.close();} catch (IOException e) {log.error("os close error", e);}}@Overridepublic String getFilePath() {return filePath;}@Overridepublic void setFilePath(String filePath) {this.filePath = filePath;}
}

CsvExport实现类

package net.demo.excel.common.export.csv;import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.csvreader.CsvWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import net.demo.excel.common.bean.Column;
import net.demo.excel.common.export.ExportWriter;
import net.demo.excel.common.export.csv.model.CsvModel;
import net.demo.excel.common.export.model.ExportModel;
import net.demo.excel.common.util.ExportUtil;/*** csv具体实现*/
@Slf4j
@Data
public class CsvExport implements ExportWriter {private ExportModel model;private OutputStream os;private String filePath;/*** 初始化** @param os*/public CsvExport(OutputStream os) {CsvWriter csvWriter = new CsvWriter(os, ',', Charset.forName("GBK"));this.model = new CsvModel(csvWriter);this.os = os;}/*** 写标题** @param columnList*/@Overridepublic void writeTitle(List<Column> columnList) {String[] headers = new String[columnList.size()];for (int i = 0; i < columnList.size(); i++) {Column tmp = columnList.get(i);headers[i] = tmp.getTitle();}try {model.getCsvWriter().writeRecord(headers);} catch (IOException e) {log.error("写入标题到csv失败!", e);throw new RuntimeException("写入标题到csv失败!");}}@Overridepublic <T extends BaseRowModel> void writeTitle(Class<T> t){try {model.getCsvWriter().writeRecord(ExportUtil.getFieldNames(t));} catch (IOException e) {log.error("写入标题到csv失败!", e);throw new RuntimeException("写入标题到csv失败!");}}/*** 内容追加** @param dataList* @param columnList*/@Overridepublic void appendContent(JSONArray dataList, List<Column> columnList) {String[] content = null;List<String> codeList = new ArrayList<String>();for (int i = 0; i < columnList.size(); i++) {Column tmp = columnList.get(i);String code = tmp.getCode();codeList.add(code);}try {for (Object obj : dataList) {JSONObject json = (JSONObject) JSONObject.toJSON(obj);content = new String[codeList.size()];for (int i = 0; i < codeList.size(); i++) {String code = codeList.get(i);Object value = json.get(code);content[i] = value == null ? "" : value.toString();}model.getCsvWriter().writeRecord(content);}} catch (Exception e) {log.error("写入内容到csv失败!", e);throw new RuntimeException("写入内容到csv失败!");}}@Overridepublic <T extends BaseRowModel> void appendContent(List<T> vos, Class<T> t) {try {for (T vo : vos) {String[] fields = ExportUtil.getFields(vo);model.getCsvWriter().writeRecord(fields);}} catch (IOException e) {log.error("写入内容到csv失败!", e);throw new RuntimeException("写入内容到csv失败!");}}/*** 关闭*/@Overridepublic void close() {CsvWriter csvWriter = model.getCsvWriter();if (csvWriter != null) {csvWriter.close();}try {os.close();} catch (IOException e) {log.error("os close error", e);}}@Overridepublic String getFilePath() {return filePath;}@Overridepublic void setFilePath(String filePath) {this.filePath = filePath;}
}

创建导入数据模板类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import lombok.experimental.Accessors;import javax.validation.constraints.NotEmpty;
import java.io.Serializable;/*** 数据导入的Excel模板实体*/
@Data
public class ImportExcelVo implements Serializable {private static final long serialVersionUID = 1L;@ColumnWidth(20)@ExcelProperty(value = "公司名称", index = 0)private String name;@ColumnWidth(20)@ExcelProperty(value = "公司联系电话", index = 1)private String phone;@ColumnWidth(28)@ExcelProperty(value = "公司统一社会信用代码", index = 2)private String creditCode;@ColumnWidth(15)@ExcelProperty(value = "区域", index = 3)private String province;@ColumnWidth(15)@ExcelProperty(value = "公司法人", index = 4)private String legalPerson;@ExcelProperty(value = "备注", index = 5)private String remark;
}

创建数据导出模板

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import lombok.experimental.Accessors;import java.io.Serializable;/*** 资质信息导出实体*/
@Data   // Lombok注解，用于生成getter setter
@Accessors(chain = true) //Lombok注解，链式赋值使用
public class ExportExcelVo extends BaseRowModel {private static final long serialVersionUID = 1L;@ColumnWidth(25)@ExcelProperty(value = "企业名称", index = 0)private String name;@ColumnWidth(25)@ExcelProperty(value = "社会统一信用代码", index = 1)private String creditCode;@ColumnWidth(15)@ExcelProperty(value = "曾用名", index = 2, converter = NullConverter.class)private String formerName;@ColumnWidth(15)@ExcelProperty(value = "公司法人", index = 3)private String legalPerson;@ExcelProperty(value = "区域", index = 4)private String province;@ExcelProperty(value = "录入时间", index = 5)private String createTime;@ColumnWidth(15)@ExcelProperty(value = "公司股东", index = 6)private String stockholder;@ExcelProperty(value = "企业联系方式", index = 7)private String contact;}

使用方法

/*** Excel批量导入数据** @param file 导入文件*/
@RequestMapping(value = "/import", method = RequestMethod.POST)
public CommonResponse<String> importEvents(MultipartFile file) {try {List<?> list = ExportUtil.importExcel(file, ImportExcelVo.class);System.out.println(list);return CommonResponse.success("数据导入完成");} catch (Exception e) {return CommonResponse.error("数据导入失败！" + e.getMessage());}
}//生成excel文件
try {exportWriter = WriterFactory.getExportWriter(dto.getExportType() == null ? "csv" : dto.getExportType(), fileName);exportWriter.writeTitle(PhoneExportVO.class);exportWriter.appendContent(list, PhoneExportVO.class);
} catch (Exception e) {log.error("导出失败", e);throw new BaseException("导出失败");
} finally {if (exportWriter != null) {exportWriter.close();}
}ExportWriter exportWriter = WriterFactory.getExportWriter(exportType, fileName);
List<Column> columns = trunkAreaVOResp.getColumns();
exportWriter.writeTitle(columns);
JSONArray dataList = (JSONArray) JSON.toJSON(trunkAreaVOResp.getTrunkArea());
exportWriter.appendContent(dataList, columns);
exportWriter.close();

EasyExcel自定义转换器Converter

Timestamp 转换器

package com.yandype.util.easyExcel;import java.sql.Timestamp;
import java.text.SimpleDateFormat;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;public class TimestampConverter implements Converter<Timestamp>{@Overridepublic Class<Timestamp> supportJavaTypeKey() {return Timestamp.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic WriteCellData<String> convertToExcelData(Timestamp timestamp, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws Exception {return new WriteCellData<String>(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timestamp));}}

NullConverter

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;public class NullConverter implements Converter<String> {/*** 回到 Java 中的对象类型** @return 支持 Java 类*/@Overridepublic Class supportJavaTypeKey() {return String.class;}/*** * 返回 excel 中的对象枚举* * @return 支持 {@link Cell DataTypeEnum}* */@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/*** 将excel对象转换为Java对象** @param cellData* Excel 单元格数据。NotNull。* @param contentProperty* 内容属性。可空。* @param globalConfiguration* 全局配置。NotNull。* @return 要放入 Java 对象的数据* @抛出异常*             例外。*/@Overridepublic String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return "-".equals(cellData.getStringValue()) ? null : cellData.getStringValue();}/*** 将 Java 对象转换为 excel 对象** @参数值* Java 数据.NotNull。* @param contentProperty* 内容属性。可空。* @param globalConfiguration* 全局配置。NotNull。* @return 数据放入 Excel* @抛出异常*             例外。*/@Overridepublic CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return new CellData<>(null == value ? "-" : value);}
}

使用方法一

每个字段都要添加@ExcelProperty(converter = NullConverter.class)代码，如果遇到大量的数据字段去填充处理会增加很多工作量。转换器仅支持需要被处理的数据字段,也就是适用于从数据库查询出来已有的数据，如日期格式或性别字段做转换时才生效

使用方法二

File uploadFile = File.createTempFile("export", ".xlsx");
String templateFilePath = systemUrl + "/template/exportPublishShop.xlsx";ExcelWriterSheetBuilder excelWriterSheetBuilder = 
EasyExcel.write(uploadFile).registerConverter(new TimestampConverter()).withTemplate(templateFilePath).sheet();List<Map<String, String>> productList = 查询数据数据// productList 如果数据量很大一定要做分页查询，避免占用内存过大
excelWriterSheetBuilder.doFill(productList);