使用poi导出大量数据到excel遇到的问题

最新推荐文章于 2023-10-31 11:29:46 发布

艺俊

最新推荐文章于 2023-10-31 11:29:46 发布

阅读量2.6k

点赞数 1

CC 4.0 BY-SA版权

本文链接：https://blog.csdn.net/qq_23412263/article/details/51892862

本文介绍了一种解决大量数据导出至Excel时性能瓶颈的方法，通过后台异步处理和前台进度反馈，有效提升了用户体验。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近在工作遇到利用poi导出大量数据到excel并提供下载的运用场景，并遇到了一个问题，当数据量过大时(几十万)，后台在进行数据写入excel中的过程会非常耗时，导致迟迟没有响应前台，结果数据还没导完，前台页面就已经崩掉了。

解决思路：接收到前台导出excel请求之后，开一个线程，在线程里进行数据的写入和将写入完成的excel保存到服务器中等耗时操作，前台定时发送ajax请求检测是否已经导出完成，如果完成则提供一个下载链接到前台供用户下载。

想到解决思路之后，自己写了一个小demo，顺便学习下利用poi导出excel，同时还在定时检测的时候加了一个百分比进度到前台显示已经导出了多少数据。

后台ExcelServlet：

package com.myj.servlet;

import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.myj.user.User;

public class ExcelServlet extends HttpServlet{
	//用户并发访问时，使用map保存相应数据
	//存放写入完成标志
	private static Map<String, Boolean> flagMap = new HashMap<String, Boolean>();
	//存放线程
	private static Map<Long, MyThread> excelHelperMap = new HashMap<Long, MyThread>();;
	@Override
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		request.setCharacterEncoding("UTf-8");
		response.setHeader("Cache-Control","no-cache");  //HTTP 1.1
		response.setHeader("Pragma","no-cache");         //HTTP 1.0
		response.setDateHeader ("Expires", 0); 
		
		String action = request.getParameter("action");
		
