easypoi结合MySQL导入_Springboot+EasyPOI+Mybatis 进行对MySQL数据库内表进行导入导出

Springboot+EasyPOI+Mybatis 进行对MySQL数据库内表进行导入导出

最近写功能用到导入导出，在网上发现easypoi，非常好用，个人感觉非常适合中小型项目的使用。

首先感谢 easypoi官方教程

还有俩位大佬的技术支持

https://www.jianshu.com/p/b49459fe3c06

https://www.jianshu.com/p/5d67fb720ece

个人感觉easypoi不像poi代码量那么大，非常轻巧，可以快速上手的api

好了，废话不多说实战and代码

导包 配置pom.xml

cn.afterturn

easypoi-base

3.0.3

cn.afterturn

easypoi-web

3.0.3

cn.afterturn

easypoi-annotation

3.0.3

还有一个jar包是我lib里面放着的，这里注意一下，如果版本是3.3.1的话会进行报错，具体原因我也很懵，之前我用3.3.1启动springboot就报错，后来解决错误时，是因为这个jar包版本太低导致，所以我强烈推荐用3.3.9版本的包

工具类 WebExcelUtil

这个工具类我借鉴了大佬们的工具类，在此感谢上面的大佬

import java.io.File;

import java.io.IOException;

import java.net.URLEncoder;

import java.util.List;

import java.util.Map;

import java.util.NoSuchElementException;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;

import org.apache.poi.ss.usermodel.Workbook;

import org.springframework.web.multipart.MultipartFile;

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;

/**

*

* @ClassName: WebExcelUtil.java

* @Description: EasyPOI Excel 导入导出工具类

* @author ajia

*

*/

public class WebExcelUtil {

public static void exportExcel(List> list, String title, String sheetName, Class> pojoClass, String fileName,

boolean isCreateHeader, HttpServletResponse response) throws Exception {

ExportParams exportParams = new ExportParams(title, sheetName);

exportParams.setCreateHeadRows(isCreateHeader);

defaultExport(list, pojoClass, fileName, response, exportParams);

}

/**

*

* @Title: exportExcel

* @Description: 常用导出

* @return void

*/

public static void exportExcel(List> list, String title, String sheetName, Class> pojoClass, String fileName,

HttpServletResponse response) {

try {

defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void exportExcel(List> list, String fileName, HttpServletResponse response) {

try {

defaultExport(list, fileName, response);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/**

*

* @Title: defaultExport

* @Description: 默认导出

* @return void

*/

private static void defaultExport(List> list, Class> pojoClass, String fileName, HttpServletResponse response,

ExportParams exportParams) throws Exception {

Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);

if (workbook != null)

;

downLoadExcel(fileName, response, workbook);

}

/**

*

* @Title: downLoadExcel

* @Description: 下载excel

* @return void

*/

private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)

throws Exception {

try {

response.setCharacterEncoding("UTF-8");

response.setHeader("content-Type", "application/vnd.ms-excel");

response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

workbook.write(response.getOutputStream());

} catch (IOException e) {

throw new Exception(e.getMessage());

}

}

private static void defaultExport(List> list, String fileName, HttpServletResponse response)

throws Exception {

Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);

if (workbook != null)

;

downLoadExcel(fileName, response, workbook);

}

public static ListimportExcel(String filePath, Integer titleRows, Integer headerRows, ClasspojoClass)

throws Exception {

if (StringUtils.isBlank(filePath)) {

return null;

}

ImportParams params = new ImportParams();

params.setTitleRows(titleRows);

params.setHeadRows(headerRows);

Listlist = null;

try {

list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);

} catch (NoSuchElementException e) {

throw new Exception("模板不能为空");

} catch (Exception e) {

e.printStackTrace();

throw new Exception(e.getMessage());

}

return list;

}

public static ListimportExcel(MultipartFile file, Integer titleRows, Integer headerRows, ClasspojoClass)

throws Exception {

if (file == null) {

return null;

}

ImportParams params = new ImportParams();

params.setTitleRows(titleRows);

params.setHeadRows(headerRows);

Listlist = null;

try {

list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);

} catch (NoSuchElementException e) {

throw new Exception("excel文件不能为空");

} catch (Exception e) {

throw new Exception(e.getMessage());

}

return list;

}

}

这个没有太多描述的感兴趣的去easypoi官网研究，开源代码

首先我们先写导出功能

Controller 编写

@PostMapping("/cfq/export")

public void export(HttpServletResponse response) {

Listlist = cfqservice.list();

logger.info("----/cfq/export---导出----");

WebExcelUtil.exportExcel(list, "设备表", "sheet1", IotCfq.class, "cfqtable.xls", response);

}

你没看错就俩行代码直接导出，其中调了一个list查询方法，对表内数据的查询方法。

Service and Serviceimp

Service 的一个接口 提示IotCfq是我对应数据库表内的实体类

Listlist();

@Override

public Listlist() {

return cfqmapper.list();

}

这里写了一个测试的所有没那么多业务代码

mapper and mapper.xml

Listlist();

需要注意的是实体类

首先你必须的有个空构造函数

然后每一个实例类上面写注解

@Excel(name = "设备编号", width = 25,orderNum = "0")

private String sebbh;

name是导入或者导出Excel对应的列名，width是Excel表列宽

这个可以去官方文档进行看，easypoi的核心就在这个注解上

以上就是导出的内容。

接下来进行写导入

工具类不变直接写controller

@PostMapping("/cfq/import")

public void upload(MultipartFile file, HttpServletRequest request) throws Exception {

ImportParams params = new ImportParams();

params.setTitleRows(0);

params.setHeadRows(1);

Listlist;

try {

// excel的数据

list = WebExcelUtil.importExcel(file, 1, 1, IotCfq.class);

/*这里是定义中循环遍历的方式，我觉得太麻烦就用了第二种

* for (int i = 0; i < list.size(); i++) { IotCfq cfq = new IotCfq();

* cfq.setSebbh(list.get(i).getSebbh()); cfq.setBjfs(list.get(i).getBjfs());

* cfq.setCfqtype(list.get(i).getCfqtype());

* cfq.setCuftj(list.get(i).getCuftj()); cfq.setMinc(list.get(i).getMinc());

* cfq.setUpdatetime(new Date()); cfq.setCfqzt(list.get(i).getCfqzt());

* cfq.setFzid(list.get(i).getFzid());

*

* // cfqservice.add(cfq); }

*/

for (IotCfq icq : list) {

IotCfq cfq = new IotCfq();

cfq.setSebbh(icq.getSebbh());

cfq.setBjfs(icq.getBjfs());

cfq.setCfqtype(icq.getCfqtype());

cfq.setCuftj(icq.getCuftj());

cfq.setMinc(icq.getMinc());

cfq.setUpdatetime(new Date());

cfq.setCfqzt(icq.getCfqzt());

cfq.setFzid(icq.getFzid());

cfqservice.add(cfq);//调用了一个新增的方法

}

logger.info("---/cfq/import---导入---");

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

导入就是将excel表内数据存储的数据库，无非就是遍历存储，这里调用可一个存储的方法add，其余只要细心无太大问题。尤其注意实体类上不想让其导入导出的字段，不用打、@Excel 就行