package com.gcloud.common.excel;
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.EventWorkbookBuilder.SheetRecordCollectingListener;
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.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;
/**
 * 導入抽象類
 * Created by charlin on 2017/9/7.
 */
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記錄行操作方法，以行索引和行元素列表為參數(shù)，對一行元素進行操作，元素為String類型
// public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;
  //excel記錄行操作方法，以sheet索引，行索引和行元素列表為參數(shù)，對sheet的一行元素進行操作，元素為String類型
  public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws Exception;
  /**
   * 遍歷 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 監(jiān)聽方法，處理 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;
      //進入sheet
      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;
    // 行結(jié)束時的操作
    if (record instanceof LastCellOfRowDummyRecord) {
      if (minColumns > 0) {
        // 列值重新置空
        if (lastColumnNumber == -1) {
          lastColumnNumber = 0;
        }
      }
      // 行結(jié)束時， 調(diào)用 optRows() 方法
      lastColumnNumber = -1;
      try {
        optRows(sheetIndex,curRow, rowlist);
      } catch (Exception e) {
        e.printStackTrace();
      }
      rowlist.clear();
    }
  }
}

package com.gcloud.common.excel;
import java.util.List;
public interface HxlsOptRowsInterface {
  public static final String SUCCESS="success";
  /**
   * 處理excel文件每行數(shù)據(jù)方法
   * @param sheetIndex
   * @param curRow
   * @param rowlist
   * @return success：成功，否則為失敗原因
   * @throws Exception
   */
  public String optRows(int sheetIndex, int curRow, List<String> rowlist) throws Exception;
}

package com.gcloud.common.excel;
import java.util.List;
public class HxlsInterfaceImpl implements HxlsOptRowsInterface {
  @Override
  public String optRows(int sheetIndex, int curRow, List<String> datalist)
      throws Exception {
    //在這里執(zhí)行數(shù)據(jù)的插入
    //System.out.println(rowlist);
    //saveData(datalist);
    return "";
  }
}

package com.gcloud.common.excel;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * excel導入工具
 * Created by charlin on 2017/9/7.
 */
public class ExcelImportUtil extends HxlsAbstract{
  //數(shù)據(jù)處理bean
  private HxlsOptRowsInterface hxlsOptRowsInterface;
  //處理數(shù)據(jù)總數(shù)
  private int optRows_sum = 0;
  //處理數(shù)據(jù)成功數(shù)量
  private int optRows_success = 0;
  //處理數(shù)據(jù)失敗數(shù)量
  private int optRows_failure = 0;
  //excel表格每列標題
  private List<String> rowtitle ;
  //失敗數(shù)據(jù)
  private List<List<String>> failrows;
  //失敗原因
  private List<String> failmsgs ;
  //要處理數(shù)據(jù)所在的sheet索引,從0開始
  private int sheetIndex;
  public ExcelImportUtil(String filename, int sheetIndex, HxlsOptRowsInterface hxlsOptRowsInterface) throws IOException,
      FileNotFoundException, SQLException {
    super(filename);
    this.sheetIndex = sheetIndex;
    this.hxlsOptRowsInterface = hxlsOptRowsInterface;
    this.rowtitle = new ArrayList<String>();
    this.failrows = new ArrayList<List<String>>();
    this.failmsgs = new ArrayList<String>();
  }
  @Override
  public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws Exception {
    /*for (int i = 0 ;i< rowlist.size();i++){
      System.out.print("'"+rowlist.get(i)+"',");
    }
    System.out.println();*/
    //將rowlist的長度補齊和標題一致
    int k=rowtitle.size()-rowlist.size();
    for(int i=0;i<k;i++){
      rowlist.add(null);
    }
    if(sheetIndex == this.sheetIndex){
      optRows_sum++;
      if(curRow == 0){//記錄標題
        rowtitle.addAll(rowlist);
      }else{
        String result = hxlsOptRowsInterface.optRows(sheetIndex, curRow, rowlist);
        if(!result.equals(hxlsOptRowsInterface.SUCCESS)){
          optRows_failure++;
          //失敗數(shù)據(jù)
          failrows.add(new ArrayList<String>(rowlist));
          failmsgs.add(result);
        }else{
          optRows_success++;
        }
      }
    }
  }
  public long getOptRows_sum() {
    return optRows_sum;
  }
  public void setOptRows_sum(int optRows_sum) {
    this.optRows_sum = optRows_sum;
  }
  public long getOptRows_success() {
    return optRows_success;
  }
  public void setOptRows_success(int optRows_success) {
    this.optRows_success = optRows_success;
  }
  public long getOptRows_failure() {
    return optRows_failure;
  }
  public void setOptRows_failure(int optRows_failure) {
    this.optRows_failure = optRows_failure;
  }
  public List<String> getRowtitle() {
    return rowtitle;
  }
  public List<List<String>> getFailrows() {
    return failrows;
  }
  public List<String> getFailmsgs() {
    return failmsgs;
  }
  public void setFailmsgs(List<String> failmsgs) {
    this.failmsgs = failmsgs;
  }
}

public static void main(String[] args){
    ExcelImportUtil importUtil;
    try {
      importUtil = new ExcelImportUtil("d:/data.xls",0, new HxlsInterfaceImpl());
      importUtil.process();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }
}