JXL实现大批量数据循环分页导出Excel研究(三)

原创 2014年07月24日 15:28:01

常见报表开发工具POI和JXL(JExcel)。本文采用JXL实现导出Excle功能。Excle-2003中对每个工作表的数据量大小有限制,行数最大限制为65536行,列数最大限制为256列。一个工作薄(workbook)可以有多个工作表(sheet)。


实现大批量数据导出Excel的方法

  • 导出csv格式的数据
缺点:数据格式不能保证
优点:操作简单
  • 导出多个中间文件,将多个中间文件合并
缺点:一个中间文件只能有一个工作表(sheet),合并文件也要耗时。
优点:只要设定好每次请求数据大小,几乎不存在内存溢出问题。
  • 导出一个文件,循环分页请求数据,写入excel是分多个工作表(sheet)写入
缺点:对于大量数据虽然利用循环分页的形式处理了一次性读取数据引发的内存溢出问题,但是,由于JXL在写入excel是,都是一次性写入的,所以还是回出现内存溢出问题。
优点:能够实现10万条以内数据导出。
  • 导出一个Excel文件的xml形式,另存为.xls文件
缺点:
优点:

实现第3种方法,实现思路:

  1. 创建一个workbook,先设定导出参数,每次从数据库请求的数据条数,每个工作表放置的最大行数,根据查询记录总数 / 每次请求记录数 = 循环分页请求数据次数。
  2. 请求后台得到数据,对数据合法性进行保证,jxl导出是,数据字段不能为null。
  3. 读取数据,创建一个sheet,如果当前的行数大于最大设定行数,则新建一个sheet写入。将数据不断转换为sheet中的单元格label对象。
  4. 请求数据结束,调用workbook写入方法,关闭workbook,关闭输出流。

下面是封装后的类:

package com.xsm.pub.tool;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;

import java.util.Map;


import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 
 * @author 
 * @时间 : 2014-7-24 上午09:23:47  
 * @功能 : TODO 大数据量导出Excel
 */
public class ExportExcelMassData {
	private static final Log log = LogFactory.getLog(ExportExcelMassData.class);

	public int totalSize;//总行数
	public int firstRow = 0; 	// 开始请求数据行号	
	public int getDataTimes;//请求数据次数 totalSize / perPageSize + 1
	
	public int perPageSize = 5000; // 一次请求分页大小
	public int perSheetMaxRows = 10000;	//每个工作表最大行数
	
	public Map mapTitle = null;	//列头
	public Map mapColumn = null;	//列数据
	public List argList = null; //导出数据
	
	//导出流
	private OutputStream toClient;
	// 得到一个工作薄
	private WritableWorkbook workbook;
	// 得到一个工作表
	private WritableSheet lastSheet;
	//总的工作表数目
	private int totalSheets = 0;
	//最后一行行号
	private int lastRow = 0;	
	
	public Map getMapTitle() {
		return mapTitle;
	}

	public void setMapTitle(Map mapTitle) {
		this.mapTitle = mapTitle;
	}

	public Map getMapColumn() {
		return mapColumn;
	}

	public void setMapColumn(Map mapColumn) {
		this.mapColumn = mapColumn;
	}

	public int getTotalSize() {
		return totalSize;
	}

	public void setTotalSize(int totalSize) {
		this.totalSize = totalSize;
	}

	public int getPerPageSize() {
		return perPageSize;
	}

	public void setPerPageSize(int perPageSize) {
		this.perPageSize = perPageSize;
	}

	public int getPerSheetMaxRows() {
		return perSheetMaxRows;
	}

	public void setPerSheetMaxRows(int perSheetMaxRows) {
		this.perSheetMaxRows = perSheetMaxRows;
	}

	public void init(OutputStream os) throws IOException {
		// 查询次数
		getDataTimes = totalSize / perPageSize + 1;
/*		//计算工作表数目
		totalSheets = totalSize / perSheetMaxRows + 1;*/
		
		toClient = new BufferedOutputStream(os);
		// 得到一个工作薄
		workbook = Workbook.createWorkbook(toClient);
		
		if (log.isDebugEnabled()) {	log.debug("JXL版本:"+Workbook.getVersion());}
		WorkbookSettings ws = new WorkbookSettings();
		ws.setLocale(new Locale("en", "EN"));
	}
	/**
	 * 
	 * @创建人: 
	 * @时间 : 2014-7-24 上午10:18:08  
	 * @功能 : TODO 数据合法性
	 * @param list
	 * @return
	 */
	private List<Map> checkDatas(List<Map> list) {
		List<Map> rtnlist = new ArrayList<Map>();;
		Map map = null;
		Map temp = null;
		String key = null;
		String value = null; // 值
		while (list.size() > 0) {
			map = (Map) list.get(0);
			temp = new HashMap();
			Iterator it = mapTitle.keySet().iterator();
			while (it.hasNext()) {
				key = (String) it.next();
				value = map.get(key) == null ? "" : map.get(key).toString();					
				temp.put(key, value);				
			}
			rtnlist.add(temp);
			temp = null;
			list.remove(0);
			
		}

		return rtnlist;
	}
	
