2011-11-17 14:42:05 hopestar2008 阅读数 694
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1398 人正在学习 去看看 钟林森

说明:我的电脑 2.0CPU 2G内存 能够十秒钟导出 20W 条数据 ,12.8M的excel内容压缩后2.68M

我们知道在POI导出Excel时,数据量大了,很容易导致内存溢出。由于Excel 一个sheet允许的最大行数是65536这时我们想到分sheet进行导出;但是这种情况也不能解决内存溢出的问题。毕竟数据还是一次性在内存中进行保存的。这时我们想是不是可以导出多个excel呢?下面我就尝试着按照导出多个excel

首先:我们要确定数据量有多大,然后确定一个excel导出多少条数据,这样就可以确定导出的Excel的数量,于是我们就可以循环的导出excel并保存在任意的临时目录中。去这样如果内存不够的话虚拟机就会去进行回收已经保存的excel在内存中的空间。

假设我们我们已经成功的生成了多个excel,这时我们怎么把这N个excel文档传到客户端呢?其实一个一个的传也未尝不可,但是考虑那样对用户来说体验不够好,再次多个文件在网络上传输也比较慢。我们可以考虑对生成的几个文件进行压缩,然后传到客户端。

总结一下第一、分批次生成excel第二、压缩后到客户端

 

下面我把我的一个小实例贴上供大家参考

 

第一、Person.java 普通javabean

 

package bean;
/**
 * 
 * @author http://javaflex.iteye.com/
 *
 */
public class Person {

	private Integer id;
	private String name;
	private String address;
	private String tel;
	private Double money=0.0;
	public Double getMoney() {
		return money;
	}
	public void setMoney(Double money) {
		this.money = money;
	}
	public Person(Integer id, String name, String address, String tel,Double money) {
		super();
		this.id = id;
		this.name = name;
		this.address = address;
		this.tel = tel;
		this.money=money;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
}

 

第二、PersonService模拟业务逻辑循环生成100023个Person对象

 

package service;

import java.util.ArrayList;
import java.util.List;

import bean.Person;
/**
 * 
 * @author http://javaflex.iteye.com/
 *
 */
public class PersonService {
	public static List getPerson(){
		List<Person> list =new ArrayList<Person>();
		for(int i=0;i<100320;i++){
			list.add(new Person(i,"zhangsan"+i,"北京"+i,"13214587632",123123.12+i));	
		}
		return list;
	}

}

 

 第三、业务处理Servlet

 

package servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import bean.Person;

import service.PersonService;

/**
 * 
 * @author http://javaflex.iteye.com/
 *
 */
public class PersonServlet extends HttpServlet {
	private String fileName;

