大数据量导出_大数据量导出解决档案 - CSDN
精华内容
参与话题
  • POI百万级大数据量EXCEL导出

    万次阅读 多人点赞 2019-06-21 17:43:29
    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表格导出/导入(基本是几十万行的数据量) 首先ajax请求就会出现超时现象 loading时间过长,用户体验也不好(一些用户可能中途F5刷新,或者登录超时等情况,会...

    问题描述

    • 前段时间遇到一个需求是需要做大数据量的excel表格导出/导入(基本是几十万行的数据量)
    1. 首先ajax请求就会出现超时现象
    2. loading时间过长,用户体验也不好(一些用户可能中途F5刷新,或者登录超时等情况,会导致这一次导入/导出结果失败)

    问题解决方案的摸索

    1. 直接把ajax的超时以及服务器超时加大(tips:记得服务器那边的超时要加大,一般是nginx服务器),默认超时为1min加大为10min,这个方案也存在体验问题和性能问题,所以废弃了
    2. 首先数据量大,后端处理的时间也很长(导出涉及到各种表的查询/导入的话要做表格行的校验等等),所以后端性能方面也要优化(从原本20min优化到7-8min)
    3. 配合后端通过WebSocket通信处理,最终解决方案

    方案讲解

    1. 首先前后端一起约束好使用WebSocket来做交互
    2. 为上面的返回结果新建一个专门的页面,里面就是上面导入/导出的结果列表,记得要有当前导入/导出状态(未生成/已生成),然后可以在这个页面下载结果(后端已经把文件生成好了,不用再进行运算查表等相关操作,直接下载的速度是很快的)
      在这里插入图片描述
    3. WebSocket交互方面 ,点击导入/导出的时候立刻返回结果(无论成功或失败),可以弹一个Notification 通知提醒,里面信息为导入/导出进行中,然后给一个链接用于跳转到导入/导出文件页面;然后处理结果完后WebSocket再通知前端弹出Notification 通知提醒成功,内容和上面同理显示
      在这里插入图片描述
    展开全文
  • 大数据量导出到Excel方法总结

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

    欢迎大家访问我的博客:地址​​​​​​​

    最近项目中牵扯到大数据量导出到Excel。传统的jxl,poi等在后端生成excel的方法就不见得有多奏效。

    1. JXL后端生成Excel代码(struts2 action方法代码):

     

    public String excel() throws Exception{
    		ByteArrayOutputStream os = new ByteArrayOutputStream();
    		WritableWorkbook wbook = Workbook.createWorkbook(os);
    		WritableSheet wsheet = wbook.createSheet("交易明细", 0);
    		String[] titles = {"序号","业务流水","营销流水","缴费流水","电话号码","费用(元)","营销金额(元)","自由花费(元)"};
    		for(int i=0;i<titles.length;i++){
    			wsheet.addCell(new Label(i,0,titles[i]));
    		}
    <span style="white-space:pre">		</span>//封装分页查询参数
    		BaseBean params = getParams();
    		params.put("pagesize", 1000);
    		int n=1;
    		int i=0;
    		while(true){
    			params.put("page", n++);
    			List<BaseBean> list = reportService.findList(params);
    			if(list != null && list.size() > 0){
    				BaseBean item = null;
    				Iterator<BaseBean> it = list.iterator();
    				while(it.hasNext()){
    					i++;
    					item = it.next();
    					wsheet.addCell(new Number(0,i,i));
    					wsheet.addCell(new Label(1,i,item.getString("appid")));
    					wsheet.addCell(new Label(2,i,item.getString("openid")));
    					wsheet.addCell(new Label(3,i,item.getString("payid")));
    					wsheet.addCell(new Label(4,i,item.getString("phone")));
    					wsheet.addCell(new Number(5,i,item.getDouble("cardmoney")));
    					wsheet.addCell(new Number(6,i,item.getDouble("acmoney")));
    					wsheet.addCell(new Number(7,i,item.getDouble("freemoney")));
    				}
    			}else{
    				break;
    			}
    		}
    		wbook.write();
    		wbook.close();
    		inputStream = new ByteArrayInputStream(os.toByteArray());
    		os.close();
    		fileName = new String("交易明细记录".getBytes("gb2312"),"ISO-8859-1");
    		return SUCCESS;
    	}

    由以上代码可知,生成Excel的过程中会创建大量的对象(new Label等),除此之外,List集合,WritableSheet对象数据量较大,所以容易造成内存泄露的问题。

     

    2. 解决方法

    2.1 后端xml拼接的方式

     

    	public String excel() throws Exception{
    		String time = Long.toString(System.currentTimeMillis());
    		FileWriter fw = new FileWriter("d:/" + time + ".xls");
    		StringBuffer sb = new StringBuffer();
    		sb.append("<?xml version=\"1.0\"?>");
    		sb.append("\n");
    		sb.append("<?mso-application progid=\"Excel.Sheet\"?>");
    		sb.append("\n");
    		sb.append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
    		sb.append("\n");
    		sb.append("  xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
    		sb.append("\n");
    		sb.append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
    		sb.append("\n");
    		sb.append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
    		sb.append("\n");
    		sb.append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
    		sb.append("\n");
    		sb.append("<Styles>\n");
    		sb.append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n");
    		sb.append("<Alignment ss:Vertical=\"Center\"/>\n");
    		sb.append("<Borders/>\n");
    		sb.append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>\n");
    		sb.append("<Interior/>\n");
    		sb.append("<NumberFormat/>\n");
    		sb.append("<Protection/>\n");
    		sb.append("</Style>\n");
    		sb.append("</Styles>\n");
    		
    		String[] titles = {"序号","业务流水","营销流水","缴费流水","电话号码","卡费(元)","营销金额(元)","自由花费(元)"};
    		BaseBean params = getParams();
    		params.put("pagesize", 20000);    //分页查询中pagesize不宜过小,否则查询次数太多,速度太慢
    		
    		int recordcount = 60000;//每个sheet页面的条数
    		int currentRecord = 0;
    		int col = titles.length;
    		
    		sb.append("<Worksheet ss:Name=\"交易明细1\">");  
            sb.append("\n");  
            sb.append("<Table ss:ExpandedColumnCount=\"" + col  
                    + "\" ss:ExpandedRowCount=\"" + (recordcount + 1)  
                    + "\" x:FullColumns=\"1\" x:FullRows=\"1\">");  
            sb.append("\n"); 
           
            sb.append("<Row>");
    		sb.append("<Cell><Data ss:Type=\"String\">" + titles[0] + "</Data></Cell>");
    		sb.append("<Cell><Data ss:Type=\"String\">" + titles[1] + "</Data></Cell>");
    		sb.append("<Cell><Data ss:Type=\"String\">" + titles[2] + "</Data></Cell>");
    		sb.append("<Cell><Data ss:Type=\"String\">" + titles[3] + "</Data></Cell>");
    		sb.append("<Cell><Data ss:Type=\"String\">" + titles[4] + "</Data></Cell>");
    		sb.append("<Cell><Data ss:Type=\"String\">" + titles[5] + "</Data></Cell>");
    		sb.append("<Cell><Data ss:Type=\"String\">" + titles[6] + "</Data></Cell>");
    		sb.append("<Cell><Data ss:Type=\"String\">" + titles[7] + "</Data></Cell>");
    		sb.append("<Cell><Data ss:Type=\"String\">" + titles[8] + "</Data></Cell>");
    		sb.append("</Row>");
    		
    		int n=1;
    		int i=0;
    		int j=1;
    		while(true){
    			if ((currentRecord >= recordcount) && i != 0) {// 一个sheet写满
    				currentRecord = 0;
    				i=0;
    				fw.write(sb.toString());  
                    sb.setLength(0);
                    
    				sb.append("</Table>");  
                    sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");  
                    sb.append("\n");  
                    sb.append("<ProtectObjects>False</ProtectObjects>");  
                    sb.append("\n");  
                    sb.append("<ProtectScenarios>False</ProtectScenarios>");  
                    sb.append("\n");  
                    sb.append("</WorksheetOptions>");  
                    sb.append("\n");  
                    sb.append("</Worksheet>");
                    
    				sb.append("<Worksheet ss:Name=\"交易明细" + ++j + "\">");
    				sb.append("\n");
    				sb.append("<Table ss:ExpandedColumnCount=\"" + col
    						+ "\" ss:ExpandedRowCount=\"" + (recordcount + 1)
    						+ "\" x:FullColumns=\"1\" x:FullRows=\"1\">");
    				sb.append("\n");
    				
    				sb.append("<Row>");
    				sb.append("<Cell><Data ss:Type=\"String\">" + titles[0] + "</Data></Cell>");
    				sb.append("<Cell><Data ss:Type=\"String\">" + titles[1] + "</Data></Cell>");
    				sb.append("<Cell><Data ss:Type=\"String\">" + titles[2] + "</Data></Cell>");
    				sb.append("<Cell><Data ss:Type=\"String\">" + titles[3] + "</Data></Cell>");
    				sb.append("<Cell><Data ss:Type=\"String\">" + titles[4] + "</Data></Cell>");
    				sb.append("<Cell><Data ss:Type=\"String\">" + titles[5] + "</Data></Cell>");
    				sb.append("<Cell><Data ss:Type=\"String\">" + titles[6] + "</Data></Cell>");
    				sb.append("<Cell><Data ss:Type=\"String\">" + titles[7] + "</Data></Cell>");
    				sb.append("<Cell><Data ss:Type=\"String\">" + titles[8] + "</Data></Cell>");
    				sb.append("</Row>");
    			}
    			params.put("page", n++);
    			List<BaseBean> list = reportService.findList(params);
    			if(list != null && list.size() > 0){
    				BaseBean item = null;
    				Iterator<BaseBean> it = list.iterator();
    				while(it.hasNext()){
    					i++;
    					item = it.next();
    					
    					sb.append("<Row>");
    					sb.append("<Cell><Data ss:Type=\"String\">" + i +"</Data></Cell>");
    					sb.append("<Cell><Data ss:Type=\"String\">" + item.getString("appid") + "</Data></Cell>");
    					sb.append("<Cell><Data ss:Type=\"String\">" + item.getString("openid") + "</Data></Cell>");
    					sb.append("<Cell><Data ss:Type=\"String\">" + item.getString("payid") + "</Data></Cell>");
    					sb.append("<Cell><Data ss:Type=\"String\">" + item.getString("phone") + "</Data></Cell>");
    					sb.append("<Cell><Data ss:Type=\"String\">" + item.getDouble("cardmoney") + "</Data></Cell>");
    					sb.append("<Cell><Data ss:Type=\"String\">" + item.getDouble("acmoney") + "</Data></Cell>");
    					sb.append("<Cell><Data ss:Type=\"String\">" + item.getDouble("freemoney") + "</Data></Cell>");
    					sb.append("</Row>");
    					
    					if (i % 1000 == 0) {
    						fw.write(sb.toString());
    						fw.flush();
    						sb.setLength(0);
    					}
    					sb.append("\n");
    					currentRecord++;
    				}
    			}else{
    				break;
    			}
    		}
    		fw.write(sb.toString());
    		fw.flush();
    		sb.setLength(0);
    		sb.append("</Table>");
    		sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
    		sb.append("\n");
    		sb.append("<ProtectObjects>False</ProtectObjects>");
    		sb.append("\n");
    		sb.append("<ProtectScenarios>False</ProtectScenarios>");
    		sb.append("\n");
    		sb.append("</WorksheetOptions>");
    		sb.append("\n");
    		sb.append("</Worksheet>");
    		sb.append("</Workbook>");
    		sb.append("\n");
    		fw.write(sb.toString());
    		fw.flush();
    		fw.close();
    		File file = new File("d:/" + time + ".xls");
    		inputStream = new FileInputStream(file);
    		fileName = new String("交易明细记录".getBytes("gb2312"),"ISO-8859-1");
    		return SUCCESS;
    	}

    以上采用xml拼接的方式,把数据写入到服务器,然后再下载到客户端,可以达到目的。

     

    删除服务器端临时文件方法

    由于struts2中数据流不关闭,文件是无法删除的

    2.1.1 把所有临时文件放在同一个目录下,程序中创建文件的时候,检查该目录下是否有相关文件,如果有则删除即可

    2.1.2 多线程的方式   点这里

    2.2 前端ajax获取数据,最终生成下载文件的方式

    Action方法代码

     

    public String excel() throws Exception{
    		String par = request.getParameter("par");
    		BaseBean params = getParams();
    		params.put("pagesize", 5000);		
    		params.put("page", par);
    		List<BaseBean> list = reportService.findList(params);
    		inputStream = new ByteArrayInputStream(list.toString().getBytes());
    		return SUCCESS;
    	}

    Struts2配置

     

     

    <action name="excel" class="account_action" method="excel">
    			<result type="stream" name="success">
    				<param name="contentType">text/html</param>
    				<param name="inputName">inputStream</param>
    			</result>
    			<result type="json" name="error">
    				<param name="root">message.bean</param>
    			</result>
    		</action>

    前端script方法

     

     

    jQuery('#export_btn').click(function(){
    				   <span style="white-space:pre">	</span>//判断浏览器
    					var explorer = window.navigator.userAgent ;
    					
    					if (explorer.indexOf("MSIE") >= 0 || explorer.indexOf("Trident") >= 0) {
    						explorer = 'ie';
    					}
    					else if (explorer.indexOf("Firefox") >= 0) {
    						explorer = 'Firefox';
    					}
    					else if(explorer.indexOf("Chrome") >= 0){
    						explorer = 'Chrome';
    					}
    					else if(explorer.indexOf("Opera") >= 0){
    						explorer = 'Opera';
    					}
    					else if(explorer.indexOf("Safari") >= 0){
    						explorer = 'Safari';
    					}				
    					
    					var params = '';
    					jQuery('#divfrom li').find('input[type=text],select').each(function(i,item){
    						params += '&' + jQuery(item).attr('name') + '=' + jQuery(item).val();
    					});
    					params = '?' + params.substring(1);
    
    				 	var results = "序号,业务流水,营销流水,缴费流水,电话号码,卡费(元),营销金额(元),自由花费(元)" + "\n";
    					
               			var i = 0;
               			var j = 0;
               			for(var m=1;m<=i+1;m++){
    	           			i++;
    						jQuery.ajax({
    							async: false,
    							cache: false,
    							type: "get",
    							url: "$!{base}/main/account/report/excel.html" + params,
    							data: "par=" + i,
    							contentType: "text",
    							success: function (data) {
    								if(data != null && data.length>2){
    									var data = eval(data);
    									jQuery.each(data,function(i,n){
    										j++;
    										var temp = '';
    										
    										temp = j + "\t," +data[i].appid + "\t," + data[i].openid + "\t," + data[i].payid + "\t," + data[i].phone + "\t,"
    										+ data[i].cardmoney + "\t," + data[i].acmoney + "\t," + data[i].freemoney + "\t\n';
    										
    										results += temp;
    										
    									});
    									//输出
    									results = results.replace(/null/g,"") 
    									results = results.replace(/undefined/g,"") 
    									if(explorer =='ie')
    									{
    										var path = prompt("输入保存路径和文件名", "d:\\交易明细" + m + ".csv")
    										var fso = new ActiveXObject("Scripting.FileSystemObject");
    										var s = fso.CreateTextFile(path, true);
    										s.WriteLine(results);
    										s.Close();
    										results = "序号,业务流水,营销流水,缴费流水,电话号码,卡费(元),营销金额(元),自由花费(元)" + "\n";
    									}
    									else{
    										results = encodeURIComponent(results);
    										var uri = 'data:text/csv;charset=utf-8,\ufeff' + results;
    										var downloadLink = document.createElement("a");
    										downloadLink.href = uri;
    										downloadLink.download = "交易明细" + m + ".csv";
    										document.body.appendChild(downloadLink);
    										downloadLink.click();
    										document.body.removeChild(downloadLink);
    										results = "序号,业务流水,营销流水,缴费流水,电话号码,卡费(元),营销金额(元),自由花费(元)" + "\n";
    									}
    									
    								}else{
    									i = -1;
    								}
    							}
    						})
    					}
    				});


    总结:采用xml方法,个人认为较好。

     

    前端拼接数据的方法,可能会存在浏览器兼容的问题,不过为了减轻服务器压力,也暂无其它的方式,毕竟个人能力有限。

    另:前端生成文件的方式,Chrome浏览器,数据量接近1W条的情况,就会存在崩溃的可能,所以采用生成多个文件的方式。

    据说借助浏览器控件,如flash可以实现浏览器端IO的可能。也有一些相关的基于jquery的组件,如downloadify,dataTables。
    这个有空再去尝试。
     


     

     

    展开全文
  • Java实现excel大数据量导出

    千次阅读 2019-10-11 15:00:31
    1.pom.xml配置依赖包 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency>...depend...

    1.pom.xml配置依赖包

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

    2.excel导出工具类

    import java.io.IOException;
    import java.io.OutputStream;
    import java.io.UnsupportedEncodingException;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFDataFormat;
    
    public class ExportExcel {
    
       /**
    	* 导出excel
    	* 
    	* @param response
    	*            HttpServletResponse
    	* @param list
    	*            导出数据集合
    	* @param lables           
    	*            表头数组
    	* @param fields
    	*            key数组
    	* @param title            
    	*            文件名
    	*/
    	public static void export(HttpServletResponse response,List<Map<String, Object>> list,String[] lables,String[] fields,String title) {		   	   
    	   response.setContentType("application/octet-stream");//告诉浏览器输出内容为流
    	   String filename = "";
    	   try {
    		   filename = new String(title.getBytes("UTF-8"), "ISO_8859_1");
    	   } catch (UnsupportedEncodingException e1) {
    		   e1.printStackTrace();
    	   }
    	   response.setHeader("Content-Disposition","attachment;filename=" + filename);
    	   OutputStream os = null;
    	   SXSSFWorkbook sxssfWorkbook = null;
    	   try {		 
    	        // 获取SXSSFWorkbook
    	        sxssfWorkbook = new SXSSFWorkbook();
    	        Sheet sheet = sxssfWorkbook.createSheet("Sheet1");
    	        // 冻结第一行
    	        sheet.createFreezePane(0, 1);	
    	        // 创建第一行,作为header表头
    	        Row header = sheet.createRow(0);
    	        // 循环创建header单元格
    	        for (int cellnum = 0; cellnum < lables.length; cellnum++) {
    	            Cell cell = header.createCell(cellnum);
    	            //cell.setCellStyle(getAndSetXSSFCellStyleHeader(sxssfWorkbook));//设置表头单元格样式,根据需要设置
    	            cell.setCellValue(lables[cellnum]);
    	            //设置每列固定宽度
    	            sheet.setColumnWidth(cellnum, 20 * 256);
    	        }
    	        // 遍历创建行,导出数据
    	        for (int rownum = 1; rownum <= list.size(); rownum++) {
    	            Row row = sheet.createRow(rownum);
    	            Map<String, Object> map = list.get(rownum-1);
    	            // 循环创建单元格
    	            for (int cellnum = 0; cellnum < fields.length; cellnum++) {
    	                Cell cell = row.createCell(cellnum);
    	                //cell.setCellStyle(getAndSetXSSFCellStyleOne(sxssfWorkbook));//设置数据行单元格样式,根据需要设置
    	                cell.setCellValue(map.get(fields[cellnum]) == null ? "" : map.get(fields[cellnum]).toString());
    	            }
    	        }
    	        //自定义各列宽度
    	        //setSheet(sheet);
    	        os = response.getOutputStream(); 
    	        sxssfWorkbook.write(os);
    	    } catch (Exception e) {
    	    	e.printStackTrace();
    	    } finally {
    	        try {
    	            if(sxssfWorkbook != null) {
    	                //处理SXSSFWorkbook导出excel时,产生的临时文件
    	                sxssfWorkbook.dispose();
    	            }
    	            if(os != null) {
    	            	os.close();
    	            }
    	        } catch (IOException e) {
    	            e.printStackTrace();
    	        }
    	    }
    	}
    
       /**
    	* 导出excel带标题
    	* 
    	* @param response
    	*            HttpServletResponse
    	* @param list
    	*            导出数据集合
    	* @param lables           
    	*            表头数组
    	* @param fields
    	*            key数组
    	* @param title            
    	*            文件名
    	* @param headTitle            
    	*            文件标题
    	*/
    	public static void titleExport(HttpServletResponse response,List<Map<String, Object>> list,String[] lables,String[] fields,String title,String headTitle) {		   	   
    	   response.setContentType("application/octet-stream");//告诉浏览器输出内容为流
    	   String filename = "";
    	   try {
    		   filename = new String(title.getBytes("UTF-8"), "ISO_8859_1");
    	   } catch (UnsupportedEncodingException e1) {
    		   e1.printStackTrace();
    	   }
    	   response.setHeader("Content-Disposition","attachment;filename=" + filename);
    	   OutputStream os = null;
    	   SXSSFWorkbook sxssfWorkbook = null;
    	   try {		 
    	        // 获取SXSSFWorkbook
    	        sxssfWorkbook = new SXSSFWorkbook();
    	        Sheet sheet = sxssfWorkbook.createSheet("Sheet1");
    	        // 创建第一行,作为标题
    	        Row headline = sheet.createRow(0);
    	        Cell c = headline.createCell(0);
    	        //设置居中
    	        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
    	        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
    	        c.setCellStyle(xssfCellStyle);
    	        c.setCellValue(headTitle);
    	        sheet.addMergedRegion(new CellRangeAddress(0,0,0,lables.length-1));//合并单元格(起始行号,终止行号,起始列号,终止列号)
    	        // 冻结第二行
    	        sheet.createFreezePane(0, 2);	
    	        // 创建第二行,作为header表头
    	        Row header = sheet.createRow(1);
    	        // 循环创建header单元格
    	        for (int cellnum = 0; cellnum < lables.length; cellnum++) {
    	            Cell cell = header.createCell(cellnum);
    	            //cell.setCellStyle(getAndSetXSSFCellStyleHeader(sxssfWorkbook));//设置表头单元格样式,根据需要设置
    	            cell.setCellValue(lables[cellnum]);
    	            //设置每列固定宽度
    	            sheet.setColumnWidth(cellnum, 10 * 256);
    	        }
    	        // 遍历创建行,导出数据
    	        for (int rownum = 1; rownum <= list.size(); rownum++) {
    	            Row row = sheet.createRow(rownum+1);
    	            Map<String, Object> map = list.get(rownum-1);
    	            // 循环创建单元格
    	            for (int cellnum = 0; cellnum < fields.length; cellnum++) {
    	                Cell cell = row.createCell(cellnum);
    	                //cell.setCellStyle(getAndSetXSSFCellStyleOne(sxssfWorkbook));//设置数据行单元格样式,根据需要设置
    	                cell.setCellValue(map.get(fields[cellnum]) == null ? "" : map.get(fields[cellnum]).toString());
    	            }
    	        }
    	        //自定义各列宽度
    	        //setSheet(sheet);
    	        os = response.getOutputStream(); 
    	        sxssfWorkbook.write(os);
    	    } catch (Exception e) {
    	    	e.printStackTrace();
    	    } finally {
    	        try {
    	            if(sxssfWorkbook != null) {
    	                //处理SXSSFWorkbook导出excel时,产生的临时文件
    	                sxssfWorkbook.dispose();
    	            }
    	            if(os != null) {
    	            	os.close();
    	            }
    	        } catch (IOException e) {
    	            e.printStackTrace();
    	        }
    	    }
    	}
    
       /**
    	* 导出excel(多个工作薄)
    	* 
    	* @param response
    	*            HttpServletResponse
    	* @param datas
    	*            导出数据集合
    	* @param title            
    	*            文件名
    	*/
    	public static void exportSheets(HttpServletResponse response,List<Map<String, Object>> datas,String title) {		   	   
    	   response.setContentType("application/octet-stream");//告诉浏览器输出内容为流
    	   String filename = "";
    	   try {
    		   filename = new String(title.getBytes("UTF-8"), "ISO_8859_1");
    	   } catch (UnsupportedEncodingException e1) {
    		   e1.printStackTrace();
    	   }
    	   response.setHeader("Content-Disposition","attachment;filename=" + filename);
    	   OutputStream os = null;
    	   SXSSFWorkbook sxssfWorkbook = null;
    	   try {		 
    	        // 获取SXSSFWorkbook
    	        sxssfWorkbook = new SXSSFWorkbook();
    	        // 根据集合的数量创建sheet
    	        for(int i=0; i<datas.size(); i++){
    	            Sheet sheet = sxssfWorkbook.createSheet("Sheet"+(i+1));
    		    // 冻结第一行
    		    sheet.createFreezePane(0, 1);	
    		    // 创建第一行,作为header表头
    		    Row header = sheet.createRow(0);
    		    // 循环创建header单元格
    		    String[] lables = (String[]) datas.get(i).get("lables");
    		    String[] fields = (String[]) datas.get(i).get("fields");
    		    for (int cellnum = 0; cellnum < lables.length; cellnum++) {
    		        Cell cell = header.createCell(cellnum);
    		        //cell.setCellStyle(getAndSetXSSFCellStyleHeader(sxssfWorkbook));//设置表头单元格样式,根据需要设置
    		        cell.setCellValue(lables[cellnum]);
    		        //设置每列固定宽度
    		        sheet.setColumnWidth(cellnum, 20 * 256);
    		    }
    		    // 遍历创建行,导出数据
    		    List<Map<String, Object>> list = (List<Map<String, Object>>) datas.get(i).get("list");
    		    for (int rownum = 1; rownum <= list.size(); rownum++) {
    		        Row row = sheet.createRow(rownum);
    		        Map<String, Object> map = list.get(rownum-1);
    		        // 循环创建单元格
    		        for (int cellnum = 0; cellnum < fields.length; cellnum++) {
    		            Cell cell = row.createCell(cellnum);
    		            //cell.setCellStyle(getAndSetXSSFCellStyleOne(sxssfWorkbook));//设置数据行单元格样式,根据需要设置
    		            cell.setCellValue(map.get(fields[cellnum]) == null ? "" : map.get(fields[cellnum]).toString());
    		        }
    		    }
    	        }
    	        os = response.getOutputStream(); 
    	        sxssfWorkbook.write(os);
    	    } catch (Exception e) {
    	    	e.printStackTrace();
    	    } finally {
    	        try {
    	            if(sxssfWorkbook != null) {
    	                //处理SXSSFWorkbook导出excel时,产生的临时文件
    	                sxssfWorkbook.dispose();
    	            }
    	            if(os != null) {
    	            	os.close();
    	            }
    	        } catch (IOException e) {
    	            e.printStackTrace();
    	        }
    	    }
    	}
    	   
       /**
        * 自定义各列宽度(单位为:字符宽度的1/256)
        */
        private static void setSheet(Sheet sheet) {       
    	sheet.setColumnWidth(0, 32 * 256);
            sheet.setColumnWidth(1, 32 * 256);
            sheet.setColumnWidth(2, 20 * 256);
            sheet.setColumnWidth(3, 20 * 256);
            sheet.setColumnWidth(4, 20 * 256);
            sheet.setColumnWidth(5, 20 * 256);
            sheet.setColumnWidth(6, 20 * 256);
            sheet.setColumnWidth(7, 20 * 256);
            sheet.setColumnWidth(8, 20 * 256);
            sheet.setColumnWidth(9, 20 * 256);
            sheet.setColumnWidth(10, 32 * 256);
        }
     
       /**
        * 获取并设置header样式
        */
        private static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
            XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
            Font font = sxssfWorkbook.createFont();
            // 字体大小
            font.setFontHeightInPoints((short) 14);
            // 字体粗细
            font.setBoldweight((short) 20);
            // 将字体应用到样式上面
            xssfCellStyle.setFont(font);
            // 是否自动换行
            xssfCellStyle.setWrapText(false);
            // 水平居中
            xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 垂直居中
            xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            return xssfCellStyle;
        }
     
       /**
        * 获取并设置样式
        */
        private static XSSFCellStyle getAndSetXSSFCellStyleOne(SXSSFWorkbook sxssfWorkbook) {
            XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
            XSSFDataFormat format = (XSSFDataFormat)sxssfWorkbook.createDataFormat();
            // 是否自动换行
            xssfCellStyle.setWrapText(false);
            // 水平居中
            xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 垂直居中
            xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 前景颜色
            xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
            // 边框
            xssfCellStyle.setBorderBottom(BorderStyle.THIN);
            xssfCellStyle.setBorderRight(BorderStyle.THIN);
            xssfCellStyle.setBorderTop(BorderStyle.THIN);
            xssfCellStyle.setBorderLeft(BorderStyle.THIN);
            xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
            xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
            xssfCellStyle.setDataFormat(format.getFormat("0"));
            return xssfCellStyle;
        }
    }

    3.业务层调用

    //导出
    @RequestMapping("/export")
    public String export(Model model,HttpServletRequest request,HttpServletResponse response) {
    	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    	System.out.println("导出开始时间:"+format.format(new Date()));
    	List<Map<String, Object>> list = jkconfigDao.exportByRksj();//查询业务数据
    	String[] lables = new String[]{"号码","入库时间"};//表头数组
    	String[] fields = new String[]{"HM","RKSJ"};//查询数据对应的属性数组
    	String title = "测试.xlsx";
    	ExportExcel.export(response, list, lables, fields, title);	
    	System.out.println("导出结束时间:"+format.format(new Date()));
    	return null;
    }

     

    展开全文
  • java大数据量的excel导入导出

    万次阅读 2017-10-09 17:24:51
    大数据量导入导出, 亲测可解决百万级excel的导入导出! 基础: 03版(xls)excel文件每个sheet最大长度为 65535 07版(xlsx)excel文件每个sheet最大长度为 104万 07版后, 底层文件为 xml, 可以将 xlsx后缀改...
  • 大数据量导出的设计总结

    千次阅读 2018-08-11 19:33:36
    由于数据量,且现有实现方式不合理,容易出现由于导出数据过多造成的“内存溢出”问题,并且由于现有导出是同步方式,处理时间相对较长,导致 WEBLOGIC 服务器监控到线程执行时间过长 从而产生 预警。...
  • Excel大数据量导出

    2019-06-16 09:55:35
    2019独角兽企业重金招聘Python工程师标准>>> ...
  • 将web页面上显示的报表导出到excel文件里是一种很常见的需求。...下面就给出大数据量导出到excel的解决方 案。 首先,对于数据超过了65535行的问题,很自然的就会想到将整个数据分块,利用excel的多sheet页的功能...
  • JAVA实现大数据量导出excel

    千次阅读 2018-09-06 13:13:50
    JAVA 实现大数据量导出操作时,如果采用POI直接导出,会出现内存溢出的情况。再者EXCEL的SHEET也存在行数的限制,Excel2003版最大行数是655536行、Excel2007版最大行数是1048576行、Excel2010版最大行数是1048576行...
  • 大数据量导出EXCEL解决方案

    千次阅读 2012-11-01 14:48:00
    将web页面上显示的报表导出到excel文件里是一种很常见的需求。...下面就给出大数据量导出到excel的理论解决方案。    首先,对于数据超过了65535行的问题,很自然的就会想到将整个数据分块,利用excel的多sh
  • java csv大数据量导出(千万级别,不会内存溢出),多线程导出 ,生产环境已经很稳定的使用着
  • java中使用poi导出Excel大批量数据 存在两个导出方法:存在一个分批量导出ZIP文件,一个导出exel文件
  • 数据库大数据量导出多线程版本

    千次阅读 2012-04-20 21:42:46
    当时写了一篇博客《在集群上支持数据库大数据量导出》,简单地讲了一些原理,并贴出了部分的源码。原理用了一张图来表述: 基本就是客户在页面申请导出请求,把请求存在数据库中,再由定时任务取出来运行:...
  • 支持模板导出 ,支持分页查询 , 支持大数据量,采用SXSSFWorkbook
  • phpexcle大数据量导出

    2019-03-31 18:10:22
    最近接到一个需求,通过选择的时间段导出对应的订单数据到excel中, 由于数据量,经常会有导出500K+数据的情况。平常的导出用的PHPexcle,将数据一次性读取到内存中再一次性写入文件,而在面对生成超大数据量的...
  • 其次,在实验过程中,大数据量导出很容易引发内存溢出,调整JVM的内存大小治标不治本。很多人建议保存为.CSV格式的文件。不过,.CSV方式导出也存在问题:首先,如果用excel来打开csv,超过65536行的数据都会看不见...
  • 解决excel大数据量导出问题

    千次阅读 2018-05-15 14:52:13
    项目中遇到用户要求导出excel功能,数据量在25w左右,原先项目中已经封装了excel导出功能,用了下发现有错误并且该功能没有源码全部是class文件封装在jar里,打开jar包反编译了半天才定位到问题所在,本想修改又怕别...
  • 大数据量导出Excel可以采用POI、JXL等等开源框架。目前,在项目中采用的是POI。   客户要求导出5w条数据,考虑到并发,这对于系统内存是个挑战,系统使用tomcat,据说tomcat的jvm内存只能升级为2g有待考证。   ...
  • 导出Excel 支持大数据量导出

    热门讨论 2020-07-29 14:20:57
    jxl 导出Excel 支持大数据量导出 导出数据量大 速度也快
  • oracle百(千)万级表数据导出工具,用于Oracle数据库导出大数据量表 ,保存格式如csv
1 2 3 4 5 ... 20
收藏数 183,521
精华内容 73,408
关键字:

大数据量导出