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。

1. 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=trueusername: root
      password:123456mybatis:mapperLocations: classpath*:mapper/*Mapper.xml# 打印sql语句logging:level:com.meng.user: debug

1. Excel2Application

@SpringBootApplication@MapperScan("com.meng.dao")publicclassExcel2Application{publicstaticvoidmain(String[] args){SpringApplication.run(Excel2Application.class, args);}}

1. entity

@DatapublicclassBaiDuResult{privateLong id;/**
     *标题
     */privateString title;/**
     *内容
     */privateString content;/**
     *内容来源
     */privateString sourceUrl;/**
     *封面图片
     */privateString imgUrl;/**
     *创建时间
     */privateDate createTime;/**
     *更新时间
     */privateDate updateTime;/**
     *是否删除
     */privateByte delFlag;}

1. dao

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

1. BaiDuResultMapper.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.meng.dao.BaiDuResultDao"><resultMapid="BaseResultMap"type="com.meng.entity.BaiDuResult"><idcolumn="id"property="id"jdbcType="BIGINT"/><resultcolumn="title"property="title"jdbcType="VARCHAR"/><resultcolumn="content"property="content"jdbcType="VARCHAR"/><resultcolumn="source_url"property="sourceUrl"jdbcType="VARCHAR"/><resultcolumn="img_url"property="imgUrl"jdbcType="VARCHAR"/><resultcolumn="create_time"property="createTime"jdbcType="TIMESTAMP"/><resultcolumn="update_time"property="updateTime"jdbcType="TIMESTAMP"/><resultcolumn="del_flag"property="delFlag"jdbcType="TINYINT"/></resultMap><sqlid="Base_Column_List">
    id, title, content, source_url, img_url, create_time, update_time, del_flag
  </sql><selectid="findAllPage"resultMap="BaseResultMap">
    select
    <includerefid="Base_Column_List"/>
    from result limit #{start} , #{pageSize}
  </select>

1. service

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

1. controller

@RestControllerpublicclassResultController{@AutowiredprivateResultService resultService;@GetMapping("/export")publicvoidexportResult(HttpServletResponse response)throwsIOException{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();}}

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

publicclassExcelUtil{publicstatic<T>WorkbookwriteToExcelByList(String[] array,List<T> list ,Class<T> clazz){//创建工作薄Workbook wb =newXSSFWorkbook();//标题和页码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);//创建sheetSheet 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 =(newPropertyDescriptor(field.getName(), clazz)).getReadMethod().invoke(t);setValue(cell , value);//设置数据的样式
                    cell.setCellStyle(dataStyle);}
                index++;}}catch(Exception e){
            e.printStackTrace();}return wb;}/**
     * 设置cellValue
     * 这里可以根据value的类型，进行格式化，比如日期格式化
     */privatestaticvoidsetValue(Cell cell ,Object value){DateFormat dateFormat =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");if(value !=null&&!"".equals(value)){if(value instanceofDate){
                cell.setCellValue(dateFormat.format(value));}else{
                cell.setCellValue(value.toString());}}}}

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

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

本文是采用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方法：

@OverridepublicStringtoString(){return id +","+ title +","+ content  +","+ sourceUrl  +","+ imgUrl +","+ createTime +","+ updateTime +","+ delFlag;}

@RestControllerpublicclassResultController{@AutowiredprivateBaiDuResultDao dao;@GetMapping("/export")publicvoidexportResult(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导出