欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

EXCEL大数据量导出的解决方案

程序员文章站 2022-07-13 13:17:43
...

将web页面上显示的报表导出到excel文件里是一种很常见的需求。然而,当数据量较大的情况下,excel本身的支持最多65535行数据的问题便凸显出来。下面就给出大数据量导出到excel的解决方 案。

首先,对于数据超过了65535行的问题,很自然的就会想到将整个数据分块,利用excel的多sheet页的功能,将超出65535行后的数据写入到下一个sheet页中,即通过多sheet页的方式,突破了最高65535行数据的限定。

具体做法就是,单独做一个链接,使用JSP导出,在JSP上通过程序判断报表行数,超过65535行后分SHEET写入。这样这个问题就得以解决了。

更进一步地说,在这种大数据量的报表生成和导出中,要占用大量的内存,尤其是在使用TOMCAT的情况下,JVM最高只能支持到2G内存,则会发生 内存溢出的情况。此时的内存开销主要是两部分,一部分是该报表生成时的开销,另一部分是该报表生成后写入一个EXCEL时的开销。由于JVM的GC机制是 不能强制回收的,因此,对于此种情形,我们给出一个变通的解决方案。

首先,将该报表设置起始行和结束行参数,在API生成报表的过程中,分步计算报表(主要性能花费在查询生成报表中),比如一张20万行数据的报表,在生成过程中,可通过起始行和结束 行分4-5次进行。这样,就降低了报表生成时的内存占用,在后面报表生成的过程中,如果发现内存不够,即可自动启动JVM的GC机制,回收前面报表的缓 存。

导出EXCEL的过程,放在每段生成报表之后立即进行,改多个SHEET页为多个EXCEL,即在分步生成报表的同时分步生成EXCEL,则通过 POI包生成EXCEL的内存消耗也得以降低。通过多次生成,同样可以在后面EXCEL生成所需要的内存不足时,有效回收前面生成EXCEL时占用的内 存。

再使用文件操作,对每个客户端的导出请求在服务器端根据SESSIONID和登陆时间生成唯一的临时目录,用来放置所生成的多个EXCEL,然后调 用系统控制台,打包多个EXCEL为RAR或者JAR方式,最终反馈给用户一个RAR包或者JAR包,响应客户请求后,再次调用控制台删除该临时目录

使用这种方法,首先是通过分段运算和生成,有效降低了报表从生成结果到生成EXCEL的内存开销。其次是通过使用压缩包,响应给用户的生成文件体积 大大缩小,降低了多用户并发访问时服务器下载文件的负担,有效减少多个用户导出下载时服务器端的流量,从而达到进一步减轻服务器负载的效果。

  • 创建系统全局线程池
final int numOfCpuCores = Runtime.getRuntime().availableProcessors();
final double blockingCoefficient = 0.9;// 阻尼系数
final int maximumPoolSize = (int)(numOfCpuCores / (1 - blockingCoefficient));
ExecutorService threadPool = new ThreadPoolExecutor(numOfCpuCores,
					maximumPoolSize, 
					0L, 
					TimeUnit.MILLISECONDS, 
					new LinkedBlockingQueue <Runnable>(),
					Executors.privilegedThreadFactory(), 
					new ThreadPoolExecutor.DiscardOldestPolicy());
复制代码
  • 采用多线程分段查询生成报表,同步生成Excel,最后压缩成Zip文件
