SpringBoot+EasyExcel+Swagger

一、SpringBoot整合EasyExcel

EasyExcel官方地址 https://easyexcel.opensource.alibaba.com/index.html

简介

Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或 者JVM频繁的full gc。

而EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一 行行读取数据，逐个解析。**

1. 导入依赖

    <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

2. 创建实体类

@Data
public class ExcelVo implements Serializable {
  @ExcelProperty("ID")
  private Integer id;
  @ExcelProperty("内容")
  private String content;
  @ExcelProperty("时间")
  private String time;

  private static final long serialVersionUID = 1L;
}

3. 编写Controller

@Slf4j
@RestController
@RequestMapping("/Excel")
public class ExcelController {

  @Autowired
  private ExcelVoMapper excelVoMapper;

  @PostMapping("/upload")
  public String upload(@Param("file") MultipartFile file) throws IOException{
    if(file!=null){
      EasyExcel.read(file.getInputStream(), ExcelVo.class, new ExcelListener(excelVoMapper)).sheet().doRead();
      return "数据读取成功";
    }else{
      return "文件为空";
    }
  }

  @PostMapping("/download")
  @ResponseBody
  public void download(@RequestBody HashMap<String,String> map) throws IOException {
    List<ExcelVo> list = excelVoMapper.select();
    log.info("入表数据{}",list.toString());
    EasyExcel.write(map.get("fileName"), ExcelVo.class).sheet("模板").doWrite(list);
  }
}

5. 编写Mapper

@Mapper
public interface ExcelVoMapper {
    List<ExcelVo> select();

    int insertBatch(List<ExcelVo> record);
}

6. 编写MapperXml

<mapper namespace="com.example.Mapper.ExcelVoMapper">
    <resultMap id="BaseResultMap" type="com.example.pojo.ExcelVo">
        <!--@mbg.generated-->
        <result column="ID" jdbcType="INTEGER" property="id"/>
        <result column="CONTENT" jdbcType="VARCHAR" property="content"/>
        <result column="TIME" jdbcType="VARCHAR" property="time"/>
    </resultMap>
    <insert id="insertBatch" parameterType="list">
        <!--@mbg.generated-->
        insert into `7-11` (ID, CONTENT, `TIME`)
        values
        <foreach collection="list" item="item" separator=",">
            (#{item.id,jdbcType=INTEGER}, #{item.content,jdbcType=VARCHAR}, #{item.time,jdbcType=VARCHAR})
        </foreach>
    </insert>

    <select id="select" resultMap="BaseResultMap">
      select * from `7-11`;
    </select>
</mapper>

7. 创建监听器

@Slf4j
public class ExcelListener extends AnalysisEventListener<ExcelVo> {

  private ExcelVoMapper excelVoMapper;

  private List<ExcelVo> list = new ArrayList<>();

  // 通过构造器注入mapper
  public ExcelListener(ExcelVoMapper excelVoMapper){
    this.excelVoMapper = excelVoMapper;
  }

  //一行一行读取Excel的内容
  @Override
  public void invoke(ExcelVo data, AnalysisContext context){

    log.info("ExcelVo = {}", data);
    // TODO
    list.add(data);

  }

  //Excel读取完成后执行此方法
  @Override
  public void doAfterAllAnalysed(AnalysisContext context){
    saveData();
  }

  private void saveData(){
    list.stream().forEach(System.out::println);
    log.info("------------  开始存储数据  ----------");
    if(!list.isEmpty()){
      excelVoMapper.insertBatch(list);
    }
    log.info("------------  开始存储数据  ----------");
  }
}

二、SpringBoot整合Swagger

简介

Swagger是一款Restful接口的文档在线自动生成和功能测试功能软件，尤其是现在前后端分离情况，更容易造成文档和代码不一致。这时，我们可以通过Swagger2来使接口规范，方便维护。

1. 导入依赖

        <!-- swagger自动生成API文档支持 -->
        <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. 配置类

@Configuration
@EnableSwagger2
public class SwaggerConfig {

  @Bean
  public Docket createRestApi(){
    return new Docket(DocumentationType.SWAGGER_2)
        .apiInfo(apiInfo())
        .select()
        .apis(RequestHandlerSelectors.withMethodAnnotation(ApiOperation.class))
        .paths(PathSelectors.any())
        .build();
  }

  private ApiInfo apiInfo() {
    return new ApiInfoBuilder()
        .title("RESTFUL API SPRINGBOOT+EASYEXCEL")
        .version("1.0")
        .description("API")
        .build();

  }
}

3. 加入注解

@ApiOperation("读取Excel到Mysql")
@PostMapping("/upload")
@ApiParam(name = "fileName",value = "Excel文件绝对路径",required = true)
public String upload(@Param("file") MultipartFile file) throws IOException

@ApiOperation("读取Mysql到Excel")
@ApiParam(name = "fileName",value = "Excel文件绝对路径",required = true)
@PostMapping("/download")
@ResponseBody
public void download(@RequestBody HashMap<String,String> map) 

4.访问地址

ip:端口号/swagger-ui.html