springboot整合poi（使用EXCEL模板导出导入）

springboot整合poi

依赖

 <!--    poi依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <!--    poi对于excel 2007的支持依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <!--    poi对于excel 2007的支持依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

列表实体类UserInfo

public class UserInfo {

  private int id;
  private String name;
  private String sex;
  private int age;
  private String telephone;
  private String address;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }


    public UserInfo() {
    }

    //方便数据导入时转换
    public UserInfo(Object[] args){
        /** DecimalFormat 用法
         * https://www.jianshu.com/p/b3699d73142e
         * Integer.valueOf 返回的时包装类  Integer.parseInt() 返回的是int
         */
        //因为传进来的args 的赋值是从1开始的
        this.id=new Double(args[0].toString()).intValue();
        this.name=args[1].toString();
        this.age=new Double(args[2].toString()).intValue();
        this.sex=args[3].toString();
        this.telephone=args[4].toString();
        this.address=args[5].toString();
    }
    
    @Override
    public String toString() {
        return "UserInfo{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", telephone='" + telephone + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

EXCEL数据导出

制作导出模板

EXCEL数据导出工具类

/**
 * 用于 Excel下载的工具类
 */
public class DownloadUtils {
    /**
     *
     * @param byteArrayOutputStream 输出字节流
     * @param response
     * @param returnName 输出到客户端的文件名
     * @throws IOException
     */
    public   void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
        response.setContentType("application/octet-stream");
        returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));            //保存的文件名,必须和页面编码一致,否则乱码
//        response.addHeader("Content-Disposition","attachment;filename=total.xls");
        response.addHeader("Content-Disposition","attachment;filename="+returnName);
        response.setContentLength(byteArrayOutputStream.size());
        response.addHeader("Content-Length", "" + byteArrayOutputStream.size());
        ServletOutputStream outputStream = response.getOutputStream();    //取得输出流
        byteArrayOutputStream.writeTo(outputStream);                    //写到输出流
        byteArrayOutputStream.close();                                    //关闭
        outputStream.flush();                                            //刷数据
    }
}

EXCEL数据导出接口

   /**
     * 导出数据 提前制作好的excel模板
     */
    @GetMapping("exportTemplate")
    public void  exportTemplate(HttpServletResponse response) throws IOException {
        //1.获取数据
        //人为构造的数据，实际是要从数据库中查的
        List<UserInfo> users=new ArrayList<>();

        for(int i=0;i<20;i++){
            UserInfo userInfo=new UserInfo();
            int rs = (int) ((Math.random() * 9 + 1) * Math.pow(10, 8 - 1));
            userInfo.setId(i+1);
            userInfo.setName("zhangsan"+i);
            userInfo.setAge(23);
            userInfo.setSex("man");
            userInfo.setTelephone("188"+rs);
            userInfo.setAddress("随便编个地址");
            users.add(userInfo);
        }

        //2.加载模板
        Resource resource = new ClassPathResource("excel/user_info.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());

        //3.根据模板创建工作簿
        Workbook wb = new XSSFWorkbook(fis);
        //4.读取工作表
        Sheet sheet = wb.getSheetAt(0);

        //5.抽取第2行的公共样式 , 因为第一行 为标题 第2行是数据 下标为1
        Row row = sheet.getRow(1);
        CellStyle styles[] = new CellStyle[row.getLastCellNum()];
        Cell cell = null;
        for (int i = 0; i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            styles[i] = cell.getCellStyle();
        }
        //6.构造单元格
        int rowIndex=1;
        for (UserInfo userInfo:users) {
            //创建每一行，同excel的第二行开始
            row=sheet.createRow(rowIndex++);
            //第一列
            cell = row.createCell(0);
            //设置单元格样式
            cell.setCellStyle(styles[0]);
            //写入数据 序号
            cell.setCellValue(userInfo.getId());
            //第2列
            cell = row.createCell(1);
            cell.setCellStyle(styles[1]);
            cell.setCellValue(userInfo.getName());

            cell = row.createCell(2);
            cell.setCellStyle(styles[2]);
            cell.setCellValue(userInfo.getAge());

            cell = row.createCell(3);
            cell.setCellStyle(styles[3]);
            cell.setCellValue(userInfo.getSex());

            cell = row.createCell(4);
            cell.setCellStyle(styles[4]);
            cell.setCellValue(userInfo.getTelephone());

            cell = row.createCell(5);
            cell.setCellStyle(styles[5]);
            cell.setCellValue(userInfo.getAddress());

        }
        //7.下载
        ByteArrayOutputStream os=new ByteArrayOutputStream();
        wb.write(os);
        String fileName = "用户信息-"+new Date().getTime()+ ".xlsx";
        new DownloadUtils().download(os,response,fileName);
    }

访问http://localhost:8080/exportTemplate，导出后的数据

EXCEL数据导入

EXCEL数据导入接口

 //跳转到EXCEL导入数据页面
    @GetMapping("/jumpFileUpload")
    public String jumpFileUpload(){
        return "/fileUpload";
    }
    /**
     * excel 文件上传
     * postman 上传文件 操作指南https://jingyan.baidu.com/article/425e69e614f472be14fc166f.html
     */
    @PostMapping("/upload")
    @ResponseBody
    public String upload(@RequestParam(name = "file") MultipartFile file) throws IOException {
        //1.解析Excel
        //1.1.根据Excel文件创建工作簿
        Workbook wb = new XSSFWorkbook(file.getInputStream());
        //1.2.获取Sheet
        Sheet sheet = wb.getSheetAt(0);//参数：索引
        //1.3.获取Sheet中的每一行，和每一个单元格
        //2.获取用户数据列表
        List<UserInfo> list = new ArrayList<>();
        System.out.println("最后一行的下标 :" + sheet.getLastRowNum());
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);//根据索引获取每一个行
//            System.out.println("列数"+row.getLastCellNum());
            Object[] values = new Object[row.getLastCellNum()];
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                Object value = getCellValue(cell);
                values[cellNum] = value;
            }
            UserInfo user = new UserInfo(values);
            list.add(user);
        }
        //3.批量保存用户
        for (UserInfo userInfo: list) {
            System.out.println(userInfo.toString());
        }
        return "SUCCESS";
    }

    public static Object getCellValue(Cell cell) {
        //1.获取到单元格的属性类型
        CellType cellType = cell.getCellType();
        //2.根据单元格数据类型获取数据
        Object value = null;
        switch (cellType) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    //日期格式
                    value = cell.getDateCellValue();
                } else {
                    //数字
                    value = cell.getNumericCellValue();
                }
                break;
            case FORMULA: //公式
                value = cell.getCellFormula();
                break;
            default:
                break;
        }
        return value;
    }

EXCEL数据导入页面

这里使用的是thymeleaf页面
thymeleaf依赖

<!--thymeleaf引擎模板-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

页面

<!DOCTYPE html>
<html lang="en"  xmlns:th="http://www.thymeleaf.org">
<head>

</head>
<body>
<div style="text-align: center">
    导入EXCEL文件
    <form th:action="@{/upload}" method="post" enctype="multipart/form-data">
        <input type="file" name="file"/>
        <input type="submit"  value="导入数据"/>
    </form>
</div>
</body>
</html>

得到数据

注解方式

springboot整合poi注解方式（使用EXCEL模板导出导入）