文档章节

POI实现超大数据的Excel的读写操作,支持Excel最大行数。

ThinkGem
 ThinkGem
发布于 2016/07/17 16:15
字数 2230
阅读 175
收藏 1

前端时间写了注解方式Excel的读取和写入,它是根据注解完成Excel的操作,虽说支持大数据,但对于超大数据就无能为力了,因为它的读写期间都是将所有数据放入系统内存的,除非你有超大的内存。

 

因项目需要对超大数据的Excel读写操作,于是网上找了个超大数据的读写代码,这个不需要太大内存。并对此进行了简单的修改。

 

原理如下:

 

Excel超大数据读取:抽象Excel2007读取器,excel2007的底层数据结构是xml文件,采用SAX的事件驱动的方法解析 xml,需要继承DefaultHandler,在遇到文件内容时,事件会触发,这种做法可以大大降低内存的耗费,特别使用于大数据量的文件。

 

Excel超大数据写入:抽象excel2007读入器,先构建.xlsx一张模板,改写模板中的sheet.xml, 使用这种方法 写入.xlsx文件,不需要太大的内存。

 

先看调用示例:

  

String file = "E:/导入测试数据.xlsx";

ExcelReader reader = new ExcelReader() {
	public void getRows(int sheetIndex, int curRow, List<String> rowList) {
		
		System.out.println("Sheet:" + sheetIndex + ", Row:" + curRow + ", Data:" +rowList);
		
	}
};
reader.process(file, 1);

 

String file = "E:/导出测试数据.xlsx";

ExcelWriter writer = new ExcelWriter() {
	public void generate() throws Exception {
		
		// 电子表格开始
		this.beginSheet();
		
		for (int rownum = 0; rownum < 100; rownum++) {
			// 插入新行
			this.insertRow(rownum);
			
			// 建立新单元格,索引值从0开始,表示第一列
			this.createCell(0, "第 " + rownum + " 行");
			this.createCell(1, 34343.123456789);
			this.createCell(2, "23.67%");
			this.createCell(3, "12:12:23");
			this.createCell(4, "2014-10-11 12:12:23");
			this.createCell(5, "true");
			this.createCell(6, "false");

			// 结束行
			this.endRow();
		}
		
		// 电子表格结束
			this.endSheet();
		}
	};
	writer.process(file);
}

 这里只展示了对数据的读取和写入,如果正式保存到数据库时建议读取一部分(如100条)再写入一次数据库,尽量不要读取一条就写入一条,这样会非常耗费资源。

 

源代码如下:

 

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * Excel超大数据读取,抽象Excel2007读取器,excel2007的底层数据结构是xml文件,采用SAX的事件驱动的方法解析
 * xml,需要继承DefaultHandler,在遇到文件内容时,事件会触发,这种做法可以大大降低 内存的耗费,特别使用于大数据量的文件。
 * @version 2014-9-2
 */
public abstract class ExcelReader extends DefaultHandler {

	// 共享字符串表
	private SharedStringsTable sst;
	
	// 上一次的内容
	private String lastContents;
	private boolean nextIsString;

	private int sheetIndex = -1;
	private List<String> rowList = new ArrayList<String>();

	// 当前行
	private int curRow = 0;
	// 当前列
	private int curCol = 0;
	// 日期标志
	private boolean dateFlag;
	// 数字标志
	private boolean numberFlag;

	private boolean isTElement;

	/**
	 * 遍历工作簿中所有的电子表格
	 * @param filename
	 * @throws Exception
	 */
	public void process(String filename) throws Exception {
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader(pkg);
		SharedStringsTable sst = r.getSharedStringsTable();
		XMLReader parser = fetchSheetParser(sst);
		Iterator<InputStream> sheets = r.getSheetsData();
		while (sheets.hasNext()) {
			curRow = 0;
			sheetIndex++;
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
		}
	}

	/**
	 * 只遍历一个电子表格,其中sheetId为要遍历的sheet索引,从1开始,1-3
	 * @param filename
	 * @param sheetId
	 * @throws Exception
	 */
	public void process(String filename, int sheetId) throws Exception {
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader(pkg);
		SharedStringsTable sst = r.getSharedStringsTable();
		XMLReader parser = fetchSheetParser(sst);
		// 根据 rId# 或 rSheet# 查找sheet
		InputStream sheet2 = r.getSheet("rId" + sheetId);
		sheetIndex++;
		InputSource sheetSource = new InputSource(sheet2);
		parser.parse(sheetSource);
		sheet2.close();
	}

	public XMLReader fetchSheetParser(SharedStringsTable sst)
			throws SAXException {
		XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
		this.sst = sst;
		parser.setContentHandler(this);
		return parser;
	}

