百万级数据导出 Excel 实战(含完整代码)

小七学习网,助您升职加薪,遇问题可联系:客服微信【1601371900】 备注:来自网站

Apache POI 是开源的一套 Java API,专门针对 Office 格式文档提供读和写功能,比如:导出百万级数据到 Excel 文档。 文章使用 POI 技术演示了三种方式导出数据到 Exc…

Apache POI 是开源的一套 Java API,专门针对 Office 格式文档提供读和写功能,比如:导出百万级数据到 Excel 文档。

文章使用 POI 技术演示了三种方式导出数据到 Excel,第一种是使用 HSSF 机制实现 2003 或以下版本 Excel 导出,第二种是使用 XSSF 机制实现 2007-2010 版本 Excel 导出,第三种是使用 SXSSF 机制实现百万级数据 Excel 导出,我会详细介绍具体开发过程、测试步骤、贴出完整代码并提供下载。

将会获得以下知识:

  1. 2003 或以下版本 Excel 导出
  2. 2007-2010 版本 Excel 导出
  3. 百万级数据 Excel 导出
  4. MySQL 模拟 100 万条数据存储过程

适合人群:Java 初中级开发。



数据库表设计及模拟数据脚本

本文只是演示如何查询 100 万数据并导出到 Excel,所以只设计了一张简单的用户表,创建表的脚本如下:

