SpringBoot基于easyexcel导出和写入Excel

easyexcel是阿里巴巴旗下开源项目，主要用于Excel文件的导入和导出处理，今天我们利用SpringBoot和easyexcel实战演示如何导出和写入Excel文件。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.xu</groupId>
    <artifactId>easyexcel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>easyexcel</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <swagger-ui.version>2.9.2</swagger-ui.version>
        <swagger2.version>2.9.2</swagger2.version>
    </properties>

    <dependencies>
        <!-- spring-boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- log related -->
        <dependency> <!-- exclude掉spring-boot的默认log配置 -->
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <!-- 表示开发的时候引入，发布的时候不会加载此包 -->
            <scope>test</scope>
        </dependency>

        <!-- mybatis-plus begin -->
        <!--mybatis-plus自动的维护了mybatis以及mybatis-spring的依赖，在springboot中这三者不能同时的出现，避免版本的冲突，表示：跳进过这个坑-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>

        <!--swagger2-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>${swagger-ui.version}</version>
        </dependency>

        <!--排除并新增1.5swagger-annotations和swagger-models
            为了解决swagger2中example注解导致的input空字符串异常错误-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>${swagger2.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>io.swagger</groupId>
                    <artifactId>swagger-annotations</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>io.swagger</groupId>
                    <artifactId>swagger-models</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>1.5.21</version>
        </dependency>

        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-models</artifactId>
            <version>1.5.21</version>
        </dependency>

        <!-- 引入Druid依赖，阿里巴巴所提供的数据源 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.29</version>
        </dependency>

        <!-- 提供mysql驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.16</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.46</version>
        </dependency>

        <!--热部署-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>springloaded</artifactId>
            <version>1.2.8.RELEASE</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- alibaba的easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta4</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

package com.jwell.classifiedProtection.entry.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.*;

/**
 * <p>
 * 系统
 * </p>
 *
 * @author RonnieXu
 * @since 2019-08-02
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class UserExcelModel extends BaseRowModel {

    private static final long serialVersionUID = 1L;

    @ExcelProperty(value = "用户名", index = 0)
    private String userName;

    @ExcelProperty(value = "密码", index = 1)
    private String password;

    @ExcelProperty(value = "真实姓名", index = 2)
    private String realName;

    @ExcelProperty(value = "电话号码", index = 3)
    private String phone;

    @ExcelProperty(value = "邮箱地址", index = 4)
    private String email;

    private String roleType;

    private Integer departmentId;

    private String remark;

}

package com.jwell.classifiedProtection.commons.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

@Slf4j
public class ExcelListener<T extends BaseRowModel> extends AnalysisEventListener<T> {

    private List<T> rows = new ArrayList<>();

    /**
     * object是每一行数据映射的对象
     * @param object
     * @param context
     */
    @Override
    public void invoke(T object, AnalysisContext context) {
        System.out.println("当前行："+context.getCurrentRowNum());
        System.out.println(object);
        rows.add(object);
        //根据自己业务做处理
        doSomething(object);
    }

    private void doSomething(T object) {
        //1、入库调用接口
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

        log.info("read {} rows %n", rows.size());
    }

    public List<T> getRows() {

        return rows;
    }

    public void setRows(List<T> rows) {
        this.rows = rows;
    }

}

