利用EasyPoi对execl进行导入导出

EasyPoi官方文档

一、前言

这是我大三开始所写的第一篇博客，因为面临面试，所以为了让自己对这些所学的知识有一个好的复习，于是想用博客来记录我所学习的技术，以此对这有更深的体验，如有错误，请各位大佬指正！！！

这是我写的一个EasyPoi对execl进行导入导出的一个demo，他不像原生的Execl导入导出那样，它实现的代码比较简洁，它本身的类封装了导入导出的方法。

1.1 以下网址是鄙人的github仓库，存放着自己的技术代码demo，如有错误，还请各位大佬指正！！！

该项目完整代码地址: https://github.com/Java567/TechnologyDemo

二、准备工作

编译器：idea 2019.3.3版本
数据库：mysql---8的版本
插件：lombok(简化实体类的编写)

2.1 导入的execl表和sql表的模板

2.2 所需的pom依赖

2.2.1 swagger2的依赖（后端生成api接口文档）

<!--集成API接口文档的生成swagger2-->
<dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger2</artifactId>
    <version>2.9.2</version>
</dependency>
<dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger-ui</artifactId>
    <version>2.9.2</version>
</dependency>

2.2.2 lombok的依赖

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

2.2.3 easypoi依赖

<!-- easypoi依赖 -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.2.0</version>
</dependency>

三、代码实现

3.1 实体类编写

package com.lj.model;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.io.Serializable;
import java.util.Date;

/**
 * @description: 报告厅基本信息实体类
 * @author: LiJun
 * @date: Created in 2021/2/15 20:42
 */
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = "Auditorium", description = "报告厅模型")
public class Auditorium implements Serializable {


    @ApiModelProperty("报告厅主键")
    private Integer auditoriumId;


    @Excel(name = "报告厅名称", width = 14.25)
    @ApiModelProperty("报告厅名称")
    private String auditoriumName;


    @Excel(name = "正在使用者", width = 14.25)
    @ApiModelProperty("正在使用者")
    private String username;


    @Excel(name = "所属楼层编号", width = 14.25)
    @ApiModelProperty("所属楼层编号")
    private Integer floorId;


    @Excel(name = "所属楼栋编号", width = 14.25)
    @ApiModelProperty("所属楼栋编号")
    private Integer buildId;


    @Excel(name = "所属校区编号", width = 14.25)
    @ApiModelProperty("所属校区编号")
    private Integer campusId;


    @Excel(name = "报告厅面积", width = 14.25)
    @ApiModelProperty("报告厅面积")
    private Integer auditoriumArea;


    @Excel(name = "可容纳人数", width = 14.25)
    @ApiModelProperty("可容纳人数")
    private Integer auditoriumCapacity;


    @Excel(name = "状态(是否空闲)", width = 14.25)
    @ApiModelProperty("状态(是否空闲)")
    private String auditoriumState;


    @Excel(name = "详情介绍", width = 14.25)
    @ApiModelProperty("详情介绍")
    private String auditoriumDetail;


    @Excel(name = "报告厅添加时间", width = 14.25)
    @ApiModelProperty("报告厅添加时间")
    @JsonProperty("auditorium_create_time")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GTM+8")
    private Date auditoriumCreateTime;
}

3.2 持久层编写

3.2.1 mapper.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="com.lj.dao.AuditoriumDAO">

    <select id="getAllAuditorium" resultType="Auditorium">
        SELECT * FROM `Auditorium`;
    </select>

    <insert id="save" parameterType="java.util.List">
        INSERT INTO `Auditorium`
        (`auditorium_name`, `username`, `floor_id`, `build_id`, `campus_id`, `auditorium_area`,
        `auditorium_capacity`, `auditorium_state`, `auditorium_detail`)
        VALUES
        <foreach collection="list" separator="," item="item" close=";">
            (#{item.auditoriumName}, #{item.username}, #{item.floorId}, #{item.buildId}, #{item.campusId},
            #{item.auditoriumArea},#{item.auditoriumCapacity}, #{item.auditoriumState}, #{item.auditoriumDetail})
        </foreach>
    </insert>
</mapper>

3.2.2 dao层接口编写

package com.lj.dao;

import com.lj.model.Auditorium;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @description:
 * @author: LiJun
 * @date: Created in 2021/2/16 20:04
 */
@Repository
public interface AuditoriumDAO {

    /**
     * 得到表信息
     */
    List<Auditorium> getAllAuditorium();

    /**
     * 保存至数据库
     */
    void save(List<Auditorium> list);
}

3.3 业务层编写（简单，省略不写，项目完整代码见github代码仓库网址）

3.4 控制层编写

package com.lj.controller;

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 com.lj.model.Auditorium;
import com.lj.service.AuditoriumService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.List;

/**
 * @description:
 * @author: LiJun
 * @date: Created in 2021/2/16 20:04
 */
@RestController
@RequestMapping("Auditorium")
@Api(tags = "Auditorium报告厅相关的API接口")
public class AuditoriumController {

    @Resource
    private AuditoriumService auditoriumService;

    /**
     * 数据导入
     */
    @ResponseBody
    @PostMapping("/easyPOiImport")
    @ApiOperation(value="execl数据导入接口", notes = "execl数据导入到对应的报告厅数据库表")
    public void loadIn(@RequestParam("file") MultipartFile file) throws Exception {
        List<Auditorium> list= ExcelImportUtil.importExcel(file.getInputStream(),Auditorium.class,new ImportParams());
        System.out.println(list);
        auditoriumService.save(list);
    }



    /**
     * 导出Auditorium信息，需要用浏览器测试,"application/vnd.ms-excel;charset=utf-8"
     * @param response
     * @return
     * @throws IOException
     */
    @GetMapping("/easyPOiExport")
    @ResponseBody
    @ApiOperation(value="execl导出Auditorium接口", notes = "导出Auditorium表里的数据并生成可以下载的execl表")
    public Integer easyPOiExport(HttpServletResponse response) throws IOException{
        List<Auditorium> list =auditoriumService.getAllAuditorium();
        String fileName = new String("Auditorium.xls".getBytes("utf-8"), "ISO-8859-1");
        if (list.isEmpty()){
            return 0;
        } else {
            // 请求头
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.flushBuffer();
            response.setCharacterEncoding("UTF-8");
            //导出execl
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), Auditorium.class, list);
            workbook.write(response.getOutputStream());
            return 1;
        }
    }
}

四、运行结果

4.1 项目启动后访问地址

http://localhost:8080/swagger-ui.html

4.2 项目运行截图

posted @ 2021-02-28 21:03 Java伍六七阅读(275) 评论(0) 收藏举报

刷新页面返回顶部

利用EasyPoi对execl进行导入导出

利用EasyPoi对execl进行导入导出

一、前言

二、准备工作

2.1 导入的execl表和sql表的模板

2.2 所需的pom依赖

2.2.1 swagger2的依赖（后端生成api接口文档）

2.2.2 lombok的依赖

2.2.3 easypoi依赖

三、代码实现

3.1 实体类编写

3.2 持久层编写

3.2.1 mapper.xml编写

3.2.2 dao层接口编写

3.3 业务层编写（简单，省略不写，项目完整代码见github代码仓库网址）

3.4 控制层编写

四、运行结果

4.1 项目启动后访问地址

4.2 项目运行截图

公告