poi 大数据量_poi解析大数据量excel - CSDN
精华内容
参与话题
  • POI百万级大数据量EXCEL导出

    万次阅读 多人点赞 2019-06-21 17:43:29
    查询数据量,导致内存溢出。 该问题可以通过分批查询来解决; 2. 最后下载的时候EXCEL转换的输出流内存溢出;该方式可以通过新版的SXSSFWorkbook来解决,可通过其构造函数执指定在内存中缓存的行数,剩余的...

    一. 简介

              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分钟

     

     

    展开全文
  • POI百万级大数据量EXCEL导出 - 请叫我猿叔叔的博客 - CSDN博客.htm
  • Java用POI实现读取大数据量Excel

    万次阅读 2018-05-22 00:33:37
    java代码使用poi的API解决在读取大数据量的Excel数据时候内存溢出的问题:首先我需要声明下面的工具类是在老袁博客(https://laoyuan.me/posts/java-read-big-excel-with-poi.html)基础上做了稍微的改造,我将老袁...

    java代码使用poi的API解决在读取大数据量的Excel数据时候内存溢出的问题:首先我需要声明下面的工具类是在老袁博客(https://laoyuan.me/posts/java-read-big-excel-with-poi.html)基础上做了稍微的改造,我将老袁的的工具类需要2个参数改成只需要一个参数就可以完成调用,当然你可以根据你自己的情况使用。
    下面是一个工具类,复制到自己的项目中直接调用即可:

    1、工具类

    package com.xxx.xxx.xxx;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
    import org.apache.poi.xssf.eventusermodel.XSSFReader;
    import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
    import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
    import org.apache.poi.xssf.model.StylesTable;
    import org.apache.poi.xssf.usermodel.XSSFComment;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.stereotype.Component;
    import org.xml.sax.InputSource;
    import org.xml.sax.SAXException;
    import org.xml.sax.XMLReader;
    import org.xml.sax.helpers.XMLReaderFactory;
    /**
     * 解析大数据量Excel工具类
     * @author RobinTime
     *
     */
    @Component
    public class ExcelParser {
        private static final Logger logger = LoggerFactory.getLogger(ExcelParser.class);
        /**
         * 表格默认处理器
         */
        private ISheetContentHandler contentHandler = new DefaultSheetHandler();
        /**
         * 读取数据
         */
        private List<String[]> datas = new ArrayList<String[]>();
    
        /**
         * 转换表格,默认为转换第一个表格
         * @param stream
         * @return
         * @throws InvalidFormatException
         * @throws IOException
         * @throws ParseException
         */
        public ExcelParser parse(InputStream stream)
                throws InvalidFormatException, IOException, ParseException {
            return parse(stream, 1);
        }
    
    
        /**
         * 
         * @param stream
         * @param sheetId:为要遍历的sheet索引,从1开始
         * @return
         * @throws InvalidFormatException
         * @throws IOException
         * @throws ParseException
         */
        public synchronized ExcelParser parse(InputStream stream, int sheetId)
                throws InvalidFormatException, IOException, ParseException {
            // 每次转换前都清空数据
            datas.clear();
            // 打开表格文件输入流
            OPCPackage pkg = OPCPackage.open(stream);
            try {
                // 创建表阅读器
                XSSFReader reader;
                try {
                    reader = new XSSFReader(pkg);
                } catch (OpenXML4JException e) {
                    logger.error("读取表格出错");
                    throw new ParseException(e.fillInStackTrace());
                }
    
                // 转换指定单元表
                InputStream shellStream = reader.getSheet("rId" + sheetId);
                try {
                    InputSource sheetSource = new InputSource(shellStream);
                    StylesTable styles = reader.getStylesTable();
                    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
                    getContentHandler().init(datas);// 设置读取出的数据
                    // 获取转换器
                    XMLReader parser = getSheetParser(styles, strings);
                    parser.parse(sheetSource);
                } catch (SAXException e) {
                    logger.error("读取表格出错");
                    throw new ParseException(e.fillInStackTrace());
                } finally {
                    shellStream.close();
                }
            } finally {
                pkg.close();
    
            }
            return this;
    
        }
    
        /**
         * 获取表格读取数据,获取数据前,需要先转换数据<br>
         * 此方法不会获取第一行数据
         * 
         * @return 表格读取数据
         */
        public List<String[]> getDatas() {
            return getDatas(true);
    
        }
    
        /**
         * 获取表格读取数据,获取数据前,需要先转换数据
         * 
         * @param dropFirstRow
         *            删除第一行表头记录
         * @return 表格读取数据
         */
        public List<String[]> getDatas(boolean dropFirstRow) {
            if (dropFirstRow && datas.size() > 0) {
                datas.remove(0);// 删除表头
            }
            return datas;
    
        }
    
        /**
         * 获取读取表格的转换器
         * 
         * @return 读取表格的转换器
         * @throws SAXException
         *             SAX错误
         */
        protected XMLReader getSheetParser(StylesTable styles, ReadOnlySharedStringsTable strings) throws SAXException {
            XMLReader parser = XMLReaderFactory.createXMLReader();
            parser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, getContentHandler(), false));
            return parser;
        }
    
        public ISheetContentHandler getContentHandler() {
            return contentHandler;
        }
    
        public void setContentHandler(ISheetContentHandler contentHandler) {
            this.contentHandler = contentHandler;
        }
    
        /**
         * 表格转换错误
         */
        public class ParseException extends Exception {
            private static final long serialVersionUID = -2451526411018517607L;
    
            public ParseException(Throwable t) {
                super("表格转换错误", t);
            }
    
        }
    
        public interface ISheetContentHandler extends SheetContentsHandler {
    
            /**
             * 设置转换后的数据集,用于存放转换结果
             * 
             * @param datas
             *            转换结果
             */
            void init(List<String[]> datas);
        }
    
        /**
         * 默认表格解析handder
         */
        class DefaultSheetHandler implements ISheetContentHandler {
            /**
             * 读取数据
             */
            private List<String[]> datas;
            private int columsLength;
            // 读取行信息
            private String[] readRow;
            private ArrayList<String> fristRow = new ArrayList<String>();
    
            @Override
            public void init(List<String[]> datas) {
                this.datas = datas;
    //          this.columsLength = columsLength;
            }
    
            @Override
            public void startRow(int rowNum) {
                if (rowNum != 0) {
                    readRow = new String[columsLength];
                }
            }
    
            @Override
            public void endRow(int rowNum) {
            //将Excel第一行表头的列数当做数组的长度,要保证后续的行的列数不能超过这个长度,这是个约定。
                if (rowNum == 0) {
                    columsLength = fristRow.size();
                    readRow = fristRow.toArray(new String[fristRow.size()]);
                }else {
                    readRow = fristRow.toArray(new String[columsLength]);
                }
                datas.add(readRow.clone());
                readRow = null;
                fristRow.clear();
            }
    
            @Override
            public void cell(String cellReference, String formattedValue, XSSFComment comment) {
                int index = getCellIndex(cellReference);//转换A1,B1,C1等表格位置为真实索引位置
                try {
                    fristRow.set(index, formattedValue);
                } catch (IndexOutOfBoundsException e) {
                    int size = fristRow.size();
                    for (int i = index - size+1;i>0;i--){
                        fristRow.add(null);
                    }
                    fristRow.set(index,formattedValue);
                }
            }
    
            @Override
            public void headerFooter(String text, boolean isHeader, String tagName) {
            }
    
            /**
             * 转换表格引用为列编号
             * 
             * @param cellReference
             *            列引用
             * @return 表格列位置,从0开始算
             */
            public int getCellIndex(String cellReference) {
                String ref = cellReference.replaceAll("\\d+", "");
                int num = 0;
                int result = 0;
                for (int i = 0; i < ref.length(); i++) {
                    char ch = cellReference.charAt(ref.length() - i - 1);
                    num = (int) (ch - 'A' + 1);
                    num *= Math.pow(26, i);
                    result += num;
                }
                return result - 1;
            }
        }
    }

    2、调用

    File tempFile = new File(this.getClass().getClassLoader().getResource("").getPath() + "tempFile\\" + (new Date()).getTime() + ".xlsx");
    //传入一个路径产生流再将流传入工具类,返回解析对象,Excel的所有数据就被解析到List<String[]> 里面,遍历list任由你处置。
    FileInputStream inputStream = new FileInputStream(tempFile);
    ExcelParser parse = excelParser.parse(inputStream);
    List<String[]> datas = parse.getDatas();
    展开全文
  • 3.8版本的POI对excel的导出操作,一般只使用HSSFWorkbook以及SXSSFWorkbook,HSSFWorkbook用来处理较少的数据量,SXSSFWorkbook用来处理大数据量以及超大数据量的导出。 3.8版本的POI新出来了SXSSFWorkbook,可以...
    3.8版本的POI对excel的导出操作,一般只使用HSSFWorkbook以及SXSSFWorkbook,HSSFWorkbook用来处理较少的数据量,SXSSFWorkbook用来处理大数据量以及超大数据量的导出。
    	    3.8版本的POI新出来了SXSSFWorkbook,可以支持大数据量的操作,只是SXSSFWorkbook只支持.xlsx格式,不支持.xls格式。 
            DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
        	Random rand = new Random();
         	// 导出Excel文件名
            String xlsName = "xxx_"+df.format(new Date())+rand.nextInt(100)+".xlsx";
            //创建excel文件,内存只有100条记录
            Workbook wb = new SXSSFWorkbook(100); 
            //一个sheet存储的记录条数
            int record = 5000;
            //总记录数
            int recordTotal = listtemp.size();
            //sheet个数(循环几次就有几个sheet,1个sheet存放5000条数据)
            int shTotal = recordTotal % EXCEL_SHEETRECORD == 0?recordTotal/EXCEL_SHEETRECORD:recordTotal/EXCEL_SHEETRECORD + 1;
            //最后一个sheet中记录的条数
    	    int lastRecord = recordTotal % EXCEL_SHEETRECORD == 0?record:recordTotal % EXCEL_SHEETRECORD;
    	    for(int shIndex = 0; shIndex < shTotal; shIndex++){
    			// 创建工作表 
    	        Sheet sh = wb.createSheet("sheet"+shIndex);
    	    	//最后一个sheet
    			if(shIndex == shTotal - 1){
    		        // 写表头 创建第一行(表头,即列名)
    		        Row row = sh.createRow((short)0);
    		        // 给列添加名字
    		        row.createCell((short)0).setCellValue("xxx");
    		        row.createCell((short)1).setCellValue("xxx");
    		        row.createCell((short)2).setCellValue("xxx");
    		        row.createCell((short)3).setCellValue("xxx");		        // 数据写入EXCEL
    		        for(short i = 0 ; i < lastRecord ; i++){
    		        	// 创建数据行
    		        	Row rowTmp = sh.createRow((short)(i+1));
    		        	//最后一个sheet的记录的索引
    		        	int ii = EXCEL_SHEETRECORD * shIndex + i;
    		        	// 得到一个对象(即一条记录)
    		        	Object[] oj = listtemp.get(ii);
    			        // Create a cell and put a value in it.
    			        rowTmp.createCell((short)0).setCellValue((oj[1]==null || oj[1].toString().equals(""))?"":oj[1].toString());
    			        rowTmp.createCell((short)1).setCellValue((oj[2]==null || oj[2].toString().equals(""))?"":oj[2].toString());
    			        rowTmp.createCell((short)2).setCellValue((oj[3]==null || oj[3].toString().equals(""))?"":oj[3].toString());
    			        rowTmp.createCell((short)3).setCellValue((oj[4]==null || oj[4].toString().equals(""))?"":oj[4].toString());
    			        			    }
    			}else{
    				// 写表头 创建第一行(表头,即列名)
    		        Row row = sh.createRow((short)0);
    		        // 给列添加名字
    		        row.createCell((short)0).setCellValue("xxx");
    		        row.createCell((short)1).setCellValue("xxx");
    		        row.createCell((short)2).setCellValue("xxx");
    		        row.createCell((short)3).setCellValue("xxx");		        // 数据写入EXCEL
    		        for(short i = 0 ; i < listtemp.size() ; i++){
    		        	// 创建数据行
    		        	Row rowTmp = sh.createRow((short)(i+1));
    		        	//第shIndex个sheet的记录的索引
    		        	int ii = EXCEL_SHEETRECORD * shIndex + i;
    		        	// 得到一个对象(即一条记录)
    		        	Object[] oj = listtemp.get(ii);
    			        // Create a cell and put a value in it.
    			        rowTmp.createCell((short)0).setCellValue((oj[1]==null || oj[1].toString().equals(""))?"":oj[1].toString());
    			        rowTmp.createCell((short)1).setCellValue((oj[2]==null || oj[2].toString().equals(""))?"":oj[2].toString());
    			        rowTmp.createCell((short)2).setCellValue((oj[3]==null || oj[3].toString().equals(""))?"":oj[3].toString());
    			        rowTmp.createCell((short)3).setCellValue((oj[4]==null || oj[4].toString().equals(""))?"":oj[4].toString());
    			        			    }
    			}
    	    }
            
            HttpServletResponse response = ServletActionContext.getResponse();
            //指定输出文件名
            response.setHeader("Content-Disposition","attachment;filename=" + xlsName); 
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            OutputStream os = null;        
        	try {
    			os = response.getOutputStream();
    			wb.write(response.getOutputStream());
    			os.flush();
    			os.close();
    		} catch (IOException e) {
    		}

     

     

     

     

    展开全文
  • POI 读写大数据量 EXCEL

    2019-07-15 18:42:56
    参考:https://www.cnblogs.com/tootwo2/p/6683143.html 转载于:https://www.cnblogs.com/lshan/p/10019555.html

    参考:https://www.cnblogs.com/tootwo2/p/6683143.html

    转载于:https://www.cnblogs.com/lshan/p/10019555.html

    展开全文
  • java中使用poi导出Excel大批量数据 存在两个导出方法:存在一个分批量导出ZIP文件,一个导出exel文件
  • 使用Poi读取大数据量excel的方法

    千次下载 热门讨论 2020-07-16 23:35:44
    使用Poi读取大数据量excel的方法 支持2003和2007的版本
  • Excel大数据量处理(poi大数据量+java 线程池机制)

    千次阅读 热门讨论 2018-09-17 16:23:33
    最近项目需求,有最低十万的数据导入最高500万的数据导入需求,poi,是开源对Excel支持非常强大的框架,因此研究了一番,此过程借阅网上多为人士的代码,和见解,我发现网上的代码都是那一套,是一个前辈在github上的一个...
  • POI读写大数据量EXCEL

    千次阅读 2018-05-07 17:38:19
    大数据量的excel一般都是.xlsx格式的,网上使用POI读写的例子比较多,但是很少提到读写非常大数据量的excel的例子,POI官网上提到XSSF有三种读写excel,POI地址:http://poi.apache.org/spreadsheet/index.html...
  • java poi导出大量数据到Excel
  • poi大数据量excel导入

    千次阅读 2013-11-22 14:49:58
    1、HxlsAbstract import java.io.FileInputStream;  import java.io.FileNotFoundException;  import java.io.IOException;  import java.io.PrintStream;  import java.sql.SQLException;...import java.u
  • Java POI Excel大数据量的导入导出

    千次阅读 2019-03-31 18:10:51
    1. 大数据量的导入 当Excel中的数据量超过10万行时,在用POI读取文件流时很容易引起失败,需要引入xlsx-streamer来进行资源的打开,剩下的处理同POI处理上百行数据量类似:filePath=>FileInputStream=>...
  • poi处理大量数据

    2013-08-31 11:47:34
    使用的pom.xml文件如下所示:  &lt;...org.apache.poi&lt;/groupId&gt; &lt;artifactId&gt;poi&lt;/artifactId&gt; &lt;version&gt;3.8&lt;/version...
  • poi实现大量数据导出excel

    千次阅读 2017-01-05 11:24:37
    2、poi的工具类 import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apa
  • POI之前的版本不支持大数据量处理,如果数据过多则经常报OOM错误,有时候调整JVM大小效果也不是太好。3.8版本的POI新出来了SXSSFWorkbook,可以支持大数据量的操作,只是SXSSFWorkbook只支持.xlsx格式,不支持.xls...
  • JAVA+POI实现大数据量excel表格的导入、导出
  • 昨天,产品提了一个紧急需求,让把十一月份已发货的商品数据导出来,写好SQL发给DBA执行之后,得到了三十多个100W数据的Excel文件。有一个属性是以JSON格式存在表中一个字段里面的,需要加工Excel文件将其单独取出来...
  • POI 读取excel大量数据方法

    万次阅读 热门讨论 2018-05-10 14:48:45
    今天在做excel文件上传时,发现数据量超过10万条后,系统会出现内存溢出。 跟踪代码发现程序卡在如下中 Workbook workbook=WorkbookFactory.create(new File(path)); 通过poi的api发现此方法属于能对excel读写...
  • 今天需要写一个导出的Excel的功能,但是发现当数据量到3万条时,列数在23列时,内存溢出,CPU使用100%,测试环境直接炸掉。在本地测试时发现,导出3000条左右的数据的时候,堆内存瞬间升高500M左右。然后发现了...
  • poi读取大数据量excel文件,避免内存溢出,行级操作 根据本网站的资源修改的。 将一些类路径错误全部进行了修正。 另外,需要自己在类路径里,放spring-context.jar和spring-beans.jar包。
1 2 3 4 5 ... 20
收藏数 14,942
精华内容 5,976
关键字:

poi 大数据量