springboot集成easyPoi实现excel数据导入到Mysql数据库
springboot集成easyPoi实现excel数据导入到Mysql数据库
-
easyPoi官方文档:
-
通过Mybatis generator生成相关Tbxxx,TbxxxExample,TbxxxMapper.java,TbxxxMapper.xml,TbxxxService
-
引入依赖:
cn.afterturn easypoi-base 3.2.0 cn.afterturn easypoi-web 3.2.0 cn.afterturn easypoi-annotation 3.2.0 -
注解实体类:
import cn.afterturn.easypoi.excel.annotation.Excel; import com.yun.cloud.base.model.BaseModel; import lombok.Data; import java.io.Serializable; @Data public class TbProductStandard extends BaseModel implements Serializable { private String id; @Excel(name = "code") private String code; @Excel(name = "parent_code") private String parentId; @Excel(name = "type_name") private String typeName; }
相关注解介绍:
@Excel 作用到实体类字段上面,是对Excel一列的一个描述
@ExcelCollection 表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示
@ExcelEntity 表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段
@ExcelIgnore 和名字一样表示这个字段被忽略跳过这个导导出
@ExcelTarget 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理
————————————————
原文链接:- 受注解的excel格式:
-
设置excel导入导出工具类:
import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.ImportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.net.URLEncoder; import java.util.List; import java.util.Map; import java.util.NoSuchElementException; public class ProductExcelUtils { public static void exportExcel(List> list, String title, String sheetName, Class> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) { ExportParams exportParams = new ExportParams(title, sheetName); exportParams.setCreateHeadRows(isCreateHeader); defaultExport(list, pojoClass, fileName, response, exportParams); } public static void exportExcel(List> list, String title, String sheetName, Class> pojoClass, String fileName, HttpServletResponse response) { defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName)); } public static void exportExcel(List
-
controller层:
import com.yun.cloud.base.result.Result; import com.yun.cloud.gap.business.ProductStandardBusiness; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; @RestController @RequestMapping("/productstandard") public class ProductStandardController { @Autowired private ProductStandardBusiness productStandardBusiness; /** * 导入excel * @param file * @return */ @PostMapping("/importExcel") public Result importExcel(@RequestParam("file") MultipartFile file) { return productStandardBusiness.importExcel(file); } }
-
business层:
import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ImportParams; import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult; import com.alibaba.fastjson.JSONObject; import com.yun.cloud.base.result.Result; import com.yun.cloud.base.utils.IdGenUtil; import com.yun.cloud.gap.model.TbProductStandard; import com.yun.cloud.gap.model.TbProductStandardExample; import com.yun.cloud.gap.service.TbProductStandardService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.util.List; @Slf4j @Service("ProductStandardBusiness") public class ProductStandardBusiness { @Autowired private TbProductStandardService tbProductStandardService; @Value("${machine}") private int machineId; @Value("${datacenter}") private int dataCenterId; public Result importExcel(MultipartFile file) { ImportParams importParams = new ImportParams(); // 数据处理 importParams.setHeadRows(1); importParams.setTitleRows(1); // 需要验证 importParams.setNeedVerfiy(false); try { ExcelImportResult
result = ExcelImportUtil.importExcelMore(file.getInputStream(), TbProductStandard.class, importParams); List tbProductStandardList = result.getList(); for (TbProductStandard tbProductStandard : tbProductStandardList) { log.info("从Excel导入数据到数据库的详细为 :{}", JSONObject.toJSONString(tbProductStandard)); //保存到mysql String id = IdGenUtil.getNextId(machineId,dataCenterId); tbProductStandard.setId(id); int i = tbProductStandardService.insertSelective(tbProductStandard); if(i < 1){ log.error("保存失败"); return Result.err("保存失败"); } } log.info("从Excel导入数据一共 {} 行 ", tbProductStandardList.size()); } catch (Exception e) { log.error("导入失败:{}", e.getMessage()); return Result.err("导入失败"); } return Result.suc(); } } -
测试
原文:https://www.cnblogs.com/horseweed/p/15134814.html