	public void startElement(String uri, String localName, String name,
			Attributes attributes) throws SAXException {

//		System.out.println("startElement: " + localName + ", " + name + ", " + attributes);

		// c => 单元格
		if ("c".equals(name)) {
			// 如果下一个元素是 SST 的索引,则将nextIsString标记为true
			String cellType = attributes.getValue("t");
			if ("s".equals(cellType)) {
				nextIsString = true;
			} else {
				nextIsString = false;
			}
			// 日期格式
			String cellDateType = attributes.getValue("s");
			if ("1".equals(cellDateType)) {
				dateFlag = true;
			} else {
				dateFlag = false;
			}
			String cellNumberType = attributes.getValue("s");
			if ("2".equals(cellNumberType)) {
				numberFlag = true;
			} else {
				numberFlag = false;
			}

		}
		// 当元素为t时
		if ("t".equals(name)) {
			isTElement = true;
		} else {
			isTElement = false;
		}

		// 置空
		lastContents = "";
	}

	public void endElement(String uri, String localName, String name)
			throws SAXException {

//		System.out.println("endElement: " + localName + ", " + name);

		// 根据SST的索引值的到单元格的真正要存储的字符串
		// 这时characters()方法可能会被调用多次
		if (nextIsString) {
			try {
				int idx = Integer.parseInt(lastContents);
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
						.toString();
			} catch (Exception e) {

			}
		}
		// t元素也包含字符串
		if (isTElement) {
			String value = lastContents.trim();
			rowList.add(curCol, value);
			curCol++;
			isTElement = false;
			// v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引
			// 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
		} else if ("v".equals(name)) {
			String value = lastContents.trim();
			value = value.equals("") ? " " : value;
			try {
				// 日期格式处理
				if (dateFlag) {
					Date date = HSSFDateUtil.getJavaDate(Double.valueOf(value));
					SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
					value = dateFormat.format(date);
				}
				// 数字类型处理
				if (numberFlag) {
					BigDecimal bd = new BigDecimal(value);
					value = bd.setScale(3, BigDecimal.ROUND_UP).toString();
				}
			} catch (Exception e) {
				// 转换失败仍用读出来的值
			}
			rowList.add(curCol, value);
			curCol++;
		} else {
			// 如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法
			if (name.equals("row")) {
				getRows(sheetIndex + 1, curRow, rowList);
				rowList.clear();
				curRow++;
				curCol = 0;
			}
		}

	}

	public void characters(char[] ch, int start, int length)
			throws SAXException {
		// 得到单元格内容的值
		lastContents += new String(ch, start, length);
	}

	/**
	 * 获取行数据回调
	 * @param sheetIndex
	 * @param curRow
	 * @param rowList
	 */
	public abstract void getRows(int sheetIndex, int curRow, List<String> rowList);

	/**
	 * 测试方法
	 */
	public static void main(String[] args) throws Exception {

String file = "E:/导入测试数据.xlsx";

ExcelReader reader = new ExcelReader() {
	public void getRows(int sheetIndex, int curRow, List<String> rowList) {
		
		System.out.println("Sheet:" + sheetIndex + ", Row:" + curRow + ", Data:" +rowList);
		
	}
};
reader.process(file, 1);

	}

}

 

 

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Writer;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel超大数据写入,抽象excel2007读入器,先构建.xlsx一张模板,改写模板中的sheet.xml,
 * 使用这种方法 写入.xlsx文件,不需要太大的内存
 * @version 2014-9-2
 */
public abstract class ExcelWriter {

	private SpreadsheetWriter sw;

	/**
	 * 写入电子表格的主要流程
	 * 
	 * @param fileName
	 * @throws Exception
	 */
	public void process(String fileName) throws Exception {
		
		// 建立工作簿和电子表格对象
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet("sheet1");
		
		// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
		String sheetRef = sheet.getPackagePart().getPartName().getName();

		// 保存模板
		FileOutputStream os = new FileOutputStream("template.xlsx");
		wb.write(os);
		os.close();

		// 生成xml文件
		File tmp = File.createTempFile("sheet", ".xml");
		Writer fw = new FileWriter(tmp);
		sw = new SpreadsheetWriter(fw);
		generate();
		fw.close();

		// 使用产生的数据替换模板
		File templateFile = new File("template.xlsx");
		FileOutputStream out = new FileOutputStream(fileName);
		substitute(templateFile, tmp, sheetRef.substring(1), out);
		out.close();
		// 删除文件之前调用一下垃圾回收器,否则无法删除模板文件
		System.gc();
		// 删除临时模板文件
		if (templateFile.isFile() && templateFile.exists()) {
			templateFile.delete();
		}
	}

	/**
	 * 类使用者应该使用此方法进行写操作
	 * 
	 * @throws Exception
	 */
	public abstract void generate() throws Exception;

	public void beginSheet() throws IOException {
		sw.beginSheet();
	}

	public void insertRow(int rowNum) throws IOException {
		sw.insertRow(rowNum);
	}

