[Java知识库]springboot项目实现excel导出

项目中经常会有列表查询，然后导出excel的功能，以下是其中一种方法，简单写个Demo
，先看项目结构：

1. pom.xml

	<properties>
        <spring-boot.version>2.3.12.RELEASE</spring-boot.version>
    </properties>

    <dependencies>
        <!--注意：由于 spring-boot-starter-web 默认替我们引入了核心启动器 spring-boot-starter，
        因此，当 Spring Boot  项目中的 pom.xml 引入了 spring-boot-starter-web 的依赖后，
        就无须在引入 spring-boot-starter 核心启动器的依赖了-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring-boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring-boot.version}</version>
            <scope>test</scope>
        </dependency>

        <!--整合mytais-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.12.3</version>
        </dependency>
        
        <!--excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <!--excel2003版本要引入此包-->
<!--        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>-->
    </dependencies>

其他依赖就不提了，主要就只这个依赖org.apache.poi.poi-ooxml
说明：

poi是Apache旗下的一个开源项目，由Apache官方维护，poi有两个不同的jar包，分别是处理excel2003和excel2007+的，对应的是poi和poi-ooxml。毕竟poi-ooxml是poi的升级版本，处理的单页数据量也是百万级别的，所以我们选择的也是poi-ooxml。

2. application.yml

spring:
  datasource:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.233.136:3306/mydata?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
      username: root
      password: 123456
mybatis:
  mapperLocations: classpath*:mapper/*Mapper.xml
  # 打印sql语句
  logging:
    level:
      com.meng.user: debug

3. Excel2Application

@SpringBootApplication
@MapperScan("com.meng.dao")
public class Excel2Application {
    public static void main(String[] args) {
        SpringApplication.run(Excel2Application.class  , args);
    }
}

4. entity

@Data
public class BaiDuResult {
    private Long id;

    /**
     *标题
     */
    private String title;

    /**
     *内容
     */
    private String content;

    /**
     *内容来源
     */
    private String sourceUrl;

    /**
     *封面图片
     */
    private String imgUrl;

    /**
     *创建时间
     */
    private Date createTime;

    /**
     *更新时间
     */
    private Date updateTime;

    /**
     *是否删除
     */
    private Byte delFlag;

}

5. dao

@Repository
public interface BaiDuResultDao {

    List<BaiDuResult> findAllPage(@Param("start") int start , @Param("pageSize")int pageSize);

6. BaiDuResultMapper.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.meng.dao.BaiDuResultDao" >
  <resultMap id="BaseResultMap" type="com.meng.entity.BaiDuResult" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="title" property="title" jdbcType="VARCHAR" />
    <result column="content" property="content" jdbcType="VARCHAR" />
    <result column="source_url" property="sourceUrl" jdbcType="VARCHAR" />
    <result column="img_url" property="imgUrl" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="del_flag" property="delFlag" jdbcType="TINYINT" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, title, content, source_url, img_url, create_time, update_time, del_flag
  </sql>
  
  <select id="findAllPage" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from result limit #{start} , #{pageSize}
  </select>

7. service

@Service
public class ResultService {

    @Autowired
    private BaiDuResultDao dao;

    public Workbook exportToExcel(){
        //这是表头
        String[] arr = {"ID","标题","内容","内容来源","封面图片","创建时间","更新时间","是否删除"};
        //这是具体数据
        List<BaiDuResult> list = dao.findAllPage(0, 1000);
        Workbook workbook = ExcelUtil.writeToExcelByList(arr, list , BaiDuResult.class);
        return workbook;
    }
}

8. controller

@RestController
public class ResultController {

    @Autowired
    private ResultService resultService;

    @GetMapping("/export")
    public void exportResult(HttpServletResponse response) throws IOException {
        Workbook wb = resultService.exportToExcel();
        OutputStream output = response.getOutputStream();
        String fileName = "结果表.xlsx";
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName);
        wb.write(output);
        output.close();
    }
}

9. ExcelUtils
   以上都不重要，重要的是这个工具类的方法

public class ExcelUtil{

