设为首页收藏本站
开启辅助访问

Java Excel的数据导入导出

 您所在的位置：网站首页 › java使用poi导出excel工具类 › Java Excel的数据导入导出

Java Excel的数据导入导出

2023-04-01 02:03| 来源: 网络整理| 查看: 265

引入依赖 com.alibaba easyexcel 2.2.7 net.sourceforge.javacsv javacsv 2.0 数据读取监听

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

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 Excel中数据的类型 */ @Getter @Setter @NoArgsConstructor public class ExcelListener extends AnalysisEventListener { // 加入一个判断标签，判断数据是否已经读取完 private volatile boolean retryLock = false; // 解析完成后的数据集合, 监听对象初始化之后，立即初始化集合对象 private final List dataList = new ArrayList(); // 每次最多导入条数 private final int batchSize = 2000; /** * 获取解析后的数据集合， 如果数据还没有被解析完成，会对读取该集合的线程进行阻塞，直到数据读取完成之后，进行解锁。 * 如果一次导入数据超过batchSize条，则以抛异常的形式阻止导入数据 * @return 解析后的数据集合 */ public List 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 */ @Override public void invoke(T data, AnalysisContext context) { dataList.add(data); } /** * 读取表头内容 * @param headMap 表头部数据 * @param context 数据解析上下文 */ @Override public void invokeHeadMap(Map headMap, AnalysisContext context) { //System.out.println("表头：" + headMap); } /** * 流中的数据解析完成之后，就会调用此方法 * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 数据解析完成，解锁 retryLock = true; } /** * 解析过程如果发生异常，会调用此方法 * @param exception * @param context */ @Override public 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 String[] getFieldNames(Class tClass) { Field[] fields = tClass.getDeclaredFields(); List 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 * @return */ public static String[] getFields(T vo) { Field[] fields = vo.getClass().getDeclaredFields(); List 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 void export(String type, List vos, HttpServletResponse response, Class 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 void export(String type,String fileName, List vos, HttpServletResponse response, Class 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 dataList = null; BufferedInputStream ipt = null; try { InputStream is = file.getInputStream(); // 用缓冲流对数据流进行包装 ipt = new BufferedInputStream(is); // 数据解析监听器 ExcelListener 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; @Override public ExcelWriter getExcelWriter() { return null; } @Override public Sheet getSheet() { return null; } @Override public 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; @Override public ExcelWriter getExcelWriter() { return excelWriter; } @Override public Sheet getSheet() { return sheet; } @Override public 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 columnList); public void writeTitle(Class t); /** * 添加内容 data的Map结构为{"字段名":"字段值"} * * @param dataList * @param columnList */ public void appendContent(JSONArray dataList, List columnList); public void appendContent(List vos, Class 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 */ @Override public void writeTitle(List columnList) { List dataList = new ArrayList(); //组装标题行 for (int i = 0; i < columnList.size(); i++) { List titleList = new ArrayList(); Column column = columnList.get(i); String title = column.getTitle(); titleList.add(title); dataList.add(titleList); } model.getSheet().setHead(dataList); } /** * 写标题 */ @Override public void writeTitle(Class t) { List dataList = new ArrayList(); String[] columns = ExportUtil.getFieldNames(t); //组装标题行 for (int i = 0; i < columns.length; i++) { List titleList = new ArrayList(); String title = columns[i]; titleList.add(title); dataList.add(titleList); } model.getSheet().setHead(dataList); } /** * 内容追加 * * @param dataList * @param columnList */ @Override public void appendContent(JSONArray dataList, List columnList) { List dataArray = new ArrayList(); //组装数据行 for (Object obj : dataList) { JSONObject json = (JSONObject) JSONObject.toJSON(obj); List 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 */ @Override public void appendContent(List vos, Class t) { //--start 修复sheet行数据为null时，列对齐错位-- List dataArray = new ArrayList(); for (T vo : vos) { Field[] fields = vo.getClass().getDeclaredFields(); List 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); } /** * 关闭流 */ @Override public void close() { ExcelWriter writer = model.getExcelWriter(); try { if (writer != null) { writer.finish(); } os.close(); } catch (IOException e) { log.error("os close error", e); } } @Override public String getFilePath() { return filePath; } @Override public 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 */ @Override public void writeTitle(List 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失败!"); } } @Override public void writeTitle(Class t){ try { model.getCsvWriter().writeRecord(ExportUtil.getFieldNames(t)); } catch (IOException e) { log.error("写入标题到csv失败!", e); throw new RuntimeException("写入标题到csv失败!"); } } /** * 内容追加 * * @param dataList * @param columnList */ @Override public void appendContent(JSONArray dataList, List columnList) { String[] content = null; List codeList = new ArrayList(); 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失败!"); } } @Override public void appendContent(List vos, Class 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失败!"); } } /** * 关闭 */ @Override public void close() { CsvWriter csvWriter = model.getCsvWriter(); if (csvWriter != null) { csvWriter.close(); } try { os.close(); } catch (IOException e) { log.error("os close error", e); } } @Override public String getFilePath() { return filePath; } @Override public 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; } 创建数据导出模板 ExcelProperty注解

可以定义表头的名称。这个注解还提供了index、order两个属性，可以定义列的位置和顺序。

 1）关于index

如果所有字段都不加index的话，默认index会从0开始，最早的声明的字段的名字的index的值就是0。之后字段的index就从0开始递增，依次类推。index是几决定了该字段数据会赋值给Excel中的第几列。如果不想按这个顺序把数据写到Excel当中。那么就可以手动设置index的值，把字段写到想要的列中去。如果index相同，直接会抛出异常，因为程序无法判断这个列放那个字段。

2）关于order

index和order虽然都决定顺序，但是两者语义不同：如果order和index同时使用，index优先占据位置，order再进行排序。index=-1的话，使用jJava进行默认排序。order的默认值为Integer.MAX_VALUE，其中order的值越小，列越靠前。

其他注解 @DateTimeFormat 日期转换，用String去接收excel日期格式的数据会调用这个注解。@NumberFormat 数字转换，用String去接收excel数字格式的数据会调用这个注解。@ColumnWith  设置列宽度。只有一个参数value，value的单位是字符长度，最大可以设置255个字符。 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; @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒") @ExcelProperty(value = "录入时间", index = 5) private String createTime; @ColumnWidth(15) @ExcelProperty(value = "公司股东", index = 6) private String stockholder; @ExcelProperty(value = "企业联系方式", index = 7) private String contact; @NumberFormat("#.##%") // 使用百分比格式格式化这个数字 @ExcelProperty("数字") private Double doubleData; } 使用方法 /** * Excel批量导入数据 * * @param file 导入文件 */ @RequestMapping(value = "/import", method = RequestMethod.POST) public CommonResponse 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 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{ @Override public Class supportJavaTypeKey() { return Timestamp.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public WriteCellData convertToExcelData(Timestamp timestamp, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { return new WriteCellData(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 { /** * 回到 Java 中的对象类型 * * @return 支持 Java 类 */ @Override public Class supportJavaTypeKey() { return String.class; } /** * * 返回 excel 中的对象枚举 * * @return 支持 {@link Cell DataTypeEnum} * */ @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } /** * 将excel对象转换为Java对象 * * @param cellData * Excel 单元格数据。NotNull。 * @param contentProperty * 内容属性。可空。 * @param globalConfiguration * 全局配置。NotNull。 * @return 要放入 Java 对象的数据 * @抛出异常 * 例外。 */ @Override public 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 * @抛出异常 * 例外。 */ @Override public 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 productList = 查询数据数据 // productList 如果数据量很大一定要做分页查询，避免占用内存过大 excelWriterSheetBuilder.doFill(productList);



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3