久久国产成人av_抖音国产毛片_a片网站免费观看_A片无码播放手机在线观看,色五月在线观看,亚洲精品m在线观看,女人自慰的免费网址,悠悠在线观看精品视频,一级日本片免费的,亚洲精品久,国产精品成人久久久久久久

搜索
分享

大數(shù)據(jù)量的excel讀取poi實(shí)際應(yīng)用

 陳湖雨_毓 2012-03-19

1、HxlsAbstract.java

 

Java代碼  收藏代碼
  1. import java.io.FileInputStream;  
  2. import java.io.FileNotFoundException;  
  3. import java.io.IOException;  
  4. import java.io.PrintStream;  
  5. import java.sql.SQLException;  
  6. import java.util.ArrayList;  
  7. import java.util.List;  
  8.   
  9. import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;  
  10. import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;  
  11. import org.apache.poi.hssf.eventusermodel.HSSFListener;  
  12. import org.apache.poi.hssf.eventusermodel.HSSFRequest;  
  13. import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;  
  14. import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;  
  15. import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;  
  16. import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;  
  17. import org.apache.poi.hssf.model.HSSFFormulaParser;  
  18. import org.apache.poi.hssf.record.BOFRecord;  
  19. import org.apache.poi.hssf.record.BlankRecord;  
  20. import org.apache.poi.hssf.record.BoolErrRecord;  
  21. import org.apache.poi.hssf.record.BoundSheetRecord;  
  22. import org.apache.poi.hssf.record.FormulaRecord;  
  23. import org.apache.poi.hssf.record.LabelRecord;  
  24. import org.apache.poi.hssf.record.LabelSSTRecord;  
  25. import org.apache.poi.hssf.record.NoteRecord;  
  26. import org.apache.poi.hssf.record.NumberRecord;  
  27. import org.apache.poi.hssf.record.RKRecord;  
  28. import org.apache.poi.hssf.record.Record;  
  29. import org.apache.poi.hssf.record.SSTRecord;  
  30. import org.apache.poi.hssf.record.StringRecord;  
  31. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
  32. import org.apache.poi.poifs.filesystem.POIFSFileSystem;  
  33.   
  34. /** 
  35.  * @項(xiàng)目名：保密 
  36.  * @包名：保密 
  37.  * @文件名：HxlsAbstract.java 
  38.  * @日期：Dec 24, 2010 10:54:52 AM 
  39.  * @備注：POI導(dǎo)入excel（大數(shù)據(jù)量） 
  40.  * @作者：apple 
  41.  */  
  42. public abstract class HxlsAbstract implements HSSFListener {  
  43.     private int minColumns;  
  44.     private POIFSFileSystem fs;  
  45.     private PrintStream output;  
  46.   
  47.     private int lastRowNumber;  
  48.     private int lastColumnNumber;  
  49.   
  50.     /** Should we output the formula, or the value it has? */  
  51.     private boolean outputFormulaValues = true;  
  52.   
  53.     /** For parsing Formulas */  
  54.     private SheetRecordCollectingListener workbookBuildingListener;  
  55.     private HSSFWorkbook stubWorkbook;  
  56.   
  57.     // Records we pick up as we process  
  58.     private SSTRecord sstRecord;  
  59.     private FormatTrackingHSSFListener formatListener;  
  60.   
  61.     /** So we known which sheet we're on */  
  62.     private int sheetIndex = -1;  
  63.     private BoundSheetRecord[] orderedBSRs;  
  64.     @SuppressWarnings("unchecked")  
  65.     private ArrayList boundSheetRecords = new ArrayList();  
  66.   
  67.     // For handling formulas with string results  
  68.     private int nextRow;  
  69.     private int nextColumn;  
  70.     private boolean outputNextStringRecord;  
  71.   
  72.     private int curRow;  
  73.     private List<String> rowlist;  
  74.     @SuppressWarnings( "unused")  
  75.     private String sheetName;  
  76.   
  77.     public HxlsAbstract(POIFSFileSystem fs)  
  78.             throws SQLException {  
  79.         this.fs = fs;  
  80.         this.output = System.out;  
  81.         this.minColumns = -1;  
  82.         this.curRow = 0;  
  83.         this.rowlist = new ArrayList<String>();  
  84.     }  
  85.   
  86.     public HxlsAbstract(String filename) throws IOException,  
  87.             FileNotFoundException, SQLException {  
  88.         this(new POIFSFileSystem(new FileInputStream(filename)));  
  89.     }  
  90.       
  91.     //excel記錄行操作方法，以行索引和行元素列表為參數(shù)，對(duì)一行元素進(jìn)行操作，元素為String類型  
  92. //  public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;  
  93.       
  94.     //excel記錄行操作方法,，以sheet索引，行索引和行元素列表為參數(shù),，對(duì)sheet的一行元素進(jìn)行操作,，元素為String類型  
  95.     public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;  
  96.       
  97.     /** 
  98.      * 遍歷 excel 文件 
  99.      */  
  100.     public void process() throws IOException {  
  101.         MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(  
  102.                 this);  
  103.         formatListener = new FormatTrackingHSSFListener(listener);  
  104.   
  105.         HSSFEventFactory factory = new HSSFEventFactory();  
  106.         HSSFRequest request = new HSSFRequest();  
  107.   
  108.         if (outputFormulaValues) {  
  109.             request.addListenerForAllRecords(formatListener);  
  110.         } else {  
  111.             workbookBuildingListener = new SheetRecordCollectingListener(  
  112.                     formatListener);  
  113.             request.addListenerForAllRecords(workbookBuildingListener);  
  114.         }  
  115.   
  116.         factory.processWorkbookEvents(request, fs);  
  117.     }  
  118.       
  119.     /** 
  120.      * HSSFListener 監(jiān)聽方法，處理 Record 
  121.      */  
  122.     @SuppressWarnings("unchecked")  
  123.     public void processRecord(Record record) {  
  124.         int thisRow = -1;  
  125.         int thisColumn = -1;  
  126.         String thisStr = null;  
  127.         String value = null;  
  128.           
  129.         switch (record.getSid()) {  
  130.         case BoundSheetRecord.sid:  
  131.             boundSheetRecords.add(record);  
  132.             break;  
  133.         case BOFRecord.sid:  
  134.             BOFRecord br = (BOFRecord) record;  
  135.             if (br.getType() == BOFRecord.TYPE_WORKSHEET) {  
  136.                 // Create sub workbook if required  
  137.                 if (workbookBuildingListener != null && stubWorkbook == null) {  
  138.                     stubWorkbook = workbookBuildingListener  
  139.                             .getStubHSSFWorkbook();  
  140.                 }  
  141.   
  142.                 // Works by ordering the BSRs by the location of  
  143.                 // their BOFRecords, and then knowing that we  
  144.                 // process BOFRecords in byte offset order  
  145.                 sheetIndex++;  
  146.                 if (orderedBSRs == null) {  
  147.                     orderedBSRs = BoundSheetRecord  
  148.                             .orderByBofPosition(boundSheetRecords);  
  149.                 }  
  150.                 sheetName = orderedBSRs[sheetIndex].getSheetname();  
  151.             }  
  152.             break;  
  153.   
  154.         case SSTRecord.sid:  
  155.             sstRecord = (SSTRecord) record;  
  156.             break;  
  157.   
  158.         case BlankRecord.sid:  
  159.             BlankRecord brec = (BlankRecord) record;  
  160.   
  161.             thisRow = brec.getRow();  
  162.             thisColumn = brec.getColumn();  
  163.             thisStr = "";  
  164.             break;  
  165.         case BoolErrRecord.sid:  
  166.             BoolErrRecord berec = (BoolErrRecord) record;  
  167.   
  168.             thisRow = berec.getRow();  
  169.             thisColumn = berec.getColumn();  
  170.             thisStr = "";  
  171.             break;  
  172.   
  173.         case FormulaRecord.sid:  
  174.             FormulaRecord frec = (FormulaRecord) record;  
  175.   
  176.             thisRow = frec.getRow();  
  177.             thisColumn = frec.getColumn();  
  178.   
  179.             if (outputFormulaValues) {  
  180.                 if (Double.isNaN(frec.getValue())) {  
  181.                     // Formula result is a string  
  182.                     // This is stored in the next record  
  183.                     outputNextStringRecord = true;  
  184.                     nextRow = frec.getRow();  
  185.                     nextColumn = frec.getColumn();  
  186.                 } else {  
  187.                     thisStr = formatListener.formatNumberDateCell(frec);  
  188.                 }  
  189.             } else {  
  190.                 thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook,  
  191.                         frec.getParsedExpression()) + '"';  
  192.             }  
  193.             break;  
  194.         case StringRecord.sid:  
  195.             if (outputNextStringRecord) {  
  196.                 // String for formula  
  197.                 StringRecord srec = (StringRecord) record;  
  198.                 thisStr = srec.getString();  
  199.                 thisRow = nextRow;  
  200.                 thisColumn = nextColumn;  
  201.                 outputNextStringRecord = false;  
  202.             }  
  203.             break;  
  204.   
  205.         case LabelRecord.sid:  
  206.             LabelRecord lrec = (LabelRecord) record;  
  207.   
  208.             curRow = thisRow = lrec.getRow();  
  209.             thisColumn = lrec.getColumn();  
  210.             value = lrec.getValue().trim();  
  211.             value = value.equals("")?" ":value;  
  212.             this.rowlist.add(thisColumn, value);  
  213.             break;  
  214.         case LabelSSTRecord.sid:  
  215.             LabelSSTRecord lsrec = (LabelSSTRecord) record;  
  216.   
  217.             curRow = thisRow = lsrec.getRow();  
  218.             thisColumn = lsrec.getColumn();  
  219.             if (sstRecord == null) {  
  220.                 rowlist.add(thisColumn, " ");  
  221.             } else {  
  222.                 value =  sstRecord  
  223.                 .getString(lsrec.getSSTIndex()).toString().trim();  
  224.                 value = value.equals("")?" ":value;  
  225.                 rowlist.add(thisColumn,value);  
  226.             }  
  227.             break;  
  228.         case NoteRecord.sid:  
  229.             NoteRecord nrec = (NoteRecord) record;  
  230.   
  231.             thisRow = nrec.getRow();  
  232.             thisColumn = nrec.getColumn();  
  233.             // TODO: Find object to match nrec.getShapeId()  
  234.             thisStr = '"' + "(TODO)" + '"';  
  235.             break;  
  236.         case NumberRecord.sid:  
  237.             NumberRecord numrec = (NumberRecord) record;  
  238.   
  239.             curRow = thisRow = numrec.getRow();  
  240.             thisColumn = numrec.getColumn();  
  241.             value = formatListener.formatNumberDateCell(numrec).trim();  
  242.             value = value.equals("")?" ":value;  
  243.             // Format  
  244.             rowlist.add(thisColumn, value);  
  245.             break;  
  246.         case RKRecord.sid:  
  247.             RKRecord rkrec = (RKRecord) record;  
  248.   
  249.             thisRow = rkrec.getRow();  
  250.             thisColumn = rkrec.getColumn();  
  251.             thisStr = '"' + "(TODO)" + '"';  
  252.             break;  
  253.         default:  
  254.             break;  
  255.         }  
  256.   
  257.         // 遇到新行的操作  
  258.         if (thisRow != -1 && thisRow != lastRowNumber) {  
  259.             lastColumnNumber = -1;  
  260.         }  
  261.   
  262.         // 空值的操作  
  263.         if (record instanceof MissingCellDummyRecord) {  
  264.             MissingCellDummyRecord mc = (MissingCellDummyRecord) record;  
  265.             curRow = thisRow = mc.getRow();  
  266.             thisColumn = mc.getColumn();  
  267.             rowlist.add(thisColumn," ");  
  268.         }  
  269.   
  270.         // 如果遇到能打印的東西,，在這里打印  
  271.         if (thisStr != null) {  
  272.             if (thisColumn > 0) {  
  273.                 output.print(',');  
  274.             }  
  275.             output.print(thisStr);  
  276.         }  
  277.   
  278.         // 更新行和列的值  
  279.         if (thisRow > -1)  
  280.             lastRowNumber = thisRow;  
  281.         if (thisColumn > -1)  
  282.             lastColumnNumber = thisColumn;  
  283.   
  284.         // 行結(jié)束時(shí)的操作  
  285.         if (record instanceof LastCellOfRowDummyRecord) {  
  286.             if (minColumns > 0) {  
  287.                 // 列值重新置空  
  288.                 if (lastColumnNumber == -1) {  
  289.                     lastColumnNumber = 0;  
  290.                 }  
  291.             }  
  292.             // 行結(jié)束時(shí),， 調(diào)用 optRows() 方法  
  293.             lastColumnNumber = -1;  
  294.             try {  
  295.                 optRows(sheetIndex,curRow, rowlist);  
  296.             } catch (SQLException e) {  
  297.                 e.printStackTrace();  
  298.             }  
  299.             rowlist.clear();  
  300.         }  
  301.     }  
  302. }  

 

