精华内容
参与话题
问答
  • POI百万级大数据量EXCEL导出

    万次阅读 多人点赞 2018-09-25 20:18:05
    excel导出,如果数据量在百万级,会出现俩点内存溢出的问题: 1. 查询数据量过大,导致内存溢出。 该问题可以通过分批查询来解决; 2. 最后下载的时候大EXCEL转换的输出流内存溢出;该方式可以通过新版的...

    一. 简介

              excel导出,如果数据量在百万级,会出现俩点内存溢出的问题:

              1. 查询数据量过大,导致内存溢出。 该问题可以通过分批查询来解决;

              2. 最后下载的时候大EXCEL转换的输出流内存溢出;该方式可以通过新版的SXSSFWorkbook来解决,可通过其构造函数执指定在内存中缓存的行数,剩余的会自动缓存在硬盘的临时目录上,同时,并不会存在页面卡顿的情况;

              3. 为了能够使用不同的mapper并分批写数据, 采用了外观模式和模板方法模式,大体分三步:

                  a. 根据总数量生成excel,确定sheet的数量和写标题;

                  b. 写数据,在可变的匿名内部类中实现写入逻辑;
                  c. 转换输出流进行下载;

              4. 使用案例在3.3 ServiceImpl中,可自行书写。

              5. 最近太忙,写的太仓促,性能我感觉还有一倍的优化空间,比如循环次数,现在存在无意义空循环(这个耗时比较多)的情况,缓冲流,mybatis的浮标等,待优化........   

              6. 优化空间很大  刚试了下 把空循环去掉  4个字段 90W条数据  40s  180W  75s  300W 122s

              7. 转战阿里开源的EasyExcel了, 那个内存控制在kb级别,绝对不会内存溢出。使用说明请参见博主的另一篇文章:

    阿里开源(EasyExcel)---导出EXCEL

              8. 导入的也有,请参见另一篇文章: 阿里开源(EasyExcel)---导入EXCEL

    二. 工具代码

    2.1 配置

    2.1.1 pom.xml

          <!-- poi -->
          <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi</artifactId>
              <version>3.17</version>
          </dependency>
          <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi-ooxml</artifactId>
              <version>3.17</version>
          </dependency>

    注: 如果是springboot2.0,则不需要poi依赖,如果是1.0,则需要poi依赖,并且poi和poi-ooxml的版本要保持一致。

              别的依赖我就不加了。

    2.1.2 application.yml

    # pagehelper
    pagehelper:
        helperDialect: mysql
        reasonable: false # 如果没有数据  返回空 而非最后一页的数据
        supportMethodsArguments: true
        params: count=countSql
        returnPageInfo: check

    注:  reasonable一定要为false, 其他的我就不粘了。

    2.2 ExcelConstant

    package com.yzx.caasscs.constant;
    
    /**
     * @author qjwyss
     * @date 2018/9/19
     * @description EXCEL常量类
     */
    public class ExcelConstant {
    
        /**
         * 每个sheet存储的记录数 100W
         */
        public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    
        /**
         * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
         */
        public static final Integer PER_WRITE_ROW_COUNT = 200000;
    
    
        /**
         * 每个sheet的写入次数 5
         */
        public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;
    
    
    }
    

    注: xlsx模式的excel每个sheet最多存储104W,此处我就每个sheet存储了 100W数据;每次查询20W数据; 自己根据内存来调合适的大小,写入次数待优化。

    2.3 写数据委托类

    package com.yzx.caasscs.util;
    
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    
    /**
     * @author qjwyss
     * @date 2018/9/20
     * @description EXCEL写数据委托类
     */
    public interface WriteExcelDataDelegated {
    
        /**
         * EXCEL写数据委托类  针对不同的情况自行实现
         *
         * @param eachSheet     指定SHEET
         * @param startRowCount 开始行
         * @param endRowCount   结束行
         * @param currentPage   分批查询开始页
         * @param pageSize      分批查询数据量
         * @throws Exception
         */
        public abstract void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;
    
    
    }
    

    2.4 DateUtil工具类(非必须)

    package com.yzx.caasscs.util;
    
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    /**
     * @author qjwyss
     * @date 2018/9/20
     * @description 日期工具类
     */
    public class DateUtil {
    
        public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
    
    
        /**
         * 将日期转换为字符串
         *
         * @param date   DATE日期
         * @param format 转换格式
         * @return 字符串日期
         */
        public static String formatDate(Date date, String format) {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
            return simpleDateFormat.format(date);
        }
    
    
    }
    

    2.5 POI工具类

    package com.yzx.caasscs.util;
    
    
    import com.yzx.caasscs.constant.ExcelConstant;
    import org.apache.poi.xssf.streaming.SXSSFCell;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.Date;
    
    /**
     * @author qjwyss
     * @date 2018/9/18
     * @description POI导出工具类
     */
    public class PoiUtil {
    
        private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);
    
        /**
         * 初始化EXCEL(sheet个数和标题)
         *
         * @param totalRowCount 总记录数
         * @param titles        标题集合
         * @return XSSFWorkbook对象
         */
        public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {
    
            // 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中
            SXSSFWorkbook wb = new SXSSFWorkbook(100);
    
            Integer sheetCount = ((totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0) ?
                    (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT) : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1));
    
            // 根据总记录数创建sheet并分配标题
            for (int i = 0; i < sheetCount; i++) {
                SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
                SXSSFRow headRow = sheet.createRow(0);
    
                for (int j = 0; j < titles.length; j++) {
                    SXSSFCell headRowCell = headRow.createCell(j);
                    headRowCell.setCellValue(titles[j]);
                }
            }
    
            return wb;
        }
    
    
        /**
         * 下载EXCEL到本地指定的文件夹
         *
         * @param wb         EXCEL对象SXSSFWorkbook
         * @param exportPath 导出路径
         */
        public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
            FileOutputStream fops = null;
            try {
                fops = new FileOutputStream(exportPath);
                wb.write(fops);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (null != wb) {
                    try {
                        wb.dispose();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                if (null != fops) {
                    try {
                        fops.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    
    
        /**
         * 下载EXCEL到浏览器
         *
         * @param wb       EXCEL对象XSSFWorkbook
         * @param response
         * @param fileName 文件名称
         * @throws IOException
         */
        public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {
    
            response.setHeader("Content-disposition", "attachment; filename="
                    + new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名
    
            OutputStream outputStream = null;
            try {
                outputStream = response.getOutputStream();
                wb.write(outputStream);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (null != wb) {
                    try {
                        wb.dispose();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                if (null != outputStream) {
                    try {
                        outputStream.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    
    
        /**
         * 导出Excel到本地指定路径
         *
         * @param totalRowCount           总记录数
         * @param titles                  标题
         * @param exportPath              导出路径
         * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
         * @throws Exception
         */
        public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
    
            logger.info("开始导出:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    
            // 初始化EXCEL
            SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
    
            // 调用委托类分批写数据
            int sheetCount = wb.getNumberOfSheets();
            for (int i = 0; i < sheetCount; i++) {
                SXSSFSheet eachSheet = wb.getSheetAt(i);
    
                for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
    
                    int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
                    int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                    int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                    int endRowCount = startRowCount + pageSize - 1;
    
    
                    writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
    
                }
            }
    
    
            // 下载EXCEL
            PoiUtil.downLoadExcelToLocalPath(wb, exportPath);
    
            logger.info("导出完成:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
        }
    
    
        /**
         * 导出Excel到浏览器
         *
         * @param response
         * @param totalRowCount           总记录数
         * @param fileName                文件名称
         * @param titles                  标题
         * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
         * @throws Exception
         */
        public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
    
            logger.info("开始导出:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    
            // 初始化EXCEL
            SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
    
    
            // 调用委托类分批写数据
            int sheetCount = wb.getNumberOfSheets();
            for (int i = 0; i < sheetCount; i++) {
                SXSSFSheet eachSheet = wb.getSheetAt(i);
    
                for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
    
                    int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
                    int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                    int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                    int endRowCount = startRowCount + pageSize - 1;
    
                    writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
    
                }
            }
    
    
            // 下载EXCEL
            PoiUtil.downLoadExcelToWebsite(wb, response, fileName);
    
            logger.info("导出完成:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
        }
    
    
    }
    

    三. 使用DEMO

    3.1 Controller

    package com.yzx.caasscs.controller.organiza;
    
    import com.yzx.caasscs.controller.BaseController;
    import com.yzx.caasscs.service.organiza.UserService;
    import com.yzx.caasscs.vo.PageVO;
    import com.yzx.caasscs.vo.ResultVO;
    import com.yzx.caasscs.vo.organiza.UserVO;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiImplicitParam;
    import io.swagger.annotations.ApiImplicitParams;
    import io.swagger.annotations.ApiOperation;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    import springfox.documentation.annotations.ApiIgnore;
    
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * @author qjwyss
     * @date 2018/8/30
     * @description 用户控制类
     */
    @Api(tags = {"UserController"}, description = "用户Controller")
    @RestController
    @RequestMapping(value = "/user")
    public class UserController extends BaseController {
    
        @Autowired
        private UserService userService;
    
    
        @ApiOperation("导出用户EXCEL")
        @ApiImplicitParams({
                @ApiImplicitParam(paramType = "query", dataType = "Long", name = "loginUid", value = "登录用户UID", required = true),
                @ApiImplicitParam(paramType = "query", dataType = "Long", name = "uid", value = "用户UID", required = true)
        })
        @GetMapping("/export")
        public ResultVO<Void> exportUser(@ApiIgnore UserVO userVO, HttpServletResponse response) throws Exception {
            return this.userService.export(userVO, response);
        }
    
    }
    

    3.2 Service

    package com.yzx.caasscs.service.organiza;
    
    import com.yzx.caasscs.vo.PageVO;
    import com.yzx.caasscs.vo.ResultVO;
    import com.yzx.caasscs.vo.organiza.UserVO;
    
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * @author qjwyss
     * @date 2018/8/30
     * @description 用户SERVICE
     */
    public interface UserService {
    
    
        /**
         * 导出用户EXCEL
         *
         * @param userVO
         * @return VOID
         * @throws Exception
         */
        ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception;
    
    
    }
    

    3.3 ServiceImpl

    package com.yzx.caasscs.service.impl.organiza;
    
    import com.github.pagehelper.PageHelper;
    import com.github.pagehelper.PageInfo;
    import com.yzx.caasscs.constant.middleware.RedisConstant;
    import com.yzx.caasscs.constant.organiza.UserApartmentConstant;
    import com.yzx.caasscs.constant.organiza.UserConstant;
    import com.yzx.caasscs.constant.organiza.UserRoleConstant;
    import com.yzx.caasscs.constant.sys.SysLogConstant;
    import com.yzx.caasscs.entity.dscaasscs.organiza.User;
    import com.yzx.caasscs.entity.dscaasscs.organiza.UserApartment;
    import com.yzx.caasscs.entity.dscaasscs.organiza.UserRole;
    import com.yzx.caasscs.mapper.dscaasscs.organiza.UserApartmentMapper;
    import com.yzx.caasscs.mapper.dscaasscs.organiza.UserMapper;
    import com.yzx.caasscs.mapper.dscaasscs.organiza.UserRoleMapper;
    import com.yzx.caasscs.mapper.dscaasscs.sys.RoleMenuMapper;
    import com.yzx.caasscs.service.organiza.UserService;
    import com.yzx.caasscs.service.sys.SysLogService;
    import com.yzx.caasscs.util.CommonUtil;
    import com.yzx.caasscs.util.DateUtil;
    import com.yzx.caasscs.util.PoiUtil;
    import com.yzx.caasscs.util.WriteExcelDataDelegated;
    import com.yzx.caasscs.vo.PageVO;
    import com.yzx.caasscs.vo.ResultVO;
    import com.yzx.caasscs.vo.organiza.UserApartmentVO;
    import com.yzx.caasscs.vo.organiza.UserRoleVO;
    import com.yzx.caasscs.vo.organiza.UserVO;
    import com.yzx.caasscs.vo.sys.MenuVO;
    import com.yzx.caasscs.vo.sys.RoleMenuVO;
    import com.yzx.caasscs.vo.sys.SysLogVO;
    import net.sf.json.JSONObject;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.util.CollectionUtils;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    /**
     * @author qjwyss
     * @date 2018/8/30
     * @description 用户SERVICEIMPL
     */
    @Service
    public class UserServiceImpl implements UserService {
    
        @Autowired
        private UserMapper userMapper;
    
        @Autowired
        private HttpServletRequest request;
    
    
        @Override
        public ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception {
    
            // 总记录数
            Integer totalRowCount = this.userMapper.selectUserVOCount(userVO);
    
            // 导出EXCEL文件名称
            String filaName = "用户EXCEL";
    
            // 标题
            String[] titles = {"账号", "密码", "状态", "昵称", "职位", "手机号", "邮箱", "创建人ID", "创建时间", "修改人ID", "修改时间"};
    
            // 开始导入
            PoiUtil.exportExcelToWebsite(response, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
                @Override
                public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
    
                    PageHelper.startPage(currentPage, pageSize);
                    List<UserVO> userVOList = userMapper.selectUserVOList(userVO);
    
                    if (!CollectionUtils.isEmpty(userVOList)) {
    
                        // --------------   这一块变量照着抄就行  强迫症 后期也封装起来     ----------------------
                        for (int i = startRowCount; i <= endRowCount; i++) {
                            SXSSFRow eachDataRow = eachSheet.createRow(i);
                            if ((i - startRowCount) < userVOList.size()) {
    
                                UserVO eachUserVO = userVOList.get(i - startRowCount);
                                // ---------   这一块变量照着抄就行  强迫症 后期也封装起来     -----------------------
    
                                eachDataRow.createCell(0).setCellValue(eachUserVO.getUsername() == null ? "" : eachUserVO.getUsername());
                                eachDataRow.createCell(1).setCellValue(eachUserVO.getPassword() == null ? "" : eachUserVO.getPassword());
                                eachDataRow.createCell(2).setCellValue(eachUserVO.getUserState() == null ? "" : (eachUserVO.getUserState() == 1 ? "启用" : "停用"));
                                eachDataRow.createCell(3).setCellValue(eachUserVO.getNickname() == null ? "" : eachUserVO.getNickname());
                                eachDataRow.createCell(4).setCellValue(eachUserVO.getPosition() == null ? "" : eachUserVO.getPosition());
                                eachDataRow.createCell(5).setCellValue(eachUserVO.getMobile() == null ? "" : eachUserVO.getMobile());
                                eachDataRow.createCell(6).setCellValue(eachUserVO.getEmail() == null ? "" : eachUserVO.getEmail());
                                if (null != eachUserVO.getCreateUid()) {
                                    eachDataRow.createCell(7).setCellValue(eachUserVO.getCreateUid());
                                }
                                if (null != eachUserVO.getCreateTime()) {
                                    eachDataRow.createCell(8).setCellValue(DateUtil.formatDate(eachUserVO.getCreateTime(), DateUtil.YYYY_MM_DD_HH_MM_SS));
                                }
                                if (null != eachUserVO.getUpdateUid()) {
                                    eachDataRow.createCell(9).setCellValue(eachUserVO.getUpdateUid());
                                }
                                if (null != eachUserVO.getUpdateTime()) {
                                    eachDataRow.createCell(10).setCellValue(DateUtil.formatDate(eachUserVO.getUpdateTime(), DateUtil.YYYY_MM_DD_HH_MM_SS));
                                }
                            }
                        }
                    }
    
                }
            });
    
            return ResultVO.getSuccess("导出用户EXCEL成功");
        }
    
    
    }
    

    3.4 mapper

    package com.yzx.caasscs.mapper.dscaasscs.organiza;
    
    import com.yzx.caasscs.entity.dscaasscs.organiza.User;
    import com.yzx.caasscs.vo.organiza.UserVO;
    
    import java.util.List;
    
    /**
     * @author qjwyss
     * @date 2018/8/29
     * @description 用户MAPPER
     */
    public interface UserMapper {
    
    
        /**
         * 查询用户列表
         *
         * @param userVO
         * @return UserVO集合
         * @throws Exception
         */
        List<UserVO> selectUserVOList(UserVO userVO) throws Exception;
    
    
        /**
         * 查询用户数量
         *
         * @param userVO
         * @return 用户数量
         * @throws Exception
         */
        Integer selectUserVOCount(UserVO userVO) throws Exception;
    
    }

    3.5 mapper.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.yzx.caasscs.mapper.dscaasscs.organiza.UserMapper" >
    
      <resultMap id="BaseResultMap" type="com.yzx.caasscs.entity.dscaasscs.organiza.User" >
        <id column="uid" property="uid" jdbcType="BIGINT" />
        <result column="username" property="username" jdbcType="VARCHAR" />
        <result column="password" property="password" jdbcType="VARCHAR" />
        <result column="state" property="state" jdbcType="INTEGER" />
        <result column="nickname" property="nickname" jdbcType="VARCHAR" />
        <result column="position" property="position" jdbcType="VARCHAR" />
        <result column="mobile" property="mobile" jdbcType="VARCHAR" />
        <result column="email" property="email" jdbcType="VARCHAR" />
        <result column="create_uid" property="createUid" jdbcType="BIGINT" />
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        <result column="update_uid" property="updateUid" jdbcType="BIGINT" />
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
      </resultMap>
    
    
      <!-- 查询用户分页列表返回MAP -->
      <resultMap id="SelectUserVOListMap" type="com.yzx.caasscs.vo.organiza.UserVO">
        <id column="uid" property="uid" jdbcType="BIGINT" />
        <result column="username" property="username" jdbcType="VARCHAR" />
        <result column="user_state" property="userState" jdbcType="INTEGER" />
        <result column="nickname" property="nickname" jdbcType="VARCHAR" />
        <result column="position" property="position" jdbcType="VARCHAR" />
        <result column="mobile" property="mobile" jdbcType="VARCHAR" />
        <result column="email" property="email" jdbcType="VARCHAR" />
        <result column="create_uid" property="createUid" jdbcType="BIGINT" />
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        <result column="update_uid" property="updateUid" jdbcType="BIGINT" />
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
        <result column="apartment_name" property="apartmentName" jdbcType="VARCHAR" />
        <result column="role_names" property="roleNames" jdbcType="VARCHAR" />
      </resultMap>
    
    
      <sql id="Base_Column_List" >
        uid, username, password, state, nickname, position, mobile, email, create_uid, create_time,
        update_uid, update_time
      </sql>
    
    
      <!-- 查询用户列表 -->
      <select id="selectUserVOList" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultMap="SelectUserVOListMap">
        SELECT
    		U.uid, U.username, U.state AS user_state, U.nickname, U.position, U.mobile, U.email, U.create_uid, U.create_time,
    		U.update_uid, U.update_time,
    		A.apartment_name,
    		(
    			SELECT
    				GROUP_CONCAT( R.role_name ) AS role_name_list
    			FROM
    				user_role AS UR
    				LEFT JOIN role AS R ON R.rid = UR.rid
    			WHERE
    				UR.state > 0
    				AND UR.uid = U.uid
    			GROUP BY UR.uid
    		) role_names
        FROM
            `user` AS U
            LEFT JOIN user_apartment AS UA ON UA.uid = U.uid
            LEFT JOIN apartment AS A ON A.aid = UA.aid
        WHERE
            U.state > 0
            <if test="userState != null">
              AND U.state = #{userState,jdbcType=INTEGER}
            </if>
            <if test="nickname != null and nickname != ''">
              AND U.nickname LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
            </if>
            <if test="apartmentName != null and apartmentName != ''">
              AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
            </if>
        ORDER BY U.create_time DESC
    
      </select>
    
    
      <!-- 查询用户列表数量 -->
      <select id="selectUserVOCount" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultType="java.lang.Integer" >
        SELECT
            COUNT(1)
        FROM
            `user` AS U
            LEFT JOIN user_apartment AS UA ON UA.uid = U.uid
            LEFT JOIN apartment AS A ON A.aid = UA.aid
        WHERE
            U.state > 0
            <if test="userState != null">
              AND U.state = #{userState,jdbcType=INTEGER}
            </if>
            <if test="nickname != null and nickname != ''">
              AND U.nickname LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
            </if>
            <if test="apartmentName != null and apartmentName != ''">
              AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
            </if>
      </select>
      
    
    </mapper>

    4. 测试

    每个sheet存100W,每次查询写20W,每条记录10个字段,时间如下:

    数据量 时间
    100W 3.5分钟
    150W 7分钟

     

     

    展开全文
  • Excel大数据量导出

    2018-11-27 19:48:00
    2019独角兽企业重金招聘Python工程师标准>>> ...
    • 问题

    在进行导出Excel的时候,由于数据量十分大,导致流不能很快的写入到Excel文件,使得流一直在内存中,导致内存占用4个多G,严重影响服务状态。

    • 方案

    找到问题点是因为流或者Excel的数据结构(如XSSFWorkbook等)在内存停留时间太长。所以要么快速处理数据,要么就是将存储位置改变。

    • 结果

    最后我们发现其实Excel已经提供了SXSSFWorkbook。他和XSSFWorkbook的优化点就在于他会将多余的数据存储在硬盘。

    SXSSFWorkbook wb = new SXSSFWorkbook();
    

    当实例化SXSSFWorkbook的时候,默认是当数据量大于100的时候,多余的数据将不会存储在内存中,他会存储到硬盘中。

    • 最后

    果然,JVM内存果然就占用比较少,同时处理速度和使用XSSFWorkbook的时候简直就是天和地!

    • 总结

    当碰到问题的时候,应该多测试代码,debug将问题定位到最细的点。当点位到最细的点的时候才能更好地对有问题的点加入更合适的方案!

    转载于:https://my.oschina.net/u/3095034/blog/2961941

    展开全文
  • java大数据导出excel的几种方法

    千次阅读 2019-02-19 18:10:54
    导出数据是一个应用常用的功能,而使用java语言时常用的工具类莫过于POI。不过当数据量很大时,会经常遇到OOM的问题。通过两天尝试,终于解决了OOM的问题,以下分享一下解决过程。 优化1:首先,我们对导出文件的...

    导出数据是一个应用常用的功能,而使用java语言时常用的工具类莫过于POI。不过当数据量很大时,会经常遇到OOM的问题。通过两天尝试,终于解决了OOM的问题,以下分享一下解决过程。

    优化1:首先,我们对导出文件的格式做了一定优化,使用csv的方式做导出,发现仍然会出现oom。通过梳理逻辑,发现导出的过程是将先将所有结果集一次性读入内存再进行文件的转化导出。于是对结果进行分页转换输出,不过对于改造的文件格式,业务方却不是很认可,要求再次将导出格式设置为excel,于是再次将导出格式定义为excel,进行了优化2的改造,所以对系统的改造如果涉及到业务,一定要先沟通。

    优化2:方案是这样,对大文件进行拆分成小文件进行保存,最后对所有临时文件进行打包导出,询问业务,不同意,于是又增加了对所有小文件进行merge。不过merge文件的过程注定是一个耗费时间的过程,因为需要的打开多个小文件再进行合并,虽然导出文件不再oom,但是超时了,于是进行优化3。

    优化3:经过调研在POI中除了HSSFWorkbook和XSSFWorkbook之外,还有一个SXSSFWorkbook,该类通过设置rowAccessWindowSize
    参数,可以调整保存在内存中的对象数量,大于rowAccessWindowSize的数据会缓存到磁盘上。通常缓存的文件大小会比结果大,如果磁盘空间有限,可以通过setCompressTempFiles参数进行文件压缩(不过压缩会消耗部分性能,实际情况综合取舍)。通过分页加载数据到SXSSFWorkbook中的方式避免了全量数据加载内存导致的溢出情况,但是当数据量巨大时,还是推荐可以将文件进行拆分,否则还是会遇到请求超时的情况。

    优化4:将优化3的方案改为异步下载,通过邮件的方式发送,可以解决超大数据导出超时的问题。

    已上是大文件导出的解决过程,本文仅分享方法,代码google吧。

    展开全文
  • 将web页面上显示的报表导出excel文件里是一种很常见的需求。然而,当数据量较大的情况下,excel本身的支持最多65535行数据的问题便凸显出来。下面就给出大数据量导出excel的解决方 案。 首先,对于数据超过了...

    将web页面上显示的报表导出到excel文件里是一种很常见的需求。然而,当数据量较大的情况下,excel本身的支持最多65535行数据的问题便凸显出来。下面就给出大数据量导出到excel的解决方 案。
    首先,对于数据超过了65535行的问题,很自然的就会想到将整个数据分块,利用excel的多sheet页的功能,将超出65535行后的数据写入到下一个sheet页中,即通过多sheet页的方式,突破了最高65535行数据的限定。
    具体做法就是,单独做一个链接,使用JSP导出,在JSP上通过程序判断报表行数,超过65535行后分SHEET写入。这样这个问题就得以解决了。
    更进一步地说,在这种大数据量的报表生成和导出中,要占用大量的内存,尤其是在使用TOMCAT的情况下,JVM最高只能支持到2G内存,则会发生 内存溢出的情况。此时的内存开销主要是两部分,一部分是该报表生成时的开销,另一部分是该报表生成后写入一个EXCEL时的开销。由于JVM的GC机制是 不能强制回收的,因此,对于此种情形,我们给出一个变通的解决方案。
    首先,将该报表设置起始行和结束行参数,在API生成报表的过程中,分步计算报表(主要性能花费在查询生成报表中),比如一张20万行数据的报表,在生成过程中,可通过起始行和结束 行分4-5次进行。这样,就降低了报表生成时的内存占用,在后面报表生成的过程中,如果发现内存不够,即可自动启动JVM的GC机制,回收前面报表的缓 存。
    导出EXCEL的过程,放在每段生成报表之后立即进行,改多个SHEET页为多个EXCEL,即在分步生成报表的同时分步生成EXCEL,则通过 POI包生成EXCEL的内存消耗也得以降低。通过多次生成,同样可以在后面EXCEL生成所需要的内存不足时,有效回收前面生成EXCEL时占用的内 存。
    再使用文件操作,对每个客户端的导出请求在服务器端根据SESSIONID和登陆时间生成唯一的临时目录,用来放置所生成的多个EXCEL,然后调 用系统控制台,打包多个EXCEL为RAR或者JAR方式,最终反馈给用户一个RAR包或者JAR包,响应客户请求后,再次调用控制台删除该临时目录。
    使用这种方法,首先是通过分段运算和生成,有效降低了报表从生成结果到生成EXCEL的内存开销。其次是通过使用压缩包,响应给用户的生成文件体积 大大缩小,降低了多用户并发访问时服务器下载文件的负担,有效减少多个用户导出下载时服务器端的流量,从而达到进一步减轻服务器负载的效果。

    创建系统全局线程池

    final int numOfCpuCores = Runtime.getRuntime().availableProcessors();
    final double blockingCoefficient = 0.9;// 阻尼系数
    final int maximumPoolSize = (int)(numOfCpuCores / (1 - blockingCoefficient));
    ExecutorService threadPool = new ThreadPoolExecutor(numOfCpuCores,
    maximumPoolSize,
    0L,
    TimeUnit.MILLISECONDS,
    new LinkedBlockingQueue (),
    Executors.privilegedThreadFactory(),
    new ThreadPoolExecutor.DiscardOldestPolicy());
    复制代码
    采用多线程分段查询生成报表,同步生成Excel,最后压缩成Zip文件

    // 1.这里每个Excel放6万条数据(分6个sheet页,每个1万条),当数据量超过6万条时,数据采用分段查询
    // 传递(起始行,结束行)参数,分段查询,即分步生成报表的同时分步生成EXCEL
    int SINGLE_EXCEPORT_EXCEL_MAX_NUM = 60000;
    int count = bo.getTotalRecord();
    final String fileNameWithTimestamp = fileName + “_” + DateUtil.getNowDateminStr();
    if (count > SINGLE_EXCEPORT_EXCEL_MAX_NUM ) {
    int excelCount = count / SINGLE_EXCEPORT_EXCEL_MAX_NUM +
    (count % SINGLE_EXCEPORT_EXCEL_MAX_NUM != 0 ? 1 : 0);
    final CountDownLatch latch = new CountDownLatch(excelCount);
    final Long userId = user.getUserId();
    for(int i = 1; i <= excelCount; i++){
    bo.setPageNo(i);
    bo.setPageSize(SINGLE_EXCEPORT_EXCEL_MAX_NUM);
    final ParkRecordQryBO itemBo = new ParkRecordQryBO(bo);
    final int index = i;
    // 取一线程执行本次查询
    threadPool.execute(new Runnable(){
    @Override
    public void run() {
    Page page = service.getParkRecord(itemBo);
    List records = page.getResults();
    try {
    // 2.生成单个excel
    ExportExcelUtil.createOneExcel(fileNameWithTimestamp, index ,
    expRowsList, records, userId);
    } catch (Exception e) {
    e.printStackTrace();
    }
    latch.countDown();
    }
    });
    }
    // 3.压缩excel文件并导出
    latch.await();
    ExportExcelUtil.createZipExport(request, response, fileNameWithTimestamp, userId);
    复制代码
    生成一个Excel存放到本地路径

    /**

    • @Description: 生成一个Excel存放到本地路径
    • @param fileNameWithTimestamp
    • @param index
    • @param excelHeader
    • @param dataList
    • @param
    • @param userId
      */
      public static void createOneExcel(final String fileNameWithTimestamp,
      int index,
      final String[] excelHeader,
      final List dataList,
      Long userId ) {
      final String localRelativePath = “” + userId + “/”+ fileNameWithTimestamp ;
      Workbook wb = null;
      FileOutputStream fos = null;
      try {
      // 创建一个Workbook,对应一个Excel文件
      wb = writeExcel(dataList, excelHeader);
      // 生成本地Excel初始文件
      Map<String, Object> fileInfo = new HashMap<String, Object>();
      FileUtil.createFile(localRelativePath, fileNameWithTimestamp +
      " + index + ".xls”, fileInfo);
      fos = new FileOutputStream(fileInfo.get(“realPath”).toString() );
      wb.write(fos);
      } catch (FileNotFoundException e) {
      e.printStackTrace();
      } catch (IOException e) {
      e.printStackTrace();
      } catch (Exception e) {
      e.printStackTrace();
      } finally {
      try {
      if (wb != null)
      wb.close();
      if (fos != null)
      fos.close();
      } catch (IOException e) {
      e.printStackTrace();
      }
      }
      }
      复制代码
      压缩打包所有Excel文件并导出

    /**

    • @param request

    • @param response

    • @param fileNameWithTimestamp

    • @param userId
      */
      public static void createZipExport(HttpServletRequest request,
      HttpServletResponse response,
      final String fileNameWithTimestamp,
      Long userId) throws Exception{
      final String localRelativePath = “” + userId + “/”+ fileNameWithTimestamp;
      // 创建文件夹,先将生成的excel保存到服务器本地目录
      // excel文件路径:’/app/file/[userId]/[fileNameWithTimestamp]/[fileNameWithTimestamp_i].xlS’
      String excelFold = FileUtil.getFileRootPath() + localRelativePath;
      // zip文件所在路径:"/app/file/userId/fileNameWithTimestamp.zip"
      String zipFold = FileUtil.getFileRootPath() + userId;

      // 生成zip文件
      final String zipFileName = fileNameWithTimestamp +".zip";
      FileUtil.createZipFile(excelFold, zipFold, zipFileName);
      // 创建导出输入流
      InputStream is = null;
      try{
      is = new FileInputStream(new File(zipFold + File.separator + zipFileName));
      } catch(IOException e){
      e.printStackTrace();
      }
      BufferedInputStream bis = new BufferedInputStream(is);
      // ServletOutputStream out = response.getOutputStream();
      BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());

      // 解决设置名称时的乱码问题
      String zipName = handleFileName(request, zipFileName);
      // 设置response参数,可以打开下载页面
      response.reset();
      response.setContentType(“application/vnd.ms-excel;charset=utf-8”);
      response.setHeader(“Content-Disposition”, “attachment;filename=” + zipName);

      byte[] buff = new byte[2048];
      int bytesRead;
      // Simple read/write loop.
      while ((bytesRead = bis.read(buff, 0, buff.length)) != -1 ) {
      bos.write(buff, 0, bytesRead);
      }
      bis.close();
      bos.close();
      // 删除用来临时保存Excel的文件夹及zip文件
      FileUtil.deleteDir(new File(zipFold));
      }

    作者:jvjs
    链接:https://juejin.im/post/5b581d72e51d4533f528220a
    来源:掘金
    著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

    展开全文
  • 大数据量导出Excel方法总结

    万次阅读 2015-07-30 11:17:56
    最近项目中牵扯到大数据量导出Excel。传统的jxl,poi等在后端生成excel的方法就不见得有多奏效。 1. JXL后端生成Excel代码(struts2 action方法代码): public String excel() throws Exception{ ...
  • 大数据快速导出EXCEL-Java

    热门讨论 2014-08-15 11:07:33
    大数据快速导出EXCEL,内存消耗很低,百万级数据导出5秒内搞定
  • 最近公司一个06年统计项目在导出Excel时造成应用...随后在网上查阅了部分资料只是在POI大数据导出API的基础上写的demo示例无任何参考价值…解决内存溢出常用方法就是打开GC日志{Heap before GC invocations=29 (full
  • java大数据导出excel

    2015-11-23 23:09:51
    package com.demo.pool; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException;...import java.io.Inpu
  • Java当中常用的Excel文档导出主要有POI、JXL和“直接IO流”这三种方式,三种方式各自分别有不同的优势与缺点,下面将分行对其进行单介绍。 导出常用文件格式 1. Excel2003格式 Excel2003支持每个工作表中最多有 ...
  • C#大数据导出Excel

    千次阅读 2014-11-04 11:15:58
    工作过程中经常会用到将数据导出Excel中,一般情况下需要导出的数据都是几百几千条或者上万条,这都没有什么问题,但有时候会遇到特殊的需求,客户要求把几十万条甚至上百万条的数据导出Excel中,这就比较麻烦了...
  • 对大数据量的导出excel,用多线程,用倒数计数器对文件进行生成,使用poi,可以支持大数据量的生成,项目中使用的poi是3.1的,上传的是4.1的。
  • 公司之前的项目中客户有一个需求是将业务数据导出Excel表中,方便他们对账,单个导出任务数据量近100W,每当月初任务量多时,导出的项目就会内存溢出,挂掉。二、原因分析: 1、每个进程在写Excel文件时,都是先...
  • poi大数据导出excel

    2018-11-13 20:40:46
    由于本人采取的是利用反射的方式,这样的好处,可以兼容,导出所有类型的数据,例如,订单,充值记录等等。 既然是公有的接口,肯定有一定的约束规范。 (1)导出的表头需要自己设置一个动态数组 (2) 头部的宽度...
  • 在日常的工作中,很多时候都需要导出各种各样的报表,但是如果导出的数据一旦比较大,很容易就导致超时,对于这种问题,有很多的解决方法,例如网上说的分批导出、采用CSV、还有就采用JAVA、甚至是C++和C等等去做...
  • java 大数据EXCEL导出

    千次阅读 2018-04-20 15:00:28
    1、需要加载以下三个包使用XSSFWorkbook 实现大数据导出,将数据分批从数据库取出,比如一次1万条,然后按顺序生成相应的EXCEL文件,再通过压缩处理,将生成的EXCEL文件压缩为ZIP包,下载该包poi-ooxml-3.17.jar、...
  • 刚开始以为这个很简单,但是做的时候却遇到很多坑。...没办法,只能借助其他工具,因为我的导出数据是日志数据,即它不怎么变化,所以我使用了mongodb去存储我的数据。OK,数据拿取很快了。 **第二个坑:内存泄漏** 这
  • 2. POI 的版本:3.17 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <...
  • java中使用poi导出Excel大批量数据 存在两个导出方法:存在一个分批量导出ZIP文件,一个导出exel文件
  • 今天博主在研究Excel大数据导出性能,发现个意外惊喜,给大家分享下。 第一次博主使用的是POI ExcelHSSF的导出方式: 这种方法是Excel 2003版本常用的一种导出方式。 以19.5W数据为例,导出耗时36秒 ...
  • SXSSFWorkbook 需要poi-ooxml包3.8及以上开始支持,我这边适使用的是3.9版本,本质是一个XSSFWorkbook类(Excel2007),它使用的方式是采用硬盘空间来大幅降低堆内存的占用,在系统的临时文件夹目录创建一个临时
  • 百万数据 使用_struts2 jxl导出excel)据解决方案,求大神回复。
  • 直接帖代码 可以直接在我博客下载那边进行demo下载 https://download.csdn.net/my/uploads
  • .net web方式大数据量导出 excel xml

    千次阅读 2008-08-06 19:34:00
    在大数据量导出时owc导出速度比较慢所以采用xml导出方式,直接从dataset导出函数原采用的方式查询出结果后再导出excel,可用多种方法都不成功最后发现虽然每次页面都已经显示出结果可是页面还没有处理完数据所以...
  • 大数据导出excel

    2017-06-23 17:21:33
    三层架构中在 创建的一个通用的数据导出,可以导出大数据,100M数据导出亲测不超3s 标签:  代码片段(1)[全屏查看所有代码] 1. [代码]这个添加了 进度条,可以去掉 跳至 [1] [全屏预览] ...
  • PHPExcel 大数据导出

    万次阅读 2017-12-05 09:48:55
    PHPExcel 是一个php语言读取导出数据、导入生成Excel的类库,使用起来非常方便,但有时会遇到以些问题,比如导出的数据超时,内存溢出等。 下面我们来说说这些问题和解决办法。 PHPExcel 版本:@version 1.8.0, ...
  • 范例 ... [code="java"] 看过很多关于Excel导出时出现内存溢出的情况,也有很多解决方案。现提供如下解决方案,如有不妥,请指正: ... 该项目使用B/S架构,由于POI、JXL在导出excel大数据量情况下会...
  • 2010.03.22(5)——大数据导出excel IO流的问题 用io流导出的excel文件如果想要直接导入数据库,必须先另存为一下,因为用io保存的其实不是excel的格式,如果导入,则会报 [code="java"]java.io....
  • POI处理大数据导入导出

    千次阅读 2019-03-19 14:57:48
    Java POI处理大量数据导入导出xls和xlsx导入数据(大量)导出数据(大量)总结 xls和xlsx xls是旧版Excel格式文件,xlsx是新版Excel格式文件;而xlsx新版格式其实是一系列文件压缩包, 如图: xls是以二进制的方式...
  • 做呗于是便考虑同时导出多个Excel表格,说做边做,结果自然是失败了,最后发现在导出第一个Excel的流出的时候,边关流了以至于后面的Excel表格就失败了.于是乎就换了种方法,那就导出一个Excel表格但里面存在多个sheet....

空空如也

1 2 3 4 5 ... 20
收藏数 4,517
精华内容 1,806
关键字:

大数据导出excel