	/**
	 * 
	 * @创建人: yinxm
	 * @时间 : 2014-7-24 上午10:19:45  
	 * @功能 : TODO 写入excel
	 * @param datas
	 * @throws RowsExceededException
	 * @throws WriteException
	 * @throws IOException
	 */
	public void writeExcel(List datas) throws RowsExceededException, WriteException, IOException {
		
		List<Map> list = checkDatas(datas);
		
		int length = list.size();
		Map map = null;
		String key = null;
		String value = null; // 值
		Label label = null; // 行
		int col = 0; // 列

		for (int i = 0; i < length; i++) {
			if (lastRow >= perSheetMaxRows || lastRow == 0) {// 创建新工作表:当前行数超过本表限制条数,或者初始开始数据
				if (log.isDebugEnabled()) {	log.debug("创建工作表: " + (totalSheets+1));}		
				lastSheet = workbook.createSheet(("工作表" + (totalSheets+1)),totalSheets);
				// 行数归1,0行为标题头
				lastRow = 0;
				// 为新工作表加列标题头
				Iterator it = mapTitle.keySet().iterator();
				while (it.hasNext()) {
					key = (String) it.next();
					value = new String((String) mapTitle.get(key));
					col = Integer.valueOf((String) mapColumn.get(key))
							.intValue();
					label = new Label(col, lastRow, value);
					lastSheet.addCell(label);
				}
				// 工作表数 增加
				totalSheets = totalSheets + 1;
				lastRow++;
			}

			map = (Map) list.get(i);
			Iterator it = map.keySet().iterator();
			while (it.hasNext()) {
				key = (String) it.next();
				// log.debug("key="+key);
				value = new String(map.get(key).toString());
				// log.debug("mapColumn.get(key)="+mapColumn.get(key));
				col = Integer.valueOf((String) mapColumn.get(key)).intValue();
				label = new Label(col, lastRow, value);
				lastSheet.addCell(label);
			}
			lastRow++;
		}


		// 下一次分页请求开始行号位置
		firstRow = firstRow + length;
//		if (log.isDebugEnabled()) {	log.debug("循环一次,下一次行号:"+firstRow);}
		toClient.flush();

	}

	public void closed() throws IOException, WriteException {
		
		// 关闭流
		if  (toClient != null) {
			toClient.flush();
			toClient.close();
		}
		if (workbook != null) {
			workbook.write();
			workbook.close();
		}
		
		
	}
}


测试方法:

package org.test.pub.tool;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.test.pub.LogOut;

import jxl.write.WriteException;

public class ExportExcelMassDataMainTest {
	

	/**  
	 * @创建人: 
	 * @时间 : 2014-7-22 下午04:29:15  
	 * @功能 : TODO 
	 * @param args    
	 * @throws IOException 
	 * @throws WriteException 
	 */
	public static void main(String[] args) {
		long start = System.currentTimeMillis();
		ExportExcelMassData tool = null;
		try {
		tool = new ExportExcelMassData();
	
		// 执行查询操作		
		List list = new ArrayList();
		
		OutputStream os = new FileOutputStream("test-export.xls");
		
		//excel表头
		List<Map> argList = new ArrayList<Map>();
		Map<String, String> title = new HashMap<String, String>();
		title.put("COL_NUM_ONE", "第一列");
		title.put("COL_NUM_TWO", "第二列");
		title.put("COL_NUM_THREE", "第三列");
		title.put("COL_NUM_FOUR", "第四列");
		title.put("COL_NUM_FIVE", "第五列");
		argList.add(title);
		//组织Excel,各列数据,一个for循环一行
		Map<String, String> seqmap = new HashMap<String, String>();
		seqmap.put("COL_NUM_ONE", "0");
		seqmap.put("COL_NUM_TWO", "1");
		seqmap.put("COL_NUM_THREE", "2");
		seqmap.put("COL_NUM_FOUR", "3");
		seqmap.put("COL_NUM_FIVE", "4");

		
		tool.setTotalSize(80000);//总数8万条,每次请求5000条数据,每个工作表最多1万行
		tool.setPerPageSize(5000);
		tool.setPerSheetMaxRows(10000);
		tool.init(os);
		tool.setMapTitle(title);
		tool.setMapColumn(seqmap);
		System.out.println("请求次数:"+(tool.getDataTimes));
		for (int k=0; k<tool.getDataTimes; k++) {
			System.out.println("请求数据:"+(k+1)+"\t开始行号:"+tool.firstRow);

			//自己模拟获取后台数据,实际应用时,将这段代码换成调用后台分页查询接口获取数据
			for (int i=tool.firstRow; (i<(tool.firstRow+tool.perPageSize) && i<8000); i++) {
				Map map = new HashMap();
				map.put("COL_NUM_ONE",i);
				map.put("COL_NUM_TWO", i+1);
				map.put("COL_NUM_THREE", i+2);
				map.put("COL_NUM_FOUR", new Integer(23));
				map.put("COL_NUM_FIVE", new BigDecimal(20));
				list.add(map);
			}
			
			//传入一次数据
			tool.writeExcel(list);
			
			list.clear();
			list = new ArrayList();
		}

		
		}catch (Exception e) {
			System.out.println(e.toString());
			LogOut.logError(e,true);

		} finally {
			try {
				tool.closed();
			} catch (WriteException e) {
				System.out.println(e.toString());
				LogOut.logError(e,true);
			} catch (IOException e) {
				System.out.println(e.toString());
				LogOut.logError(e,true);

			}
		}
		System.out.println("耗时:"+(System.currentTimeMillis()-start));
	}
}