    public static <T> Workbook writeToExcelByList(String[] array, List<T> list , Class<T> clazz) {
        //创建工作薄
        Workbook wb = new XSSFWorkbook();
        //标题和页码
        CellStyle titleStyle = wb.createCellStyle();
        // 设置单元格对齐方式,水平居左
        titleStyle.setAlignment(HorizontalAlignment.LEFT);
        //单元格边框
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        // 设置字体样式
        Font titleFont = wb.createFont();
        // 字体高度
        titleFont.setFontHeightInPoints((short) 12);
        // 字体样式
        titleFont.setFontName("黑体");
        titleStyle.setFont(titleFont);
        //创建sheet
        Sheet sheet = wb.createSheet("接入详情");
        // 自动设置宽度
        sheet.autoSizeColumn(0);
        // 在sheet中添加标题行// 行数从0开始
        Row row = sheet.createRow(0);
        for (int i = 0; i < array.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(array[i]);
            cell.setCellStyle(titleStyle);
        }
        // 数据样式 因为标题和数据样式不同 需要分开设置 不然会覆盖
        CellStyle dataStyle = wb.createCellStyle();
        //单元格边框
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        // 设置居中样式，水平居中
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        //数据从序号1开始
        try {
            int index = 1;
            Field[] fields = clazz.getDeclaredFields();
            for (T t : list) {
                // 默认的行数从0开始，为了统一格式设置从1开始，就是从excel的第二行开始
                row = sheet.createRow(index);
                for (int i = 0; i < fields.length; i++) {
                    // 默认的行数从0开始，为了统一格式设置从1开始，就是从excel的第二行开始
                    Cell cell = row.createCell(i);
                    // 为当前列赋值
                    Field field = fields[i];
                    if (!field.isAccessible()) {
                        field.setAccessible(true);
                    }
                    Object value = (new PropertyDescriptor(field.getName(), clazz)).getReadMethod().invoke(t);
                    setValue(cell , value);
                    //设置数据的样式
                    cell.setCellStyle(dataStyle);
                }
                index++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 设置cellValue
     * 这里可以根据value的类型，进行格式化，比如日期格式化
     */
    private static void setValue(Cell cell , Object value){
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if(value != null && !"".equals(value)){
            if(value instanceof Date){
                cell.setCellValue(dateFormat.format(value));
            }else{
                cell.setCellValue(value.toString());
            }
        }
    }
}

这个writeToExcelByList，是利用反射，获取class的fields，然后循环遍历插入到cell，这里可以自定义Annotation，然后进行一些自定义的操作，比如日期格式化、该field是否导出到excel中等，这里只做简单的演示

10. 导出结果
    
    这里的单元格宽度应该是可以设置的，但我就不研究了，有需要再说

本文是采用Apache的开源项目poi来实现的，这个在企业中应用较多（个人观点），还有一个EasyExcel项目，是alibaba出的，看起来也不错，但我就不尝试了，放个链接 简洁、快速、节约内存的Excel处理工具EasyExcel ，或者直接去官网看文档吧，更清楚 EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel

本文参考：Spring boot实现Excel导出文件

大数据量的处理

首先介绍一下目前导出excel的几种格式：Excel 2003、Excel 2007

Excel 2003：在POI中使用HSSF对象时，excel 2003最多只允许存储65536条数据，一般用来处理较少的数据量。这时对于百万级别数据，Excel肯定容纳不了。

Excel 2007：当POI升级到XSSF对象时，它可以直接支持excel2007以上版本，因为它采用ooxml格式。这时excel可以支持1048576条数据，单个sheet表就支持近104万条数据了,虽然这时导出100万数据能满足要求，但使用XSSF测试后发现偶尔还是会发生堆溢出，所以也不适合百万数据的导出。

在POI3.8之后新增加了一个类，SXSSFWorkbook，采用当数据加工时不是类似前面版本的对象，

它可以控制excel数据占用的内存，他通过控制在内存中的行数来实现资源管理，即当创建对象超过了设定的行数，

它会自动刷新内存，将数据写入文件，这样导致打印时，占用的CPU，和内存很少。

所以可以使用SXXFWorkBook来实现百万级别数据量的导出。
只要将上文中的Workbook，替换成SXSSFWorkbook即可

这里参考： Java 使用POI 导出 百万级别的数据量的 Excel

大数据量的处理二

其实上文的大数据量处理方式有个问题，比如我要从数据库查询100万的数据，然后导出到excel，这个查询的过程（包括数据处理的过程）是很漫长的，等处理完，页面才会弹出下载弹窗，可能要等待十几秒甚至几十秒，不知情的用户还以为是系统异常了，也就是说不能快速响应，至少我还没找到快速响应的方式

那么还有另一种方式解决这个问题，就是使用csv文件替代表格（csv可以和表格互相转换且使用效果基本相同）。csv格式文件和txt文件一样理论上没有大小上限，这种方式就不需要poi的依赖了。

直接上代码：

entity加上toString方法：

    @Override
    public String toString() {
        return id +
                "," + title +
                "," + content  +
                "," + sourceUrl  +
                "," + imgUrl +
                "," + createTime +
                "," + updateTime +
                "," + delFlag;
    }

@RestController
public class ResultController {

    @Autowired
    private BaiDuResultDao dao;

    @GetMapping("/export")
    public void exportResult(HttpServletResponse response){

        try {
            response.reset();
            response.setContentType("application/csv;charset=GBK");
            response.setHeader("Content-Disposition","attachment;filename=file" + System.currentTimeMillis() + ".csv");
            response.setCharacterEncoding("GBK");
            PrintWriter out = response.getWriter();
            out.println("id,标题,内容,内容来源,封面图片,创建时间,更新时间,是否删除");
            int limit = 100000;
            int pageSize = 10000;
            for (int i = 0; i < limit; i++) {
                List<BaiDuResult> list = dao.findAllPage(i, pageSize);
                for (BaiDuResult baiDuResult : list) {
                    String str = baiDuResult.toString();
                    out.println(str);
                }
                i += pageSize;
            }
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

这里可以看到，这里可以用分页的方式，将每次分页结果直接响应给页面，也就是用户点击下载，立马弹窗，然后下载的过程就耗时较长了，这样用户的交互会好一些。

但是！！！！！！ csv文件会比excel文件大很多很多，完全不在一个数量级，因为csv可以用记事本打开，本质上是字符串文件，没有任何压缩，xls之类的都是有压缩的，需要专门软件打开。

参考：java实现大数据Excel导出