POI读写海量Excel(详细解读)
更新时间:2024-05-27 04:44:01 阅读量: 综合文库 文档下载
- poi读写excel推荐度:
- 相关推荐
目前处理Excel的开源javaAPI主要有两种,一是Jxl(Java Excel API),Jxl只支持Excel2003以下的版本。另外一种是Apache的Jakarta POI,相比于Jxl,POI对微软办公文档的支持更加强大,但是它使用复杂,上手慢。POI可支持更高的Excel版本2007。对Excel的读取,POI有两种模式,一是用户模式,这种方式同Jxl的使用很类似,使用简单,都是将文件一次性读到内存,文件小的时候,没有什么问题,当文件大的时候,就会出现OutOfMemory的内存溢出问题。第二种是事件驱动模式,拿Excel2007来说,其内容采用XML的格式来存储,所以处理excel就是解析XML,而目前使用事件驱动模式解析XML的API是SAX(Simple API for XML),这种模型在读取XML文档时,并没有将整个文档读入内存,而是按顺序将整个文档解析完,在解析过程中,会主动产生事件交给程序中相应的处理函数来处理当前内容。因此这种方式对系统资源要求不高,可以处理海量数据。笔者曾经做过测试,这种方法处理一千万条,每条五列的数据花费大约11分钟。可见处理海量数据的文件事件驱动是一个很好的方式。而本文中用到的AbstractExcel2003Reader、AbstractExcel2007Reader对Excel的读取都是采用这种POI的事件驱动模式。至于Excel的写操作,对较高版本的Excel2007,POI提供了很好的支持,主要流程是第一步构建工作薄和电子表格对象,第二步在一个流中构建文本文件,第三步使用流中产生的数据替换模板中的电子表格。这种方式也可以处理海量数据文件。AbstractExcel2007Writer就是使用这种方式进行写操作。对于写入较低版本的Excel2003,POI使用了用户模式来处理,就是将整个文档加载进内存,如果数据量大的话就会出现内存溢出的问题,Excel2003Writer就是使用这种方式。据笔者的测试,如果数据量大于3万条,每条8列的话,就会报OutOfMemory的错误。Excel2003中每个电子表格的记录数必须在65536以下,否则就会发生异常。目前还没有好的解决方案,建议对于海量数据写入操作,尽量使用Excel2007。 /**
* 抽象Excel2003读取器,通过实现HSSFListener监听器,采用事件驱动模式解析excel2003 * 中的内容,遇到特定事件才会触发,大大减少了内存的使用。 * */
public class Excel2003Reader implements HSSFListener{ private int minColumns = -1; private POIFSFileSystem fs; private int lastRowNumber; private int lastColumnNumber; /** Should we output the formula, or the value it has? */ private boolean outputFormulaValues = true; /** For parsing Formulas */ private SheetRecordCollectingListener workbookBuildingListener; //excel2003工作薄 private HSSFWorkbook stubWorkbook; // Records we pick up as we process private SSTRecord sstRecord;
private FormatTrackingHSSFListener formatListener; //表索引
private int sheetIndex = -1;
private BoundSheetRecord[] orderedBSRs; @SuppressWarnings(\
private ArrayList boundSheetRecords = new ArrayList(); // For handling formulas with string results private int nextRow; private int nextColumn;
private boolean outputNextStringRecord; //当前行
private int curRow = 0;
//存储行记录的容器
private List
private IRowReader rowReader;
public void setRowReader(IRowReader rowReader){ this.rowReader = rowReader; } /**
* 遍历excel下所有的sheet * @throws IOException */
public void process(String fileName) throws IOException { this.fs = new POIFSFileSystem(new FileInputStream(fileName)); MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this); formatListener = new FormatTrackingHSSFListener(listener); HSSFEventFactory factory = new HSSFEventFactory(); HSSFRequest request = new HSSFRequest(); if (outputFormulaValues) { request.addListenerForAllRecords(formatListener); } else { workbookBuildingListener = new SheetRecordCollectingListener( formatListener); request.addListenerForAllRecords(workbookBuildingListener); }
factory.processWorkbookEvents(request, fs); } /**
* HSSFListener 监听方法,处理 Record */
@SuppressWarnings(\
public void processRecord(Record record) { int thisRow = -1; int thisColumn = -1; String thisStr = null; String value = null; switch (record.getSid()) { case BoundSheetRecord.sid: boundSheetRecords.add(record); break; case BOFRecord.sid: BOFRecord br = (BOFRecord) record; if (br.getType() == BOFRecord.TYPE_WORKSHEET) { // 如果有需要,则建立子工作薄 if (workbookBuildingListener != null && stubWorkbook == null) { stubWorkbook = workbookBuildingListener .getStubHSSFWorkbook(); } sheetIndex++; if (orderedBSRs == null) { orderedBSRs = BoundSheetRecord .orderByBofPosition(boundSheetRecords); } sheetName = orderedBSRs[sheetIndex].getSheetname(); } break; case SSTRecord.sid: sstRecord = (SSTRecord) record; break; case BlankRecord.sid: BlankRecord brec = (BlankRecord) record; thisRow = brec.getRow(); thisColumn = brec.getColumn(); thisStr = \ rowlist.add(thisColumn, thisStr);
break;
case BoolErrRecord.sid: //单元格为布尔类型 BoolErrRecord berec = (BoolErrRecord) record; thisRow = berec.getRow(); thisColumn = berec.getColumn(); thisStr = berec.getBooleanValue()+\ rowlist.add(thisColumn, thisStr); break;
case FormulaRecord.sid: //单元格为公式类型 FormulaRecord frec = (FormulaRecord) record; thisRow = frec.getRow(); thisColumn = frec.getColumn(); if (outputFormulaValues) { if (Double.isNaN(frec.getValue())) { // Formula result is a string // This is stored in the next record outputNextStringRecord = true; nextRow = frec.getRow(); nextColumn = frec.getColumn(); } else { thisStr = formatListener.formatNumberDateCell(frec); } } else { thisStr = '\ frec.getParsedExpression()) + '\ } rowlist.add(thisColumn,thisStr); break;
case StringRecord.sid://单元格中公式的字符串 if (outputNextStringRecord) { // String for formula StringRecord srec = (StringRecord) record; thisStr = srec.getString(); thisRow = nextRow; thisColumn = nextColumn; outputNextStringRecord = false; } break;
case LabelRecord.sid: LabelRecord lrec = (LabelRecord) record; curRow = thisRow = lrec.getRow(); thisColumn = lrec.getColumn(); value = lrec.getValue().trim();
}
value = value.equals(\ this.rowlist.add(thisColumn, value); break;
case LabelSSTRecord.sid: //单元格为字符串类型 LabelSSTRecord lsrec = (LabelSSTRecord) record; curRow = thisRow = lsrec.getRow(); thisColumn = lsrec.getColumn(); if (sstRecord == null) { rowlist.add(thisColumn, \ } else { value = sstRecord .getString(lsrec.getSSTIndex()).toString().trim(); value = value.equals(\ rowlist.add(thisColumn,value); } break;
case NumberRecord.sid: //单元格为数字类型 NumberRecord numrec = (NumberRecord) record; curRow = thisRow = numrec.getRow(); thisColumn = numrec.getColumn(); value = formatListener.formatNumberDateCell(numrec).trim(); value = value.equals(\ // 向容器加入列值 rowlist.add(thisColumn, value); break; default: break;
// 遇到新行的操作
if (thisRow != -1 && thisRow != lastRowNumber) { lastColumnNumber = -1; }
// 空值的操作
if (record instanceof MissingCellDummyRecord) { MissingCellDummyRecord mc = (MissingCellDummyRecord) record; curRow = thisRow = mc.getRow(); thisColumn = mc.getColumn(); rowlist.add(thisColumn,\}
// 更新行和列的值 if (thisRow > -1)
} /**
}
lastRowNumber = thisRow; if (thisColumn > -1) lastColumnNumber = thisColumn;
// 行结束时的操作
if (record instanceof LastCellOfRowDummyRecord) { if (minColumns > 0) { // 列值重新置空 if (lastColumnNumber == -1) { lastColumnNumber = 0; } } lastColumnNumber = -1; }
// 每行结束时, 调用getRows() 方法 rowReader.getRows(sheetIndex,curRow, rowlist);
// 清空容器 rowlist.clear();
* 抽象Excel2007读取器,excel2007的底层数据结构是xml文件,采用SAX的事件驱动的方法解析
* xml,需要继承DefaultHandler,在遇到文件内容时,事件会触发,这种做法可以大大降低
* 内存的耗费,特别使用于大数据量的文件。 * */
public class Excel2007Reader extends DefaultHandler { //共享字符串表 private SharedStringsTable sst;
//上一次的内容
private String lastContents; private boolean nextIsString;
private int sheetIndex = -1;
private List
private int curRow = 0; //当前列
private int curCol = 0;
//日期标志
private boolean dateFlag; //数字标志
private boolean numberFlag;
private boolean isTElement;
private IRowReader rowReader;
public void setRowReader(IRowReader rowReader){ this.rowReader = rowReader; }
/**只遍历一个电子表格,其中sheetId为要遍历的sheet索引,从1开始,1-3 * @param filename * @param sheetId * @throws Exception */
public void processOneSheet(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(\sheetIndex++;
InputSource sheetSource = new InputSource(sheet2); parser.parse(sheetSource); sheet2.close();
/**
* 遍历工作簿中所有的电子表格 * @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
}
while (sheets.hasNext()) { curRow = 0; sheetIndex++; InputStream sheet = sheets.next(); InputSource sheetSource = new InputSource(sheet); parser.parse(sheetSource); sheet.close(); }
public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException { XMLReader parser = XMLReaderFactory .createXMLReader(\ this.sst = sst; parser.setContentHandler(this); return parser; }
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
// c => 单元格
if (\
// 如果下一个元素是 SST 的索引,则将nextIsString标记为true String cellType = attributes.getValue(\if (\ nextIsString = true; } else { nextIsString = false; }
//日期格式
String cellDateType = attributes.getValue(\if (\ dateFlag = true; } else { dateFlag = false; }
String cellNumberType = attributes.getValue(\if(\ numberFlag = true; } else { numberFlag = false; }
}
}
//当元素为t时 if(\ isTElement = true; } else { isTElement = false; }
// 置空
lastContents = \
public void endElement(String uri, String localName, String name) throws SAXException {
// 根据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 (\ String value = lastContents.trim(); value = value.equals(\ //日期格式处理 if(dateFlag){ Date date = HSSFDateUtil.getJavaDate(Double.valueOf(value)); SimpleDateFormat dateFormat = new SimpleDateFormat( \
value = dateFormat.format(date); } //数字类型处理 if(numberFlag){ BigDecimal bd = new BigDecimal(value); value = bd.setScale(3,BigDecimal.ROUND_UP).toString(); } rowlist.add(curCol, value); curCol++; }else {
//如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法 if (name.equals(\ rowReader.getRows(sheetIndex,curRow,rowlist); rowlist.clear(); curRow++; curCol = 0; } } } public void characters(char[] ch, int start, int length) throws SAXException { //得到单元格内容的值 lastContents += new String(ch, start, length); } }
public class ExcelReaderUtil {
//excel2003扩展名
public static final String EXCEL03_EXTENSION = \
//excel2007扩展名
public static final String EXCEL07_EXTENSION = \ /**
* 读取Excel文件,可能是03也可能是07版本 * @param excel03 * @param excel07 * @param fileName * @throws Exception */
public static void readExcel(IRowReader reader,String fileName) throws Exception{ // 处理excel2003文件
if (fileName.endsWith(EXCEL03_EXTENSION)){ Excel2003Reader excel03 = new Excel2003Reader(); excel03.setRowReader(reader); excel03.process(fileName); // 处理excel2007文件
} else if (fileName.endsWith(EXCEL07_EXTENSION)){ Excel2007Reader excel07 = new Excel2007Reader(); excel07.setRowReader(reader); excel07.process(fileName); } else {
throw new Exception(\文件格式错误,fileName的扩展名只能是xls或xlsx。\ } } }
public interface IRowReader { /**业务逻辑实现方法 * @param sheetIndex * @param curRow * @param rowlist */ public void getRows(int sheetIndex,int curRow, List
public class RowReader implements IRowReader{ /* 业务逻辑实现方法 * @see com.eprosun.util.excel.IRowReader#getRows(int, int, java.util.List) */ public void getRows(int sheetIndex, int curRow, List
public class Main {
public static void main(String[] args) throws Exception { IRowReader reader = new RowReader(); //ExcelReaderUtil.readExcel(reader, \ ExcelReaderUtil.readExcel(reader, \ } }
public class Excel2003Writer { /** * @param args */ public static void main(String[] args) { try{ System.out.println(\开始写入excel2003....\ writeExcel(\
} /**
System.out.println(\写完xcel2003\} catch (IOException e) { }
* 写入excel并填充内容,一个sheet只能写65536行以下,超出会报异常,写入时建议使用AbstractExcel2007Writer * @param fileName * @throws IOException */ public static void writeExcel(String fileName) throws IOException{
// 创建excel2003对象
Workbook wb = new HSSFWorkbook();
// 设置文件放置路径和文件名
FileOutputStream fileOut = new FileOutputStream(fileName); // 创建新的表单
Sheet sheet = wb.createSheet(\ // 创建新行
for(int i=0;i<20000;i++){ Row row = sheet.createRow(i); // 创建单元格 Cell cell = row.createCell(0);
// 设置单元格值 cell.setCellValue(1); row.createCell(1).setCellValue(1+i); row.createCell(2).setCellValue(true); row.createCell(3).setCellValue(0.43d); row.createCell(4).setCellValue('d'); row.createCell(5).setCellValue(\ row.createCell(6).setCellValue(\第七列\ row.createCell(7).setCellValue(\第八列\ } wb.write(fileOut); fileOut.close(); } } /**
* 抽象excel2007读入器,先构建.xlsx一张模板,改写模板中的sheet.xml,使用这种方法 * 写入.xlsx文件,不需要太大的内存 * */
public abstract class AbstractExcel2007Writer { private SpreadsheetWriter sw; /**
* 写入电子表格的主要流程 * @param fileName * @throws Exception */
public void process(String fileName) throws Exception{
// 建立工作簿和电子表格对象
XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(\
// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml String sheetRef = sheet.getPackagePart().getPartName().getName(); // 保存模板
FileOutputStream os = new FileOutputStream(\wb.write(os); os.close();
// 生成xml文件
} /**
File tmp = File.createTempFile(\Writer fw = new FileWriter(tmp); sw = new SpreadsheetWriter(fw); generate(); fw.close();
// 使用产生的数据替换模板
File templateFile = new File(\
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(\
Enumeration
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(\ public SpreadsheetWriter(Writer out) { _out = out; } public void beginSheet() throws IOException { _out.write(\ + xmlns=\\\ _out.write(\ } public void endSheet() throws IOException { _out.write(\ _out.write(\ } /** * 插入新行 * * @param rownum 以0开始 */ public void insertRow(int rownum) throws IOException { _out.write(\ this._rownum = rownum; } /** * 插入行结束标志 */ public void endRow() throws IOException { _out.write(\ } /** * 插入新列
\
* @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(\ if (styleIndex != -1) _out.write(\ _out.write(\ _out.write(\ _out.write(\}
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(\ if (styleIndex != -1) _out.write(\ _out.write(\ _out.write(\ _out.write(\}
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); }
} }
public class Excel2007WriterImpl extends AbstractExcel2007Writer{ /** * @param args * @throws Exception */ public static void main(String[] args) throws Exception { // TODO Auto-generated method stub System.out.println(\ long start = System.currentTimeMillis();
} /*
//构建excel2007写入器
AbstractExcel2007Writer excel07Writer = new Excel2007WriterImpl(); //调用处理方法
excel07Writer.process(\long end = System.currentTimeMillis();
System.out.println(\
* 可根据需求重写此方法,对于单元格的小数或者日期格式,会出现精度问题或者日期格式转化问题,建议使用字符串插入方法 * @see com.excel.ver2.AbstractExcel2007Writer#generate() */ @Override public void generate()throws Exception { //电子表格开始 beginSheet();
for (int rownum = 0; rownum < 100; rownum++) { //插入新行
insertRow(rownum);
//建立新单元格,索引值从0开始,表示第一列 createCell(0, \中国<\ createCell(1, 34343.123456789); createCell(2, \ createCell(3, \
createCell(4, \ createCell(5, \ createCell(6, \
//结束行 endRow(); }
//电子表格结束 endSheet(); } }
public class XMLEncoder {
private static final String[] xmlCode = new String[256];
static {
// Special characters xmlCode['\\''] = \
xmlCode['\\\ xmlCode['&'] = \ xmlCode['<'] = \ xmlCode['>'] = \ }
/** *
* Encode the given text into xml. *
** @param string the text to encode * @return the encoded string */
public static String encode(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(); } }
可以参见转载文章:http://blog.csdn.net/goodkuang2012/article/details/7350985
正在阅读:
POI读写海量Excel(详细解读)05-27
小学一年级数学上册试题常用字(一)带拼音05-31
一次函数知识点、经典例题、练习06-13
区审计局上半年推动审计监督工作部署07-30
2013中公heoffcn公务员考试行测真题及答案解析05-20
我们为什么一定要发展足球05-20
吉他软件 中文版下载08-15
市场分析:黄岩模具 塑料模具企业群集01-03
2017—2018学年语文S版五年级语文下册期中考试试卷09-09
模具制造工艺课程设计 - 图文10-27
- 多层物业服务方案
- (审判实务)习惯法与少数民族地区民间纠纷解决问题(孙 潋)
- 人教版新课标六年级下册语文全册教案
- 词语打卡
- photoshop实习报告
- 钢结构设计原理综合测试2
- 2014年期末练习题
- 高中数学中的逆向思维解题方法探讨
- 名师原创 全国通用2014-2015学年高二寒假作业 政治(一)Word版
- 北航《建筑结构检测鉴定与加固》在线作业三
- XX县卫生监督所工程建设项目可行性研究报告
- 小学四年级观察作文经典评语
- 浅谈110KV变电站电气一次设计-程泉焱(1)
- 安全员考试题库
- 国家电网公司变电运维管理规定(试行)
- 义务教育课程标准稿征求意见提纲
- 教学秘书面试技巧
- 钢结构工程施工组织设计
- 水利工程概论论文
- 09届九年级数学第四次模拟试卷
- 读写
- 海量
- 解读
- 详细
- Excel
- POI
- 银行业风险案例精析2013年4月 - 图文
- 四川省成都市高新区2017 - 2018学年九年级语文9月月考试题新人教
- 2018年郑州市初中毕业年级适应性测试
- matlab四合一(dream)
- 微课比武主持讲稿
- 汉英口译分类词汇 - 热门话题词汇
- 北京理工大学远程教育学院应用文写作
- 辩论 奋斗和机遇哪个更重要(函大学生创业资料)
- 材料与工艺调研报告 - 图文
- 某高层建筑物供配电系统设计
- 临沂市燃气管理办法
- 《c++程序设计》谭浩强 答案 - 第05章
- 技术管理办法27
- 移动通信光缆线路工程施工技术标准和要求
- 2013中级会计财务管理课件 第五章 筹资管理(下)(完整版)
- 用友U9权限管理
- 化工厂禁止吸烟管理规定
- 基于PROE小型插齿机的设计
- 桩基础施工组织设计(机场)
- 宿舍楼综合布线方案书(内含CAD - Visio整套图纸)