	public PersonServlet() {
		super();
	}

	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// 文件名获取
		Date date = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
		String f = "Person-" + format.format(date);
		this.fileName = f;
		setResponseHeader(response);
		OutputStream out = null;
		try {
			out = response.getOutputStream();
			List<Person> list = PersonService.getPerson();
			toExcel(list,request,10000,f,out);
		} catch (IOException e1) {
			e1.printStackTrace();
		} finally {
			try {
				out.flush();
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/** 设置响应头 */
	public void setResponseHeader(HttpServletResponse response) {
		try {
			response.setContentType("application/octet-stream;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ java.net.URLEncoder.encode(this.fileName, "UTF-8")
					+ ".zip");
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		doGet(request, response);
	}
	public void init() throws ServletException {
		// Put your code here
	}

	public void toExcel(List<Person> list, HttpServletRequest request,
			int length, String f, OutputStream out) throws IOException {
		List<String> fileNames = new ArrayList();// 用于存放生成的文件名称s
		File zip = new File(request.getRealPath("/files") + "/" + f + ".zip");// 压缩文件
		// 生成excel
		for (int j = 0, n = list.size() / length + 1; j < n; j++) {
			Workbook book = new HSSFWorkbook();
			Sheet sheet = book.createSheet("person");

			double d = 0;// 用来统计
			String file = request.getRealPath("/files") + "/" + f + "-" + j
					+ ".xls";

			fileNames.add(file);
			FileOutputStream o = null;
			try {
				o = new FileOutputStream(file);

				// sheet.addMergedRegion(new
				// CellRangeAddress(list.size()+1,0,list.size()+5,6));
				Row row = sheet.createRow(0);
				row.createCell(0).setCellValue("ID");
				row.createCell(1).setCellValue("NAME");
				row.createCell(2).setCellValue("ADDRESS");
				row.createCell(3).setCellValue("TEL");
				row.createCell(4).setCellValue("Money");

				int m = 1;

				for (int i = 1, min = (list.size() - j * length + 1) > (length + 1) ? (length + 1)
						: (list.size() - j * length + 1); i < min; i++) {
					m++;
					Person user = list.get(length * (j) + i - 1);
					Double dd = user.getMoney();
					if (dd == null) {
						dd = 0.0;
					}
					d += dd;
					row = sheet.createRow(i);
					row.createCell(0).setCellValue(user.getId());
					row.createCell(1).setCellValue(user.getName());
					row.createCell(2).setCellValue(user.getAddress());
					row.createCell(3).setCellValue(user.getTel());
					row.createCell(4).setCellValue(dd);

				}
				CellStyle cellStyle2 = book.createCellStyle();
				cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);
				row = sheet.createRow(m);
				Cell cell0 = row.createCell(0);
				cell0.setCellValue("Total");
				cell0.setCellStyle(cellStyle2);
				Cell cell4 = row.createCell(4);
				cell4.setCellValue(d);
				cell4.setCellStyle(cellStyle2);
				sheet.addMergedRegion(new CellRangeAddress(m, m, 0, 3));
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				book.write(o);
			} catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				o.flush();
				o.close();
			}
		}
		File srcfile[] = new File[fileNames.size()];
		for (int i = 0, n = fileNames.size(); i < n; i++) {
			srcfile[i] = new File(fileNames.get(i));
		}
		util.FileZip.ZipFiles(srcfile, zip);
		FileInputStream inStream = new FileInputStream(zip);
		byte[] buf = new byte[4096];
		int readLength;
		while (((readLength = inStream.read(buf)) != -1)) {
			out.write(buf, 0, readLength);
		}
		inStream.close();
	}
}

最后还有个工具类package util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
 * 
 * @author http://javaflex.iteye.com/
 *
 */
public class FileZip {
	/**
	 * 
	 * @param srcfile 文件名数组
	 * @param zipfile 压缩后文件
	 */
	public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) {
		byte[] buf = new byte[1024];
		try {
			ZipOutputStream out = new ZipOutputStream(new FileOutputStream(
					zipfile));
			for (int i = 0; i < srcfile.length; i++) {
				FileInputStream in = new FileInputStream(srcfile[i]);
				out.putNextEntry(new ZipEntry(srcfile[i].getName()));
				int len;
				while ((len = in.read(buf)) > 0) {
					out.write(buf, 0, len);
				}
				out.closeEntry();
				in.close();
			}
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

OK全部内容完成



 

12.8M的excel内容压缩后2.68M,给力吧

以后记得代码加注释

 

亲,记得给个评论哦

2012-09-06 11:58:43 kjkhi 阅读数 251
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1398 人正在学习 去看看 钟林森

POI读取Excel文件有两种方式,一种是使用usermodel方式读取,这种方式的优势是统一接口开发,读取.xls文件的HSSFWorkbook与读取.xlsx文件的XSSFWorkbook两个类都实现了Workbook接口。另外一种是eventusermodel方式读取,这种方式比前面一种方式读取复杂很多,并且对与2003版本和2007版本的Excel处理没有统一接口,需要了解Excel的内部文件组织原理,但是效率却比第一种方式快得多,并却能轻松读取大量数据的Excel文件,而不会把内存溢出。本文也是主要介绍使用eventusermodel方式读取2007版本的Excel文件。

eventusermodel其实是使用了XML的文件格式读取Excel的,因为Excel内部组织也是通过XML实现了(可以把后缀名改为.zip)。

xl\worksheets\sheet1.xml  - 第一个sheet的内容

 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"><dimension ref="A1:D1"/>
<sheetViews><sheetView workbookViewId="0"><selection sqref="A1:XFD1"/></sheetView></sheetViews>
<sheetFormatPr defaultRowHeight="13.5" x14ac:dyDescent="0.15"/>
	<sheetData>
		<row r="1" spans="1:4" x14ac:dyDescent="0.15">
			<c r="A1" t="s"><v>0</v></c>
			<c r="B1" t="s"><v>1</v></c>
			<c r="C1" t="s"><v>2</v></c>
			<c r="D1" t="s"><v>15</v></c>
		</row>
	</sheetData>
<phoneticPr fontId="1" type="noConversion"/><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/><pageSetup paperSize="0" orientation="portrait" horizontalDpi="0" verticalDpi="0" copies="0"/></worksheet>

<c />标签表示单元格,t=“s”,说明当前的单元格类型是字符串,那此时<v>0</v>,0则是在sharedStrings.xml的一个索引值。是<si />标签序号。 

 

xl\sharedStrings.xml - Excel文件中字符串的值,如其内容片段

 

<si>
	<t>col1</t><phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
	<t>col2</t><phoneticPr fontId="1" type="noConversion"/>
</si>

 POI的eventusermodel也是通过这样的原理读取Excel文件的。

首先读取Excel文件,取得XSSFReader实例:

 

XSSFReader reader =  new XSSFReader(OPCPackage.open(file));
XMLReader xmlReader = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");

// sharedStrings.xml实体
SharedStringsTable table = reader.getSharedStringsTable();

xmlReader.setContentHandler(new ContentHandler()//实现该接口的一个实例);

InputStream sheet = reader.getSheet("rId"+sheetId);

InputSourcesheetSource  =  new InputSource(sheet )

xmlReader.parse(sheetSource);
 
package net.bingosoft.import4excel.common;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

public class TestContentHandler extends DefaultHandler{

	private SharedStringsTable table;
	
	private boolean isString;
	
	private String value;
	
	private FileWriter writer;
	
	public TestContentHandler(SharedStringsTable table){
		this.table = table;
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#characters(char[], int, int)
	 */
	@Override
	public void characters(char[] ch, int start, int length)
			throws SAXException {
		value = new String(ch,start,length);
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#endDocument()
	 */
	@Override
	public void endDocument() throws SAXException {
		try {
			writer.flush();
			writer.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public void endElement(String uri, String localName, String qName)
			throws SAXException {
		try {
			if(qName.equals("v")){
				if(isString) value = table.getEntryAt(Integer.valueOf(value.trim())).getT();
				writer.write(value+",");
			}
			if(qName.equals("row")){
				writer.write("\r\n");
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#startDocument()
	 */
	@Override
	public void startDocument() throws SAXException {
		try {
			File file = new File("D:/test.txt");
			if(file.exists()) file.delete();
			writer = new FileWriter(file);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
	 */
	@Override
	public void startElement(String uri, String localName, String qName,
			Attributes attributes) throws SAXException {
		if(qName.equals("c")){
			String type = attributes.getValue("t");
			if(StringUtils.isNotBlank(type) && type.equals("s")){
				isString = true;
			}else{
				isString = false;
			}
		}
		value = "";
	}
	
	
}

 

2012-11-25 19:03:36 lyjilu2008 阅读数 421
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1398 人正在学习 去看看 钟林森

分析导出实现代码,XLSX支持:

 

 

/**
	 * 生成XLSX,2007版本的excel,每个sheet无6.5W的行数限制,但是到了一定数量,可能内存溢出,
	 * 次方法适合在预计10W以下的数据导出时使用,本机测试,14W可以导出。列数量在8列左右
	 * 
	 * @param fileOut
	 *            输出流
	 * @param sheetMap
	 *            要设置的数据信息
	 * @throws SQLException
	 */
	public static void createXSLXByResultSet(OutputStream fileOut, WriteXLSBean... beans)
			throws SQLException {
		try {
			//重点 Workbook
			Workbook wb = new XSSFWorkbook();
			for (int i = 0, len = beans.length; i < len; i++) {
				WriteXLSBean xlsBean = beans[i];
				Sheet sheet = wb.createSheet(xlsBean.getSheetName());
				ResultSet rs = xlsBean.getRs();
				ResultSetMetaData rsmd = rs.getMetaData();
				TypeHandlerRegistry tr = BeanContext.tr;
				Map<String, String> th = xlsBean.getTh();
				int index = 0;
				while (rs.next()) {
					long t1 = System.currentTimeMillis();
					org.apache.poi.ss.usermodel.Row row = sheet
							.createRow(index);
					for (int j = 0, numberOfColumns = rsmd.getColumnCount(); j < numberOfColumns; j++) {
						String key = rsmd.getColumnLabel(j + 1).toLowerCase();
						if (th.containsKey(key)) {
							TypeHandler<?> type = tr.getTypeHandler(JdbcType
									.forCode(rsmd.getColumnType(j + 1)));
							Object obj = type.getResult(rs, key);
							row.createCell(j).setCellValue(obj == null ? ""
									: obj.toString());
						}
					}
					System.out.println(index + " :"
							+ (System.currentTimeMillis() - t1));
					index++;
				}
			}
			//重点 Workbook
			wb.write(fileOut);
		} catch (IOException e) {
			e.printStackTrace();
			throw new ServiceRunTimeException("生产xls文档错误", e);
		} finally {

		}
	}

 在上面 标注了重点的两处,分别是:

1.构建一个Excel对象

2.将该对象写入一个OutPutStream

而在构建过程中,没有地方写入OutPutSteam ,也就是说必须在内存中构建整个 Excel,才能进行写出操作,在大数据量情况下,这样将导致所有数据加载到内存中,而不能输出,导致最后 内存溢出。

根据运行环境不用,可能内存溢出的 情况不同

根据情况,如果数据量达到10W以上,建议使用

1、多个Excel,每个Excel一个Sheet,因为所有Sheet都是Workbook的组成部分。如果不分多个Excel,即使分Sheet也没用,

 

2、每个Excel中列数适中,比如: 5W行每个Excel档,实现分多次导出和分页查询原理一样 

 

3、对多个Excel导出到一个临时目录,并通过程序压缩,然后提供给客户下载

 

4、用另外一种方式,见 2 http://lyjilu.iteye.com/blog/2083106

 

2003版通过数据库结果存到List中,然后进行生产:Table 就是List Row 是Map

 

/**
	 * 生产xls,2003版本的excel,每个sheet有6.5W的行数限制
	 * 
	 * @param fileOut
	 *            输出流,未关闭
	 * @param sheetMap
	 *            要导出的数据信息
	 */
	public static void createXSLByMap(OutputStream fileOut, Map<String, Table<Row<String, Object>>> sheetMap) {
		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			Set<String> keys = sheetMap.keySet();
			for (Iterator<String> iterator = keys.iterator(); iterator
					.hasNext();) {
				String SheetKey = iterator.next();
				Sheet sheet = wb.createSheet(SheetKey);
				List<Row<String, Object>> sheetRows = sheetMap.get(SheetKey);
				for (int i = 0, len = sheetRows.size(); i < len; i++) {
					Map<String, Object> cellMap = sheetRows.get(i);
					Set<String> cellSet = cellMap.keySet();
					org.apache.poi.ss.usermodel.Row row = sheet.createRow(i);
					int j = 0;
					for (Iterator<String> iterCell = cellSet.iterator(); iterCell
							.hasNext(); j++) {
						String cellKey = iterCell.next();
						Object obj = cellMap.get(cellKey);
						row.createCell(j).setCellValue(obj == null ? ""
								: obj.toString());
					}
				}
			}
			wb.write(fileOut);
		} catch (IOException e) {
			e.printStackTrace();
			throw new ServiceRunTimeException("生产xls文档错误", e);
		} finally {
		}
	}

 

 

 

 新版本 POI+office 2007版本excel可以导出几十万条而不内存溢出,详细见:

http://lyjilu.iteye.com/blog/2083106

出处: http://lyjilu.iteye.com/

 

2016-07-12 22:02:41 qq_23412263 阅读数 1713
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1398 人正在学习 去看看 钟林森

最近在工作遇到利用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的代码后,问题解决。



2013-01-25 10:18:04 tangpeng13875730110 阅读数 697
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1398 人正在学习 去看看 钟林森

用poi导出excel时,当导出大量的数据时。会出现内存溢出的情况,后来查看api,poi3.8以后提供了一个新的工作薄:SXSSFWorkbook

能提供批量导出,但是SXSSFWorkbook导出的excel的文件的后缀要为“xlsx”:

//导出excel方法

public void write_Excel( String xls_write_Address,ArrayList<ArrayList> ls,String[] sheetnames) throws IOException  {  
FileOutputStream output = new FileOutputStream(new File(xls_write_Address));  //读取的文件路径   
        SXSSFWorkbook wb = new SXSSFWorkbook(10000);//内存中保留 10000 条数据,以免内存溢出,其余写入 硬盘          
        for(int sn=0;sn<ls.size();sn++){  
            Sheet sheet = wb.createSheet(String.valueOf(sn));  
  
            wb.setSheetName(sn, sheetnames[sn]);     
            ArrayList<String[]> ls2 = ls.get(sn);   
            for(int i=0;i<ls2.size();i++){  
                Row row = sheet.createRow(i);  
                String[] s = ls2.get(i);                  
                for(int cols=0;cols<s.length;cols++){  
                    Cell cell = row.createCell(cols);                     
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);//文本格式                    
                    sheet.setColumnWidth(cols, s[cols].length()*384); //设置单元格宽度  
                    cell.setCellValue(s[cols]);//写入内容  
                }  
            }              
         }    
        wb.write(output); 
        output.flush();
        output.close();           
    }  

//测试所用数据

public void test(){
String xls_add = "e:/st.xlsx";
String[] sheetName = {"id","name","age","kemu","score"};

ArrayList<ArrayList> ls = new ArrayList<ArrayList>();

ArrayList<String[]> arr1 = new ArrayList<String[]>();
for(int i=0;i<200000;i++){
String[] s={"aa"+i,"bb"+i,"cc"+i,"dd"+i,"dd"+i};
arr1.add(s);
}
ls.add(arr1);

try {
write_Excel(xls_add,ls,sheetName);
} catch (IOException e) {
e.printStackTrace();
}
}

//mian函数

public static void main(String[] args) {

ExpExcel ex = new ExpExcel();
ex.test();
}


poi之大数据量导出

阅读数 1683

没有更多推荐了,返回首页