poi读取大数据量excel文件，避免内存溢出，行级操作

package com.telesound.dataimport; import java.io.File; import java.io.IOException; import java.lang.reflect.Constructor; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import com.telesound.dataimport.excel.ExcelProcessor; import com.telesound.dataimport.excel.XCell; import com.telesound.dataimport.excel.XRow; import com.telesound.dataimport.exception.CellContentTooLongException; import com.telesound.dataimport.exception.ColumnRuleNotFoundException; import com.telesound.dataimport.exception.CustomProduceMethodNotFoundException; import com.telesound.dataimport.exception.DataImportException; import com.telesound.dataimport.exception.DataLengthOutofBoundException; import com.telesound.dataimport.exception.DataTypeNotMatchException; import com.telesound.dataimport.exception.DateFormatNotFoundException; import com.telesound.dataimport.exception.DicItemNotFoundException; import com.telesound.dataimport.exception.EmptyDefaultValueException; import com.telesound.dataimport.exception.ErrorDataListException; import com.telesound.dataimport.exception.ExcelExistSameColumnException; import com.telesound.dataimport.exception.FromListNotFoundException; import com.telesound.dataimport.exception.NoValueOfDictionaryElementException; import com.telesound.dataimport.exception.NullCellValueException; import com.telesound.dataimport.exception.TableRuleNotFoundException; import com.telesound.dataimport.util.StringUtil; import com.telesound.dataimport.util.variable.DataTypeVariable; import com.telesound.dataimport.util.variable.ProduceMethodVariable; /** * Excel导入器，是整个Excel导入的核心类 * @author zhangchaofeng * @version 1.0 * @date Sep 28, 2011 */ public class ExcelImporter{ private String uuid; private String fileName; private int sheetIndex=-1; private ExcelProcessorThread p; private Thread processorThread; private DataImportTableRule tableRule; // 应用的导入表规则 private boolean writeLog=false; private String loggerFile; private static Map<String,ExcelImporter> importerMap=new HashMap<String,ExcelImporter>(); public ExcelImporter(String uuid,String fileName,String tableRuleId,boolean writeLog,String loggerFile) throws Exception { this.tableRule=DataImportTableRule.getTableRule(tableRuleId); if(this.tableRule==null){ throw new TableRuleNotFoundException(tableRuleId); } this.uuid=uuid; this.fileName=fileName; this.writeLog=writeLog; this.loggerFile=loggerFile; } private Thread getProcessorThread(){ return this.processorThread; } /** * 导入所有sheet的数据 * @throws Exception */ public void importAllSheetData() throws Exception { this.sheetIndex=-1; ExcelProcessorThread thread=new ExcelProcessorThread(fileName); this.p=thread; this.processorThread=new Thread(thread); processorThread.start(); } /** * 导入指定sheet的数据 * @param sheetIndex * @throws Exception */ public void importData(int sheetIndex) throws Exception { this.sheetIndex=sheetIndex; ExcelProcessorThread thread=new ExcelProcessorThread(fileName); this.p=thread; this.processorThread=new Thread(thread); processorThread.start(); } /** * 导入第一个sheet的数据 * @throws Exception */ public void importFirstSheetData() throws Exception { importData(1); } /** * 暂停导入数据 * @throws InterruptedException */ public static void suspend(String uuid) throws InterruptedException{ ExcelImporter importer=importerMap.get(uuid); if(importer!=null){ //importer.processorThread.wait(); importer.p.setRunFlag(false); } } /** * 继续导入数据 */ public static void resume(String uuid){ ExcelImporter importer=importerMap.get(uuid); if(importer!=null){ //importer.processorThread.notifyAll(); importer.p.setRunFlag(true); } } /** * 停止导入数据 */ public static void stop(String uuid){ ExcelImporter importer=importerMap.get(uuid); if(importer!=null){ importer.processorThread.interrupt(); try { importer.p.stop(); } catch (IOException e) { e.printStackTrace(); } } importerMap.remove(uuid); } public static ImportResult getProcess(String uuid){ ExcelImporter importer=importerMap.get(uuid); if(importer==null){ return null; } ImportResult result=new ImportResult(); ExcelProcessorThread pp=importer.p; result.setCorrentRow(pp.correntRowIndex+""); result.setFailed(pp.failedCount); result.setSuccess(pp.successCount); if(pp.complete){ result.setComplete(true); result.setCorrentRow("结束"); File excel=new File(importer.fileName); if(excel.exists()){ excel.delete(); } } return result; } public static String getProcessMessage(String uuid){ ExcelImporter importer=importerMap.get(uuid); if(importer==null){ return null; } List importMessages=importer.p.importMessages; StringBuffer message=new StringBuffer(""); for(int i=0;i<importMessages.size();i++){ message.append(importMessages.get(i)+"<br>"); } return message.toString(); } private class ExcelProcessorThread extends ExcelProcessor implements Runnable{ private StringBuffer sql1=new StringBuffer("insert into "+tableRule.getTableName()+" ("); private StringBuffer sql2=new StringBuffer(") values ("); private String sql3=")"; private int correntRowIndex=0; private int correntColumnIndex=0; private int successCount=0; private int failedCount=0; private boolean runFlag=true; private boolean complete=false; private Map<Integer,String> headerNames; private Map<String,String> columnValues; private Map<String,DataImportColumnRule> pkColumnRules; private List importMessages=new ArrayList(); public ExcelProcessorThread(String fileName) throws Exception { super(fileName); this.headerNames=new HashMap<Integer,String>(); this.columnValues=new HashMap<String,String>(); this.pkColumnRules=new HashMap<String,DataImportColumnRule>(); } public void setRunFlag(boolean b){ this.runFlag=b; } /** * 导入策略判断 * @param value * @param columnRule * @throws NullCellValueException * @throws DicItemNotFoundException * @throws NoValueOfDictionaryElementException * @throws CellContentTooLongException * @throws DataTypeNotMatchException * @throws EmptyDefaultValueException * @throws DataLengthOutofBoundException * @throws DateFormatNotFoundException * @throws CustomProduceMethodNotFoundException * @throws FromListNotFoundException * @throws ErrorDataListException */ private void step(String value,DataImportColumnRule columnRule) throws DataTypeNotMatchException, CellContentTooLongException, NoValueOfDictionaryElementException, DicItemNotFoundException, NullCellValueException, EmptyDefaultValueException, DataLengthOutofBoundException, DateFormatNotFoundException, CustomProduceMethodNotFoundException, FromListNotFoundException, ErrorDataListException{ String columnName=columnRule.getColumnName(); String dataType=columnRule.getDataType(); int maxLength=columnRule.getMaxLength(); String method=columnRule.getProduceMethod(); String produceMethodValue=columnRule.getProduceMethodValue(); if(value!=null){ value=value.trim(); } //默认值时的处理策略 if(ProduceMethodVariable.DEFAULT_VALUE.equals(method)){ String defaultValue=columnRule.getProduceMethodValue().trim(); if(!StringUtil.hasValue(defaultValue)){ if(columnRule.getRequired()){ throw new EmptyDefaultValueException("列规则生成策略为默认值，但实际默认值为空，columnName："+columnName+"，"+correntRowIndex+"行，"+