2、SPImportAction.java

 

Java代碼  收藏代碼
  1. import java.io.FileNotFoundException;  
  2. import java.io.IOException;  
  3. import java.sql.SQLException;  
  4. import java.util.ArrayList;  
  5. import java.util.List;  
  6.   
  7. import net.hlj.cms.gnet.model.SPModel;  
  8. import net.hlj.cms.gnet.service.SPImportService;  
  9. import net.hlj.cms.gnet.service.imp.SPImportServiceImpl;  
  10. import net.hlj.cms.gnet.util.HxlsAbstract;  
  11.   
  12. import org.springframework.context.ApplicationContext;  
  13. import org.springframework.context.support.FileSystemXmlApplicationContext;  
  14.   
  15. /** 
  16.  * @項(xiàng)目名：保密 
  17.  * @包名：保密 
  18.  * @文件名：SPImportAction.java 
  19.  * @日期：Dec 23, 2010 3:54:16 PM 
  20.  * @備注：增值業(yè)務(wù)導(dǎo)入 
  21.  * @作者：apple 
  22.  */  
  23. public class SPImportAction extends HxlsAbstract{  
  24.   
  25.     private static String fileName="增值業(yè)務(wù)(測(cè)試庫).xls";//文件名  
  26.     private static String path="D:\\net\\";//路徑  
  27.     private ApplicationContext ctx = new FileSystemXmlApplicationContext("/WebRoot/WEB-INF/applicationContext.xml");//spring 上下文  
  28.     private SPImportService server=new SPImportServiceImpl();//接口  
  29.     private static int count=0;  
  30.     private static int successCount=0;  
  31.     private static int failCount=0;  
  32.     private static ArrayList errorList=new ArrayList();  
  33.     /** 
  34.      * @param args 
  35.      */  
  36.     public static void main(String[] args)   
  37.     {  
  38.         SPImportAction excel;//excel對(duì)象  
  39.         try   
  40.         {  
  41.             excel=new SPImportAction(path+fileName);  
  42.             excel.process();  
  43.             System.out.println("一共："+count+"條數(shù)據(jù)");  
  44.             System.out.println("成功："+successCount+"條數(shù)據(jù)");  
  45.             System.out.println("失?。?+failCount+"條數(shù)據(jù)");  
  46.             for(int i=0;i<errorList.size();i++)  
  47.             {  
  48.                 System.out.println("失敗行數(shù)："+errorList.get(i));  
  49.             }  
  50.         } catch (FileNotFoundException e) {  
  51.             e.printStackTrace();  
  52.         } catch (IOException e) {  
  53.             e.printStackTrace();  
  54.         } catch (SQLException e) {  
  55.             e.printStackTrace();  
  56.         }  
  57.     }  
  58.       
  59.   @Override    
  60.   public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException   
  61.   {     
  62.       String row="";  
  63.       if(curRow>0)  
  64.       {  
  65.           if( rowlist.size()==8)  
  66.           {  
  67.               for (int i = 0 ;i< rowlist.size();i++){     
  68.                   row+=rowlist.get(i)+",";  
  69.               }  
  70.               SPModel obj=new SPModel();  
  71.               obj.setPRODUCTID(rowlist.get(1));  
  72.               obj.setV_busiCode(rowlist.get(2));  
  73.               obj.setV_busiName(rowlist.get(4));  
  74.               obj.setV_busiDes(rowlist.get(5));  
  75.               obj.setEFFDATE(rowlist.get(6));  
  76.               obj.setEXPDATE(rowlist.get(7));  
  77.               if(rowlist.get(3).equals("4"))  
  78.               {  
  79.                   obj.setFlag("1");  
  80.               }  
  81.               else  
  82.               {  
  83.                   obj.setFlag("0");  
  84.               }  
  85.                 
  86.               if(server.isSP(obj, ctx))  
  87.               {  
  88.                   int uflag=server.SPUpdate(obj, ctx);  
  89.                   if(uflag>0)  
  90.                   {  
  91.                       successCount++;  
  92.                       System.out.println("第"+rowlist.get(0)+"行數(shù)據(jù)修改成功");  
  93.                   }  
  94.                   else  
  95.                   {  
  96.                       failCount++;  
  97.                       System.out.println("第"+rowlist.get(0)+"行數(shù)據(jù)修改失敗");  
  98.                       errorList.add(rowlist.get(0));  
  99.                   }  
  100.               }  
  101.               else  
  102.               {  
  103.                   int aflag=server.SPAdd(obj, ctx);  
  104.                   if(aflag>0)  
  105.                   {  
  106.                       successCount++;  
  107.                       System.out.println("第"+rowlist.get(0)+"行數(shù)據(jù)新增成功");   
  108.                   }  
  109.                   else  
  110.                   {  
  111.                       failCount++;  
  112.                       System.out.println("第"+rowlist.get(0)+"行數(shù)據(jù)新增失敗");  
  113.                       errorList.add(rowlist.get(0));  
  114.                   }  
  115.               }  
  116.               count++;  
  117.               System.out.println(row.substring(0,row.length()-1));   
  118.           }  
  119.       }  
  120.         
  121.         
  122.         
  123.   }     
  124.   public SPImportAction(String filename) throws IOException,FileNotFoundException, SQLException   
  125.   {     
  126.       super(filename);     
  127.   }     
  128. }  

    本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間,，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn),。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式,、誘導(dǎo)購買等信息，謹(jǐn)防詐騙,。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,，請(qǐng)點(diǎn)擊一鍵舉報(bào),。
    轉(zhuǎn)藏 分享 獻(xiàn)花（0）

    0條評(píng)論

    發(fā)表

    請(qǐng)遵守用戶 評(píng)論公約

    類似文章 更多