// 1.这里每个Excel放6万条数据(分6个sheet页,每个1万条),当数据量超过6万条时,数据采用分段查询
//  传递(起始行,结束行)参数,分段查询,即分步生成报表的同时分步生成EXCEL
int SINGLE_EXCEPORT_EXCEL_MAX_NUM = 60000;
int count = bo.getTotalRecord();
final String fileNameWithTimestamp = fileName + "_" + DateUtil.getNowDateminStr();
if (count > SINGLE_EXCEPORT_EXCEL_MAX_NUM ) {
	int excelCount = count / SINGLE_EXCEPORT_EXCEL_MAX_NUM +
			(count % SINGLE_EXCEPORT_EXCEL_MAX_NUM != 0 ? 1 : 0);
	final CountDownLatch latch = new CountDownLatch(excelCount);
	final Long userId = user.getUserId();
	for(int i = 1; i <= excelCount; i++){
		bo.setPageNo(i);
		bo.setPageSize(SINGLE_EXCEPORT_EXCEL_MAX_NUM);
		final ParkRecordQryBO itemBo = new ParkRecordQryBO(bo);
		final int index = i;
		// 取一线程执行本次查询
		threadPool.execute(new Runnable(){
			@Override
			public void run() {
				Page page = service.getParkRecord(itemBo);
				List<ParkRecordQryBO> records = page.getResults();
				try {
					// 2.生成单个excel
					ExportExcelUtil.createOneExcel(fileNameWithTimestamp, index ,
							expRowsList, records, userId);
				} catch (Exception e) {
					e.printStackTrace();
				}
				latch.countDown();
			}
		});
	}
	// 3.压缩excel文件并导出
	latch.await();
	ExportExcelUtil.createZipExport(request, response, fileNameWithTimestamp, userId);
复制代码
  • 生成一个Excel存放到本地路径
/**
 * @Description: 生成一个Excel存放到本地路径
 * @param fileNameWithTimestamp
 * @param index
 * @param excelHeader
 * @param dataList
 * @param <T>
 * @param userId
 */
public static <T> void createOneExcel(final String fileNameWithTimestamp, 
                                int index,
                            	final String[] excelHeader, 
                            	final List<T> dataList, 
                            	Long userId ) {
	final String localRelativePath = "" + userId + "/"+ fileNameWithTimestamp ;
	Workbook wb = null;
	FileOutputStream fos = null;
	try {
		// 创建一个Workbook,对应一个Excel文件
		wb = writeExcel(dataList, excelHeader);
		// 生成本地Excel初始文件
		Map<String, Object> fileInfo = new HashMap<String, Object>();
		FileUtil.createFile(localRelativePath, fileNameWithTimestamp +
				"_" + index + "_.xls", fileInfo);
		fos = new FileOutputStream(fileInfo.get("realPath").toString() );
		wb.write(fos);
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			if (wb != null)
			    wb.close();
			if (fos != null) 
			    fos.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
复制代码
  • 压缩打包所有Excel文件并导出
/**
 * @param request
 * @param response
 * @param fileNameWithTimestamp
 * @param userId
 */
public static void createZipExport(HttpServletRequest request, 
                                HttpServletResponse response,
                                final String fileNameWithTimestamp, 
                                Long userId) throws Exception{
	final String localRelativePath = "" + userId + "/"+ fileNameWithTimestamp;
	// 创建文件夹,先将生成的excel保存到服务器本地目录
	// excel文件路径:'/app/file/[userId]/[fileNameWithTimestamp]/[fileNameWithTimestamp_i].xlS'
	String excelFold = FileUtil.getFileRootPath() + localRelativePath;
	// zip文件所在路径:"/app/file/userId/fileNameWithTimestamp.zip"
	String zipFold = FileUtil.getFileRootPath() + userId;

	// 生成zip文件
	final String zipFileName = fileNameWithTimestamp +".zip";
	FileUtil.createZipFile(excelFold, zipFold, zipFileName);
	// 创建导出输入流
	InputStream is = null;
	try{
		is = new FileInputStream(new File(zipFold + File.separator + zipFileName));
	} catch(IOException e){
		e.printStackTrace();
	}
	BufferedInputStream bis = new BufferedInputStream(is);
	// ServletOutputStream out = response.getOutputStream();
	BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
	
	// 解决设置名称时的乱码问题
	String zipName = handleFileName(request, zipFileName);
	// 设置response参数,可以打开下载页面
	response.reset();
	response.setContentType("application/vnd.ms-excel;charset=utf-8");
	response.setHeader("Content-Disposition", "attachment;filename=" + zipName);

	byte[] buff = new byte[2048];
	int bytesRead;
	// Simple read/write loop.
	while ((bytesRead = bis.read(buff, 0, buff.length)) != -1 ) {
		bos.write(buff, 0, bytesRead);
	}
	bis.close();
	bos.close();
	// 删除用来临时保存Excel的文件夹及zip文件
	FileUtil.deleteDir(new File(zipFold));
}
复制代码