poi大数据_poi大数据导出excel - CSDN
精华内容
参与话题
  • java中使用poi导出Excel大批量数据 存在两个导出方法:存在一个分批量导出ZIP文件,一个导出exel文件
  • poi导入100万大数据

    千次阅读 2017-07-06 10:46:39
    poi导入100万大数据,前提你的jvm调过有 调大堆的大小。
    package com.jk.controller.user;
    
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.List;
    
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.ss.formula.functions.T;
    import org.apache.poi.xssf.eventusermodel.XSSFReader;
    import org.apache.poi.xssf.model.SharedStringsTable;
    import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    import org.apache.tomcat.jni.User;
    import org.xml.sax.Attributes;
    import org.xml.sax.InputSource;
    import org.xml.sax.SAXException;
    import org.xml.sax.XMLReader;
    import org.xml.sax.helpers.DefaultHandler;
    import org.xml.sax.helpers.XMLReaderFactory;
    
    import com.jk.model.user.UserBean;
    import com.jk.service.user.UserService;
    
    public class ReadExcel extends DefaultHandler {
    	private SharedStringsTable sst;
    	private String lastContents;
    	private boolean nextIsString;
    	private int sheetIndex = -1;
    	private List<String> rowlist = new ArrayList<String>();
    	private int curRow = 0;
    	private int curCol = 0;
    	public List<UserBean> dataList = new ArrayList<UserBean>();
    	private UserService userService;
    	public ReadExcel(UserService userService) {
    		super();
    		this.userService = userService;
    	}
        /**  
         * 读取所有工作簿的入口方法  
         * @param path  
         * @throws Exception 
         */  
        public void process(InputStream inputStream) throws Exception {   
            OPCPackage pkg = OPCPackage.open(inputStream);
            XSSFReader r = new XSSFReader(pkg);   
            SharedStringsTable sst = r.getSharedStringsTable();   
            XMLReader parser = fetchSheetParser(sst);   
            Iterator<InputStream> sheets = r.getSheetsData();   
            while (sheets.hasNext()) {   
                curRow = 0;   
                sheetIndex++;   
                InputStream sheet = sheets.next();
                InputSource sheetSource = new InputSource(sheet);   
                parser.parse(sheetSource);   
                sheet.close();   
            }   
        }   
        /**  
         * 该方法自动被调用,每读一行调用一次,在方法中写自己的业务逻辑即可 
         * @param sheetIndex 工作簿序号 
         * @param curRow 处理到第几行 
         * @param rowList 当前数据行的数据集合 
         */  
        public void optRow(int sheetIndex, int curRow, List<String> rowList) {   
        	if (curRow >=1) {
        		if (dataList.size() >= 500 || rowlist.size() == 0) {
        			userService.addUserBatch(dataList);
        			dataList.clear();
        		}
        		if (rowlist.size() > 0) {
        			UserBean userBean = new UserBean();
            		userBean.setName(rowList.get(0));
            		userBean.setSex(rowList.get(1).trim().equals("男") ? 1:0);
            		userBean.setRegisTime(rowList.get(2));
            		userBean.setLoginNumer(rowList.get(3));
            		userBean.setPassword(rowList.get(4));
            		userBean.setHeadImg(rowList.get(5));
            		String status = rowList.get(6);
            		if (status.trim().equals("正常")) {
            			userBean.setStatus(1);
            		}else if(status.trim().equals("冻结")){
            			userBean.setStatus(2);
            		}else if(status.trim().equals("账户异常")){
            			userBean.setStatus(3);
            		}else if(status.trim().equals("锁定")){
            			userBean.setStatus(4);
            		}
            		userBean.setRemark(rowList.get(7));
            		dataList.add(userBean);
    			}
    		}
        }   
           
        public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {   
            XMLReader parser = XMLReaderFactory   
                    .createXMLReader("org.apache.xerces.parsers.SAXParser");   
            this.sst = sst;   
            parser.setContentHandler(this);   
            return parser;   
        }   
           
        public void startElement(String uri, String localName, String name,   
                Attributes attributes) throws SAXException {   
            // c => 单元格  
            if (name.equals("c")) {   
                // 如果下一个元素是 SST 的索引,则将nextIsString标记为true  
                String cellType = attributes.getValue("t");   
                if (cellType != null && cellType.equals("s")) {   
                    nextIsString = true;   
                } else {   
                    nextIsString = false;   
                }   
            }   
            // 置空   
            lastContents = "";   
        }   
           
        public void endElement(String uri, String localName, String name)   
                throws SAXException { 
        	if (localName.equals("worksheet")) {
        		rowlist.clear();
        		optRow(sheetIndex, 1, rowlist);
        		curRow = 0;
    		}
            // 根据SST的索引值的到单元格的真正要存储的字符串  
            // 这时characters()方法可能会被调用多次  
            if (nextIsString) {   
                try {   
                    int idx = Integer.parseInt(lastContents);   
                    lastContents = new XSSFRichTextString(sst.getEntryAt(idx))   
                            .toString();   
                } catch (Exception e) {   
      
                }   
            }   
            // v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引  
            // 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符  
            if (name.equals("v")) {   
                String value = lastContents.trim();   
                value = value.equals("") ? " " : value;   
                rowlist.add(curCol, value);   
                curCol++;   
            } else {   
                // 如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法  
                if (name.equals("row")) {   
                    optRow(sheetIndex, curRow, rowlist);   
                    rowlist.clear();   
                    curRow++;   
                    curCol = 0;   
                }   
            }   
        }   
        public void characters(char[] ch, int start, int length)   
                throws SAXException {   
            // 得到单元格内容的值  
            lastContents += new String(ch, start, length);   
        }
    }
    

    展开全文
  • POI百万级大数据量EXCEL导出

    万次阅读 多人点赞 2019-06-21 17:43:29
    一. 简介 excel导出,如果数据量在百万级,会出现俩点内存溢出的问题: 1.... 该问题可以通过分批查询来解决; 2....该方式可以通过新版的SXSSFWorkbook来解决,可通过其构造函数执指定在内存中缓存的行数,剩余的会...

    一. 简介

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

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

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

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

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

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

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

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

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

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

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

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

    二. 工具代码

    2.1 配置

    2.1.1 pom.xml

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

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

              别的依赖我就不加了。

    2.1.2 application.yml

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

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

    2.2 ExcelConstant

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

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

    2.3 写数据委托类

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

    2.4 DateUtil工具类(非必须)

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

    2.5 POI工具类

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

    三. 使用DEMO

    3.1 Controller

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

    3.2 Service

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

    3.3 ServiceImpl

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

    3.4 mapper

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

    3.5 mapper.xml

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

    4. 测试

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

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

     

     

    展开全文
  • poi导出大数据 1000万

    2017-11-20 19:13:38
    @RequestMapping("exportUserToExcel.do")   public void exportUserToExcel(UserBean userBean,HttpServletRequest request,HttpServletResponse response) throws Exception{   SXSSFWorkbook wb =...
    1.  @RequestMapping("exportUserToExcel.do")  
    2.     public void exportUserToExcel(UserBean userBean,HttpServletRequest request,HttpServletResponse response) throws Exception{  
    3.         SXSSFWorkbook wb = new SXSSFWorkbook(5000);//内存中保留 10000 条数据,以免内存溢出,其余写入 硬盘       
    4.         int count = userService.getUserExcelCount(userBean);  
    5.         int page = (int) Math.ceil(count / 500000)+1;  
    6.         for (int i = 1; i <= page; i++) {  
    7.             System.out.println(i);  
    8.             userBean.setPage(i);  
    9.             userBean.setRows(500000);  
    10.             userBean.calculate();  
    11.             List<UserBean> userList = userService.getUserExcelList(userBean);  
    12.             Sheet sheet = wb.createSheet("sheet"+i);   
    13.             Row row = sheet.createRow(0);  
    14.             Cell cell = row.createCell(0);  
    15.             cell.setCellValue("id");  
    16.             cell = row.createCell(1);  
    17.             cell.setCellValue("员工姓名");  
    18.             cell = row.createCell(2);  
    19.             cell.setCellValue("员工性别");  
    20.             cell = row.createCell(3);  
    21.             cell.setCellValue("开号日期");  
    22.             cell = row.createCell(4);  
    23.             cell.setCellValue("账号");  
    24.             cell = row.createCell(5);  
    25.             cell.setCellValue("密码");  
    26.             cell = row.createCell(6);  
    27.             cell.setCellValue("头像");  
    28.             cell = row.createCell(7);  
    29.             cell.setCellValue("状态");  
    30.             cell = row.createCell(8);  
    31.             cell.setCellValue("备注");  
    32.             for (int j = 0; j < userList.size(); j++) {  
    33.                 row = sheet.createRow(j+1);  
    34.                 row.createCell(0).setCellValue(userList.get(i).getId());  
    35.                 row.createCell(1).setCellValue(userList.get(j).getName());  
    36.                 row.createCell(2).setCellValue(userList.get(j).getSex() == 1 ? "男":"女");  
    37.                 row.createCell(3).setCellValue(userList.get(j).getRegisTime());  
    38.                 row.createCell(4).setCellValue(userList.get(j).getLoginNumer());  
    39.                 row.createCell(5).setCellValue(userList.get(j).getPassword());  
    40.                 row.createCell(6).setCellValue(userList.get(j).getHeadImg());  
    41.                 row.createCell(7).setCellValue(userList.get(j).getStatusName());  
    42.                 row.createCell(8).setCellValue(userList.get(j).getRemark());  
    43.             }  
    44.         }  
    45.         FileOutputStream fout = new FileOutputStream("G:\\students.xlsx");    
    46.         wb.write(fout);  
    47.         System.out.println("完成");  
    48.         fout.flush();  
    49.         fout.close();  
    50.         wb.dispose();  
    51.   
    52.         FileUtil.downloadFile(request, response,"G:\\students.xlsx","测试.xlsx");  
    53.         File file = new File("G:\\students.xlsx");  
    54.         file.delete();  
    55.     }  
    展开全文
  • 使用Poi读取大数据量excel的方法

    千次下载 热门讨论 2020-07-16 23:35:44
    使用Poi读取大数据量excel的方法 支持2003和2007的版本
  • Java用POI实现读取大数据量Excel

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

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

    1、工具类

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

    2、调用

    File tempFile = new File(this.getClass().getClassLoader().getResource("").getPath() + "tempFile\\" + (new Date()).getTime() + ".xlsx");
    //传入一个路径产生流再将流传入工具类,返回解析对象,Excel的所有数据就被解析到List<String[]> 里面,遍历list任由你处置。
    FileInputStream inputStream = new FileInputStream(tempFile);
    ExcelParser parse = excelParser.parse(inputStream);
    List<String[]> datas = parse.getDatas();
    展开全文
  • POI处理大数据导入导出

    千次阅读 2019-04-10 13:47:25
    Java POI处理大量数据导入导出xls和xlsx导入数据(大量)导出数据(大量)总结 xls和xlsx xls是旧版Excel格式文件,xlsx是新版Excel格式文件;而xlsx新版格式其实是一系列文件压缩包, 如图: xls是以二进制的方式...
  • POI 提供了 好几种生成Excel的方式,查看官方的API可以发现 第一种:HSSFWorkbook  针对是 EXCEL2003 版本,扩展名为 .xls;所以 此种的局限就是 导出的行数 至多为 65535 行,此种 因为行数不足七万行 所以 一般...
  • 最近公司一个06年统计项目在导出Excel时造成应用...随后在网上查阅了部分资料只是在POI大数据导出API的基础上写的demo示例无任何参考价值…解决内存溢出常用方法就是打开GC日志{Heap before GC invocations=29 (full
  • This one comes up quite a lot, but often the reason isn't what you might initially think. So, the first thing to check is - what's the source of the problem? Your file? Your code? Your environment?...
  • poi大数据导出excel

    2019-08-02 14:49:48
    前提: 由于本人采取的是利用反射的方式,这样的好处,可以兼容,导出所有类型的数据,例如,订单,充值记录等等。 既然是公有的接口,肯定有一定的约束规范。 (1)导出的表头需要自己设置一个动态数组 ...
  • poi 大数据导出测试

    千次阅读 2016-12-17 16:33:19
    可以多次写入excel,避免内存溢出   import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream;...import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.a
  • 哎,最近碰见一个非常让人郁闷的需求,导出数据并且是数十万乃至百万,这就有些蛋疼了,没办法产品最大吗! 做呗于是便考虑同时导出多个Excel表格,说做边做,结果自然是失败了,最后发现在导出第一个Excel的流出的时候,边关...
  • 首先将poi的jar导入到项目中,ssm直接在xml中配置package com.aspire.birp.excel;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;...
  • poi导出大数据excel

    2020-04-29 23:32:58
    使用poi导出大量数据,避免内存溢出问题
  • poi大数据读写excel

    2019-08-20 16:20:05
    一、描述前端时间写了注解方式Excel的读取和写入,它是根据注解完成Excel的操作,虽说支持大数据,但对于超大数据就无能为力了,因为它的读写期间都是将所有数据放入系统内存的,除非你有超大的内存。因项目需要对...
  • SXSSFWorkbook 需要poi-ooxml包3.8及以上开始支持,我这边适使用的是3.9版本,本质是一个XSSFWorkbook类(Excel2007),它使用的方式是采用硬盘空间来大幅降低堆内存的占用,在系统的临时文件夹目录创建一个临时
  • 二、 Excel格式报表生成POI 1、 Excel导入大数据 (一) 、依赖 (二) 、模板编写 (三) 、Handler编写 (四)、SpringUtil (五)、ExcelField (六)、实体类 (七)、ExcelParser (八)、Service层 (九)、...
  • POI大数据Excel生成

    2019-07-06 21:21:02
    package com.hd.erpreport.controller; import java.io.File; import java.io.FileOutputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement;...impor...
  • 1 package com.jeeframe.cms.updata.service.impl; 2 3 import java.io.IOException; 4 import java.io.InputStream; 5 import java.sql.Connection; 6 import java.sql.DriverManager;... ...
1 2 3 4 5 ... 20
收藏数 3,143
精华内容 1,257
关键字:

poi大数据