如何比较POI读取Excel xls大文件两种方法的处理速度及数据操作技巧？

本文共计1742个文字，预计阅读时间需要7分钟。

java可读性增强的代码开头内容：javaimport org.apache.poi.ss.usermodel.*;import org.apache.poi.hssf.eventusermodel.HSSFListener;import org.apache.poi.hssf.eventusermodel.*;import org.apache.poi.hssf.record.*;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import java.io.FileInputStream;

package excel.POI; import org.apache.poi.hssf.eventusermodel.HSSFListener; import org.apache.poi.hssf.eventusermodel.*; import org.apache.poi.hssf.record.*; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.Iterator; import java.util.LinkedList; import java.util.List; /** * 只可读类型的XlsMethod，比可读写快（比例：20/13 */ public class XlsReadOnlyMethod implements HSSFListener { private SSTRecord sstRec = null; private List iterList = new LinkedList (); private String[] tmpStr_ = null; private int colLastRealIndex = 0; //总列 private int rowLastRealIndex = 0; //总行 private int thisRow = 0; // 当前行指针 private boolean helpThisRow = true; private boolean shouldInit = true; private static int sheetAt = 0; // 传入的sheet，不用static会丢失值 private int count = 0; // 用来计数是第几个sheet private boolean isActiveSheet = false; // 用来标记是第几个sheet private static FormatTrackingHSSFListener formatListener; public static void main(String[] args) { try { Long start = System.currentTimeMillis(); String filePath = "F:\\temp\\bigexcel\\x1.xls"; XlsReadOnlyMethod xlsReadOnlyMethod = new XlsReadOnlyMethod(); Iterator iter = xlsReadOnlyMethod.RowIterator(filePath, 0); Long end = System.currentTimeMillis(); System.out.println("spending time = " + (end - start)); } catch (IOException e) { e.printStackTrace(); } } /** * sheet想调哪张调哪张。操作流程： * 1. 构造HSSFRequest对象 * 2. 使用输入流让事件工厂运作，直接监听到读取数据的事件，在监听processRecord中拿数据。 */ public Iterator RowIterator(String filePath,int sheetAt) throws IOException { this.sheetAt = sheetAt; FileInputStream fin = new FileInputStream(filePath); InputStream inputStream = new POIFSFileSystem(fin).createDocumentInputStream("Workbook"); HSSFRequest hssfRequest = new HSSFRequest(); // hssfRequest.addListenerForAllRecords(new XlsReadOnlyMethod(this.getSource())); // add listener HSSFEventFactory hssfEventFactory = new HSSFEventFactory(); // 使用formatListener可以分别获得date和double MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this); formatListener = new FormatTrackingHSSFListener(listener); hssfRequest.addListenerForAllRecords(formatListener); hssfEventFactory.processEvents(hssfRequest, inputStream); if(null != tmpStr_) if(0 != tmpStr_.length) { iterList.add(tmpStr_); } fin.close(); inputStream.close(); return iterList.iterator(); } /** * 实现HSSFListener的方法，获取xls的内容 * eg : poi.apache.org/spreadsheet/how-to.html * */ /** * 1. 初始化 * 获取一行所有列 * 设置 * 初始化tmpStr_ * 设置初始化在每行只走一次 === 3/2 * 2. 获取值 * 把值丢到tmpStr_里（空的就不丢 * 3. 换行 * tmpStr_加到iterList里去 * 如果跳到下一行，我就换行，并初始化1/3 * 4. 循环 * 到最后一行的时候跳出来，我再执行一边添加。 * */ public void processRecord(Record record) { switch (record.getSid()) { // the BOFRecord can represent either the beginning of a sheet or the workbook case BOFRecord.sid: BOFRecord bof = (BOFRecord) record; if (bof.getType() == bof.TYPE_WORKSHEET) { //遇到sheet if(count++ == sheetAt) { isActiveSheet = true; } else { isActiveSheet = false; } } break; case RowRecord.sid: // 用来初始化当前行有几个cell if(isActiveSheet) { if(null != tmpStr_) { iterList.add(tmpStr_); } RowRecord rowrec = (RowRecord) record; // 可以获得行列的最大值。 如果有错就给后面的数字加大。 colLastRealIndex = rowrec.getLastCol() - rowrec.getFirstCol() + 5; rowLastRealIndex = rowrec.getRowNumber(); if(helpThisRow) { //初始化当前行指针 thisRow = rowrec.getRowNumber(); shouldInit = true; helpThisRow = false; } // System.out.println("我要判定 " + rowrec.getRowNumber() + " 行有" + (colLastRealIndex)+ "个cell了"); } break; /** * 不管是哪条路，只要第一次跑到 就开始 * */ case NumberRecord.sid: // Date int double if(isActiveSheet) { NumberRecord numrec = (NumberRecord) record; //1.如果是下一行，2.如果是第一次运行，我就初始化 checkNextRowAndInit(numrec); tmpStr_[numrec.getColumn()] = formatListener.formatNumberDateCell(numrec); // System.out.println("Cell found with value " + formatListener.formatNumberDateCell(numrec) // + " at row " + numrec.getRow() + " and column " + numrec.getColumn()); } break; case FormulaRecord.sid: // formula if(isActiveSheet) { FormulaRecord forRec = (FormulaRecord) record; checkNextRowAndInit(forRec); tmpStr_[forRec.getColumn()] = String.valueOf(forRec.getValue()); // System.out.println("Fomula found with value " + forRec.getValue() + " at row " + forRec.getRow() + " and column " + forRec.getColumn()); } break; // SSTRecords store a array of unique strings used in Excel. case SSTRecord.sid: sstRec = (SSTRecord) record; // to get SSTRecord break; case LabelSSTRecord.sid: if(isActiveSheet) { LabelSSTRecord lrec = (LabelSSTRecord) record; checkNextRowAndInit(lrec); tmpStr_[lrec.getColumn()] = String.valueOf(sstRec.getString(lrec.getSSTIndex())); // System.out.println("String cell found with value " // + sstRec.getString(lrec.getSSTIndex()) + " at row " + lrec.getRow() // + " and column " + lrec.getColumn()); } break; } } private void checkNextRowAndInit(CellRecord record) { if(thisRow != record.getRow()) { // 已经到下一行了 thisRow = record.getRow(); iterList.add(tmpStr_); // 添加tmpStr_ shouldInit = true; } if(shouldInit) { tmpStr_ = new String[colLastRealIndex]; shouldInit = false; } } } 可写

package excel.POI; import org.apache.commons.lang.time.DateFormatUtils; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.*; import java.io.File; import java.io.FileInputStream; import java.util.Iterator; import java.util.LinkedList; import java.util.List; /** * 可读可写的Xls/Xlsx * */ public class XlsReadWriteMethod { private String filePath; XlsReadWriteMethod(String filePath) { this.filePath = filePath; } public static void main(String[] args) { try { Long start = System.currentTimeMillis(); String filePath = "F:\\temp\\bigexcel\\x1.xls"; XlsReadWriteMethod xlsReadWriteMethod = new XlsReadWriteMethod(filePath); Iterator iter = xlsReadWriteMethod.RowIterator(0); Long end = System.currentTimeMillis(); System.out.println("get data time = " + (end - start)); } catch (Exception e) { e.printStackTrace(); } } /** * 迭代FileBean的每一行 * */ public Iterator RowIterator(int sheetAt) throws Exception { Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(filePath))); Sheet sheet = workbook.getSheetAt(sheetAt); //get index of sheet List list = new LinkedList (); for (int i = 0; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if(null == row) continue; // 这一行是空行：没东西 int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); String[] oneRowValue = new String[lastCellNum - firstCellNum]; int countNoneCol = 0; for (int j = firstCellNum; j < lastCellNum; j++) { Cell cell = row.getCell(j); int thisCol = j - firstCellNum; if(null == cell) { oneRowValue[thisCol] = ""; continue; } switch (cell.getCellType()) // Cell.getCellType() has not deprecated in API { // Because of Cell.getCellType().getCode() has deprecated And do not provide a replacement way So just use direct ways case -1 : // _NONE oneRowValue[thisCol] = ""; break; case 0 : // NUMERIC Date short dataFormat = cell.getCellStyle().getDataFormat(); // 31/176 : yyyy年m月d日（自定义型和日期型） ， 58 : m月d日 ， 32 : h时mm分 // 有时候176也是数字型，那这样会报错，干脆直接抓个异常。总之抓一堆dateFormat来兼容就好了。 if(HSSFDateUtil.isCellDateFormatted(cell) || HSSFDateUtil.isCellDateFormatted(cell) || 31 == dataFormat || 58 == dataFormat || 32 == dataFormat || 176 == dataFormat) { try { oneRowValue[thisCol] = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd hh:MM:ss"); }catch ( Exception e) { try { cell.setCellType(CellType.STRING); }catch (Exception ex) { //出问题就不转 } oneRowValue[thisCol] = String.valueOf(cell.getStringCellValue()); } } else { try { cell.setCellType(CellType.STRING); }catch (Exception ex) { //出问题就不转 } oneRowValue[thisCol] = String.valueOf(cell.getStringCellValue()); } break; case 1 : // STRING oneRowValue[thisCol] = cell.getStringCellValue(); break; case 2 : // FORMULA try { oneRowValue[thisCol] = String.valueOf(cell.getNumericCellValue()); } catch (IllegalStateException e) { try { oneRowValue[thisCol] = String.valueOf(cell.getRichStringCellValue()); } catch (Exception ex){ oneRowValue[thisCol] = ""; } } break; case 3 : // BLANK oneRowValue[thisCol] = ""; break; case 4 : // BOOLEAN oneRowValue[thisCol] = String.valueOf(cell.getBooleanCellValue()); break; case 5 : // ERROR oneRowValue[thisCol] = String.valueOf(cell.getErrorCellValue()); break; default : oneRowValue[thisCol] = ""; break; } // end switch if("".equals(oneRowValue[thisCol])) countNoneCol++; } // end for if(countNoneCol == (lastCellNum - firstCellNum)) continue;//这一行是空行，里面的东西都无效 list.add(oneRowValue); } // end for 2 return list.iterator(); } } 又读又写

package excel.POI; 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.*; import org.xml.sax.helpers.DefaultHandler; import org.xml.sax.helpers.XMLReaderFactory; import java.io.InputStream; import java.util.Iterator; /** * 获取xlsx的读取方法 * */ public class XlsxReadOnlyLoaderTest { public void processOneSheet(String filename) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader( pkg ); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); // To look up the Sheet Name / Sheet Order / rID, // you need to process the core Workbook stream. // Normally it's of the form rId# or rSheet# InputStream sheet2 = r.getSheet("rId1"); InputSource sheetSource = new InputSource(sheet2); parser.parse(sheetSource); sheet2.close(); } public void processAllSheets(String filename) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader( pkg ); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); Iterator sheets = r.getSheetsData(); while(sheets.hasNext()) { System.out.println("Processing new sheet:\n"); InputStream sheet = sheets.next(); InputSource sheetSource = new InputSource(sheet); parser.parse(sheetSource); sheet.close(); System.out.println(""); } } public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException { XMLReader parser = XMLReaderFactory.createXMLReader( "com.sun.org.apache.xerces.internal.parsers.SAXParser" ); ContentHandler handler = new SheetHandler(sst); parser.setContentHandler(handler); return parser; } /** * See org.xml.sax.helpers.DefaultHandler javadocs */ private static class SheetHandler extends DefaultHandler { private SharedStringsTable sst; private String lastContents; private boolean nextIsString; private SheetHandler(SharedStringsTable sst) { this.sst = sst; } public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException { // c => cell if(name.equals("c")) { // Print the cell reference System.out.print(attributes.getValue("r") + " - "); // Figure out if the value is an index in the SST String cellType = attributes.getValue("t"); if(cellType != null && cellType.equals("s")) { nextIsString = true; } else { nextIsString = false; } } // Clear contents cache lastContents = ""; } public void endElement(String uri, String localName, String name) throws SAXException { // Process the last contents as required. // Do now, as characters() may be called more than once if(nextIsString) { int idx = Integer.parseInt(lastContents); lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString(); nextIsString = false; } // v => contents of a cell // Output after we've seen the string contents if(name.equals("v")) { System.out.println(lastContents); } } public void characters(char[] ch, int start, int length) throws SAXException { lastContents += new String(ch, start, length); } } public static void main(String[] args) throws Exception { XlsxReadOnlyLoaderTest example = new XlsxReadOnlyLoaderTest(); example.processOneSheet("F:\\temp\\excel\\2.xlsx"); // example.processAllSheets("F:\\temp\\excel\\2.xlsx"); } } maven_test-master.zip maven_test-master.zip