		//flagMap.put(ip, false);
		if("outExcel".equals(action)){
			
			String fileName = String.valueOf(new Date().getTime())+".xls";
			System.out.println("fileName:"+fileName);
			flagMap.put(fileName, false);
			System.out.println(flagMap.get(fileName));
			//ServletOutputStream sos = response.getOutputStream();
			String filePath = request.getServletContext().getRealPath("/") + fileName;
			System.out.println(request.getSession().getServletContext().getRealPath("/"));
			System.out.println("filePath:"+filePath);
			//开启线程进行写入操作
			long threadId = beginProcess(request, filePath, response, fileName);
			//将必要的参数写回前台，并在定时检测时提交回来
			response.getWriter().write(fileName+","+threadId);
		//定时检测是否写入完成	
		}else if("checkFin".equals(action)){

			String fileName = request.getParameter("fileName");
			long threadId = Long.valueOf(request.getParameter("threadId"));
			if(flagMap.get(fileName)==null?false:flagMap.get(fileName)){
				response.reset();
				response.getWriter().write(String.valueOf(flagMap.get(fileName)));
				flagMap.put(fileName, false);
			}else{
				//将进度写回前台显示
				response.getWriter().write(procBar(threadId));
			}
			System.out.println(procBar(threadId));
			//response.getWriter().write("");
		//下载excel
		}else if("downloadExcel".equals(action)){
			String fileName = request.getParameter("fileName");
			response.reset();
			response.setContentType("APPLICATION/OCTET-STREAM");   
		    response.setHeader("Content-Disposition", "attachment;filename="+"report.xls");   
		    String filePath = request.getServletContext().getRealPath("/")+fileName;
		    System.out.println(filePath);
		    FileInputStream fis = new FileInputStream(filePath);
		    PrintWriter out = response.getWriter();
		    int i = 0;
		    System.out.println("开始下载excel...");
		    while((i=fis.read())!=-1){
		    	out.write(i);
		    }
		    System.out.println("下载完毕.");
		    fis.close();
		    out.flush();
		    out.close();
		}
		
	}
	//获取测试数据
	public static List<User> getUserList(){
		List<User> userList = new ArrayList<User>();
		
		for(int i=0;i<1000000;i++){
			User user = new User();
			user.setName("user"+i);
			user.setAge(20);
			userList.add(user);
		}
		
		return userList;
		
	}
	//接收请求之后开启一个线程进行将数据写入excel和保存服务器操作
	public long beginProcess(HttpServletRequest request, String filePath, HttpServletResponse response, String fileName) {
		//test case
		MyThread excelHelper = new MyThread(request, filePath, response, fileName);
		excelHelper.start();
		long threadId = excelHelper.getId();
		excelHelperMap.put(threadId, excelHelper);
		return threadId;
	}

	public int getRowIndex(long threadId) {
		MyThread excelHelper = excelHelperMap.get(threadId);
		if ((excelHelper.getRowIndex()-2)<excelHelper.getCount()) {
			return excelHelper.getRowIndex();
		}else {
			return -1;
		}
	}
	//获取百分比进度
	public String procBar(long threadId){
		MyThread excelHelper = excelHelperMap.get(threadId);
		//减去两行才是数据行
		double pro = (double)(excelHelper.getRowIndex()-2)/(double)excelHelper.getCount();
		NumberFormat nf = NumberFormat.getPercentInstance();
		nf.setMinimumFractionDigits(2);
		return nf.format(pro);
	}
	
	/**
	 * 进行将数据写入excel和将写入完成的excel
	 * 保存到服务器等耗时的操作
	 *
	 */
	public class MyThread extends Thread{
		
		private HttpServletRequest request;
		private String filePath;
		private HttpServletResponse response;
		private String fileName;
		private int rownum = 0;
		private int count;
		
		public MyThread(HttpServletRequest request, String filePath, HttpServletResponse response, String fileName){
			this.request = request;
			this.filePath = filePath;
			this.response = response;
			this.fileName = fileName;
			this.count = getUserList().size();
		}
		
		public int getRowIndex() {
			return rownum;
		}

		public int getCount() {
			return count;
		}
		
		@Override
		public void run() {
			
			
			Sheet sheet = null;
			Row row = null;
			Workbook workbook = new SXSSFWorkbook(2000);
			sheet = workbook.createSheet();
			row = sheet.createRow(0);
			row.createCell(5).setCellValue("测试表");
			row = sheet.createRow(1);
			row.createCell(0).setCellValue("姓名");
			row.createCell(1).setCellValue("年龄");
			
			rownum = 2;
			
			System.out.println("开始将数据写入excel...");
			for(User u : getUserList()){
				row = sheet.createRow(rownum);
				
				row.createCell(0).setCellValue(u.getName());
				row.createCell(1).setCellValue(u.getAge());
				rownum++;
			}
			
			System.out.println("写入完毕，开始保存到服务器硬盘...");
			BufferedOutputStream bos = null;
			try {
				bos = new BufferedOutputStream(new FileOutputStream(filePath));
				workbook.write(bos);
				System.out.println("保存完毕。");
				bos.flush();
				
//				workbook = null;
//				workbook = new SXSSFWorkbook(2000);
				System.out.println("线程里的fileName:"+fileName);
				flagMap.put(fileName, true);
				System.out.println("线程里的flag:"+flagMap.get(fileName));
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			}catch (IOException e) {
				e.printStackTrace();
			}finally{
				try {
					bos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		
	}

}

前台testExcel.html：

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>

<script type="text/javascript">
	var fileName = "";
	var threadId = "";
	function out(){
		var xmlhttp;
		if (window.XMLHttpRequest)
		  {// code for IE7+, Firefox, Chrome, Opera, Safari
		  xmlhttp=new XMLHttpRequest();
		  }
		else
		  {// code for IE6, IE5
		  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
		  }
		xmlhttp.onreadystatechange=function()
		  {
		  if (xmlhttp.readyState==4 && xmlhttp.status==200)
		    {
			  data = xmlhttp.responseText;
			  dataArr = data.split(",");
			  fileName = dataArr[0];
			  threadId = dataArr[1];
			  checkFin();
		    }
		  }
		xmlhttp.open("POST","excelServlet",true);
		xmlhttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
		xmlhttp.send("action=outExcel");
	}
	
	function checkFin(){
		loadXMLDoc();
		t = setTimeout("checkFin()", 500);
	}
	
	function stop(){
		clearTimeout(t);
	}
	//定时发送ajax请求，检测后台时候都出完毕
	function loadXMLDoc()
	{
		var xmlhttp;
		if (window.XMLHttpRequest)
		  {// code for IE7+, Firefox, Chrome, Opera, Safari
		  xmlhttp=new XMLHttpRequest();
		  }
		else
		  {// code for IE6, IE5
		  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
		  }
		xmlhttp.onreadystatechange=function()
		  {
		  if (xmlhttp.readyState==4 && xmlhttp.status==200)
		    {
		    var flag = xmlhttp.responseText;
		    if(flag == "true"){	//导出完毕，提供连接进行下载
		    	clearTimeout(t);
		    	var a = document.createElement("a");
		    	a.setAttribute("href","excelServlet?action=downloadExcel&fileName="+fileName);
		    	a.innerText = "下载excel";
		    	document.getElementById("d1").appendChild(a);
		    }else{	//还没导完，显示导了多少数据
		    	document.getElementById("proc").innerText = flag;
		    }
		    
		    }
		  }
		xmlhttp.open("POST","excelServlet?action=checkFin&fileName="+fileName+"&threadId="+threadId,true);
		xmlhttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
		xmlhttp.send(); 
		//window.location.href = "excelServlet?action=checkFin";
	}
</script>

</head>
<body>
	<form action="excelServlet">
		<input type="button" name="submit1" value="输出excel" onclick="out();"/>
		<input type="hidden" name="action" value="outExcel"/>
	</form>
	<div id="d1"></div>
	<h3 id="proc"></h3>
</body>
</html>

根据自己写的demo，修改了原来项目导出excel的代码后，问题解决。