前端时间写了注解方式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);
}
}
相关推荐
对于Excel的写操作，POI 对 Excel 2007 的支持更加完善。在写入过程中，主要分为三步：首先创建工作簿和电子表格对象，然后在流中构建文本文件，最后用流中的数据替换模板中的电子表格。这种方式允许处理大规模数据...
通过以上示例代码，我们可以清楚地看到如何使用Java POI库来操作Excel文件，包括读取Sheet、获取行数和列数等。这对于自动化办公和数据分析任务来说是非常有用的技能。希望本文能帮助读者更好地理解和掌握这一技术。
Excel读写类库，封装其他几个有POI类库实现对Excel文件的读取和保存数据到Excel，比较实用强大！ 此Excel读写类库，比调用DotNet自带office库，强大。自己已经比较，稳定性、效率都大大提高。 DotNet自带Excel类库...
- 读写操作：除了读取，POI还可以用来创建新的Excel文件，或者更新已有的文件。 7. **性能考虑** - 由于`.xlsx`文件是基于XML的，所以处理起来可能会比`.xls`文件更耗资源。在处理大量数据时，应考虑优化内存使用...
在Java世界中，如果你想对Excel进行读写操作，POI库是不可或缺的工具。下面将详细介绍如何使用Java POI来操作Excel以及相关的知识点。 1. **基本概念** - HSSF（Horrible Spreadsheet Format）：这是POI库处理...
标题中的“poi包 EXCEL模板读取填数据并导出以及在模板某行插入新的行”涉及到了Apache POI库的使用，这是一个Java API，专门用于处理Microsoft Office格式的文件，如Excel。以下是对这个主题的详细解释： Apache ...
本篇将深入探讨如何利用Apache POI来实现大数据量的Excel文件解析。 首先，Apache POI提供了两种主要的API用于处理Excel文件：HSSF（Horizontally Stored Sheet Format）用于处理.xls文件（老版本的Excel），而XSSF...
Easy-Poi提供了更简洁的API，便于开发者快速地实现Excel的读写操作，尤其适合于批量处理Excel文件。 在使用Easy-Poi之前，确保引入了必要的依赖。在本实例中，我们有以下四个jar包： 1. poi-3.15.jar：这是Apache ...
EasyExcel是一个基于Java的简单、易用且高效的Excel处理工具，它支持读写Excel文件，并且能够有效地避免内存溢出问题。 **EasyExcel特点：** - **内存消耗低：** EasyExcel使用流式处理的方式读取Excel文件，只在...
Apache POI是一个用于读写Microsoft Office格式档案的Java API，包括Excel、Word等文件格式。本文将详细介绍如何使用Apache POI结合JSF技术框架来实现一个简单的Excel文件导出功能。 #### 技术栈与环境准备 - **...
Apache POI是Java的一个开源项目，专门用于读写Microsoft Office格式的文件，包括Excel。本文将深入探讨如何利用Java和Apache POI来高效地解析百万行的Excel数据。 首先，理解Excel文件的结构至关重要。Excel 2007...
在实际开发中，这些方法的组合使用可以实现对 Excel 文件的全面操作，包括读取数据、写入数据、格式化单元格、合并单元格、设置样式等。通过 POI，开发者可以构建出复杂的 Excel 处理功能，例如报表生成、数据导入...
`initial_data`变量定义了Excel表格可容纳的初始数据量，这是为了避免超过Excel 2003的最大行数限制（65535行）。 5. **方法实现**： 类中可能包含各种方法，用于创建Excel工作簿、工作表，设置标题行，以及根据...
Apache POI是一个开源项目，提供了对Microsoft Office格式文件的读写支持，包括Excel。在Spring Boot中，我们可以借助Apache POI创建HSSFWorkbook对象，然后通过工作表（Sheet）、行（Row）和单元格（Cell）进行数据...
在Java世界中，如果你需要对Excel进行读写操作，POI是首选的库。下面将详细介绍如何使用Java POI进行Excel的操作，以及相关的知识点。 **1. 导入与导出Excel** 在Java POI中，主要使用`HSSFWorkbook`（针对老版的...
7. **优化性能**：对于大数据量的读写，可以使用SXSSF（Streaming Usermodel API），它在内存中仅保留有限行数，其余行存储在磁盘上，从而降低内存消耗。 综上，Apache POI是Java操作Excel的强大工具，无论是在读取...
尽管POI HSSF提供了丰富的API来操作Excel表格数据，但它在直接支持Excel图表方面存在局限性。截止至POI版本2.5.1，org.apache.poi.hssf.usermodel包中虽包含HSSFChart类，但该类仅提供了一个空的createBarChart()...
POI提供了两种主要的API来操作Excel文件：HSSF（用于处理.xls文件，即Excel 97-2003格式）和XSSF（用于处理.xlsx文件，即Excel 2007及以上版本）。由于现代Excel文件通常采用.xlsx格式，我们将主要关注XSSF API。 ...