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

     

     

    展开全文
  • Java POI处理大量数据导入导出xls和xlsx导入数据(大量)导出数据(大量)总结 xls和xlsx xls是旧版Excel格式文件,xlsx是新版Excel格式文件;而xlsx新版格式其实是一系列文件压缩包, 如图: xls是以二进制的方式...

    xls和xlsx

    1. xls是旧版Excel格式文件,xlsx是新版Excel格式文件;而xlsx新版格式其实是一系列文件压缩包,
      如图:
      在这里插入图片描述
    2. xls是以二进制的方式存储,这种格式不易被其他软件读取使用;而xlsx采用了基于XML的ooxml开放文档标准,ooxml使用XML和ZIP技术结合进行文件存储,XML是一个基于文本的格式,而且ZIP容器支持内容的压缩,所以其一大优势是可以大大减小文件的尺寸;
    3. 使用POI来读写Excel文件有两种方式,一种用户模式(UserModel),读取时消耗大量内存,造成OOM问题;一种事件模式(SAX模式),仅仅关注文件内部数据,内存消耗很低;
    4. 导出文件同样如此,使用Workbook普通导出,数据量小的时候可以正常使用,但时间等待仍然很长,这时推荐使用POI提供的SXXFWorkbook处理,其使用时间窗口原理(具体可以查询)限制访问,刷出内存,降低内存消耗,提升效率。
      另外还需要注意,根据你使用的功能,仍然可能消耗大量内存,例如合并区域,超链接,注释……,这些内容只存储在内存中。
      在这里插入图片描述

    导入数据(大量)

    大量数据导入在网络上搜寻到的相关代码大部分通过集成POI原生的DefaultHandler重写其startElement, endElement, characters方法进行相关的解析,而POI已经将相关逻辑封装在XSSFSheetXMLHandler,只要实现暴露的接口SheetContentsHandler即可。
    使用SheetContentsHandler的例子可以参考官方的XLSX2CVS
    本例实现该接口:

    package cn.skio.venus.api;
    
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.util.SAXHelper;
    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.xml.sax.InputSource;
    import org.xml.sax.SAXException;
    import org.xml.sax.XMLReader;
    
    import javax.xml.parsers.ParserConfigurationException;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.LinkedList;
    import java.util.List;
    
    /**
     * @autor jasmine
     */
    public class ExcelEventParser {
        private String fileName;
        private SimpleSheetContentsHandler handler;
        // 测试使用对比使用SAX和UserModel模式选择(实际使用不需要)
        private Integer saxInterupt;
    	private void setHandler(SimpleSheetContentsHandler handler) {
    		this.handler = handler;
    	}
    
    	// 放置读取数据
        protected List<List<String>> table = new ArrayList<>();
    
        public ExcelEventParser(String filename, Integer saxInterupt){
            this.fileName = filename;
            this.saxInterupt = saxInterupt;
        }
    
        public List<List<String>> parse() {
            OPCPackage opcPackage = null;
            InputStream inputStream = null;
    
            try {
                FileInputStream fileStream = new FileInputStream(fileName);
                opcPackage = OPCPackage.open(fileStream);
                XSSFReader xssfReader = new XSSFReader(opcPackage);
    
                StylesTable styles = xssfReader.getStylesTable();
                ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opcPackage);
                inputStream = xssfReader.getSheetsData().next();
    
                processSheet(styles, strings, inputStream);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (inputStream != null) {
                    try {
                        inputStream.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (opcPackage != null) {
                    try {
                        opcPackage.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
            return table;
        }
    
    	// 确定XMLReader解析器,使用SAX模式解析xml文件
        private void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws SAXException, ParserConfigurationException, IOException {
            XMLReader sheetParser = SAXHelper.newXMLReader();
    
            if (handler == null) {
                setHandler(new SimpleSheetContentsHandler());
            }
            sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, handler, false));
    
            try {
                sheetParser.parse(new InputSource(sheetInputStream));
            } catch (RuntimeException e) {
                System.out.println("---> 遇到空行读取文件结束!");
            }
        }
    
    	// 实现SheetContentsHandler
        public class SimpleSheetContentsHandler implements SheetContentsHandler{
            protected List<String> row;
            @Override
            public void startRow(int rowNum) {
                row = new LinkedList<>();
            }
    
            @Override
            public void endRow(int rowNum) {
            	// 判断是否使用异常作为文件读取结束(有些Excel文件格式特殊,导致很多空行,浪费内存)
                if (saxInterupt == 1) {
                    if (row.isEmpty()) {
                        throw new RuntimeException("Excel文件读取完毕");
                    }
                }
    			// 添加数据到list集合
                table.add(row);
            }
    
            /**
             * 所有单元格数据转换为string类型,需要自己做数据类型处理
             * @param cellReference 单元格索引
             * @param formattedValue 单元格内容(全部被POI格式化为字符串)
             * @param comment
             */
            @Override
            public void cell(String cellReference, String formattedValue, XSSFComment comment) {
                row.add(formattedValue);
            }
    
            @Override
            public void headerFooter(String text, boolean isHeader, String tagName) {
            }
        }
    }
    
    

    经测试结果,发现使用SAX模式(抛弃了样式等,只关注数据)仅仅消耗很少内存,效率高;而普通Workbook读取数据(测试文件为5.2MB的有大量空行文件)内存消耗 > 1GB(此时线上系统OOM概率非常大);
    在这里插入图片描述

    导出数据(大量)

    导出数据的话瓶颈主要在于数据写入Excel文件,代码(同样的74273条数据导出)如下:

    	// 使用SXSSFwrokbook,大量数据处理快速
    	@GetMapping("/outExcel")
        public void outPutExcel(HttpServletResponse response) throws Exception {
            // 每次写100行数据,就刷新数据出缓存
            SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
            Sheet sh = wb.createSheet();
            List<Tmp> tmps = tmpDao.findAll();
            log.info("---> 数据量:{}", tmps.size());
    
            for(int rowNum = 0; rowNum < tmps.size(); rowNum++){
                Row row = sh.createRow(rowNum);
                Tmp tmp = tmps.get(rowNum);
                Cell cell1 = row.createCell(0);
                cell1.setCellValue(tmp.getSource());
    
                Cell cell2 = row.createCell(1);
                cell2.setCellValue(tmp.getName());
                Cell cell3 = row.createCell(2);
                cell3.setCellValue(tmp.getPhone());
                Cell cell4 = row.createCell(3);
                cell4.setCellValue(tmp.getCity());
            }
    
            String fileName = "sxssf.xlsx";
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            wb.write(response.getOutputStream());
            wb.close();
        }
    
    	// XSSFWorkbook, 效率低下
    	@GetMapping("/outExcel2")
        public void outPutExcel2(HttpServletResponse response) throws Exception {
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sh = wb.createSheet();
            List<Tmp> tmps = tmpDao.findAll();
            log.info("---> 数据量:{}", tmps.size());
    
            for(int rowNum = 0; rowNum < tmps.size(); rowNum++){
                Row row = sh.createRow(rowNum);
                Tmp tmp = tmps.get(rowNum);
                Cell cell1 = row.createCell(0);
                cell1.setCellValue(tmp.getSource());
    
                Cell cell2 = row.createCell(1);
                cell2.setCellValue(tmp.getName());
                Cell cell3 = row.createCell(2);
                cell3.setCellValue(tmp.getPhone());
                Cell cell4 = row.createCell(3);
                cell4.setCellValue(tmp.getCity());
            }
    
            String fileName = "sxssf.xlsx";
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "GBK"));
            wb.write(response.getOutputStream());
            wb.close();
        }
    

    效率对比:

    对象 耗时
    SXSSFWorkbook 在这里插入图片描述
    XSSFWorkbook 在这里插入图片描述

    CPU和内存消耗对比:
    在这里插入图片描述

    总结

    1. 大文件读取使用SAX
    2. 大文件写入使用SXSSFWorkbook

    参考链接:
    [1]: https://blog.csdn.net/Holmofy/article/details/82532311
    [2]: https://blog.csdn.net/daiyutage/article/details/53010491
    [3]: https://www.cnblogs.com/yfrs/p/5689347.html
    [4]: easyexcel

    展开全文
  • maven 依赖 (版本必须一致,否则使用SXSSFworkbook 时...org.apache.poi&lt;/groupId&gt; &lt;artifactId&gt;poi&lt;/artifactId&gt; &lt;version&gt;3.9&lt;/version&gt;

    maven 依赖 (版本必须一致

    ,否则使用SXSSFworkbook 时程序会报错)

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

    HSSFworkbook,XSSFworkbook,SXSSFworkbook 三者 区别

    HSSFworkbook:操作Excel2003版本,扩展名为xls

    XSSFworkbook:操作Excel2007版本,扩展名为xlsx

    SXSSFworkbook :用于大数据量导出,当数据量超过 65536后 程序 会报错:Invalid row number (65536) outside allowable range (0..65535)


    例子1:简单导出Excel

    @Test
        public void test1() throws IOException {
            // 读取文件
            POIFSFileSystem fs = new POIFSFileSystem(Thread.currentThread().getContextClassLoader().getResourceAsStream("test.xls"));
            // 创建一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            // 获取第一个sheet
            HSSFSheet sheet = workbook.getSheetAt(0);
            System.out.println(sheet.getSheetName());
            // 获取第一行
            HSSFRow row = sheet.getRow(0);
            // 获取第一行第一列
            HSSFCell cell = row.getCell(0);
            System.out.println(cell.getStringCellValue());
    
            // 创建一行
            HSSFRow row1 = sheet.createRow(1);
            // 创建单元格
            HSSFCell cell1 = row1.createCell(0);
            // 单元格赋值
            cell1.setCellValue("我是程序创建的内容");
            System.out.println(cell1.getStringCellValue());
    
            // 创建输出流
            FileOutputStream os = new FileOutputStream(new File("D:\\8888.xls"));
            // 输出文件
            workbook.write(os);
        }

    例子2:导出查询数据

    @Test
        public void test2() {
            // 模拟导出数据
            Object[] obj = new Object[]{"哈哈", "呵呵", "哼哼"};
            List<Object[]> list = new ArrayList<Object[]>();
            // HSSFWorkbook 只支持2003版本及以下版本Excel 且容量最大为65536
            for (int i = 0; i < 65536; i++) {
                list.add(obj);
            }
    
            export("test.xls", list, 2);
        }
    
        /**
         * poi 导出
         * @param fileName
         * @param objs
         * @param rowIndex
         */
        private void export(String fileName, List<Object[]> objs, int rowIndex) {
            POIFSFileSystem fs = null;
            FileOutputStream os = null;
            try {
                fs = new POIFSFileSystem(Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName));
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 创建一个工作簿
            try {
                HSSFWorkbook workbook = new HSSFWorkbook(fs);
                HSSFCellStyle style = setCellStyle(workbook);
                // 获取一个sheet页
                HSSFSheet sheet = workbook.getSheetAt(0);
    
                for (int i = rowIndex - 1; i < objs.size(); i++) {
                    // 创建行
                    HSSFRow row = sheet.createRow(i);
    
                    // 创建列
                    for (int j = 0; j < objs.get(i).length; j++) {
                        HSSFCell cell = row.createCell(j);
                        // 设置单元格样式
                        cell.setCellStyle(style);
                        cell.setCellValue(objs.get(i)[j].toString());
                    }
                }
                // 创建输出流
                 os = new FileOutputStream(new File("D:\\8888.xls"));
                // 输出文件
                workbook.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                if (os != null) {
                    try {
                        os.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    
    
        /**
         * 设置样式
         * @param workbook
         */
        private HSSFCellStyle setCellStyle(HSSFWorkbook workbook) {
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            // 字号
            font.setFontHeightInPoints((short) 12);
            style.setFont(font);
            // 左右居中 上下居中
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            return style;
        }

    例子3:大数据量导出

    /**
         * 大数据量导出
         * @throws IOException
         */
        @Test
        public void text2() throws IOException {
    
            XSSFWorkbook xssfWorkbook = new   XSSFWorkbook(Thread.currentThread().getContextClassLoader().getResourceAsStream("bigdata.xlsx"));
            SXSSFWorkbook wb = new SXSSFWorkbook(xssfWorkbook, 100);
    
            Sheet sh = wb.getSheetAt(0);
            for(int rownum = 1; rownum < 75537; rownum++){
                Row row = sh.createRow(rownum);
                for(int cellnum = 0; cellnum < 10; cellnum++){
                    Cell cell = row.createCell(cellnum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }
    
            }
    
    //        // Rows with rownum < 900 are flushed and not accessible
    //        for(int rownum = 0; rownum < 900; rownum++){
    //            Assert.assertNull(sh.getRow(rownum));
    //        }
    //
    //        // ther last 100 rows are still in memory
    //        for(int rownum = 900; rownum < 1000; rownum++){
    //            Assert.assertNotNull(sh.getRow(rownum));
    //        }
    
            FileOutputStream out = new FileOutputStream("D:\\sxssf.xlsx");
            wb.write(out);
            out.close();
    
            // dispose of temporary files backing this workbook on disk
            wb.dispose();
        }

    展开全文
  • 需要导出数据量可能非常(超过10W条),而且要照顾到没装07Excel的机器,不能用SXSSFworkbook。鉴于一个sheet页最多65000多条记录,那么大数据就需要多个sheet页。由于导出前要先查数据库,是应该一次查出所有...
  • java中使用poi导出Excel大批量数据 存在两个导出方法:存在一个分批量导出ZIP文件,一个导出exel文件
  • POI大数据量导出

    2010-10-28 15:44:04
    前段时间,做POI导出的时候,应为数据量大(十万条数据),所以总是出现OOM错误,在网上找到了解决办法,大数据量导出Excel的方案:http://devbbs.doit.com.cn/redirect.php?tid=46&amp;goto=lastpost解决大批量...

    前段时间,做POI导出的时候,应为数据量大(十万条数据),所以总是出现OOM错误,在网上找到了解决办法,大数据量导出Excel的方案:http://devbbs.doit.com.cn/redirect.php?tid=46&goto=lastpost
    解决大批量数据导出Excel产生内存溢出的方案:http://cnaning.iteye.com/blog/347158
    解决大批量数据导出Excel产生内存溢出的方案(二):http://cnaning.iteye.com/blog/347160

    1. import java.io.ByteArrayInputStream;   
    2. import java.io.File;   
    3. import java.io.FileOutputStream;   
    4. import java.io.IOException;   
    5. import java.io.InputStream;   
    6. import java.io.OutputStream;   
    7. import java.util.ArrayList;   
    8. import java.util.Collections;   
    9. import java.util.HashMap;   
    10. import java.util.Iterator;   
    11. import java.util.List;   
    12. import java.util.Map;   
    13.   
    14. import org.apache.log4j.Logger;   
    15. import org.apache.poi.hssf.model.Sheet;   
    16. import org.apache.poi.hssf.model.Workbook;   
    17. import org.apache.poi.hssf.record.CellValueRecordInterface;   
    18. import org.apache.poi.hssf.record.LabelRecord;   
    19. import org.apache.poi.hssf.record.LabelSSTRecord;   
    20. import org.apache.poi.hssf.record.Record;   
    21. import org.apache.poi.hssf.record.RowRecord;   
    22. import org.apache.poi.hssf.record.SSTRecord;   
    23. import org.apache.poi.hssf.record.UnicodeString;   
    24. import org.apache.poi.poifs.filesystem.POIFSFileSystem;   
    25. import org.apache.struts.taglib.html.RewriteTag;   
    26.   
    27. @SuppressWarnings("unchecked")   
    28. public class XlsMergeUtil {   
    29.     private static Logger logger = Logger.getLogger(XlsMergeUtil.class);   
    30.   
    31.     /**  
    32.      * 将多个Xls文件合并为一个,适用于只有一个sheet,并且格式相同的文档  
    33.      *   
    34.      * @param inputs  
    35.      *            输入的Xls文件,第一个XLS文件必须给出足够sheet空间 例如,总共200000行数据,第一个文件至少3个空白sheet  
    36.      * @param out  
    37.      *            输出文件  
    38.      */  
    39.     public static void merge(InputStream[] inputs, OutputStream out) {   
    40.         if (inputs == null || inputs.length <= 1) {   
    41.             throw new IllegalArgumentException("没有传入输入流数组,或只有一个输入流.");   
    42.         }   
    43.   
    44.         List<Record> rootRecords = getRecords(inputs[0]);   
    45.         Workbook workbook = Workbook.createWorkbook(rootRecords);   
    46.         List<Sheet> sheets = getSheets(workbook, rootRecords);   
    47.         if (sheets == null || sheets.size() == 0) {   
    48.             throw new IllegalArgumentException("第一篇文档的格式错误,必须有至少一个sheet");   
    49.         }   
    50.         // 以第一篇文档的第一个sheet为根,以后的数据都追加在这个sheet后面   
    51.         Sheet rootSheet = sheets.get(0);   
    52.         int rootRows = getRowsOfSheet(rootSheet); // 记录第一篇文档的行数,以后的行数在此基础上增加   
    53.         rootSheet.setLoc(rootSheet.getDimsLoc());   
    54.         Map<Integer, Integer> map = new HashMap(10000);   
    55.         int sheetIndex = 0;   
    56.   
    57.         for (int i = 1; i < inputs.length; i++) { // 从第二篇开始遍历   
    58.             List<Record> records = getRecords(inputs[i]);   
    59.             // 达到最大行数限制,换一个sheet   
    60.             if (getRows(records) + rootRows >= RowRecord.MAX_ROW_NUMBER) {   
    61.                 if ((++sheetIndex) > (sheets.size() - 1)) {   
    62.                     logger.warn("第一个文档给出的sheets小于需要的数量,部分数据未能合并.");   
    63.                     break;   
    64.                 }   
    65.                 rootSheet = sheets.get(sheetIndex);   
    66.                 rootRows = getRowsOfSheet(rootSheet);   
    67.                 rootSheet.setLoc(rootSheet.getDimsLoc());   
    68.                 logger.debug("切换Sheet" + sheetIndex + "");   
    69.             }   
    70.             int rowsOfCurXls = 0;   
    71.             // 遍历当前文档的每一个record   
    72.             for (Iterator itr = records.iterator(); itr.hasNext();) {   
    73.                 Record record = (Record) itr.next();   
    74.                 if (record.getSid() == RowRecord.sid) { // 如果是RowRecord   
    75.                     RowRecord rowRecord = (RowRecord) record;   
    76.                     // 调整行号   
    77.                     rowRecord.setRowNumber(rootRows + rowRecord.getRowNumber());   
    78.                     rootSheet.addRow(rowRecord); // 追加Row   
    79.                     rowsOfCurXls++; // 记录当前文档的行数   
    80.                 }   
    81.                 // SST记录,SST保存xls文件中唯一的String,各个String都是对应着SST记录的索引   
    82.                 else if (record.getSid() == SSTRecord.sid) {   
    83.                     SSTRecord sstRecord = (SSTRecord) record;   
    84.                     for (int j = 0; j < sstRecord.getNumUniqueStrings(); j++) {   
    85.                         int index = workbook.addSSTString(sstRecord   
    86.                                 .getString(j));   
    87.                         // 记录原来的索引和现在的索引的对应关系   
    88.                         map.put(Integer.valueOf(j), Integer.valueOf(index));   
    89.                     }   
    90.                 } else if (record.getSid() == LabelSSTRecord.sid) {   
    91.                     LabelSSTRecord label = (LabelSSTRecord) record;   
    92.                     // 调整SST索引的对应关系   
    93.                     label.setSSTIndex(map.get(Integer.valueOf(label   
    94.                             .getSSTIndex())));   
    95.                 }   
    96.                 // 追加ValueCell   
    97.                 if (record instanceof CellValueRecordInterface) {   
    98.                     CellValueRecordInterface cell = (CellValueRecordInterface) record;   
    99.                     int cellRow = cell.getRow() + rootRows;   
    100.                     cell.setRow(cellRow);   
    101.                     rootSheet.addValueRecord(cellRow, cell);   
    102.                 }   
    103.             }   
    104.             rootRows += rowsOfCurXls;   
    105.         }   
    106.   
    107.         byte[] data = getBytes(workbook, sheets.toArray(new Sheet[0]));   
    108.         write(out, data);   
    109.     }   
    110.   
    111.     static void write(OutputStream out, byte[] data) {   
    112.         POIFSFileSystem fs = new POIFSFileSystem();   
    113.         // Write out the Workbook stream   
    114.         try {   
    115.             fs.createDocument(new ByteArrayInputStream(data), "Workbook");   
    116.             fs.writeFilesystem(out);   
    117.             out.flush();   
    118.         } catch (IOException e) {   
    119.             e.printStackTrace();   
    120.         } finally {   
    121.             try {   
    122.                 out.close();   
    123.             } catch (IOException e) {   
    124.                 e.printStackTrace();   
    125.             }   
    126.         }   
    127.     }   
    128.   
    129.     static List<Sheet> getSheets(Workbook workbook, List records) {   
    130.         int recOffset = workbook.getNumRecords();   
    131.         int sheetNum = 0;   
    132.   
    133.         // convert all LabelRecord records to LabelSSTRecord   
    134.         convertLabelRecords(records, recOffset, workbook);   
    135.         List<Sheet> sheets = new ArrayList();   
    136.         while (recOffset < records.size()) {   
    137.             Sheet sh = Sheet.createSheet(records, sheetNum++, recOffset);   
    138.   
    139.             recOffset = sh.getEofLoc() + 1;   
    140.             if (recOffset == 1) {   
    141.                 break;   
    142.             }   
    143.             sheets.add(sh);   
    144.         }   
    145.         return sheets;   
    146.     }   
    147.   
    148.     static int getRows(List<Record> records) {   
    149.         int row = 0;   
    150.         for (Iterator itr = records.iterator(); itr.hasNext();) {   
    151.             Record record = (Record) itr.next();   
    152.             if (record.getSid() == RowRecord.sid) {   
    153.                 row++;   
    154.             }   
    155.         }   
    156.         return row;   
    157.     }   
    158.   
    159.     static int getRowsOfSheet(Sheet sheet) {   
    160.         int rows = 0;   
    161.         sheet.setLoc(0);   
    162.         while (sheet.getNextRow() != null) {   
    163.             rows++;   
    164.         }   
    165.         return rows;   
    166.     }   
    167.   
    168.     @SuppressWarnings("deprecation")   
    169.     static List<Record> getRecords(InputStream input) {   
    170.         try {   
    171.             POIFSFileSystem poifs = new POIFSFileSystem(input);   
    172.             InputStream stream = poifs.getRoot().createDocumentInputStream(   
    173.                     "Workbook");   
    174.             return org.apache.poi.hssf.record.RecordFactory.createRecords(stream);   
    175.         } catch (IOException e) {   
    176.             logger.error("IO异常:" + e.getMessage() + "");   
    177.             e.printStackTrace();   
    178.         }   
    179.         return Collections.EMPTY_LIST;   
    180.     }   
    181.   
    182.     static void convertLabelRecords(List records, int offset, Workbook workbook) {   
    183.   
    184.         for (int k = offset; k < records.size(); k++) {   
    185.             Record rec = (Record) records.get(k);   
    186.   
    187.             if (rec.getSid() == LabelRecord.sid) {   
    188.                 LabelRecord oldrec = (LabelRecord) rec;   
    189.   
    190.                 records.remove(k);   
    191.                 LabelSSTRecord newrec = new LabelSSTRecord();   
    192.                 int stringid = workbook.addSSTString(new UnicodeString(oldrec   
    193.                         .getValue()));   
    194.   
    195.                 newrec.setRow(oldrec.getRow());   
    196.                 newrec.setColumn(oldrec.getColumn());   
    197.                 newrec.setXFIndex(oldrec.getXFIndex());   
    198.                 newrec.setSSTIndex(stringid);   
    199.                 records.add(k, newrec);   
    200.             }   
    201.         }   
    202.     }   
    203.   
    204.     public static byte[] getBytes(Workbook workbook, Sheet[] sheets) {   
    205.         // HSSFSheet[] sheets = getSheets();   
    206.         int nSheets = sheets.length;   
    207.   
    208.         // before getting the workbook size we must tell the sheets that   
    209.         // serialization is about to occur.   
    210.         for (int i = 0; i < nSheets; i++) {   
    211.             sheets[i].preSerialize();   
    212.         }   
    213.   
    214.         int totalsize = workbook.getSize();   
    215.         // pre-calculate all the sheet sizes and set BOF indexes   
    216.         int[] estimatedSheetSizes = new int[nSheets];   
    217.         for (int k = 0; k < nSheets; k++) {   
    218.             workbook.setSheetBof(k, totalsize);   
    219.             int sheetSize = sheets[k].getSize();   
    220.             estimatedSheetSizes[k] = sheetSize;   
    221.             totalsize += sheetSize;   
    222.         }   
    223.         logger.debug("分配内存" + totalsize + "bytes");   
    224.         byte[] retval = new byte[totalsize];   
    225.         int pos = workbook.serialize(0, retval);   
    226.         for (int k = 0; k < nSheets; k++) {   
    227.             int serializedSize = sheets[k].serialize(pos, retval);   
    228.             if (serializedSize != estimatedSheetSizes[k]) {   
    229.                 // Wrong offset values have been passed in the call to   
    230.                 // setSheetBof() above.   
    231.                 // For books with more than one sheet, this discrepancy would   
    232.                 // cause excel   
    233.                 // to report errors and loose data while reading the workbook   
    234.                 throw new IllegalStateException(   
    235.                         "Actual serialized sheet size (" + serializedSize   
    236.                                 + ") differs from pre-calculated size ("  
    237.                                 + estimatedSheetSizes[k] + ") for sheet (" + k   
    238.                                 + ")");   
    239.                 // write mis-aligned offsets either   
    240.             }   
    241.             pos += serializedSize;   
    242.         }   
    243.         return retval;   
    244.     }   
    245.   
    246. }  
    展开全文
  • POI 导入导出功能,引用jar包是关键,maven依赖支持1.37版. 介绍: 首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(页/表)...

    POI 导入导出功能,引用jar包是关键,maven依赖支持3.17版.

    介绍:

    首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(页/表)(HSSFSheet)组成,一个sheet是由多个row(行)(HSSFRow)组成,一个row是由多个cell(单元格)(HSSFCell)组成。
    1、用HSSFWorkbook打开或者创建“Excel文件对象
    2、用HSSFWorkbook对象返回或者创建Sheet对象
    3、用Sheet对象返回行对象,用行对象得到Cell对象
    4、对Cell对象读写

    maven 依赖:

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

    mvc 依赖:

    poi-3.7-20101029.jar
    poi-3.9.jar
    poi-ooxml-3.9.jar
    poi-ooxml-schemas-3.9.jar
    

    ExcelUtil.java (导入导出工能封装)

    import com.alibaba.fastjson.JSONArray;
    import com.alibaba.fastjson.JSONObject;
    import org.apache.poi.common.usermodel.HyperlinkType;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    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.apache.poi.xssf.usermodel.XSSFHyperlink;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.math.BigDecimal;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * Created by cdw on 2018/04/19.
     *
     * Apache POI操作Excel对象 HSSF:操作Excel 2007之前版本(.xls)格式,生成的EXCEL不经过压缩直接导出
     * XSSF:操作Excel 2007及之后版本(.xlsx)格式,内存占用高于HSSF SXSSF:从POI3.8
     * beta3开始支持,基于XSSF,低内存占用,专门处理大数据量(建议)。
     *
     * 注意: 值得注意的是SXSSFWorkbook只能写(导出)不能读(导入)
     *
     * 说明: .xls格式的excel(最大行数65536行,最大列数256列) .xlsx格式的excel(最大行数1048576行,最大列数16384列)
     * 这里引用的是阿里的json包,也可以自行转换成net.sf.json.JSONArray net.sf.json.JSONObject
     */
    public class ExcelUtil {
    
    	private final static String Excel_2003 = ".xls"; // 2003 版本的excel
    	private final static String Excel_2007 = ".xlsx"; // 2007 版本的excel
    
    	public static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd HH:mm:ss"; // 默认日期格式(类型为Date即可转换)
    	public static final int DEFAULT_COLUMN_WIDTH = 17; // 默认列宽
    
    	/**
    	 * 导入Excel
    	 *
    	 * @param file
    	 *            输入文件流
    	 */
    	public static List<List<Object>> importExcel(@RequestParam(value = "file", required = false) MultipartFile file)
                throws Exception {
    		String fileName = file.getOriginalFilename();
    		String xls = fileName.substring(fileName.lastIndexOf('.'));
    		if (Excel_2003.equals(xls) || Excel_2007.equals(xls)) {
    			return ExcelUtil.getImportExcel(file);
    		} else {
    			// 导入格式不正确
    			System.out.println("导入格式不正确:导入失败!");
    		}
    		return null;
    	}
    
    	/**
    	 * 导出Excel
    	 *
    	 * @param titleList
    	 *            表格头信息集合
    	 * @param dataArray
    	 *            数据数组
    	 * @param os
    	 *            文件输出流
    	 */
    	public static void exportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os)
    			throws Exception {
    		ExcelUtil.getExportExcel(titleList, dataArray, os);
    	}
    
    	/**
    	 * 导入Excel
    	 *
    	 * @param file
    	 *            导入文件流对象
    	 */
    	private static List<List<Object>> getImportExcel(MultipartFile file) throws Exception {
    		ImportExcelUtil util = new ImportExcelUtil();
    		String fileName = file.getOriginalFilename();
    		InputStream inputStream = file.getInputStream();
    		// 将导入的Excel数据转换成list集合
    		List<List<Object>> excelLists = util.getBankListByExcel(inputStream, fileName);
    		// 获取工作模板行数据对象
    //        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream));
            // 或
    //        Workbook workbook = util.getWorkbook(inputStream, fileName);
    //
    //		for (int i = 0; i < excelLists.size(); i++) { // 循环行
    //			List<Object> list = excelLists.get(i); // 获取行级列集合
    //			for (int j = 0; j < list.size(); j++) {
    //				System.out.println("获取" + i + "行级" + j + "列的值:" + list.get(j));
    //			}
    //		}
    //
    //		for (List<Object> excelList : excelLists) { // 循环行
    //			for (Object obj : excelList) { // 循环列
    //				// 获取行级列的值
    //			}
    //		}
    //
    //        for (Sheet rows : workbook) { // 循环行
    //            for (Row row : rows) { // 循环列
    //                // 获取行级列的值
    //            }
    //        }
    		return excelLists;
    	}
    
    	/**
    	 * 导出Excel
    	 *
    	 * @param titleList
    	 *            表格头信息集合
    	 * @param dataArray
    	 *            数据数组
    	 * @param os
    	 *            文件输出流
    	 */
    	private static void getExportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os)
    			throws Exception {
    		String datePattern = DEFAULT_DATE_PATTERN;
    		int minBytes = DEFAULT_COLUMN_WIDTH;
    
    		/**
    		 * 声明一个工作薄
    		 */
    		SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 大于1000行时会把之前的行写入硬盘
    		workbook.setCompressTempFiles(true);
    
    		// 表头1样式
    		CellStyle title1Style = workbook.createCellStyle();
    		title1Style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
    		title1Style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
    		Font titleFont = workbook.createFont();// 字体
    		titleFont.setFontHeightInPoints((short) 20);
    		titleFont.setBold(true);
    		titleFont.setFontHeight((short) 700);
    		title1Style.setFont(titleFont);
    
    		// 表头2样式
    		CellStyle title2Style = workbook.createCellStyle();
    		title2Style.setAlignment(HorizontalAlignment.CENTER);
    		title2Style.setVerticalAlignment(VerticalAlignment.CENTER);
    		title2Style.setBorderTop(BorderStyle.THIN);// 上边框
    		title2Style.setBorderRight(BorderStyle.THIN);// 右
    		title2Style.setBorderBottom(BorderStyle.THIN);// 下
    		title2Style.setBorderLeft(BorderStyle.THIN);// 左
    		Font title2Font = workbook.createFont();
    		title2Font.setUnderline((byte) 1);
    		title2Font.setColor(HSSFColor.BLUE.index);
    		title2Style.setFont(title2Font);
    
    		// head样式
    		CellStyle headerStyle = workbook.createCellStyle();
    		headerStyle.setAlignment(HorizontalAlignment.CENTER);
    		headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    		headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置颜色
    		headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 前景色纯色填充
    		headerStyle.setBorderTop(BorderStyle.THIN);
    		headerStyle.setBorderRight(BorderStyle.THIN);
    		headerStyle.setBorderBottom(BorderStyle.THIN);
    		headerStyle.setBorderLeft(BorderStyle.THIN);
    		Font headerFont = workbook.createFont();
    		headerFont.setFontHeightInPoints((short) 12);
    		headerFont.setBold(true); // 是否加粗
    		headerFont.setFontHeight((short) 500); // 字体大小
    		headerStyle.setFont(headerFont);
    
    		// 单元格样式
    		CellStyle cellStyle = workbook.createCellStyle();
    		cellStyle.setAlignment(HorizontalAlignment.CENTER);
    		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    		cellStyle.setBorderTop(BorderStyle.THIN);
    		cellStyle.setBorderRight(BorderStyle.THIN);
    		cellStyle.setBorderBottom(BorderStyle.THIN);
    		cellStyle.setBorderLeft(BorderStyle.THIN);
    		Font cellFont = workbook.createFont();
    		cellFont.setBold(false); // 是否加粗
    		cellFont.setFontHeight((short) 300); // 字体大小
    		cellStyle.setFont(cellFont);
    
    		String title1 = (String) titleList.get(0).get("title1");
    		String title2 = (String) titleList.get(0).get("title2");
    		LinkedHashMap<String, String> headMap = titleList.get(1);
    
    		/**
    		 * 生成一个(带名称)表格
    		 */
    		SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title1);
    		sheet.createFreezePane(0, 3, 0, 3);// (单独)冻结前三行
    
    		/**
    		 * 生成head相关信息+设置每列宽度
    		 */
    		int[] colWidthArr = new int[headMap.size()];// 列宽数组
    		String[] headKeyArr = new String[headMap.size()];// headKey数组
    		String[] headValArr = new String[headMap.size()];// headVal数组
    		int i = 0;
    		for (Map.Entry<String, String> entry : headMap.entrySet()) {
    			headKeyArr[i] = entry.getKey();
    			headValArr[i] = entry.getValue();
    
    			int bytes = headKeyArr[i].getBytes().length;
    			colWidthArr[i] = bytes < minBytes ? minBytes : bytes;
    			sheet.setColumnWidth(i, colWidthArr[i] * 256);// 设置列宽
    			i++;
    		}
    
    		/**
    		 * 遍历数据集合,产生Excel行数据,除去 title + head 数据起始行为0,赋值为3(即第四行起)
    		 */
    		int rowIndex = 0;
    		for (Object obj : dataArray) {
    			// 生成title+head信息
    			if (rowIndex == 0) {
    				SXSSFRow title1Row = (SXSSFRow) sheet.createRow(0);// title1行
    				title1Row.createCell(0).setCellValue(title1);
    				title1Row.getCell(0).setCellStyle(title1Style);
    				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));// 合并单元格
    
    				SXSSFRow title2Row = (SXSSFRow) sheet.createRow(1);// title2行
    				title2Row.createCell(0).setCellValue(title2);
    
    				CreationHelper createHelper = workbook.getCreationHelper();
    				XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);
    				hyperLink.setAddress(title2);
    				title2Row.getCell(0).setHyperlink(hyperLink);// 添加超链接
    
    				title2Row.getCell(0).setCellStyle(title2Style);
    				sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headMap.size() - 1));// 合并单元格
    
    				SXSSFRow headerRow = (SXSSFRow) sheet.createRow(2);// head行
    				for (int j = 0; j < headValArr.length; j++) {
    					headerRow.createCell(j).setCellValue(headValArr[j]);
    					headerRow.getCell(j).setCellStyle(headerStyle);
    				}
    				rowIndex = 3;
    			}
    
    			JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
    			// 生成数据
    			SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);// 创建行
    			for (int k = 0; k < headKeyArr.length; k++) {
    				SXSSFCell cell = (SXSSFCell) dataRow.createCell(k);// 创建单元格
    				Object o = jo.get(headKeyArr[k]);
    				String cellValue = "";
    
    				if (o == null) {
    					cellValue = "";
    				} else if (o instanceof Date) {
    					cellValue = new SimpleDateFormat(datePattern).format(o);
    				} else if (o instanceof Float || o instanceof Double) {
    					cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
    				} else {
    					cellValue = o.toString();
    				}
    
    				cell.setCellValue(cellValue);
    				cell.setCellStyle(cellStyle);
    			}
    			rowIndex++;
    		}
    
    //      // 另外一种导出方式
    //		HSSFWorkbook workbook = new HSSFWorkbook();
    //		HSSFSheet sheet = workbook.createSheet("测试表");
    //		HSSFRow titleRow = sheet.createRow(0);
    //		sheet.setColumnWidth(titleRow.createCell(0).getColumnIndex(), 256 * 20);
    //		titleRow.createCell(0).setCellValue("名称");
    //		sheet.setColumnWidth(titleRow.createCell(1).getColumnIndex(), 256 * 20);
    //		titleRow.createCell(1).setCellValue("状态(0-已生成,1-待生成)");
    //		// 设置应用类型,以及编码
    //		response.setContentType("application/msexcel;charset=utf-8");
    //		response.setHeader("Content-Disposition", "filename=" + new String("测试表.xls".getBytes("gb2312"), "iso8859-1"));
    //		workbook.write(output);
    //		output.flush();
    //		output.close();
    
    		try {
    			workbook.write(os);
    			os.flush();// 刷新此输出流并强制将所有缓冲的输出字节写出
    			os.close();// 关闭流
    			workbook.dispose();// 释放workbook所占用的所有windows资源
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    
    }
    

    ImportExcelUtil.java (Excel数据转换类)

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.InputStream;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    
    /**
     * Created by cdw on 2018/04/19.
     *
     * 转换类
     *
     * 说明: .xls格式的excel(最大行数65536行,最大列数256列) .xlsx格式的excel(最大行数1048576行,最大列数16384列)
     */
    public class ImportExcelUtil {
    	
    	private final static String Excel_2003 = ".xls"; //2003 版本的excel
    	private final static String Excel_2007 = ".xlsx"; //2007 版本的excel
    
    	/**
    	 * @param in
    	 * @param fileName
    	 *
    	 * @return
    	 */
    	public List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception {
    		List<List<Object>> list = null;
    
    		//创建Excel工作簿
    		Workbook work = this.getWorkbook(in, fileName);
    		if (work == null) {
    			throw new Exception("创建Excel工作簿为空!");
    		}
    		Sheet sheet = null;
    		Row row = null;
    		Cell cell = null;
    		list = new ArrayList<List<Object>>();
    		//遍历Excel中的所有sheet
    		for (int i = 0; i < work.getNumberOfSheets(); i++) {
    			sheet = work.getSheetAt(i);
    			if (sheet == null) {
    				continue;
    			}
    			//遍历当前sheet中的所有行
    			//int totalRow = sheet.getPhysicalNumberOfRows();//如果excel有格式,这种方式取值不准确
    			int totalRow = sheet.getPhysicalNumberOfRows();
    			for (int j = sheet.getFirstRowNum(); j < totalRow; j++) {
    				row = sheet.getRow(j);
    				if (!isRowEmpty(row)) {
    					//if(row != null && !"".equals(row)) {
    					//获取第一个单元格的数据是否存在
    					Cell fristCell = row.getCell(0);
    					if (fristCell != null) {
    						//遍历所有的列
    						List<Object> li = new ArrayList<Object>();
    						//int totalColum = row.getLastCellNum();
    						for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
    							cell = row.getCell(y);
    							String callCal = this.getCellValue(cell) + "";
    							li.add(callCal);
    						}
    						list.add(li);
    					}
    
    				} else if (isRowEmpty(row)) {
    					continue;
    				}
    
    			}
    		}
    		in.close();
    		return list;
    	}
    
    	/**
    	 * 判断行是否为空
    	 *
    	 * @param row
    	 *
    	 * @return
    	 */
    	public static boolean isRowEmpty(Row row) {
    		for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
    			Cell cell = row.getCell(c);
    			if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
    				return false;
    		}
    		return true;
    	}
    
    	/**
    	 * 描述:根据文件后缀,自动适应上传文件的版本
    	 *
    	 * @param inStr,fileName
    	 *
    	 * @return
    	 *
    	 * @throws Exception
    	 */
    	public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
    		Workbook work = null;
    		String fileType = fileName.substring(fileName.lastIndexOf("."));
    		if (Excel_2003.equals(fileType)) {
    			work = new HSSFWorkbook(inStr);//2003 版本的excel
    		} else if (Excel_2007.equals(fileType)) {
    			work = new XSSFWorkbook(inStr);//2007 版本的excel
    		} else {
    			throw new Exception("解析文件格式有误!");
    		}
    		return work;
    	}
    
    	/**
    	 * 描述:对表格中数值进行格式化
    	 *
    	 * @param cell
    	 *
    	 * @return
    	 */
    	public Object getCellValue(Cell cell) {
    		/*Object value = null;
    		DecimalFormat df1 = new DecimalFormat("0.00");//格式化number,string字符
    		SimpleDateFormat sdf = new  SimpleDateFormat("yyy-MM-dd");//日期格式化
    		DecimalFormat df2 = new DecimalFormat("0.00");//格式化数字
    		if(cell !=null && !"".equals(cell)) {
    			switch (cell.getCellType()) {
    			case Cell.CELL_TYPE_STRING:
    				value = cell.getRichStringCellValue().getString();
    				break;
    			case Cell.CELL_TYPE_NUMERIC:
    				if("General".equals(cell.getCellStyle().getDataFormatString())) {
    					value = df1.format(cell.getNumericCellValue());
    				}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
    					value = sdf.format(cell.getDateCellValue());
    				}else if(HSSFDateUtil.isCellDateFormatted(cell)){
    					Date date = cell.getDateCellValue();
    					value = sdf.format(date);				
    				}
    				else {
    					value = df2.format(cell.getNumericCellValue());
    				}
    				break;
    			case Cell.CELL_TYPE_BOOLEAN:
    				value = cell.getBooleanCellValue();
    				break;
    			case Cell.CELL_TYPE_BLANK:
    				value = "";
    				break;
    			default:
    				break;
    			}
    		}		
    		return value;*/
    		String result = new String();  
            switch (cell.getCellType()) {  
            case HSSFCell.CELL_TYPE_FORMULA:  //Excel公式
                try {  
                	result = String.valueOf(cell.getNumericCellValue());  
                } catch (IllegalStateException e) {  
                	result = String.valueOf(cell.getRichStringCellValue());
                }  
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("h:mm")) {  
                        sdf = new SimpleDateFormat("HH:mm");  
                    } else {// 日期  
                        sdf = new SimpleDateFormat("yyyy-MM-dd");  
                    }  
                    Date date = cell.getDateCellValue();  
                    result = sdf.format(date);  
                } else if (cell.getCellStyle().getDataFormat() == 58) {  
                    // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)  
                    SimpleDateFormat sdf = new SimpleDateFormat("M月d日");  
                    double value = cell.getNumericCellValue();  
                    Date date = org.apache.poi.ss.usermodel.DateUtil
                            .getJavaDate(value);  
                    result = sdf.format(date);  
                } else {  
                    double value = cell.getNumericCellValue();  
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();  
                    // 单元格设置成常规  
                    if (temp.equals("General")) {  
                        format.applyPattern("#.##");  
                    }  
                    result = format.format(value);  
                }  
                break;  
            case HSSFCell.CELL_TYPE_STRING:// String类型  
                result = cell.getRichStringCellValue().toString();  
                break;  
            case HSSFCell.CELL_TYPE_BLANK:  
                result = "";  
            default:  
                result = "";  
                break;  
            }  
            return result;  
    	}
    	
    	public String getFormat(String str) {
    		if(str.equals("null")) {
    			str="";
    			return str;
    		}else{
    			return str;
    		}	
    	}
    	public Integer getFormats(Integer str) {
    		if(str==null) {
    			str=0;
    			return str;
    		}else{
    			return str;
    		}	
    	}
    
    	/**
    	 * 获取字符串中的数字订单号、数字金额等,如从"USD 374.69"中获取到374.69、从“交易单号:123456789”获取到123456789
    	 *
    	 * @return
    	 */
    	public static String getFormatNumber(String str){
    		str = str.trim();
    		 Pattern p = Pattern.compile("[0-9]");
    		 int indexNum = 0;
    		 int lenght = str.length();
    		 String num = "";
    		 for(int i=0;i<lenght;i++){
    			num += str.charAt(i);
    		 	Matcher m = p.matcher(num);
    		 	if(m.find()){
    		 		indexNum = i;
    		 		break;
    		 	}
    		  }
    		 String formatNumber = str.substring(indexNum,lenght);
    		 return formatNumber;
    	}
    }
    

    导出功能引用方法:

    // 导出
    @ResponseBody
    @RequestMapping(value = "/excelOut", method = RequestMethod.GET)
    public String excelOut(HttpServletRequest request, HttpServletResponse response) {
        try {
            // 获得输出流
            OutputStream output = response.getOutputStream();
    
            ArrayList<LinkedHashMap> titleList = new ArrayList<LinkedHashMap>();
            LinkedHashMap<String, String> titleMap = new LinkedHashMap<String, String>();
            // title1设置标题,key固定
            titleMap.put("title1", "测试导出表");
            titleMap.put("title2", "测试导出表链接");
    
            LinkedHashMap<String, String> headMap = new LinkedHashMap<String, String>();
            headMap.put("ranking", "序号");
            headMap.put("posterName", "名称");
            headMap.put("status", "状态");
            titleList.add(titleMap);
            titleList.add(headMap);
    
            // 数据集合,下面的字段名必须和上面的map对象key或者数据实体类参数保持一致
            List<JSONObject> objects = new ArrayList<JSONObject>();
            for (int i = 0; i < 20; i++) {
                JSONObject result = new JSONObject();
                result.put("ranking", "value" + i);
                result.put("posterName", "value" + i);
                result.put("status", "value" + i);
                objects.add(result);
            }
            JSONArray objectsList = JSONArray.parseArray(objects.toString());
            // 设置应用类型,以及编码
            response.setContentType("application/msexcel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "filename=" + new String("测试导出表.xlsx/测试导出表.xls".getBytes("gb2312"), "iso8859-1"));
            ExcelUtil.exportExcel(titleList, objectsList, output);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "success";
    }
    

    导入功能引用方法:

    // 导入
    @ResponseBody
    @RequestMapping(value = "/excelIn", method = RequestMethod.POST)
    public String excelIn(@RequestParam(value = "file", required = false) MultipartFile file,
                          HttpServletResponse response) {
        try {
            List<List<Object>> lists = ExcelUtil.importExcel(file);
            System.out.println("导入结果:" + lists.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "success";
    }
    

    导入功能前台引用文件:

    文件引用
    jquery.min.js
    jquery.form.js
    

    导入功能前台处理方法:

    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <!DOCTYPE html>
    <head>
        <title>导入导出-导入页面</title>
        <script src="${pageContext.request.contextPath}/js/jquery.min.js?v=2.1.4"></script>
        <script src="${pageContext.request.contextPath}/js/jquery.form.js?v=2.1.4"></script>
        <script src="${pageContext.request.contextPath}/js/bootstrap.min.js?v=3.3.6"></script>
    </head>
    <body>
    <!--导入-->
    <form action="/user/excelIn" enctype="multipart/form-data" method="post" id="fileForm" name="fileForm">
        <input type="file" id="file" name="file" accept="application/vnd.ms-excel" onchange="chooseFile(this.value)">
        <input type="button" value="选择文件" class="filebtn" id="filebtn"/>
        <input type="text" id="filetxt" class="filetxt" readonly>
        <input type="submit" value="上传Excel" id="importbtn" class="importbtn"/>
    </form>
    <script>
        function exportExcel() {
            // 导出接口地址
            window.location.href = "http://127.0.0.1:8081/user/excelIn";
        }
    
        //触发file change事件
        function chooseFile(path) {
            if (path) {
                $('#filetxt').val(path);
            } else {
                $('#filetxt').attr('readonly', true);
            }
        }
    
        $(function () {
            $("#fileForm").ajaxForm({
                beforeSubmit: function () {
                    // 表单数据提交之前的操作
                },
                success: function (obj) {
                    console.log("导入结果:" + obj);
                },
                error: function (msg) {
                    console.log("导入错误:" + msg);
                }
            });
        });
    </script>
    </body>
    </html>
    

    示例:

    导入结果:[[测试导出表], [测试导出表链接], [序号, 名称, 状态], [value0, value0, value0], [value1, value1, value1], [value2, value2, value2], [value3, value3, value3], [value4, value4, value4], [value5, value5, value5], [value6, value6, value6], [value7, value7, value7], [value8, value8, value8], [value9, value9, value9], [value10, value10, value10], [value11, value11, value11], [value12, value12, value12], [value13, value13, value13], [value14, value14, value14], [value15, value15, value15], [value16, value16, value16], [value17, value17, value17], [value18, value18, value18], [value19, value19, value19]]

    导出结果:
    导出结果展示

    以上就是完整的导入导出功能,支持大数据量快速导出,导入.

    转载请注明出处!

    展开全文
  • 3.8版本的POI对excel的导出操作,一般只使用HSSFWorkbook以及SXSSFWorkbook,HSSFWorkbook用来处理较少的数据量,SXSSFWorkbook用来处理大数据量以及超大数据量导出。 3.8版本的POI新出来了SXSSFWorkbook,可以...
  • 说明: 前面我们介绍了使用xls或xlsx模板导出excel数据。但是当数据量比较时,这样的方式就会特别慢。导出2万条(每条数据占... POI3.8之前的版本不支持大数据量处理,如果数据过多则经常报OOM错误,有时候调整JVM...
  • java poi导出大量数据到Excel
  • 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
  • 1. 大数据量的导入 当Excel中的数据量超过10万行时,在用POI读取文件流时很容易引起失败,需要引入xlsx-streamer来进行资源的打开,剩下的处理同POI处理上百行数据量类似:filePath=>FileInputStream=>...
  • 使用POI导出大数据量到EXCEL
  • 总体的实现思想为:每次查询出2w数据,并写入到临时文件然后把这些文件写入到一个EXCEL里边,或者把这些文件压缩为zip文件,然后把Zip文件提供给下载(这里使用zip打包是因为,在Linux上也能进行Zip打包)。...
  • 简单记录一下导出几百万数据的过程, 导出的话可以,但是你要一次性查出百万数据估计肯定会内存溢出,所以处理查询的需要自己去写 我们的业务需求是, ...由于我筛选以后数据量并不大,最大的差不多...
  • Excel2003版最大行数是65536行。Excel2007开始的版本最大行数是...poi导出excel,不使用模板的 http://happyqing.iteye.com/blog/2075985   xls格式导出使用HSSFWorkbook,(这个暂时没有好办法)   xlsx格式...
  • POI之SXSSFWorkbook大量数据导出至excel POI之SXSSFWorkbook大量数据导出至excel ....
  • 之前使用的是poi的HSSFWorkbook,发现在导出5万多数据量的时候,jvm就不行了。非常慢,还报内存溢出。 解决办法:在配置tomcat的时候增加下面这句 -Xms128m -Xms1024m -XX:MaxPermSize=512m 方法可行,但是当数据量...
  • 之前遇到一个需求,将数据以excel文件的形式导出。首先想到的是用poi进行导出。 了解poi的都知道,poi有两个类,HSSFWorkbook用来导出.lsx的excel文件,也就是excel2003以前的版本,XSSFWorkbook用来导出.xlsx的...
  • poi导入、导出,支持百万级数据模板导出、合并excel。...但是大数据量导出性能太低,自己看着用。 注意此版本不支持分页导出,一次性导出大批量数据也会出现内存溢出问题,最新上传的版本支持分页导出
1 2 3 4 5 ... 20
收藏数 4,040
精华内容 1,616