java实现Excel的导入导出

最近遇到了这么一个功能，要实现Excel文件的导入导出功能，下面直接上代码好了：

项目目录图：

导入导出主要使用了这三个pom依赖：

<!-- 导入导出 --> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>4.0.0</version> </dependency>

yml配置

#配置数据源 spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/mybatisplus?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8& username: root password: root main: allow-bean-definition-overriding: on #指定mybatis映射文件的地址 mybatis: mapper-locations: classpath:mapper/*.xml

UserController 代码：

package easypoi.controller; import easypoi.entity.User; import easypoi.service.UserService; import easypoi.utils.ExcelUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.util.List; @RestController public class UserController { @Autowired private UserService userService; /** * * @Title: impUser * @Description: excle导入 * @param file * @return String */ @PostMapping("/impUser") public String impUser(MultipartFile file){ List<User> users = ExcelUtils.importData(file, 1, User.class); userService.insertAll(users); return "success"; } /** * * @Title: expUser * @Description: 导出excel * @param response * @return void */ @GetMapping("/expUser") public void expUser(HttpServletResponse response){ List<User> users = userService.select(); if(users != null && users.size() > 0){ ExcelUtils.exportExcel(users, null, "用户数据", User.class, "用户数据表.xls", response); } } }

User实体类

package easypoi.entity; import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Data; @Data public class User { @Excel(name = "id") private Long id; @Excel(name = "姓名") private String name; @Excel(name = "年龄") private Integer age; }

mapper接口：

package easypoi.mapper; import easypoi.entity.User; import org.apache.ibatis.annotations.Mapper; import java.util.List; @Mapper public interface UserMapper { void insertAll(User user); List<User> selectAll(); }

UserService：

package easypoi.service; import easypoi.entity.User; import easypoi.mapper.UserMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class UserService { @Autowired private UserMapper userMapper; public void insertAll(List<User> users) { for (User user : users) { userMapper.insertAll(user); } } public List<User> select() { List<User> users = userMapper.selectAll(); return users; } }

核心 导入导出工具类

package easypoi.utils; 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 cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import lombok.extern.slf4j.Slf4j; 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; /** * * @ClassName: ExcelUtils * @Description: excle工具类 * @author chenqi * @date 2018年11月17日 * */ @Slf4j public class ExcelUtils { /** * * @Title: importData * @Description: 导入excle 数据 * @param file 文件 * @param headerRows 忽略头行数 * @param pojoClass 转换的实体 * @return List<User> 返回的集合 */ public static <T> List<T> importData(MultipartFile file, Integer headerRows, Class<T> pojoClass){ if (file == null) { return null; } ImportParams params = new ImportParams(); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } return list; } /** * * @Title: exportExcel * @Description: 导出excel * @param list 导出的数据 * @param title 文件标题 * @param sheetName sheet名称 * @param pojoClass 集合的类 * @param fileName 文件名 * @param response * @return void */ public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,HttpServletResponse response) { Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title, sheetName), pojoClass, list); if (workbook != null) { try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1")); workbook.write(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } } } }

UserMapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="easypoi.mapper.UserMapper"> <insert id="insertAll" parameterType="easypoi.entity.User"> insert into mybatisplus.user (name, age) values (#{name},#{age}) </insert> <select id="selectAll" resultType="easypoi.entity.User"> select * from user; </select> </mapper>

到此整个代码部分就完成了，大家就可以到postman中去测试导入功能了，也可以在浏览器中测试导出功能，在导入时需要注意使用post方法，并且注意

好了，今天的分享就到这里了。希望能帮助到大家！！！