CREATE TABLE `t_user` (  `id` int(11) NOT NULL,  `user_name` varchar(50) DEFAULT NULL COMMENT \'用户名\',  `nick_name` varchar(50) DEFAULT NULL COMMENT \'昵称\',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

模拟 100 万条数据的存储过程,毕竟是需要插入 100 万条数据,执行存储过程需花费 1 个小时左右,脚本如下:

### 如果存在则删除存储过程drop procedure if exists p_add_data;### 创建存储过程create procedure p_add_data()begin    declare id int;  declare userName varchar(20);  declare nickName varchar(20);    set id=0;  set userName=\'\';  set nickName=\'\';  while id<1000000 do        set userName=CONCAT(\'用户名\', (id+1));    set nickName=CONCAT(\'昵称\', (id+1));        insert into t_user values(id+1, userName, nickName);          set id=id+1;  end while;end### 调用存储过程,模拟 100 万条数据call p_add_data();

具体开发过程及代码分析

代码结构

在这里插入图片描述

pom.xml 文件配置

下面配置是工程需要使用的所有 jar 和 maven 打包策略,即 pom.xml 文件,代码如下:

    <?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.6.0</version>            <relativePath/> <!-- lookup parent from repository -->        </parent>        <groupId>com.example</groupId>        <artifactId>demo-bigdata-download</artifactId>        <version>0.0.1-SNAPSHOT</version>        <name>demo-bigdata-download</name>        <description>Demo project for Spring Boot</description>        <properties>            <java.version>1.8</java.version>        </properties>        <dependencies>            <dependency>                <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>org.springframework.boot</groupId>                <artifactId>spring-boot-starter-web</artifactId>            </dependency>            <dependency>                <groupId>org.springframework.boot</groupId>                <artifactId>spring-boot-starter-aop</artifactId>            </dependency>            <dependency>                <groupId>org.apache.poi</groupId>                <artifactId>poi</artifactId>                <version>3.15</version>            </dependency>            <dependency>                <groupId>org.apache.poi</groupId>                <artifactId>poi-ooxml</artifactId>                <version>3.14</version>            </dependency>            <dependency>                <groupId>cn.hutool</groupId>                <artifactId>hutool-all</artifactId>                <version>4.0.3</version>            </dependency>            <dependency>                <groupId>com.baomidou</groupId>                <artifactId>mybatis-plus</artifactId>                <version>2.1.9</version>                <exclusions>                    <exclusion>                        <groupId>org.freemarker</groupId>                        <artifactId>freemarker</artifactId>                    </exclusion>                </exclusions>            </dependency>            <dependency>                <groupId>com.baomidou</groupId>                <artifactId>mybatis-plus-boot-starter</artifactId>                <version>2.1.9</version>            </dependency>            <dependency>                <groupId>mysql</groupId>                <artifactId>mysql-connector-java</artifactId>            </dependency>            <dependency>                <groupId>javax.validation</groupId>                <artifactId>validation-api</artifactId>                <version>2.0.1.Final</version>            </dependency>        </dependencies>        <build>            <plugins>                <plugin>                    <groupId>org.springframework.boot</groupId>                    <artifactId>spring-boot-maven-plugin</artifactId>                </plugin>            </plugins>        </build>    </project>

工程配置文件和启动类

application.properties 配置了数据库相关配置、mybatis-plus 相关配置、poi 相关等配置,启动类也是非常简单,具体代码如下: application.properties 文件:

    server.port=8888    spring.datasource.type=com.zaxxer.hikari.HikariDataSource    spring.datasource.url=jdbc:mysql://127.0.0.1:3306/download?useSSL=false&useUnicode=yes&characterEncoding=utf-8&serverTimezone=GMT%2B8    spring.datasource.username=root    spring.datasource.password=123456    mybatis-plus.mapper-locations=classpath:/mapper/*Mapper.xml    mybatis-plus.typeAliasesPackage=com.example.demo.entity    ###主键类型  0:\"数据库 ID 自增\", 1:\"用户输入 ID\",2:\"全局唯一 ID (数字类型唯一 ID)\", 3:\"全局唯一 ID UUID\";    mybatis-plus.global-config.id-type=2    #字段策略 0:\"忽略判断\",1:\"非 NULL 判断\"),2:\"非空判断\"    mybatis-plus.global-config.field-strategy=2    ###驼峰下划线转换    mybatis-plus.global-config.db-column-underline=true    ###刷新 mapper 调试神器    mybatis-plus.global-config.refresh-mapper=false

DemoBigdataDownloadApplication 文件:

    package com.example.demo;    import org.springframework.boot.SpringApplication;    import org.springframework.boot.autoconfigure.SpringBootApplication;    import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;    @SpringBootApplication    public class DemoBigdataDownloadApplication {        public static void main(String[] args) {            SpringApplication.run(DemoBigdataDownloadApplication.class, args);        }    }

MybatisPlus 相关配置类、实体类和 Mapper 类

MybatisPlusConfig 配置了 SQL 执行效率插件,控制台打印操作数据库时 sql 语句和执行语句的耗时,配置了分页插件拦截器,让我们的分页代码更简洁,代码如下:

    package com.example.demo.config;    import com.baomidou.mybatisplus.plugins.PaginationInterceptor;    import com.baomidou.mybatisplus.plugins.PerformanceInterceptor;    import org.mybatis.spring.annotation.MapperScan;    import org.springframework.context.annotation.Bean;    import org.springframework.context.annotation.Configuration;    import org.springframework.context.annotation.EnableAspectJAutoProxy;    import org.springframework.transaction.annotation.EnableTransactionManagement;    @Configuration    @EnableTransactionManagement    @EnableAspectJAutoProxy(proxyTargetClass = true)    @MapperScan(\"com.example.demo.mapper*\")    public class MybatisPlusConfig {        /**         * mybatis-plus SQL 执行效率插件【生产环境可以关闭】         */        @Bean        public PerformanceInterceptor performanceInterceptor() {            PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();            performanceInterceptor.setWriteInLog(true);            return new PerformanceInterceptor();        }        /**         * mybatis-plus 分页插件<br>         * 文档:http://mp.baomidou.com<br>         */        @Bean        public PaginationInterceptor paginationInterceptor() {            PaginationInterceptor paginationInterceptor = new PaginationInterceptor();            paginationInterceptor.setLocalPage(true);            return paginationInterceptor;        }    }    package com.example.demo.entity;    import com.baomidou.mybatisplus.annotations.TableName;    import com.example.demo.base.BaseEntity;    @TableName(\"T_USER\")    public class UserEntity extends BaseEntity<UserEntity> {        /**         * 用户名         */        private String userName;        /**         * 昵称         */        private String nickName;        public String getUserName() {            return userName;        }        public void setUserName(String userName) {            this.userName = userName;        }        public String getNickName() {            return nickName;        }        public void setNickName(String nickName) {            this.nickName = nickName;        }        /**         * 用户表的列枚举         */        public enum Column {            /**             * 用户 id 列             */            ID,            /**             * 用户名列             */            USER_NAME,            /**             * 用户昵称列             */            NICK_NAME;        }    }    package com.example.demo.mapper;    import com.baomidou.mybatisplus.mapper.BaseMapper;    import com.example.demo.entity.UserEntity;    public interface UserMapper extends BaseMapper<UserEntity> {    }

基础相关类和 Excel 导出工具类

基础相关类包括实体基类、分页类、VO 类,Excel 导出工具类涵盖了 Excel 2003、2007 及 2010 版本导出实现方案,代码如下:

    package com.example.demo.base;    import com.baomidou.mybatisplus.activerecord.Model;    import com.baomidou.mybatisplus.annotations.TableId;    import com.baomidou.mybatisplus.enums.IdType;    /**     * 实体基础类     */    @SuppressWarnings({ \"serial\", \"rawtypes\" })    public abstract class BaseEntity<T extends Model> extends Model<T> {        /**         * 统一配置的主键,采用统一序列赋值         */        @TableId(value = \"ID\", type = IdType.ID_WORKER)        private Long id;        public Long getId() {            return id;        }        public void setId(Long id) {            this.id = id;        }        @Override        protected Long pkVal() {            return this.id;        }    }    package com.example.demo.base;    import com.baomidou.mybatisplus.plugins.pagination.Pagination;    import java.util.List;    /**     * 分页对象,因为 mybatis 插件返回的是一个零件.     */    public class Page<T> {        /**         * 页码         */        private Integer pageNumber;        /**         * 每页记录数         */        private Integer pageSize;        /**         * 总记录数         */        private Integer total;        /**         * 总页数         */        private Integer totalPage;        /**         * 分页数据列表         */        private List<T> list;        public Page(Integer pageNumber, Integer pageSize, Integer total, Integer totalPage, List<T> list) {            this.pageNumber = pageNumber;            this.pageSize = pageSize;            this.total = total;            this.totalPage = totalPage;            this.list = list;        }        public Page(List<T> list, Pagination page) {            this(page.getCurrent(), page.getSize(), page.getTotal(), page.getPages(), list);        }        public Integer getPageNumber() {            return pageNumber;        }        public void setPageNumber(Integer pageNumber) {            this.pageNumber = pageNumber;        }        public Integer getPageSize() {            return pageSize;        }        public void setPageSize(Integer pageSize) {            this.pageSize = pageSize;        }        public Integer getTotal() {            return total;        }        public void setTotal(Integer total) {            this.total = total;        }        public Integer getTotalPage() {            return totalPage;        }        public void setTotalPage(Integer totalPage) {            this.totalPage = totalPage;        }        public List<T> getList() {            return list;        }        public void setList(List<T> list) {            this.list = list;        }    }    package com.example.demo.base;    import com.baomidou.mybatisplus.plugins.pagination.Pagination;    import javax.validation.constraints.Min;    import java.io.Serializable;    /**     * 分页查询参数     */    public class PageQueryParam implements Serializable {        @Min(value = 1, message = \"页码最小值为 1\")        private Integer pageNumber = 1;        @Min(value = 1, message = \"页大小最小值为 10\")        private Integer pageSize = 10;        public Integer getPageNumber() {            return pageNumber;        }        public void setPageNumber(Integer pageNumber) {            if (pageNumber != null) {                this.pageNumber = pageNumber;            }        }        public Integer getPageSize() {            return pageSize;        }        public void setPageSize(Integer pageSize) {            if (pageSize != null) {                this.pageSize = pageSize;            }        }        /**         * 获取分页对象         *         * @return 分页对象         */        public Pagination toPagination() {            return new Pagination(pageNumber, pageSize);        }        /**         * 获取分页对象         *         * @return 分页对象         */        public <T> com.baomidou.mybatisplus.plugins.Page<T> getMybatisPage(Class<T> c) {            return new com.baomidou.mybatisplus.plugins.Page<>(pageNumber, pageSize);        }    }    package com.example.demo.vo;    import com.example.demo.base.PageQueryParam;    import java.io.Serializable;    /**     * 查询用户列表 VO     */    public class UserPageVO extends PageQueryParam implements Serializable {        /**         * 昵称         */        private String nickName;        /**         * 用户名         */        private String userName;        /**         * 用户 ID         */        private Long id;        public String getNickName() {            return nickName;        }        public void setNickName(String nickName) {            this.nickName = nickName;        }        public String getUserName() {            return userName;        }        public void setUserName(String userName) {            this.userName = userName;        }        public Long getId() {            return id;        }        public void setId(Long id) {            this.id = id;        }    }    package com.example.demo.util;    import cn.hutool.core.util.StrUtil;    import org.apache.poi.hssf.usermodel.*;    import org.apache.poi.ss.usermodel.*;    import org.apache.poi.xssf.streaming.SXSSFSheet;    import org.apache.poi.xssf.streaming.SXSSFWorkbook;    import org.apache.poi.xssf.usermodel.*;    import java.io.ByteArrayOutputStream;    import java.io.FileOutputStream;    import java.io.IOException;    import java.math.BigDecimal;    import java.util.List;    import java.util.Map;    public class ExcelExportUtil {        //各个列的表头        private String[] headList;        //各个列的元素 key 值        private String[] headKey;        //需要填充的数据信息        private List<Map> data;        //默认字体大小        private int fontSize = 14;        //工作表名称        private String sheetName = \"xxx 数据\";        public String[] getHeadList() {            return headList;        }        public void setHeadList(String[] headList) {            this.headList = headList;        }        public String[] getHeadKey() {            return headKey;        }        public void setHeadKey(String[] headKey) {            this.headKey = headKey;        }        public List<Map> getData() {            return data;        }        public void setData(List<Map> data) {            this.data = data;        }        public int getFontSize() {            return fontSize;        }        public void setFontSize(int fontSize) {            this.fontSize = fontSize;        }        public String getSheetName() {            return sheetName;        }        public void setSheetName(String sheetName) {            this.sheetName = sheetName;        }        /**         * 2007-2010 版本 Excel 导出         * @throws IOException         */        public void excelExportByXSSF() throws IOException {            //检查参数配置信息            checkConfig();            //创建工作簿            XSSFWorkbook wb = new XSSFWorkbook();            //创建工作表            XSSFSheet wbSheet= wb.createSheet(this.sheetName);            //设置默认行宽            wbSheet.setDefaultColumnWidth(20);            //设置第一列的宽度            wbSheet.setColumnWidth(0, 20 * 128);            //表头样式            XSSFCellStyle cellStyle = wb.createCellStyle();            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中            XSSFFont fontStyle = wb.createFont();            fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);            fontStyle.setBold(true);   //字体加粗            fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小            cellStyle.setFont(fontStyle);            //数据样式            XSSFCellStyle style = wb.createCellStyle();            //设置单元格样式            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);            //设置字体            XSSFFont font = wb.createFont();            font.setFontHeightInPoints((short) this.fontSize);            font.setFontName(\"宋体\");//设置字体名称            style.setFont(font);            //写入表头数据            XSSFRow row = wbSheet.createRow(0);            //设置列头元素            XSSFCell cellHead = null;            for (int i = 0; i < headList.length; i++) {                cellHead = row.createCell(i);                cellHead.setCellValue(headList[i]);                cellHead.setCellStyle(cellStyle);            }            //写入实体数据信息            int a = 1;            for (int i = 0; i < data.size(); i++) {                XSSFRow roww = wbSheet.createRow(a);                Map map = data.get(i);                XSSFCell cell = null;                for (int j = 0; j < headKey.length; j++) {                    cell = roww.createCell(j);                    cell.setCellStyle(style);                    Object valueObject = map.get(headKey[j]);                    String value = null;                    if (valueObject == null) {                        valueObject = \"\";                    }                    if (valueObject instanceof String) {                        //取出的数据是字符串直接赋值                        value = (String) map.get(headKey[j]);                    } else if (valueObject instanceof Integer) {                        //取出的数据是 Integer                        value = String.valueOf(((Integer) (valueObject)).intValue());                    } else if (valueObject instanceof BigDecimal) {                        //取出的数据是 BigDecimal                        value = String.valueOf(((BigDecimal) (valueObject)).floatValue());                    } else {                        value = valueObject.toString();                    }                    cell.setCellValue(StrUtil.isEmpty(value) ? \"\" : value);                }                a++;            }            try {                FileOutputStream out = new FileOutputStream(\"D:\\\\bigdata\\\\XSSF.xlsx\");                wb.write(out);                out.close();            } catch (Exception ex) {                ex.printStackTrace();                throw new IOException(\"导出 Excel 出现异常,异常信息:\" + ex.getMessage());            }        }        /**         * 2003 或以下版本 Excel 导出         * @throws IOException         */        public void excelExportByHSSF() throws IOException {            //检查参数配置信息            checkConfig();            //创建工作簿            HSSFWorkbook wb = new HSSFWorkbook();            //创建工作表            HSSFSheet wbSheet= wb.createSheet(this.sheetName);            //设置默认行宽            wbSheet.setDefaultColumnWidth(20);            //设置第一列的宽度            wbSheet.setColumnWidth(0, 20 * 128);            //表头样式            HSSFCellStyle cellStyle = wb.createCellStyle();            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中            HSSFFont fontStyle = wb.createFont();            fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);            fontStyle.setBold(true);   //字体加粗            fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小            cellStyle.setFont(fontStyle);            //数据样式            HSSFCellStyle style = wb.createCellStyle();            //设置单元格样式            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);            //设置字体            HSSFFont font = wb.createFont();            font.setFontHeightInPoints((short) this.fontSize);            font.setFontName(\"宋体\");//设置字体名称            style.setFont(font);            //写入表头数据            HSSFRow row = wbSheet.createRow(0);            //设置列头元素            HSSFCell cellHead = null;            for (int i = 0; i < headList.length; i++) {                cellHead = row.createCell(i);                cellHead.setCellValue(headList[i]);                cellHead.setCellStyle(cellStyle);            }            //写入实体数据信息            int a = 1;            for (int i = 0; i < data.size(); i++) {                HSSFRow roww = wbSheet.createRow(a);                Map map = data.get(i);                HSSFCell cell = null;                for (int j = 0; j < headKey.length; j++) {                    cell = roww.createCell(j);                    cell.setCellStyle(style);                    Object valueObject = map.get(headKey[j]);                    String value = null;                    if (valueObject == null) {                        valueObject = \"\";                    }                    if (valueObject instanceof String) {                        //取出的数据是字符串直接赋值                        value = (String) map.get(headKey[j]);                    } else if (valueObject instanceof Integer) {                        //取出的数据是 Integer                        value = String.valueOf(((Integer) (valueObject)).intValue());                    } else if (valueObject instanceof BigDecimal) {                        //取出的数据是 BigDecimal                        value = String.valueOf(((BigDecimal) (valueObject)).floatValue());                    } else {                        value = valueObject.toString();                    }                    cell.setCellValue(StrUtil.isEmpty(value) ? \"\" : value);                }                a++;            }            try {                FileOutputStream out = new FileOutputStream(\"D:\\\\bigdata\\\\HSSF.xls\");                wb.write(out);                out.close();            } catch (Exception ex) {                ex.printStackTrace();                throw new IOException(\"导出 Excel 出现异常,异常信息:\" + ex.getMessage());            }        }        /**         * 百万级数据场景使用         * @throws IOException         */        public void excelExportBySXSSF() throws IOException {            //检查参数配置信息            checkConfig();            //创建工作簿            XSSFWorkbook xssfwb = new XSSFWorkbook();            //创建工作表            SXSSFWorkbook wb = new SXSSFWorkbook(xssfwb, 100);            //创建工作表            SXSSFSheet wbSheet= wb.createSheet(this.sheetName);            //设置默认行宽            wbSheet.setDefaultColumnWidth(20);            //设置第一列的宽度            wbSheet.setColumnWidth(0, 20 * 128);            //表头样式            CellStyle cellStyle = wb.createCellStyle();            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中            Font fontStyle = wb.createFont();            fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);            fontStyle.setBold(true);   //字体加粗            fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小            cellStyle.setFont(fontStyle);            //数据样式            CellStyle style = wb.createCellStyle();            //设置单元格样式            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);            //设置字体            Font font = wb.createFont();            font.setFontHeightInPoints((short) this.fontSize);            font.setFontName(\"宋体\");//设置字体名称            style.setFont(font);            //写入表头数据            Row row = wbSheet.createRow(0);            //设置列头元素            Cell cellHead = null;            for (int i = 0; i < headList.length; i++) {                cellHead = row.createCell(i);                cellHead.setCellValue(headList[i]);                cellHead.setCellStyle(cellStyle);            }            //写入实体数据信息            int a = 1;            for (int i = 0; i < data.size(); i++) {                Row roww = wbSheet.createRow(a);                Map map = data.get(i);                Cell cell = null;                for (int j = 0; j < headKey.length; j++) {                    cell = roww.createCell(j);                    cell.setCellStyle(style);                    Object valueObject = map.get(headKey[j]);                    String value = null;                    if (valueObject == null) {                        valueObject = \"\";                    }                    if (valueObject instanceof String) {                        //取出的数据是字符串直接赋值                        value = (String) map.get(headKey[j]);                    } else if (valueObject instanceof Integer) {                        //取出的数据是 Integer                        value = String.valueOf(((Integer) (valueObject)).intValue());                    } else if (valueObject instanceof BigDecimal) {                        //取出的数据是 BigDecimal                        value = String.valueOf(((BigDecimal) (valueObject)).floatValue());                    } else {                        value = valueObject.toString();                    }                    cell.setCellValue(StrUtil.isEmpty(value) ? \"\" : value);                }                a++;            }            try {                FileOutputStream out = new FileOutputStream(\"D:\\\\bigdata\\\\SXSSF.xlsx\");                wb.write(out);                out.close();            } catch (Exception ex) {                ex.printStackTrace();                throw new IOException(\"导出 Excel 出现异常,异常信息:\" + ex.getMessage());            }        }        /**         * 检查数据配置问题         * @throws IOException 抛出数据异常类         */        protected void checkConfig() throws IOException {            if (headKey == null || headList.length == 0) {                throw new IOException(\"列名数组不能为空或者为空\");            }            if (fontSize < 0) {                throw new IOException(\"字体不能为负值\");            }            if (StrUtil.isEmpty(sheetName)) {                throw new IOException(\"工作表名称不能为空\");            }        }        /**         * 2007-2010 版本 Excel 导出         * @throws IOException         */        public byte[] excelExportByXSSFForWeb() throws IOException {            //检查参数配置信息            checkConfig();            //创建工作簿            XSSFWorkbook wb = new XSSFWorkbook();            //创建工作表            XSSFSheet wbSheet= wb.createSheet(this.sheetName);            //设置默认行宽            wbSheet.setDefaultColumnWidth(20);            //设置第一列的宽度            wbSheet.setColumnWidth(0, 20 * 128);            //表头样式            XSSFCellStyle cellStyle = wb.createCellStyle();            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中            XSSFFont fontStyle = wb.createFont();            fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);            fontStyle.setBold(true);   //字体加粗            fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小            cellStyle.setFont(fontStyle);            //数据样式            XSSFCellStyle style = wb.createCellStyle();            //设置单元格样式            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);            //设置字体            XSSFFont font = wb.createFont();            font.setFontHeightInPoints((short) this.fontSize);            font.setFontName(\"宋体\");//设置字体名称            style.setFont(font);            //写入表头数据            XSSFRow row = wbSheet.createRow(0);            //设置列头元素            XSSFCell cellHead = null;            for (int i = 0; i < headList.length; i++) {                cellHead = row.createCell(i);                cellHead.setCellValue(headList[i]);                cellHead.setCellStyle(cellStyle);            }            //写入实体数据信息            int a = 1;            for (int i = 0; i < data.size(); i++) {                XSSFRow roww = wbSheet.createRow(a);                Map map = data.get(i);                XSSFCell cell = null;                for (int j = 0; j < headKey.length; j++) {                    cell = roww.createCell(j);                    cell.setCellStyle(style);                    Object valueObject = map.get(headKey[j]);                    String value = null;                    if (valueObject == null) {                        valueObject = \"\";                    }                    if (valueObject instanceof String) {                        //取出的数据是字符串直接赋值                        value = (String) map.get(headKey[j]);                    } else if (valueObject instanceof Integer) {                        //取出的数据是 Integer                        value = String.valueOf(((Integer) (valueObject)).intValue());                    } else if (valueObject instanceof BigDecimal) {                        //取出的数据是 BigDecimal                        value = String.valueOf(((BigDecimal) (valueObject)).floatValue());                    } else {                        value = valueObject.toString();                    }                    cell.setCellValue(StrUtil.isEmpty(value) ? \"\" : value);                }                a++;            }            try {                ByteArrayOutputStream out = new ByteArrayOutputStream();                wb.write(out);                return out.toByteArray();            } catch (Exception ex) {                ex.printStackTrace();                throw new IOException(\"导出 Excel 出现异常,异常信息:\" + ex.getMessage());            }        }        /**         * 2003 或以下版本 Excel 导出         * @throws IOException         */        public byte[] excelExportByHSSFForWeb() throws IOException {            //检查参数配置信息            checkConfig();            //创建工作簿            HSSFWorkbook wb = new HSSFWorkbook();            //创建工作表            HSSFSheet wbSheet= wb.createSheet(this.sheetName);            //设置默认行宽            wbSheet.setDefaultColumnWidth(20);            //设置第一列的宽度            wbSheet.setColumnWidth(0, 20 * 128);            //表头样式            HSSFCellStyle cellStyle = wb.createCellStyle();            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中            HSSFFont fontStyle = wb.createFont();            fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);            fontStyle.setBold(true);   //字体加粗            fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小            cellStyle.setFont(fontStyle);            //数据样式            HSSFCellStyle style = wb.createCellStyle();            //设置单元格样式            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);            //设置字体            HSSFFont font = wb.createFont();            font.setFontHeightInPoints((short) this.fontSize);            font.setFontName(\"宋体\");//设置字体名称            style.setFont(font);            //写入表头数据            HSSFRow row = wbSheet.createRow(0);            //设置列头元素            HSSFCell cellHead = null;            for (int i = 0; i < headList.length; i++) {                cellHead = row.createCell(i);                cellHead.setCellValue(headList[i]);                cellHead.setCellStyle(cellStyle);            }            //写入实体数据信息            int a = 1;            for (int i = 0; i < data.size(); i++) {                HSSFRow roww = wbSheet.createRow(a);                Map map = data.get(i);                HSSFCell cell = null;                for (int j = 0; j < headKey.length; j++) {                    cell = roww.createCell(j);                    cell.setCellStyle(style);                    Object valueObject = map.get(headKey[j]);                    String value = null;                    if (valueObject == null) {                        valueObject = \"\";                    }                    if (valueObject instanceof String) {                        //取出的数据是字符串直接赋值                        value = (String) map.get(headKey[j]);                    } else if (valueObject instanceof Integer) {                        //取出的数据是 Integer                        value = String.valueOf(((Integer) (valueObject)).intValue());                    } else if (valueObject instanceof BigDecimal) {                        //取出的数据是 BigDecimal                        value = String.valueOf(((BigDecimal) (valueObject)).floatValue());                    } else {                        value = valueObject.toString();                    }                    cell.setCellValue(StrUtil.isEmpty(value) ? \"\" : value);                }                a++;            }            try {                ByteArrayOutputStream out = new ByteArrayOutputStream();                wb.write(out);                return out.toByteArray();            } catch (Exception ex) {                ex.printStackTrace();                throw new IOException(\"导出 Excel 出现异常,异常信息:\" + ex.getMessage());            }        }        /**         * 百万级数据场景使用         * @throws IOException         */        public byte[] excelExportBySXSSFForWeb() throws IOException {            //检查参数配置信息            checkConfig();            //创建工作簿            XSSFWorkbook xssfwb = new XSSFWorkbook();            //创建工作表            SXSSFWorkbook wb = new SXSSFWorkbook(xssfwb, 100);            //创建工作表            SXSSFSheet wbSheet= wb.createSheet(this.sheetName);            //设置默认行宽            wbSheet.setDefaultColumnWidth(20);            //设置第一列的宽度            wbSheet.setColumnWidth(0, 20 * 128);            //表头样式            CellStyle cellStyle = wb.createCellStyle();            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中            Font fontStyle = wb.createFont();            fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);            fontStyle.setBold(true);   //字体加粗            fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小            cellStyle.setFont(fontStyle);            //数据样式            CellStyle style = wb.createCellStyle();            //设置单元格样式            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);            //设置字体            Font font = wb.createFont();            font.setFontHeightInPoints((short) this.fontSize);            font.setFontName(\"宋体\");//设置字体名称            style.setFont(font);            //写入表头数据            Row row = wbSheet.createRow(0);            //设置列头元素            Cell cellHead = null;            for (int i = 0; i < headList.length; i++) {                cellHead = row.createCell(i);                cellHead.setCellValue(headList[i]);                cellHead.setCellStyle(cellStyle);            }            //写入实体数据信息            int a = 1;            for (int i = 0; i < data.size(); i++) {                Row roww = wbSheet.createRow(a);                Map map = data.get(i);                Cell cell = null;                for (int j = 0; j < headKey.length; j++) {                    cell = roww.createCell(j);                    cell.setCellStyle(style);                    Object valueObject = map.get(headKey[j]);                    String value = null;                    if (valueObject == null) {                        valueObject = \"\";                    }                    if (valueObject instanceof String) {                        //取出的数据是字符串直接赋值                        value = (String) map.get(headKey[j]);                    } else if (valueObject instanceof Integer) {                        //取出的数据是 Integer                        value = String.valueOf(((Integer) (valueObject)).intValue());                    } else if (valueObject instanceof BigDecimal) {                        //取出的数据是 BigDecimal                        value = String.valueOf(((BigDecimal) (valueObject)).floatValue());                    } else {                        value = valueObject.toString();                    }                    cell.setCellValue(StrUtil.isEmpty(value) ? \"\" : value);                }                a++;            }            try {                ByteArrayOutputStream out = new ByteArrayOutputStream();                wb.write(out);                return out.toByteArray();            } catch (Exception ex) {                ex.printStackTrace();                throw new IOException(\"导出 Excel 出现异常,异常信息:\" + ex.getMessage());            }        }    }

Controller 类和 Service 类

Controller 类了 3 个接口,分别是 Excel2003 版使用 HSSFWorkbook 类实现导出;Excel 2007~Excel 2010 版使用 XSSFWorkbook 类实现导出,百万级数据使用 SXSSFWorkbook 类实现导出,代码如下:

    package com.example.demo.controller;    import com.example.demo.service.DemoService;    import org.springframework.beans.factory.annotation.Autowired;    import org.springframework.http.HttpHeaders;    import org.springframework.web.bind.annotation.GetMapping;    import org.springframework.web.bind.annotation.RestController;    import javax.servlet.http.HttpServletResponse;    @RestController    public class DemoController {        @Autowired        private DemoService demoService;        /**         * 2003 或以下版本 Excel 导出         * @param response         * @throws Exception         */        @GetMapping(value = \"/demo/export/HSSF\")        public void excelExportByHSSF(HttpServletResponse response) throws Exception {            byte[] data = demoService.excelExportByHSSF();            response.setContentType(\"application/vnd.ms-excel;charset=UTF-8\");            response.addHeader(HttpHeaders.CONTENT_DISPOSITION, \"attachment; filename=HSSF.xls\");            response.setContentLength(data.length);            response.getOutputStream().write(data);            response.getOutputStream().flush();        }        /**         * 2007-2010 版本 Excel 导出         * @param response         * @throws Exception         */        @GetMapping(value = \"/demo/export/XSSF\")        public void excelExportByXSSF(HttpServletResponse response) throws Exception {            byte[] data = demoService.excelExportByXSSF();            response.setContentType(\"application/vnd.ms-excel;charset=UTF-8\");            response.addHeader(HttpHeaders.CONTENT_DISPOSITION, \"attachment; filename=XSSF.xls\");            response.setContentLength(data.length);            response.getOutputStream().write(data);            response.getOutputStream().flush();        }        /**         * 百万级数据导出场景使用         * @param response         * @throws Exception         */        @GetMapping(value = \"/demo/export/SXSSF\")        public void excelExportBySXSSF(HttpServletResponse response) throws Exception {            byte[] data = demoService.excelExportBySXSSF();            response.setContentType(\"application/vnd.ms-excel;charset=UTF-8\");            response.addHeader(HttpHeaders.CONTENT_DISPOSITION, \"attachment; filename=SXSSF.xls\");            response.setContentLength(data.length);            response.getOutputStream().write(data);            response.getOutputStream().flush();        }    }    package com.example.demo.service;    import com.baomidou.mybatisplus.mapper.EntityWrapper;    import com.baomidou.mybatisplus.plugins.pagination.Pagination;    import com.example.demo.entity.UserEntity;    import com.example.demo.mapper.UserMapper;    import com.example.demo.util.ExcelExportUtil;    import com.example.demo.vo.UserPageVO;    import org.springframework.stereotype.Service;    import javax.annotation.Resource;    import java.io.IOException;    import java.util.ArrayList;    import java.util.HashMap;    import java.util.List;    import java.util.Map;    @Service    public class DemoService {        @Resource        private UserMapper userMapper;        public List<UserEntity> pageUser(UserPageVO userPageVO) {            Pagination pagination = userPageVO.toPagination();            EntityWrapper<UserEntity> wrapperDecorator = new EntityWrapper<>();            return userMapper.selectPage(pagination, wrapperDecorator);        }        public byte[] excelExportByHSSF() throws IOException {            ExcelExportUtil excelExport = new ExcelExportUtil();            excelExport.setHeadKey(this.setHeadKey());            excelExport.setHeadList(this.setHeadList());            excelExport.setData(this.setData());            excelExport.setFontSize(14);            return excelExport.excelExportByHSSFForWeb();        }        public byte[] excelExportByXSSF() throws IOException {            ExcelExportUtil excelExport = new ExcelExportUtil();            excelExport.setHeadKey(this.setHeadKey());            excelExport.setHeadList(this.setHeadList());            excelExport.setData(this.setData());            excelExport.setFontSize(14);            return excelExport.excelExportByXSSFForWeb();        }        public byte[] excelExportBySXSSF() throws IOException {            ExcelExportUtil excelExport = new ExcelExportUtil();            excelExport.setHeadKey(this.setHeadKey());            excelExport.setHeadList(this.setHeadList());            //分页默认参数            int pageNum = 1;//页数            int pageSize = 100000;//页数大小            UserPageVO userPageVO = new UserPageVO();            userPageVO.setPageNumber(pageNum);            userPageVO.setPageSize(pageSize);            //总导出数据            List<Map> allMapList = new ArrayList<>();            //每次分页查询的数据,即临时数据            List<UserEntity> userList = new ArrayList<>();            //遍历查询数据,每次 10 万,避免一次性查询 100 万数据比较慢,影响性能            do {                //清空临时数据                userList.clear();                // 动态设置分页的页数                userPageVO.setPageNumber(pageNum);                // 分页查询数据                userList = this.pageUser(userPageVO);                //转换数据                List<Map> _mapList = new ArrayList<>();                for (UserEntity user: userList) {                    Map _map = new HashMap();                    _map.put(\"id\", user.getId());                    _map.put(\"userName\", user.getUserName());                    _map.put(\"nickName\", user.getNickName());                    _mapList.add(_map);                }                //合并数据                allMapList.addAll(_mapList);                //页码+1                pageNum++;                //每次查询 10 万,查询 10 次即 100 万                if (pageNum > 10 || userList.size() == 0){                    break;                }            } while (true);            excelExport.setData(allMapList);            excelExport.setFontSize(14);            return excelExport.excelExportBySXSSFForWeb();        }        /**         * 需要填充的数据信息         * @return         */        private List<Map> setData(){            List<Map> dataList = new ArrayList<>();            Map map1 = new HashMap();            map1.put(\"id\", 1);            map1.put(\"userName\", \"zhangsan\");            map1.put(\"nickName\", \"小三\");            Map map2 = new HashMap();            map2.put(\"id\", 2);            map2.put(\"userName\", \"lisi\");            map2.put(\"nickName\", \"小四\");            dataList.add(map1);            dataList.add(map2);            return dataList;        }        /**         * 表头数据的对应的 key 值         * @return         */        private String[] setHeadKey(){            String[] headKey = new String[]{\"id\",\"userName\",\"nickName\"};            return headKey;        }        /**         * 表头数据         * @return         */        private String[] setHeadList(){            String[] headList = new String[]{\"ID\",\"用户名\",\"昵称\"};            return headList;        }    }

JUnit 测试类

JUnit 测试类也是定义了 3 个方法,分别是 Excel 2003 版导出、Excel2007~Excel2010 版导出、百万级数据(没有查询数据)导出,代码如下:

    package com.example.demo;    import com.example.demo.util.ExcelExportUtil;    import org.junit.jupiter.api.Test;    import org.springframework.boot.test.context.SpringBootTest;    import java.io.IOException;    import java.util.ArrayList;    import java.util.HashMap;    import java.util.List;    import java.util.Map;    @SpringBootTest    class ApplicationTests {        /**         *  2003 或以下版本 Excel 导出         * @throws IOException         */        @Test        void excelExportByHSSF() throws IOException {            ExcelExportUtil excelExport = new ExcelExportUtil();            excelExport.setHeadKey(this.setHeadKey());            excelExport.setHeadList(this.setHeadList());            excelExport.setData(this.setData());            excelExport.setFontSize(14);            excelExport.excelExportByHSSF();        }        /**         * 2007-2010 版本 Excel 导出         * @throws IOException         */        @Test        void excelExportByXSSF() throws IOException {            ExcelExportUtil excelExport = new ExcelExportUtil();            excelExport.setHeadKey(this.setHeadKey());            excelExport.setHeadList(this.setHeadList());            excelExport.setData(this.setData());            excelExport.setFontSize(14);            excelExport.excelExportByXSSF();        }        /**         * 百万级数据场景使用         * @throws IOException         */        @Test        void excelExportBySXSSF() throws IOException {            ExcelExportUtil excelExport = new ExcelExportUtil();            excelExport.setHeadKey(this.setHeadKey());            excelExport.setHeadList(this.setHeadList());            excelExport.setData(this.setDataForSXSSF());            excelExport.setFontSize(14);            excelExport.excelExportBySXSSF();        }        /**         * 需要填充的数据信息         * @return         */        private List<Map> setData(){            List<Map> dataList = new ArrayList<>();            Map map1 = new HashMap();            map1.put(\"id\", 1);            map1.put(\"userName\", \"wangwu\");            map1.put(\"nickName\", \"王五\");            Map map2 = new HashMap();            map2.put(\"id\", 2);            map2.put(\"userName\", \"chenliu\");            map2.put(\"nickName\", \"陈六\");            dataList.add(map1);            dataList.add(map2);            return dataList;        }        /**         * 需要填充的数据信息(一百万条数据,不查数据库)         * @return         */        private List<Map> setDataForSXSSF(){            List<Map> dataList = new ArrayList<>();            for (int i = 0; i < 1000000; i++) {                Map map = new HashMap();                map.put(\"id\", i+1);                map.put(\"userName\", \"用户\"+(i+1));                map.put(\"nickName\", \"昵称\"+(i+1));                dataList.add(map);            }            return dataList;        }        /**         * 表头数据的对应的 key 值         * @return         */        private String[] setHeadKey(){            String[] headKey = new String[]{\"id\",\"userName\",\"nickName\"};            return headKey;        }        /**         * 表头数据         * @return         */        private String[] setHeadList(){            String[] headList = new String[]{\"ID\",\"用户名\",\"昵称\"};            return headList;        }    }

JUnit 代码测试

1. 2003 或以下版本 Excel 导出,运行 ApplicationTests 类 excelExportByHSSF 方法

在这里插入图片描述

在这里插入图片描述

2. 2007-2010 版本 Excel 导出,运行 ApplicationTests 类 excelExportByXSSF 方法

在这里插入图片描述

在这里插入图片描述

3. 百万级数据 Excel 导出,运行 ApplicationTests 类 excelExportBySXSSF 方法,仅仅耗时 12 秒就可以导出 100 万条数据

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

接口测试

1. 2003 或以下版本 Excel 导出

url:http://127.0.0.1:8888/demo/export/HSSF

在这里插入图片描述

在这里插入图片描述

2. 2003 或以下版本 Excel 导出

url:http://127.0.0.1:8888/demo/export/XSSF

在这里插入图片描述

在这里插入图片描述

3. 百万级数据 Excel 导出,查询数据库 100 万条数据并导出到 Excel,仅仅耗时 40 秒左右

url:http://127.0.0.1:8888/demo/export/SXSSF

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

注意事项

  1. 2003 版本 Excel 的每个 sheel 的最大行数是 65536(即 256*256),2007、2010 或更高版本 Excel 的最大行数是 1048576(即 1024*1024)。
  2. 执行模拟 100 万条数据存储过程需要等待 1 个小时左右才能执行完毕,毕竟是在遍历执行 insert 语句 100 万次。
  3. MyBatis 分页查询每次查 10 万条数据比一次性查 100 万条数据效率高,并可以排除内存溢出风险。

总结

通过这次的百万级数据导出 Excel 实战,让我们掌握了三种导出方式应对不同的 Excel 版本,特别是得到了一套可运行的高性能的百万级数据导出实现方案,可以快速运用到日常开发当中,解决各种大数据量导出场景。

百万级数据导出 Excel 实战完整代码已上传到 Gitee,下载地址如下:

Gitee

PS:如有写错请指正,感谢您阅读。

小七学习网,助您升职加薪,遇问题可联系:客服微信【1601371900】 备注:来自网站

免责声明: 1、本站信息来自网络,版权争议与本站无关 2、本站所有主题由该帖子作者发表,该帖子作者与本站享有帖子相关版权 3、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者和本站的同意 4、本帖部分内容转载自其它媒体,但并不代表本站赞同其观点和对其真实性负责 5、用户所发布的一切软件的解密分析文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。 6、您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。 7、请支持正版软件、得到更好的正版服务。 8、如有侵权请立即告知本站(邮箱:1099252741@qq.com,备用微信:1099252741),本站将及时予与删除 9、本站所发布的一切破解补丁、注册机和注册信息及软件的解密分析文章和视频仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。如有侵权请邮件与我们联系处理。