本文共 13858 字，大约阅读时间需要 46 分钟。

1、创建导入抽象类

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
   
     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
    
     ();    }    public HxlsAbstract(String filename) throws IOException,            FileNotFoundException, SQLException {        this(new POIFSFileSystem(new FileInputStream(filename)));    }    //excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型//  public abstract void optRows(int curRow, List
     
       rowlist) throws SQLException ;    //excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型    public abstract void optRows(int sheetIndex,int curRow, List
      
        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 监听方法，处理 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;        // 行结束时的操作        if (record instanceof LastCellOfRowDummyRecord) {            if (minColumns > 0) {                // 列值重新置空                if (lastColumnNumber == -1) {                    lastColumnNumber = 0;                }            }            // 行结束时， 调用 optRows() 方法            lastColumnNumber = -1;            try {                optRows(sheetIndex,curRow, rowlist);            } catch (Exception e) {                e.printStackTrace();            }            rowlist.clear();        }    }}
      
     
    
   

2、创建导入接口

package com.gcloud.common.excel;import java.util.List;public interface HxlsOptRowsInterface {
       public static final String SUCCESS="success";    /**     * 处理excel文件每行数据方法     * @param sheetIndex     * @param curRow     * @param rowlist     * @return success：成功，否则为失败原因     * @throws Exception     */    public String optRows(int sheetIndex, int curRow, List
   
     rowlist) throws Exception;}
   

3、创建实现类， 在这个方法实现把导入的数据添加到数据库中

package com.gcloud.common.excel;import java.util.List;public class HxlsInterfaceImpl implements HxlsOptRowsInterface {
       @Override    public String optRows(int sheetIndex, int curRow, List
   
     datalist)            throws Exception {        //在这里执行数据的插入        //System.out.println(rowlist);        //saveData(datalist);        return "";    }}
   

4、导入工具实现

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{
       //数据处理bean    private  HxlsOptRowsInterface hxlsOptRowsInterface;    //处理数据总数    private int optRows_sum = 0;    //处理数据成功数量    private int optRows_success = 0;    //处理数据失败数量    private int optRows_failure = 0;    //excel表格每列标题    private List
   
     rowtitle ;    //失败数据    private List
    
     
      > failrows;    //失败原因    private List
      
        failmsgs ;    //要处理数据所在的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
       
        ();        this.failrows = new ArrayList
        
         
          >(); this.failmsgs = new ArrayList
          
           (); } @Override public void optRows(int sheetIndex,int curRow, List
           
             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
            
             (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
             
               getRowtitle() { return rowtitle; } public List
              
               
                > getFailrows() { return failrows; } public List
                
                  getFailmsgs() { return failmsgs; } public void setFailmsgs(List
                 
                   failmsgs) { this.failmsgs = failmsgs; }}
                 
                
               
              
             
            
           
          
         
        
       
      
     
    
   

5、导入实现方法：

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();        }    }

———————————————————————

https://item.taobao.com/item.htm?spm=686.1000925.0.0.4a155084hc8wek&id=555888526201

01.高级架构师四十二个阶段高

转载地址：https://lovoo.blog.csdn.net/article/details/77905613 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！