	public void createCell(int columnIndex, String value) throws IOException {
		sw.createCell(columnIndex, value, -1);
	}

	public void createCell(int columnIndex, double value) throws IOException {
		sw.createCell(columnIndex, value, -1);
	}

	public void endRow() throws IOException {
		sw.endRow();
	}

	public void endSheet() throws IOException {
		sw.endSheet();
	}

	/**
	 * 
	 * @param zipfile
	 *            the template file
	 * @param tmpfile
	 *            the XML file with the sheet data
	 * @param entry
	 *            the name of the sheet entry to substitute, e.g.
	 *            xl/worksheets/sheet1.xml
	 * @param out
	 *            the stream to write the result to
	 */
	private static void substitute(File zipfile, File tmpfile, String entry,
			OutputStream out) throws IOException {
		ZipFile zip = new ZipFile(zipfile);
		ZipOutputStream zos = new ZipOutputStream(out);

		@SuppressWarnings("unchecked")
		Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
		while (en.hasMoreElements()) {
			ZipEntry ze = en.nextElement();
			if (!ze.getName().equals(entry)) {
				zos.putNextEntry(new ZipEntry(ze.getName()));
				InputStream is = zip.getInputStream(ze);
				copyStream(is, zos);
				is.close();
			}
		}
		zos.putNextEntry(new ZipEntry(entry));
		InputStream is = new FileInputStream(tmpfile);
		copyStream(is, zos);
		is.close();
		zos.close();
	}

	private static void copyStream(InputStream in, OutputStream out)
			throws IOException {
		byte[] chunk = new byte[1024];
		int count;
		while ((count = in.read(chunk)) >= 0) {
			out.write(chunk, 0, count);
		}
	}

	/**
	 * 在写入器中写入电子表格
	 * 
	 */
	public static class SpreadsheetWriter {
		private final Writer _out;
		private int _rownum;
		private static String LINE_SEPARATOR = System
				.getProperty("line.separator");

		public SpreadsheetWriter(Writer out) {
			_out = out;
		}

		public void beginSheet() throws IOException {
			_out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>"
					+ "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
			_out.write("<sheetData>" + LINE_SEPARATOR);
		}

		public void endSheet() throws IOException {
			_out.write("</sheetData>");
			_out.write("</worksheet>");
		}

		/**
		 * 插入新行
		 * 
		 * @param rownum
		 *            以0开始
		 */
		public void insertRow(int rownum) throws IOException {
			_out.write("<row r=\"" + (rownum + 1) + "\">" + LINE_SEPARATOR);
			this._rownum = rownum;
		}

		/**
		 * 插入行结束标志
		 */
		public void endRow() throws IOException {
			_out.write("</row>" + LINE_SEPARATOR);
		}

		/**
		 * 插入新列
		 * 
		 * @param columnIndex
		 * @param value
		 * @param styleIndex
		 * @throws IOException
		 */
		public void createCell(int columnIndex, String value, int styleIndex)
				throws IOException {
			String ref = new CellReference(_rownum, columnIndex)
					.formatAsString();
			_out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
			if (styleIndex != -1)
				_out.write(" s=\"" + styleIndex + "\"");
			_out.write(">");
			_out.write("<is><t>" + encoderXML(value) + "</t></is>");
			_out.write("</c>");
		}

		public void createCell(int columnIndex, String value)
				throws IOException {
			createCell(columnIndex, value, -1);
		}

		public void createCell(int columnIndex, double value, int styleIndex)
				throws IOException {
			String ref = new CellReference(_rownum, columnIndex)
					.formatAsString();
			_out.write("<c r=\"" + ref + "\" t=\"n\"");
			if (styleIndex != -1)
				_out.write(" s=\"" + styleIndex + "\"");
			_out.write(">");
			_out.write("<v>" + value + "</v>");
			_out.write("</c>");
		}

		public void createCell(int columnIndex, double value)
				throws IOException {
			createCell(columnIndex, value, -1);
		}

		public void createCell(int columnIndex, Calendar value, int styleIndex)
				throws IOException {
			createCell(columnIndex, DateUtil.getExcelDate(value, false),
					styleIndex);
		}
	}

	// XML Encode
	private static final String[] xmlCode = new String[256];

	static {
		// Special characters
		xmlCode['\''] = "'";
		xmlCode['\"'] = "\""; // double quote
		xmlCode['&'] = "&"; // ampersand
		xmlCode['<'] = "<"; // lower than
		xmlCode['>'] = ">"; // greater than
	}

