山东省住房和城乡建设厅定额站子网站,网站建设云服务器与虚拟主机,长沙县网页设计培训,安徽省招标投标信息网文章目录 前言一、业务流程二、实现1、引入easyexcel、fastjson、lombok包2、创建Json工具类3、创建自定义字典转换注解4、创建字典转换实现类5、创建数据对象类6、创建多sheet页封装对象7、创建Excel导入工具类8、创建测试类 三、接口测试1、启用项目2、使用数据导出的文件作为导入的文件或者重新编写 四、总结 前言
上次介绍了使用easyexcel导出数据本次介绍使用easyexcel导入数据。
一、业务流程
像导出数据一样导入数据也有对应的业务场景那就是数据输入所以通过页面输入数据遇到的问题导入数据也要处理。下面介绍下数据输入必须要经过业务流程
输入需要的数据属性数据属性和自然语言映射关系将使用者可以理解的自然语言转为数据对象的属性数据字典值和自然语言映射关系将使用者可以理解的自然语言转为属性的字典值
二、实现
1、引入easyexcel、fastjson、lombok包
!--easy excel--
dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion4.0.3/version
/dependency
!--fastjson--
dependencygroupIdcom.alibaba/groupIdartifactIdfastjson/artifactIdversion1.2.46/version
/dependency
!--工具--
dependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdversion1.18.0/version
/dependency2、创建Json工具类
package com.yu.demo.tools;import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.core.type.TypeReference;import java.lang.reflect.Type;
import java.util.Map;/*** JSON工具类** author admin*/
public abstract class JsonUtil {private JsonUtil() {}public final static Type MAP_INTEGER_STRING new TypeReferenceMapInteger, String() {}.getType();/*** json串转Map(Map的value类型一致时使用)** param jsonString json串* return 对象*/public static K, V MapK, V json2Map(String jsonString, Type type) {return JSON.parseObject(jsonString, type);}}3、创建自定义字典转换注解
package com.yu.demo.tools;import java.lang.annotation.*;Target({ElementType.FIELD})
Retention(RetentionPolicy.RUNTIME)
Documented
public interface DictSource {/*** 字典类型主键*/String dictTypeId() default ;/*** 字典内容json串*/String dictContentJson() default ;}4、创建字典转换实现类
package com.yu.demo.web.easyexcel.component;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.yu.demo.web.easyexcel.util.JsonUtil;
import org.apache.poi.util.StringUtil;import java.lang.reflect.Field;
import java.util.Map;
import java.util.Set;public class IntegerDictConverter implements ConverterInteger {/*** 导入支持的字段类型*/Overridepublic Class? supportJavaTypeKey() {return Integer.class;}/*** 导出支持的字段类型*/Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/*** 导入转换*/Overridepublic Integer convertToJavaData(ReadCellData? cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {String stringValue cellData.getStringValue();if (StringUtil.isBlank(stringValue)) {return null;}//获取添加ExcelProperty注解且converter IntegerDictConverter.class的属性Field field contentProperty.getField();//获取该属性的DictConverter注解信息DictSource dictSource field.getAnnotation(DictSource.class);//配置了converter IntegerDictConverter.class的属性但是没有添加DictSource注解的直接强转if (dictSource null) {try {//未配置字典时直接强转return Integer.parseInt(stringValue);} catch (NumberFormatException ignored) {//转化失败时返回空return null;}}//获取配置的dictTypeIdString dictTypeId dictSource.dictTypeId();//获取配置的dictContentJsonString dictContentJson dictSource.dictContentJson();//判断dictTypeId是否为空boolean nullDictType StringUtil.isBlank(dictTypeId);//判断nullDictContentJson是否为空boolean nullDictContentJson StringUtil.isBlank(dictContentJson);//字典配置都为空时直接强转if (nullDictType nullDictContentJson) {try {return Integer.parseInt(stringValue);} catch (NumberFormatException ignored) {//转化失败时返回空return null;}}//优先使用dictTypeId处理转换if (!nullDictType) {//通过dictTypeId获取字典内容集合ListDictContent dictContents dictContentService.listByDictTypeId(dictTypeId);//主键是数值的将dictTypeId转为数值//遍历字典内容匹配输入值与字典名称name.equals(dictContent.getName())//匹配成功后获取字典值返回return dictContent.getValue();//如果没有匹配成功使用dictContentJson处理转换}if (!nullDictContentJson) {MapInteger, String dictContentMap JsonUtil.json2Map(dictContentJson, JsonUtil.MAP_INTEGER_STRING);SetMap.EntryInteger, String entrySet dictContentMap.entrySet();for (Map.EntryInteger, String entry : entrySet) {if (stringValue.equals(entry.getValue())) {return entry.getKey();}}}//没有转换成功时直接强转try {return Integer.parseInt(stringValue);} catch (NumberFormatException ignored) {//转化失败时返回空return null;}}/*** 导出转换*/Overridepublic WriteCellData? convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {//属性值为空时直接返回if (value null) {//为空时的处理与前端展示保持一致即可return new WriteCellData();}//获取添加ExcelProperty注解且converter IntegerDictConverter.class的属性Field field contentProperty.getField();//获取该属性的DictConverter注解信息DictSource dictSource field.getAnnotation(DictSource.class);//配置了converter IntegerDictConverter.class的属性但是没有添加DictSource注解的直接返回if (dictSource null) {return new WriteCellData(String.valueOf(value));}//获取配置的dictTypeIdString dictTypeId dictSource.dictTypeId();//获取配置的dictContentJsonString dictContentJson dictSource.dictContentJson();//判断dictTypeId是否为空boolean nullDictType StringUtil.isBlank(dictTypeId);//判断nullDictContentJson是否为空boolean nullDictContentJson StringUtil.isBlank(dictContentJson);//字典配置都为空时将属性值转为字符串直接返回if (nullDictType nullDictContentJson) {return new WriteCellData(String.valueOf(value));}//优先使用dictTypeId处理转换if (!nullDictType) {//通过dictTypeId获取字典内容集合ListDictContent dictContents dictContentService.listByDictTypeId(dictTypeId);//主键是数值的将dictTypeId转为数值//遍历字典内容匹配属性值与字典值value.equals(dictContent.getValue())//匹配成功后获取字典名称返回return new WriteCellData(dictContent.getName());//如果没有匹配成功使用dictContentJson处理转换}if (!nullDictContentJson) {MapInteger, String dictContentMap JsonUtil.json2Map(dictContentJson, JsonUtil.MAP_INTEGER_STRING);String cnName dictContentMap.get(value);if (StringUtil.isNotBlank(cnName)) {return new WriteCellData(cnName);}}//没有转换成功时使用默认属性值return new WriteCellData(String.valueOf(value));}
}5、创建数据对象类
package com.yu.demo.web.easyexcel.entity;import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.converters.date.DateStringConverter;
import com.yu.demo.web.easyexcel.component.DictSource;
import com.yu.demo.web.easyexcel.component.IntegerDictConverter;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;import java.util.Date;Setter
Getter
ToString
//类上添加ExcelIgnoreUnannotated时属性没有ExcelProperty注解时不导出
//类上未添加ExcelIgnoreUnannotated属性没有ExcelProperty注解时也导出
ExcelIgnoreUnannotated
public class User {/*** 名称*/ExcelProperty(名称)private String name;/*** 密码* 类添加ExcelIgnoreUnannotated属性未添加ExcelProperty不导出*/private String password;/*** 生日* 日期样式处理* 1.使用DateTimeFormat设置导出样式* 2.使用DateStringConverter处理导出*/DateTimeFormat(yyyy-MM-dd HH:mm:ss)ExcelProperty(value 生日, converter DateStringConverter.class)private Date birthday;/*** 性别* 字典转换处理*/ColumnWidth(7)//指定列宽度优先级高于LongestMatchColumnWidthStyleStrategyExcelProperty(value 性别, converter IntegerDictConverter.class)DictSource(dictContentJson {0:女,1:男,2:保密})private Integer sex;}6、创建多sheet页封装对象
package com.yu.demo.tools;import lombok.Getter;
import lombok.Setter;
import lombok.ToString;import java.util.List;/*** excel导入导出数据对象*/
Setter
Getter
ToString
public class SheetEntityT {/*** sheet页名称导出参数* 可以为空为空时单sheet页没有名称多sheet页序号为名称*/private String sheetName;/*** 数据类型导入导出参数*/private ClassT head;/*** 数据导出参数*/private ListT data;/*** 读取数据监听器导入参数*/private ReadListenerT readListener;}7、创建Excel导入工具类
导入数据说明
通过文件或者文件流导入导入的数据同步方式写入集合适合小数据量导入的数据异步方式写入集合适合大数据量
package com.yu.demo.web.easyexcel.util;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.yu.demo.web.easyexcel.entity.SheetEntity;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.util.StringUtil;import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;/*** excel导入导出工具类easyExcel实现* easyPoi并发量和数据量都不大时推荐定制化的导出支持非常的丰富* easyExcel高并发、大数据量时推荐*/
public abstract class ExcelUtil {// 设置居中对齐的样式private static final WriteCellStyle CONTENT_WRITE_CELL_STYLE;private static final WriteHandler HORIZONTAL_CELL_STYLE_STRATEGY;static {CONTENT_WRITE_CELL_STYLE new WriteCellStyle();//水平居中CONTENT_WRITE_CELL_STYLE.setHorizontalAlignment(HorizontalAlignment.CENTER);//垂直居中CONTENT_WRITE_CELL_STYLE.setVerticalAlignment(VerticalAlignment.CENTER);HORIZONTAL_CELL_STYLE_STRATEGY new HorizontalCellStyleStrategy(null, CONTENT_WRITE_CELL_STYLE);}private ExcelUtil() {}/*** 使用EasyExcel导出** param fullFileName 文件路径文件名后缀文件已存在时覆盖目录不存在时Windows报错linux不报错* param sheetName sheet名称为空时使用默认值0* param head 数据类型为空时没有表头只有数据* param exportData 需要导出的数据为空时没有数据*/public static void exportByEasyExcel(String fullFileName, String sheetName, Class? head, List? exportData) {File targetFile new File(fullFileName);// 判断文件父目录是否存在if (!targetFile.getParentFile().exists()) {boolean mkdirResult targetFile.getParentFile().mkdirs();if (!mkdirResult) {return;}}ExcelWriterBuilder excelWriterBuilder EasyExcel.write(targetFile, head);if (fullFileName.endsWith(ExcelTypeEnum.XLS.getValue())) {excelWriterBuilder.excelType(ExcelTypeEnum.XLS);} else if (fullFileName.endsWith(ExcelTypeEnum.CSV.getValue())) {excelWriterBuilder.excelType(ExcelTypeEnum.CSV);} else {excelWriterBuilder.excelType(ExcelTypeEnum.XLSX);}excelWriterBuilder//设置列按最大长度调整.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//设置水平垂直居中.registerWriteHandler(HORIZONTAL_CELL_STYLE_STRATEGY).sheet(sheetName).doWrite(exportData);}/*** 使用EasyExcel导出** param outputStream 输出流* param sheetName sheet名称为空时使用默认值0* param head 数据类型为空时没有表头只有数据* param exportData 需要导出的数据为空时没有数据*/public static void exportByEasyExcel(OutputStream outputStream, ExcelTypeEnum excelType, String sheetName, Class? head, List? exportData) {EasyExcel.write(outputStream, head).excelType(excelType)//设置列按最大长度调整非线程安全每次都需要new.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//设置水平垂直居中.registerWriteHandler(HORIZONTAL_CELL_STYLE_STRATEGY).sheet(sheetName).doWrite(exportData);}/*** 使用EasyExcel导出多sheet页数据** param outputStream 输出流* param sheetEntities 导出数据对象集合*/public static void exportByEasyExcel(OutputStream outputStream, ExcelTypeEnum excelType, ListSheetEntity? sheetEntities) {ExcelWriterBuilder excelWriterBuilder EasyExcel.write(outputStream).excelType(excelType);writeSheets(excelWriterBuilder, sheetEntities);}/*** 同步导入适合小数据量** param inputStream 数据文件流*/public static T ListT importByEasyExcel(InputStream inputStream, ClassT head) {return EasyExcel.read(inputStream).head(head).sheet().doReadSync();}/*** 异步导入解析的数据通过回调函数返回适合大数据量** param inputStream 数据文件流* param head 数据类型* param readListener 回调监听器*/public static void importByEasyExcel(InputStream inputStream, Class? head, ReadListener? readListener) {EasyExcel.read(inputStream, head, readListener).sheet().doRead();}/*** 多sheet页导入** param inputStream 数据文件流* param sheetEntities 导入数据对象集合*/public static void importByEasyExcel(InputStream inputStream, ListSheetEntity? sheetEntities) {if (inputStream null || CollectionUtils.isEmpty(sheetEntities)) {return;}ExcelReader excelReader EasyExcel.read(inputStream).build();readSheets(excelReader, sheetEntities);}/*** 多sheet页导入** param file 数据文件* param sheetEntities 导入数据对象集合*/public static void importByEasyExcel(File file, ListSheetEntity? sheetEntities) {if (file null || CollectionUtils.isEmpty(sheetEntities)) {return;}ExcelReader excelReader EasyExcel.read(file).build();readSheets(excelReader, sheetEntities);}private static void readSheets(ExcelReader excelReader, ListSheetEntity? sheetEntities) {ListReadSheet readSheets new ArrayList(sheetEntities.size());for (int i 0; i sheetEntities.size(); i) {SheetEntity? sheetEntity sheetEntities.get(i);ReadSheet readSheet EasyExcel.readSheet(i).head(sheetEntity.getHead()).registerReadListener(sheetEntity.getReadListener()).build();readSheets.add(readSheet);}excelReader.read(readSheets);}private static void writeSheets(ExcelWriterBuilder excelWriterBuilder, ListSheetEntity? sheetEntities) {excelWriterBuilder.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(HORIZONTAL_CELL_STYLE_STRATEGY);ExcelWriter excelWriter excelWriterBuilder.build();for (int i 0; i sheetEntities.size(); i) {SheetEntity? sheetEntity sheetEntities.get(i);Class? head sheetEntity.getHead();List? exportData sheetEntity.getData();String sheetName StringUtil.isBlank(sheetEntity.getSheetName()) ? String.valueOf(i 1) : sheetEntity.getSheetName();WriteSheet writeSheet EasyExcel.writerSheet(i 1, sheetName).head(head).build();excelWriter.write(exportData, writeSheet);}excelWriter.finish();}}
8、创建测试类
package com.yu.demo.web.easyexcel.web;import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.yu.demo.web.easyexcel.entity.SheetEntity;
import com.yu.demo.web.easyexcel.entity.User;
import com.yu.demo.web.easyexcel.util.ExcelUtil;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpHeaders;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;RestController
RequestMapping(user)
public class UserController {Value(${download.path})private String filePath;private ListUser users;private ListSheetEntity? sheetEntities;PostConstructpublic void init() {users new ArrayList(5);for (int i 0; i 5; i) {User user new User();user.setName(i 号用户);user.setPassword(String.valueOf(i * 1000));user.setBirthday(new Date());user.setSex(i % 3);users.add(user);}sheetEntities new ArrayList(2);for (int i 0; i 2; i) {SheetEntityUser sheetEntity new SheetEntity();sheetEntity.setSheetName(i 号sheet);sheetEntity.setHead(User.class);sheetEntity.setData(users);sheetEntities.add(sheetEntity);}}/*** 单sheet页通过全路径文件名导出测试接口也可以通过文件流导出* 返回文件名前端通过web路径文件名下载文件*/GetMapping(/filePath)public String filePath() {String fileName 用户.xlsx;String fullFileName filePath fileName;ExcelUtil.exportByEasyExcel(fullFileName, 用户, User.class, users);return fileName;}/*** 多sheet页通过文件流导出也可以通过全路径文件名导出*/GetMapping(/download)public void download(HttpServletResponse response) throws IOException {String fileName 用户;response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(StandardCharsets.UTF_8.name());String encodeFileName URLEncoder.encode(fileName, UTF-8).replaceAll(\\, %20);response.setHeader(HttpHeaders.CONTENT_DISPOSITION, attachment;filename*utf-8 encodeFileName ExcelTypeEnum.XLSX.getValue());ExcelUtil.exportByEasyExcel(response.getOutputStream(), ExcelTypeEnum.XLSX, sheetEntities);}/*** 上传数据* 单sheet页同步导入示例接口*/PostMapping(/upload)public String upload(MultipartFile file) throws IOException {ListUser list ExcelUtil.importByEasyExcel(file.getInputStream(), User.class);//根据业务处理数据这里直接打印数据System.out.println(list);return success;}/*** 上传数据* 多sheet页异步导入示例接口*/PostMapping(/upload2)public String upload2(MultipartFile file) throws IOException {ListSheetEntity? sheetEntities new ArrayList(2);sheetEntities.add(getUserImportEntity());//多sheet页时按照顺序添加SheetEntitysheetEntities.add(getUserImportEntity());ExcelUtil.importByEasyExcel(file.getInputStream(), sheetEntities);return success;}private SheetEntityUser getUserImportEntity() {SheetEntityUser sheetEntity new SheetEntity();sheetEntity.setHead(User.class);//根据业务处理数据这里直接打印数据ReadListenerUser pageReadListener new PageReadListener(System.out::println);sheetEntity.setReadListener(pageReadListener);return sheetEntity;}
}
三、接口测试
1、启用项目 2、使用数据导出的文件作为导入的文件或者重新编写
多sheet页导出接口地址http://localhost:8080/user/download获取导入的文件单sheet页导入接口地址http://localhost:8080/user/upload多sheet页导入接口地址http://localhost:8080/user/upload2postman测试接口同文件一个单sheet页时只解析第一个sheet页 测试结果 四、总结
使用Entity对象作为关系映射的载体使用ExcelProperty注解映射属性名称并可以指定转换器、序号等信息使用自定义注解DictSource注解和指定转换器转换字典值数据导入日期类型格式要和Entity中日期属性的注解DateTimeFormat(“yyyy-MM-dd HH:mm:ss”)中格式保持一致SpringBoot集成easyexcel数据导入案例下载
