使用SpringMVC+Layui操作excel的导入导出

导入导出所需的maven依赖

 <!-- EasyPOI核心依赖 基于Apache POI 所以需要下面那两个Apache POI依赖 -->
<!--  作用:Excel 导入导出和数据校验--><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.0</version> <!-- 请替换为实际使用的最新版本号 --></dependency><!-- 如果EasyPOI没有自带Apache POI依赖，或者您需要特定版本的Apache POI，可以添加以下依赖 --><!-- Apache POI依赖 --><!-- 作用:读写excel文件,操作excel表格--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.3</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version></dependency><!--通常与 Apache POI 一起使用来处理 Excel 文件中的 XML 结构--><dependency><groupId>org.apache.xmlbeans</groupId><artifactId>xmlbeans</artifactId><version>5.1.1</version></dependency><!--输出输入流 操作excel时需要用到-->
<!--作用:处理 Excel 文件的读写操作，如复制文件、读取文件内容到内存中、将内存中的数据写入文件等--><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.11.0</version> <!-- 使用最新的稳定版本 --></dependency>

一，导出excel表格
1.1，controller控制层
把获取的数据解析为User类实例，之后进行数据校验，校验不通过返回给前端，校验通过则把User实例添加到List集合中 之后操作数据库进行插入操作

  @PostMapping(value = "/uuApplyUserInfo")public ApiResult importMonitor( MultipartFile file) throws Exception {
//       判断文件不能为空if (file == null) {return new ApiResult(400,"文件不能为空",null);}
//       导入excel文件的参数配置类ImportParams params = new ImportParams();params.setNeedVerify(true);//是否开启校验params.setHeadRows(1); //头行忽略的行数final ExcelImportService excelImportService = new ExcelImportService();
//          excelImportResult包含了导入操作的结果  将获取的数据解析为User类实例 params为导入excel文件的参数配置
//           执行Excel文件的导入操作ExcelImportResult excelImportResult = excelImportService.importExcelByIs(file.getInputStream(), User.class, params, false);//校验成功数据List<User> list = excelImportResult.getList();
//          通过暴力反射获取校验失败的数据final Field failCollection = ExcelImportService.class.getDeclaredField("failCollection");failCollection.setAccessible(true);//校验失败数据List<User> failList = (List) failCollection.get(excelImportService);if (list.size() == 0 && failList.size() == 0) {return new ApiResult(500,"导入失败，上传文件数据不能为空",null);}if (failList.size() > 0){return new ApiResult(500,"导入失败，上传文件数据与模板不一致",null);}//如果没有错误，可以存入数据库if (list.size() >= 0 && CollectionUtils.isEmpty(list)== false) {//批量插入sql语句userManageService.insertList(list);}else{return new ApiResult(500,"导入失败，上传文件数据不能为空",null);}return new ApiResult(200,"导入成功",null);}

1.2，实体类
使用@Excel注解让实体类的字段跟excel的字段名保持一致进行映射
@Excel(name = “用户名”）

package com.lwt.maven.entity;import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.validator.constraints.Length;import javax.validation.constraints.Max;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.NotNull;
import java.math.BigDecimal;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {private Integer id;@Excel(name = "用户名")private String uname;@Excel(name = "卡号")@NotEmpty(message = "卡号不能为空")private String card_number;@Excel(name = "手机号码")@Length(max = 11,message = "手机号不能超过11位")private String phone;@Excel(name = "余额")@Max(value = 99999999,message = "金额不能超过999999999")private BigDecimal balance;private String acutorimg;private String regitstime;private Integer page;private Integer limit;private String endTime;private String startTime;}

1.3，前端页面
使用Layui的组件进行文件上传

   <button type="button" class="layui-btn" id="test1"><i class="layui-icon">&#xe67c;</i>上传EXCEL</button>

     	//执行实例var uploadInst = upload.render({elem: '#test1',//绑定元素accept: 'file',url: 'http://localhost:8080/bank_maven_war_exploded/usermanage/uuApplyUserInfo', //上传接口// 选择文件后回调choose: function (obj) {console.log(obj);}, done: function (res) {//上传完毕回调layer.msg(res.message)}, error: function (res) {//请求异常回调layer.msg(res.message)}});

1.4，sql语句
将excel表格的数据获取到list之后循环打印获取值（separator：每次循环结束后拼接上去）

    <insert id="insertList">insert into user(`uname`,`card_number`,`phone`,`balance`) values<foreach collection="list" item="user" separator=",">(#{user.uname},#{user.card_number},#{user.phone},#{user.balance})</foreach></insert>

二，Excel文件导出
1.1，controller控制层
创建了一个工作簿并生成了一个excel表格（Users），然后添加表头和表数据并设置列宽，之后把工作簿通过输出流输出给response.getOutputStream()(前端)，需注意的是要设置响应头的响应类型为excel文件和响应的模式为下载excel表格

        @GetMapping("/exportList")public void exportList(HttpServletResponse response,User u) throws IOException {response.setCharacterEncoding("UTF-8");
//            设置响应的类型为excel文件response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//            设置浏览器的响应模式：为文件下载response.setHeader("Content-Disposition", "attachment; filename=users.xlsx");// 创建一个新的工作簿Workbook workbook = new XSSFWorkbook();// 创建一个新的工作表（Users）Sheet sheet = workbook.createSheet("Users");// 创建表头行 （在工作表sheet中添加第一行）Row headerRow = sheet.createRow(0);
//        表头行数据String[] columns = {"编号","用户名", "手机号码", "卡号","余额","头像地址","注册时间"};for (int i = 0; i < columns.length; i++) {
//            在第一行中创建单元格并赋值数据Cell cell = headerRow.createCell(i);
//            在单元格赋值cell.setCellValue(columns[i]);}// 获取数据库用户表数据List<User> users = userManageService.queryList(u);// 填充数据行int rowNum = 1;for (User user : users) {
//            有一个对象就创建一行并添加数据Row row = sheet.createRow(rowNum++);
//            在此行的第一个单元格中添加数据 以此类推...row.createCell(0).setCellValue(user.getId());row.createCell(1).setCellValue(user.getUname());row.createCell(2).setCellValue(user.getPhone());row.createCell(3).setCellValue(user.getCard_number());row.createCell(4).setCellValue((String.valueOf(user.getBalance())));row.createCell(5).setCellValue(user.getAcutorimg());row.createCell(6).setCellValue(user.getRegitstime());}// 设置列宽for (int i = 0; i < columns.length; i++) {sheet.autoSizeColumn(i);}
//            将excel表格流(输入流)写入到响应体中workbook.write(response.getOutputStream());// 关闭工作簿try {workbook.close();} catch (IOException e) {e.printStackTrace();}}

1.2，sql语句
根据查询的结果而导入excel文件

    <select id="queryAll" resultType="com.lwt.maven.entity.User">select * from user where 1=1<include refid="query"></include></select><sql id="query"><if test="uname != null and uname != ''">AND uname like CONCAT('%', #{uname}, '%')</if><if test="card_number != null and card_number != ''">AND card_number like CONCAT('%', #{card_number}, '%')</if><if test="startTime != null and startTime != '' and endTime != null and endTime != ''">AND regitstime BETWEEN #{startTime} AND #{endTime}</if><if test="phone != null and phone != ''">AND phone like CONCAT('%', #{phone}, '%')</if></sql>

1.3，前端页面

 <button type="button" class="layui-btn" id="exprotList">导出excel</button>

$("#exprotList").on("click",function(){window.location.href="http://localhost:8080/bank_maven_war_exploded/usermanage/exportList?uname="+$("#input_name_one").val()+"&startTime="+startTime+"&endTime="+endTime+"&phone="+$("#input_name_two").val()+"&card_number="+$("#input_name_three").val()
})