poi 导出大数据量数据到excel. 百万数据

最新推荐文章于 2024-07-22 19:11:06 发布

一起喝芬达2010

最新推荐文章于 2024-07-22 19:11:06 发布

阅读量1.9k

点赞数

分类专栏： java 文章标签：大数据 poi java excel

本文链接：https://blog.csdn.net/UncleTian/article/details/78916807

版权

java 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

	public void print() throws IOException{
		Long startTime = System.currentTimeMillis();
		HpaDAO oDao = (HpaDAO) this.getDao("daoHpa");
		List<Hpa> dataList = oDao.find("from Hpa o");
		Workbook wb = new SXSSFWorkbook(1000); //重点在这句 .每1000条写一个临时文件
		Sheet sheet = wb.createSheet("我的第一个工作簿");
		Row nRow =null;
		Cell nCell = null;
		int rowNo = 0;
		short colNo = 0;
		for(Hpa hpa : dataList){
			colNo = 0;
			nRow = sheet.createRow(rowNo++);
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getId());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getBreast());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getAdipocytes());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getNegative());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getStaining());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getSupportive());
			if(rowNo%1000==0){
				System.out.println(rowNo);
			}
		}


		//生成excel文件
		ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();//生成流对象
		wb.write(byteArrayOutputStream);
		
		//工具类 , 封装弹出下载框:
		String outFile = "bigdata.xls";
		DownloadBase downloadBase = new DownloadBase();
		HttpServletResponse response = ServletActionContext.getResponse();
		downloadBase.download(byteArrayOutputStream, response, outFile);
		
		
		Long stopTime = System.currentTimeMillis();
		System.out.println((stopTime-startTime)+"ms");

	}

Workbook wb = new SXSSFWorkbook(1000); //重点在这句 .每1000条写一个临时文件
		Sheet sheet = wb.createSheet("我的第一个工作簿");
		Row nRow =null;
		Cell nCell = null;
		int rowNo = 0;
		short colNo = 0;
		for(Hpa hpa : dataList){
			colNo = 0;
			nRow = sheet.createRow(rowNo++);
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getId());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getBreast());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getAdipocytes());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getNegative());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getStaining());
			
			nCell = nRow.createCell(colNo++);
			nCell.setCellValue(hpa.getSupportive());
			if(rowNo%1000==0){
				System.out.println(rowNo);
			}
		}


		//生成excel文件
		ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();//生成流对象
		wb.write(byteArrayOutputStream);
		
		//工具类 , 封装弹出下载框:
		String outFile = "bigdata.xls";
		DownloadBase downloadBase = new DownloadBase();
		HttpServletResponse response = ServletActionContext.getResponse();
		downloadBase.download(byteArrayOutputStream, response, outFile);
		
		
		Long stopTime = System.currentTimeMillis();
		System.out.println((stopTime-startTime)+"ms");

	}

本人实测104万的数据 用时18秒到 excel

注意点:

Workbook wb = new SXSSFWorkbook(1000); //重点在这句 .每1000条写一个临时文件

导入jar包：


核心包：
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar


依赖包：
commons-beanutils-1.7.0.jar
commons-collections-3.0.jar
commons-lang-2.0.jar
commons-logging-1.0.4.jar
dom4j-1.6.1.jar
standard-1.0.2.jar
stax-api-1.0.1.jar
xmlbeans-2.3.0.jar
mysql-connector-java-5.1.10-bin.jar
junit-4.8.2.jar