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

博客分类：
• java基础

ExcelApacheCMSSQL ServerApple

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.  * @项目名：保密 
36.  * @包名：保密 
37.  * @文件名：HxlsAbstract.java 
38.  * @日期：Dec 24, 2010 10:54:52 AM 
39.  * @备注：POI导入excel（大数据量） 
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记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型  
92. //  public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;  
93.       
94.     //excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为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 监听方法，处理 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.         // 行结束时的操作  
285.         if (record instanceof LastCellOfRowDummyRecord) {  
286.             if (minColumns > 0) {  
287.                 // 列值重新置空  
288.                 if (lastColumnNumber == -1) {  
289.                     lastColumnNumber = 0;  
290.                 }  
291.             }  
292.             // 行结束时， 调用 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.  * @项目名：保密 
17.  * @包名：保密 
18.  * @文件名：SPImportAction.java 
19.  * @日期：Dec 23, 2010 3:54:16 PM 
20.  * @备注：增值业务导入 
21.  * @作者：apple 
22.  */  
23. public class SPImportAction extends HxlsAbstract{  
24.   
25.     private static String fileName="增值业务(测试库).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对象  
39.         try   
40.         {  
41.             excel=new SPImportAction(path+fileName);  
42.             excel.process();  
43.             System.out.println("一共："+count+"条数据");  
44.             System.out.println("成功："+successCount+"条数据");  
45.             System.out.println("失败："+failCount+"条数据");  
46.             for(int i=0;i<errorList.size();i++)  
47.             {  
48.                 System.out.println("失败行数："+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)+"行数据修改成功");  
93.                   }  
94.                   else  
95.                   {  
96.                       failCount++;  
97.                       System.out.println("第"+rowlist.get(0)+"行数据修改失败");  
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)+"行数据新增成功");   
108.                   }  
109.                   else  
110.                   {  
111.                       failCount++;  
112.                       System.out.println("第"+rowlist.get(0)+"行数据新增失败");  
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. }