	/**
	 * <p>
	 * Encode the given text into xml.
	 * </p>
	 * 
	 * @param string
	 *            the text to encode
	 * @return the encoded string
	 */
	public static String encoderXML(String string) {
		if (string == null)
			return "";
		int n = string.length();
		char character;
		String xmlchar;
		StringBuffer buffer = new StringBuffer();
		// loop over all the characters of the String.
		for (int i = 0; i < n; i++) {
			character = string.charAt(i);
			// the xmlcode of these characters are added to a StringBuffer
			// one by one
			try {
				xmlchar = xmlCode[character];
				if (xmlchar == null) {
					buffer.append(character);
				} else {
					buffer.append(xmlCode[character]);
				}
			} catch (ArrayIndexOutOfBoundsException aioobe) {
				buffer.append(character);
			}
		}
		return buffer.toString();
	}

	/**
	 * 测试方法
	 */
	public static void main(String[] args) throws Exception {

	String file = "E:/导出测试数据.xlsx";
	
	ExcelWriter writer = new ExcelWriter() {
		public void generate() throws Exception {
			
			// 电子表格开始
			this.beginSheet();
			
			for (int rownum = 0; rownum < 100; rownum++) {
				// 插入新行
				this.insertRow(rownum);
				
				// 建立新单元格,索引值从0开始,表示第一列
				this.createCell(0, "第 " + rownum + " 行");
				this.createCell(1, 34343.123456789);
				this.createCell(2, "23.67%");
				this.createCell(3, "12:12:23");
				this.createCell(4, "2014-10-11 12:12:23");
				this.createCell(5, "true");
				this.createCell(6, "false");

				// 结束行
				this.endRow();
			}
			
			// 电子表格结束
			this.endSheet();
		}
	};
	writer.process(file);
}
		
}

 

 

 

 

 

 

 

本文转载自:http://thinkgem.iteye.com/blog/2150940

共有 人打赏支持
ThinkGem

ThinkGem

粉丝 991
博文 137
码字总数 22174
作品 1
济南
架构师
JavaScript与Excel灵活交互制作Web报表

【IT168 技术】Web报表的开发是一个比较常见的功能,然而在B/S构件上实现这些功能并没有在C/S构架上那些简单,针对这样的问题,本篇文章提供相应的解决方案,在下面的内容中将介绍JS与Excel...

天极网
2012/03/01
0
0
Java程序员从笨鸟到菜鸟之(一百零四)java操作office和pdf文件(二)利用POI实现数据导出excel报表

在上一篇博客中,我们简单介绍了java读取word,excel和pdf文档内容 ,但在实际开发中,我们用到最多的是把数据库中数据导出excel报表形式。不仅仅简单的读取office中的数据.尤其是在生产管理...

长平狐
2012/11/12
1K
0
自动化excl学习笔记

学习excl操作 import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.text.DecimalFormat; import java.util.A......

知止内明
2017/11/21
0
0
使用Apache POI读取Excel文件

网上关于介绍Apache POI操作Excel的文章已经很多了,但都讲得比较复杂。poi的API 与实际使用中的Excel很类似,可以说是POI把Excel中的workbook、sheet、cell等对象化了,在实际使用中极易理解...

zlikun
2013/05/13
0
4
Apache POI使用详解

1.POI结构与常用类 (1)POI介绍 Apache POI是Apache软件基金会的开源项目,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 .NET的开发人员则可以利用NPOI (POI for .NET) 来存...

小湘西
2016/07/26
125
2

没有更多内容

加载失败,请刷新页面

加载更多

Shiro | 实现权限验证完整版

写在前面的话 提及权限,就会想到安全,是一个十分棘手的话题。这里只是作为学校Shiro的一个记录,而不是,权限就应该这样设计之类的。 Shiro框架 1、Shiro是基于Apache开源的强大灵活的开源...

冯文议
今天
1
0
linux 系统的运行级别

运行级别 运行级别 | 含义 0 关机 1 单用户模式,可以想象为windows 的安全模式,主要用于修复系统 2 不完全的命令模式,不含NFS服务 3 完全的命令行模式,就是标准的字符界面 4 系统保留 5 ...

Linux学习笔记
今天
2
0
学习设计模式——命令模式

任何模式的出现,都是为了解决一些特定的场景的耦合问题,以达到对修改封闭,对扩展开放的效果。命令模式也不例外: 命令模式是为了解决命令的请求者和命令的实现者之间的耦合关系。 解决了这...

江左煤郎
今天
3
0
字典树收集(非线程安全,后续做线程安全改进)

将500W个单词放进一个数据结构进行存储,然后进行快速比对,判断一个单词是不是这个500W单词之中的;来了一个单词前缀,给出500w个单词中有多少个单词是该前缀. 1、这个需求首先需要设计好数据结...

算法之名
昨天
15
0
GRASP设计模式

此文参考了这篇博客,建议读者阅读原文。 面向对象(Object-Oriented,OO)是当下软件开发的主流方法。在OO分析与设计中,我们首先从问题领域中抽象出领域模型,在领域模型中以适当的粒度归纳...

克虏伯
昨天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部