关于Excel导入

首先要下载模板，以我写的用户导入为例

一、这里需要用到两个文件依赖,一个工具依赖

        <!--easy-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.1</version>
        </dependency>
        <!--hutool-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.12</version>
        </dependency>

二、写一个excel模板，注意：要新建一个文件，在默认的sheet1中写入模板，然后把文件放入代码文件resoures下，我放在

附上模板样式

三、开始写下载模板代码

Controller层：前端不需要传任何参数

    @GetMapping("/exporttemplate")
    public void exporttemplate(HttpServletResponse response) throws IOException {
         userService.exporttemplate(response);
    }

ServiceImpl层：把模板文件转成byte传入下载类中

@Override
    public void exporttemplate(HttpServletResponse response) throws IOException {
        try {
            InputStream inputStream = POICacheManager.getFile("poimoban/userImportTemplate.xlsx");
            byte[] bytes = IoUtil.readBytes(inputStream);
            CommonDownloadUtil.download("用户导入模板.xlsx", bytes, response);
        }catch (Exception e) {
            response.setCharacterEncoding(CharsetUtil.UTF_8);
            response.setContentType(ContentType.JSON.toString());
            response.getWriter().write("下载用户导入模板失败");
        }
    }

其中CommonDownloadUtil工具代码，需要新建一个class然后把代码粘进去

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.URLUtil;
import lombok.extern.slf4j.Slf4j;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;

/**
 * 文件下载工具类，使用本类前，对参数校验的异常使用CommonResponseUtil.renderError()方法进行渲染
 *
 * @author xuyuxiang
 * @date 2020/8/5 21:45
 */
@Slf4j
public class CommonDownloadUtil {

    /**
     * 下载文件
     *
     * @param file     要下载的文件
     * @param response 响应
     * @author xuyuxiang
     * @date 2020/8/5 21:46
     */
    public static void download(File file, HttpServletResponse response) {
        download(file.getName(), FileUtil.readBytes(file), response);
    }

    /**
     * 下载文件
     *
     * @author xuyuxiang
     * @date 2022/7/31 10:57
     */
    public static void download(String fileName, byte[] fileBytes, HttpServletResponse response) {
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLUtil.encode(fileName));
            response.addHeader("Content-Length", "" + fileBytes.length);
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setContentType("application/octet-stream;charset=UTF-8");
            IoUtil.write(response.getOutputStream(), true, fileBytes);
        } catch (IOException e) {
            log.error(">>> 文件下载异常：", e);
        }
    }
}

下载之后，写导入代码

一、Controller层

@PostMapping("/importusers")
    public JSONObject importusers(@RequestPart("file")MultipartFile file){
        return userService.importusers(file);
    }

二、serviceImpl层：

首先接收后创建临时文件用于读取数据，

然后用easyExcel读取数据获得数据集合

那么EasyExcel是怎么读取表格的呢？通过注解！

    @ExcelProperty("用户账号")
    private String userName;
    /**用户姓名*/
    @ExcelProperty("用户姓名")
    private String realName;
    /**IP地址*/
    @ExcelProperty("IP地址")
    private String ip;

由于账户不能重复，所以要查询已有的所有数据，用于导入时看看是否已有

然后再循环excel中读取到的数据一条一条的导入

@Override
    public JSONObject importusers(MultipartFile file) {

        try {
            int successCount = 0;
            int errorCount = 0;
            JSONArray errorDetail = JSONUtil.createArray();
            //创建临时文件
            File tempFile = FileUtil.writeBytes(file.getBytes(), FileUtil.file(FileUtil.getTmpDir()
                    + FileUtil.FILE_SEPARATOR + "userImportTemplate.xlsx"));
            //用easyExcel读取excel
            List<UserImportParam> sysUserImportParamList = EasyExcel.read(tempFile).head(UserImportParam.class)
                    .sheet().headRowNumber(3).doReadSync();
            System.out.println(sysUserImportParamList);
            //获取当前所有用户信息
            List<User> allUserList = userRepository.findWithCondition(new UserDto());
            //非空之后再循环防止空指针异常
            if(!allUserList.isEmpty()){
                //对excel数据进行循环
                for (int i = 0; i < sysUserImportParamList.size(); i++) {
                    JSONObject jsonObject=this.doImport(allUserList,sysUserImportParamList.get(i),i);
                    if(jsonObject.getBool("success")) {
                        successCount += 1;
                    } else {
                        errorCount += 1;
                        errorDetail.add(jsonObject);
                    }
                }
            }

            return JSONUtil.createObj()
                    .set("totalCount", sysUserImportParamList.size())
                    .set("successCount", successCount)
                    .set("errorCount", errorCount)
                    .set("errorDetail", errorDetail);

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

导入的方法：导入方法根据自己的需求写，this.save(user);方法，用了新建用户的方法；

就是读取了excel之后循环再一条一条的新建而已；

private JSONObject doImport(List<User> allUserList, UserImportParam userImportParam, int i) {
        //效验必填参数值
        String ip = userImportParam.getIp();
        String duties = userImportParam.getDuties();
        String email = userImportParam.getEmail();
        String tel = userImportParam.getTel();
        String userName = userImportParam.getUserName();
        String realName = userImportParam.getRealName();
        String firstDepartment = userImportParam.getFirstDepartment();
        String secondDepartment = userImportParam.getSecondDepartment();
        String thirdDepartment = userImportParam.getThirdDepartment();
        String fourDepartment = userImportParam.getFourDepartment();
        //判断必填字段是否填充完毕
        if (ObjectUtil.hasEmpty(duties,email,tel,userName,realName,firstDepartment,secondDepartment
                ,thirdDepartment,fourDepartment)){
            return JSONUtil.createObj().set("index",i+1).set("success",false).set("msg","必填字段存在空值");
        }else {
            try {
                User user = new User();
                //看看这个用户账号是否重复
                int index = allUserList.stream().map(User::getUsername).collect(Collectors.toList()).indexOf(userName);
                //账户不重复，则开始录入
                if(index==-1){
                    //准备数据，将数据封装进
                    user.setEmail(email);
                    user.setRealName(realName);
                    user.setTel(tel);
                    user.setUsername(userName);
                    user.setDuties(duties);
                    //根据所填部门查找部门id
                    String departName = firstDepartment+"|"+secondDepartment+"|"+thirdDepartment+"|"+fourDepartment;
                    Integer departmaentId=departmentDao.findDepartIdByFullName(departName);
                    user.setDepartId(departmaentId);
                }else {
                    return JSONUtil.createObj().set("success",false).set("msg","用户账户重复");
                }
                //保存更新
                this.save(user);
                //返回成功
                return JSONUtil.createObj().set("success",true);
            }catch (Exception e){
                return JSONUtil.createObj().set("success",false).set("index",i+1).set("msg","数据导入异常");
            }
        }
    }

最后附上：实体类和数据库字段与表映射注释

@JsonIgnoreProperties({"password"})
@Entity
@Table(name = "b_user",
        uniqueConstraints = {@UniqueConstraint(name = "B_USER_PK", columnNames = {"USER_NAME_"})})
public class User{

    @Column(name = "USER_NAME_", unique = true, nullable = false)
    private String username;

    @Column(name = "PASSWORD", nullable = false)
    private String password;

    @Column(name = "USER_IP", length = 50)
    private String userIp;

    @Column(name = "USER_MAC", length = 50)
    private String userMac;
}