大数据量的excel读取poi实际应用

1、HxlsAbstract.java

 

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NoteRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.RKRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.StringRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * @项目名：保密
 * @包名：保密
 * @文件名：HxlsAbstract.java
 * @日期：Dec 24, 2010 10:54:52 AM
 * @备注：POI导入excel（大数据量）
 * @作者：apple
 */
public abstract class HxlsAbstract implements HSSFListener {
	private int minColumns;
	private POIFSFileSystem fs;
	private PrintStream output;

	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;
	private HSSFWorkbook stubWorkbook;

	// Records we pick up as we process
	private SSTRecord sstRecord;
	private FormatTrackingHSSFListener formatListener;

	/** So we known which sheet we're on */
	private int sheetIndex = -1;
	private BoundSheetRecord[] orderedBSRs;
	@SuppressWarnings("unchecked")
	private ArrayList boundSheetRecords = new ArrayList();

	// For handling formulas with string results
	private int nextRow;
	private int nextColumn;
	private boolean outputNextStringRecord;

	private int curRow;
	private List<String> rowlist;
	@SuppressWarnings( "unused")
	private String sheetName;

	public HxlsAbstract(POIFSFileSystem fs)
			throws SQLException {
		this.fs = fs;
		this.output = System.out;
		this.minColumns = -1;
		this.curRow = 0;
		this.rowlist = new ArrayList<String>();
	}

	public HxlsAbstract(String filename) throws IOException,
			FileNotFoundException, SQLException {
		this(new POIFSFileSystem(new FileInputStream(filename)));
	}
	
	//excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型
//	public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;
	
	//excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型
	public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;
	
	/**
	 * 遍历 excel 文件
	 */
	public void process() throws IOException {
		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("unchecked")
	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) {
				// Create sub workbook if required
				if (workbookBuildingListener != null && stubWorkbook == null) {
					stubWorkbook = workbookBuildingListener
							.getStubHSSFWorkbook();
				}

				// Works by ordering the BSRs by the location of
				// their BOFRecords, and then knowing that we
				// process BOFRecords in byte offset order
				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 = "";
			break;
		case BoolErrRecord.sid:
			BoolErrRecord berec = (BoolErrRecord) record;

			thisRow = berec.getRow();
			thisColumn = berec.getColumn();
			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 = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook,
						frec.getParsedExpression()) + '"';
			}
			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("")?" ":value;
			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("")?" ":value;
				rowlist.add(thisColumn,value);
			}
			break;
		case NoteRecord.sid:
			NoteRecord nrec = (NoteRecord) record;

			thisRow = nrec.getRow();
			thisColumn = nrec.getColumn();
			// TODO: Find object to match nrec.getShapeId()
			thisStr = '"' + "(TODO)" + '"';
			break;
		case NumberRecord.sid:
			NumberRecord numrec = (NumberRecord) record;

			curRow = thisRow = numrec.getRow();
			thisColumn = numrec.getColumn();
			value = formatListener.formatNumberDateCell(numrec).trim();
			value = value.equals("")?" ":value;
			// Format
			rowlist.add(thisColumn, value);
			break;
		case RKRecord.sid:
			RKRecord rkrec = (RKRecord) record;

			thisRow = rkrec.getRow();
			thisColumn = rkrec.getColumn();
			thisStr = '"' + "(TODO)" + '"';
			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 (thisStr != null) {
			if (thisColumn > 0) {
				output.print(',');
			}
			output.print(thisStr);
		}

		// 更新行和列的值
		if (thisRow > -1)
			lastRowNumber = thisRow;
		if (thisColumn > -1)
			lastColumnNumber = thisColumn;

		// 行结束时的操作
		if (record instanceof LastCellOfRowDummyRecord) {
			if (minColumns > 0) {
				// 列值重新置空
				if (lastColumnNumber == -1) {
					lastColumnNumber = 0;
				}
			}
			// 行结束时， 调用 optRows() 方法
			lastColumnNumber = -1;
			try {
				optRows(sheetIndex,curRow, rowlist);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rowlist.clear();
		}
	}
}

 

2、SPImportAction.java

 

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.hlj.cms.gnet.model.SPModel;
import net.hlj.cms.gnet.service.SPImportService;
import net.hlj.cms.gnet.service.imp.SPImportServiceImpl;
import net.hlj.cms.gnet.util.HxlsAbstract;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

/**
 * @项目名：保密
 * @包名：保密
 * @文件名：SPImportAction.java
 * @日期：Dec 23, 2010 3:54:16 PM
 * @备注：增值业务导入
 * @作者：apple
 */
public class SPImportAction extends HxlsAbstract{

	private static String fileName="增值业务(测试库).xls";//文件名
	private static String path="D:\\net\\";//路径
	private ApplicationContext ctx = new FileSystemXmlApplicationContext("/WebRoot/WEB-INF/applicationContext.xml");//spring 上下文
	private SPImportService server=new SPImportServiceImpl();//接口
	private static int count=0;
	private static int successCount=0;
	private static int failCount=0;
	private static ArrayList errorList=new ArrayList();
	/**
	 * @param args
	 */
	public static void main(String[] args) 
	{
		SPImportAction excel;//excel对象
		try 
		{
			excel=new SPImportAction(path+fileName);
			excel.process();
			System.out.println("一共："+count+"条数据");
			System.out.println("成功："+successCount+"条数据");
			System.out.println("失败："+failCount+"条数据");
			for(int i=0;i<errorList.size();i++)
			{
				System.out.println("失败行数："+errorList.get(i));
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
  @Override  
  public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException 
  {   
	  String row="";
	  if(curRow>0)
	  {
		  if( rowlist.size()==8)
		  {
			  for (int i = 0 ;i< rowlist.size();i++){   
				  row+=rowlist.get(i)+",";
		      }
			  SPModel obj=new SPModel();
			  obj.setPRODUCTID(rowlist.get(1));
			  obj.setV_busiCode(rowlist.get(2));
			  obj.setV_busiName(rowlist.get(4));
			  obj.setV_busiDes(rowlist.get(5));
			  obj.setEFFDATE(rowlist.get(6));
			  obj.setEXPDATE(rowlist.get(7));
			  if(rowlist.get(3).equals("4"))
			  {
				  obj.setFlag("1");
			  }
			  else
			  {
				  obj.setFlag("0");
			  }
			  
			  if(server.isSP(obj, ctx))
			  {
				  int uflag=server.SPUpdate(obj, ctx);
				  if(uflag>0)
				  {
					  successCount++;
					  System.out.println("第"+rowlist.get(0)+"行数据修改成功");
				  }
				  else
				  {
					  failCount++;
					  System.out.println("第"+rowlist.get(0)+"行数据修改失败");
					  errorList.add(rowlist.get(0));
				  }
			  }
			  else
			  {
				  int aflag=server.SPAdd(obj, ctx);
				  if(aflag>0)
				  {
					  successCount++;
					  System.out.println("第"+rowlist.get(0)+"行数据新增成功"); 
				  }
				  else
				  {
					  failCount++;
					  System.out.println("第"+rowlist.get(0)+"行数据新增失败");
					  errorList.add(rowlist.get(0));
				  }
			  }
			  count++;
			  System.out.println(row.substring(0,row.length()-1)); 
		  }
	  }
	  
	  
	  
  }   
  public SPImportAction(String filename) throws IOException,FileNotFoundException, SQLException 
  {   
	  super(filename);   
  }   
}