package com.jwell.classifiedProtection.commons.excel;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.jwell.classifiedProtection.entry.excel.UserExcelModel;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class ExcelUtils {


    /**
     * @param is   导入文件输入流
     * @param clazz Excel实体映射类
     * @return
     */
    public static <T extends BaseRowModel> List<T> readExcel(InputStream is, final Class<? extends BaseRowModel> clazz){

        List<T> rowsList = null;
        BufferedInputStream bis = null;
        try {
            bis = new BufferedInputStream(is);
            // 解析每行结果在listener中处理
            ExcelListener listener = new ExcelListener();
            ExcelReader excelReader = EasyExcelFactory.getReader(bis, listener);
            excelReader.read(new Sheet(1, 1, clazz));
            rowsList = listener.getRows();

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return rowsList;
    }

    /**
     *
     * @param os 文件输出流
     * @param clazz Excel实体映射类
     * @param data 导出数据
     * @return
     */
    public static Boolean writeExcel(OutputStream os, Class clazz, List<? extends BaseRowModel> data){

        BufferedOutputStream bos= null;
        try {
            bos = new BufferedOutputStream(os);
            ExcelWriter writer = new ExcelWriter(bos, ExcelTypeEnum.XLSX);
            //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
            Sheet sheet1 = new Sheet(1, 0,clazz);
            writer.write(data, sheet1);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return true;
    }

    /**
     * ResponseEntity下载文件
     *
     * @param fileName
     * @param byteOutPutStream
     */
    public static ResponseEntity<byte[]> downloadExcel(String fileName, ByteArrayOutputStream byteOutPutStream) {

        //下载文件
        try {
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            headers.setContentDispositionFormData("attachment",
                    new String(fileName.getBytes("GBK"), "ISO8859-1"));// 文件名称

            ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(byteOutPutStream.toByteArray(), headers, HttpStatus.CREATED);
            return responseEntity;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;

    }

    /**
     * 这是你最熟悉的老朋友Main方法，哈哈哈
     * @param args
     */
    public static void main(String[] args) {
        //1.导入Excel
        FileInputStream fis = null;
        try {
            fis = new FileInputStream("c:\\test\\test1.xls");
            List<UserExcelModel> userExcelModelList = ExcelUtils.readExcel(fis, UserExcelModel.class);
            System.out.println("导入是否成功：-------------->"+"数据行数是："+userExcelModelList.size());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            if (fis != null){
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        //2.导出Excel
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream("D:\\export.xlsx");
            //FileOutputStream fos, Class clazz, List<? extends BaseRowModel> data
            List<UserExcelModel> list = new ArrayList<>();
            for (int i = 0; i < 5; i++) {
                UserExcelModel excelEntity = new UserExcelModel();
                excelEntity.setUserName("我是名字" + i);
                list.add(excelEntity);
            }
            Boolean flag = ExcelUtils.writeExcel(fos, UserExcelModel.class, list);
            System.out.println("导出是否成功：" + flag);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

package com.xu.easyexcel.controller;

import com.xu.easyexcel.commons.excel.ExcelUtils;
import com.xu.easyexcel.pojo.User;
import com.xu.easyexcel.pojo.excel.UserExcelModel;
import com.xu.easyexcel.service.IUserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 * 整改建议详情列表详情列表 前端控制器
 * </p>
 *
 * @author RonnieXu
 * @since 2019-08-29
 */
@Slf4j
@Transactional(rollbackFor = Exception.class)
@Api(value = "用户接口", tags = "用户接口")
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private IUserService iUserService;

    @ApiOperation(value = "导入Excel表格", notes = "导入Excel表格")
    @RequestMapping(value = "/readExcel", method = RequestMethod.POST)
    public void readExcelModel() {
        try{

            FileInputStream inputStream = new FileInputStream("c:\\test\\test1.xlsx");
            List<UserExcelModel> list = ExcelUtils.readExcel(new BufferedInputStream(inputStream), UserExcelModel.class);

            List<User> userList = new ArrayList<>();
            list.forEach(demo->{
                User user = new User(
                        demo.getUserName(),
                        demo.getPassword(),
                        demo.getRealName(),
                        demo.getPhone(),
                        demo.getEmail());
                userList.add(user);
            });
            System.out.println(list.size());
            //将表格数据保存到数据库
            iUserService.saveOrUpdateBatch(userList);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @ApiOperation(value = "导出Excel表格", notes = "导出Excel表格")
    @RequestMapping(value = "/exprotExcel", method = RequestMethod.POST)
    public ResponseEntity exprotExcel() {

        try {
            List<User> list = iUserService.list();
            List<UserExcelModel> dataList = new ArrayList<>();
            list.forEach(user->{
                UserExcelModel userExcelModel = new UserExcelModel(
                        user.getUserName(),
                        user.getPassword(),
                        user.getRealName(),
                        user.getPhone(),
                        user.getEmail());
                dataList.add(userExcelModel);
            });
            System.out.println(dataList.size());

            //数据写入到字节流
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            boolean flag = ExcelUtils.writeExcel(bos, UserExcelModel.class, dataList);

            //下载文件
            String fileName = "下载整改建议.xls";
            log.info("开始下载导出的Excel文件");
            return ExcelUtils.downloadExcel(fileName, bos);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}