结果:

本地测试,导出8万条以内数据能够导出,耗时1710毫秒。





超过8万条数据报内存溢出错误:

java.lang.OutOfMemoryError: Java heap space
at jxl.write.biff.File.write(File.java:136)
at jxl.write.biff.RowRecord.writeCells(RowRecord.java:296)
at jxl.write.biff.SheetWriter.write(SheetWriter.java:410)
at jxl.write.biff.WritableSheetImpl.write(WritableSheetImpl.java:1165)
at jxl.write.biff.WritableWorkbookImpl.write(WritableWorkbookImpl.java:805)
at org.test.pub.tool.ExportExcelMassData.closed(ExportExcelMassData.java:198)
at org.test.pub.tool.ExportExcelMassDataMainTest.main(ExportExcelMassDataMainTe
st.java:91)
Exception in thread "main" java.lang.RuntimeException: java.lang.OutOfMemoryErro
r: Java heap space
at jxl.write.biff.File.write(File.java:147)
at jxl.write.biff.RowRecord.writeCells(RowRecord.java:296)
at jxl.write.biff.SheetWriter.write(SheetWriter.java:410)
at jxl.write.biff.WritableSheetImpl.write(WritableSheetImpl.java:1165)
at jxl.write.biff.WritableWorkbookImpl.write(WritableWorkbookImpl.java:805)
at org.test.pub.tool.ExportExcelMassData.closed(ExportExcelMassData.java:198)
at org.test.pub.tool.ExportExcelMassDataMainTest.main(ExportExcelMassDataMainTe
st.java:91)
Caused by: java.lang.OutOfMemoryError: Java heap space
at jxl.write.biff.File.write(File.java:136)
... 6 more





Jxls导出excel的若干方式总结(十四)-- 动态设置分页符

因为在导出excel表格的时候,存在一条记录占用多行,并且一次导出若干条记录。为了用户打印方便,所以要针对性对这些记录进行分页设置。以下示例,模板设计时一条记录占用7行,实测6条记录占用一页,故代码中...

利用JXLS根据模板导出Excel实例教程

看了几天了,感觉迷迷糊糊的,今天终于搞出来了。 先做模板,做成想要的格式样子保存,然后通过程序根据模板生成对应样式的Excel文件,代码简单。什么连接数据库查询然后将结果生成Excel文件就不讲...

使用jxls分页导出excel

项目有一个导出excel的功能使用的是2003版的,那2003版的excel单个sheet最多只能导出65535条数据,现在项目有一个导出的功能数据会超过65535条。所以需要导出到多个sheet里面...

java jxl 的打印设置

JXL的打印设置在jxl.SheetSettings这个类中,我们可以通过方法Sheet(或者WritableSheet)#getSettings()获取。   1.页面 1.1方向 Shee...

打印excel图片分页 jxl

通过设置excel的行高来设置每张图片在excel的打印位置,让图片占据一行位置,大小根据自己的需求设置(跨多列),这样就不会在打印时发生一张图片占据两张A4纸的情况                ...

java分页数据导出excel

/** * 订单导出(用于统计利润) * @return */ public String orderExport() throws IOException{ if (queryOr...
  • jspamd
  • jspamd
  • 2015年06月12日 21:44
  • 4098

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

POI生成Excel一个简单的分页例子

POI生成Excel一个简单的分页例子 其实分页很简单,当前页面记录数满足分页条件后,用工作簿对象再创一个sheet。 实现思路如下: 1、定义每页最大的记录数。 2、根据最大记录...

Java操作POI批量导出多个excle

一、前端JS //站内搜索查询报表-Excel导出 function exportRecord(){ var browseUrl=$("#browseUrl").val(); if(brows...

java导出Excel多个工作表(添加多个sheet)

如果数据量比较大(十万以上)建议导成多个excel文件,之后压缩成zip文件进行下载 /生成一个excel文件           WritableWorkbook wwb = null;    ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:JXL实现大批量数据循环分页导出Excel研究(三)
举报原因:
原因补充:

(最多只允许输入30个字)