001    /*
002     * Copyright (c) 2009 The openGion Project.
003     *
004     * Licensed under the Apache License, Version 2.0 (the "License");
005     * you may not use this file except in compliance with the License.
006     * You may obtain a copy of the License at
007     *
008     *     http://www.apache.org/licenses/LICENSE-2.0
009     *
010     * Unless required by applicable law or agreed to in writing, software
011     * distributed under the License is distributed on an "AS IS" BASIS,
012     * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
013     * either express or implied. See the License for the specific language
014     * governing permissions and limitations under the License.
015     */
016    package org.opengion.hayabusa.report;
017    
018    import org.opengion.fukurou.util.Closer ;
019    import org.opengion.fukurou.util.LogWriter;
020    
021    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
022    import org.apache.poi.hssf.eventusermodel.HSSFRequest ;
023    import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
024    import org.apache.poi.hssf.eventusermodel.HSSFListener ;
025    import org.apache.poi.hssf.record.SSTRecord ;
026    import org.apache.poi.hssf.record.Record ;
027    import org.apache.poi.hssf.record.BOFRecord  ;
028    import org.apache.poi.hssf.record.BoundSheetRecord;
029    import org.apache.poi.hssf.record.LabelSSTRecord;
030    import org.apache.poi.hssf.record.UnicodeString;
031    // import org.apache.poi.hssf.record.common.UnicodeString;              // X.X.X.X (2012/XX/XX) POI3.0縺ｮ蝣ｴ蜷?
032    
033    import java.io.File;
034    import java.io.InputStream;
035    import java.io.FileInputStream;
036    import java.io.IOException;
037    
038    /**
039     * 縲職XCEL蜿冶ｾｼ縲鷹屁蠖｢EXCEL繧ｷ繝ｼ繝医?隗｣譫仙?逅?ｒ陦後≧轤ｺ縺ｮ縲？SSFListener 諡｡蠑ｵ繧ｯ繝ｩ繧ｹ縺ｧ縺吶?
040     * 縺薙?繧ｪ繝悶ず繧ｧ繧ｯ繝医?縲？SSFRequest 繧ｯ繝ｩ繧ｹ縺ｮ addListenerForAllRecords 繝｡繧ｽ繝?ラ縺ｫ貂｡縺?
041     * HSSFListener 繧､繝ｳ繧ｿ繝ｼ繝輔ぉ繝ｼ繧ｹ繧貞ｮ溯｣?＠縺ｦ縺?∪縺吶?縺ｾ縺溘?髮帛ｽ｢EXCEL 繧貞?逅?ｾ後?ExcelLayout
042     * 邂｡逅?け繝ｩ繧ｹ繧貞叙蠕励☆繧九％縺ｨ縺悟?譚･縺ｾ縺吶?
043     *
044     * @og.rev 3.8.0.0 (2005/06/07) 譁ｰ隕剰ｿｽ蜉?
045     * @og.group 蟶ｳ逾ｨ繧ｷ繧ｹ繝?Β
046     *
047     * @version  4.0
048     * @author   Kazuhiko Hasegawa
049     * @since    JDK5.0,
050     */
051    public class HybsHSSFListener implements HSSFListener {
052            private SSTRecord sstrec;
053            private int       sheetNo = -1;
054            private int       sheetSize = 0;
055            private boolean   trace  = false;
056    
057            private ExcelLayout laynot = null;
058    
059            /**
060             * 髮帛ｽ｢EXCEL縺ｮ {&#064;繧ｫ繝ｩ繝? 隗｣譫先ュ蝣ｱ繧定ｨｭ螳壹＠縺ｾ縺吶?
061             *
062             * 髮帛ｽ｢EXCEL縺ｯ縲？SSFListener 繧剃ｽｿ逕ｨ縺励※縲√う繝吶Φ繝磯ｧ?虚縺ｧ蜿門ｾ励＠縺ｾ縺吶?縺昴?蝣ｴ蜷医?
063             * {&#064;繧ｫ繝ｩ繝?繧貞性繧?そ繝ｫ繧定ｦ九▽縺代ｋ驛ｽ蠎ｦ縲√％縺ｮ繝｡繧ｽ繝?ラ繧貞他縺ｳ蜃ｺ縺励※縲＋&#064;繧ｫ繝ｩ繝?縺ｮ
064             * 菴咲ｽｮ(陦悟?逡ｪ蜿ｷ)繧定ｨｭ螳壹＠縺ｾ縺吶?
065             * 繝??繧ｿEXCEL縺九ｉ繝??繧ｿ繧定ｪｭ縺ｿ蜃ｺ縺吝?蜷医?縲√％縺薙〒逋ｻ骭ｲ縺励◆繧ｫ繝ｩ繝??陦悟?繧医ｊ縲∬ｪｭ縺ｿ霎ｼ縺ｿ縺ｾ縺吶?
066             * 蜈ｷ菴鍋噪縺ｫ縺ｯ縲？SSFListener#processRecord( Record )縺ｧ縲ヾSTRecord.sid 縺ｮ 諠??繧偵く繝ｼ繝励＠縺ｦ縺翫″縲?
067             * LabelSSTRecord.sid 豈弱↓縲＋&#064;繧ｫ繝ｩ繝?繧貞性繧?°繝√ぉ繝?け縺励?蜷ｫ繧??蜷医↓縲√％縺ｮ繝｡繧ｽ繝?ラ縺ｫ
068             * 隗｣譫先ュ蝣ｱ繧定ｨｭ螳壹＠縺ｾ縺吶?
069             *
070             * @og.rev 4.3.4.0 (2008/12/01) POI3.2蟇ｾ蠢?蠑墓焚縺ｮ蝙句､画峩(SSTRecord 縺ｮ toString() 蛹?
071             *
072             * @param record Record繧ｪ繝悶ず繧ｧ繧ｯ繝?
073             */
074            public void processRecord( final Record record ) {
075                    switch ( record.getSid() ) {
076                            // Workbook 縺ｧ縺ｮ 繧ｷ繝ｼ繝域ｯ弱↓蜻ｼ縺ｰ繧後∪縺吶?蜈郁｡後☆繧九◆繧√?縺薙?蝗樊焚繧偵す繝ｼ繝域焚縺ｨ縺励※謨ｰ縺医∪縺吶?
077                            case BoundSheetRecord.sid:
078                                    // 4.0.0.0 (2007/11/29) 蜈･繧悟ｭ進f 縺ｮ邨ｱ蜷?
079                                    if( trace && record instanceof BoundSheetRecord ) {
080                                            BoundSheetRecord bsr = (BoundSheetRecord) record;
081                                            System.out.println("Sheet named: " + bsr.getSheetname() );
082                                    }
083                                    sheetSize ++ ;
084                                    break;
085                            // TYPE_WORKSHEET 縺ｮ蝣ｴ蜷医?繧ｷ繝ｼ繝磯幕蟋区ｯ弱↓蜻ｼ縺ｰ繧後∪縺吶?
086                            case BOFRecord.sid:
087                                    if( record instanceof BOFRecord ) {
088                                            BOFRecord bof = (BOFRecord) record;
089                                            if(bof.getType() == BOFRecord.TYPE_WORKBOOK) {
090                                                    if( trace ) { System.out.println("Encountered workbook"); }
091                                            }
092                                            else if(bof.getType() == BOFRecord.TYPE_WORKSHEET) {
093                                                    // 繧ｷ繝ｼ繝磯幕蟋区ｯ弱↓蜻ｼ縺ｰ繧後ｋ縺溘ａ縲∫樟蝨ｨ縺ｮ繧ｷ繝ｼ繝育分蜿ｷ繧貞ｾ励ｋ縺ｮ縺ｫ菴ｿ逕ｨ縺励∪縺吶?
094                                                    sheetNo++ ;
095                                                    if( trace ) { System.out.println("Encountered sheet [" + sheetNo + "]" ); }
096                                            }
097                                    }
098                                    break;
099                            // 縺吶∋縺ｦ縺ｮ繝ｬ繧ｳ繝ｼ繝峨↓繧｢繧ｯ繧ｻ繧ｹ縺吶ｋ縺溘ａ縺ｫ縲∽ｸ?ｺｦ縺?￠蜻ｼ縺ｰ繧後∪縺吶?縺薙％縺ｧ蛻晄悄蛹悶＠縺ｦ縺翫″縺ｾ縺吶?
100                            case SSTRecord.sid:
101                                    if( record instanceof SSTRecord ) {
102                                            sstrec = (SSTRecord) record;
103                                            laynot = new ExcelLayout( sheetSize );
104                                            if( trace ) { System.out.println( "SSTRecord:[" + sstrec.getNumUniqueStrings() + "]" ); }
105                            //              for( int k=0; k<sstrec.getNumUniqueStrings(); k++ )  {
106                            //                      String val = sstrec.getString(k);
107                            //                      System.out.println("SSTRecord id=[" + k + "]=[" + val + "]" );
108                            //              }
109                                    }
110                                    break;
111                            // 譁?ｭ怜梛縺ｮ繝ｬ繧ｳ繝ｼ繝峨〒縺吶?
112                            case LabelSSTRecord.sid:
113                                    if( record instanceof LabelSSTRecord ) {
114                                            LabelSSTRecord lrec = (LabelSSTRecord) record;
115    
116                                            int   row  = lrec.getRow();
117                                            short col  = lrec.getColumn();
118    // POI3.0縺ｮ蝣ｴ蜷? String val = sstrec.getString( lrec.getSSTIndex() ) ;
119                                            UnicodeString ustr = sstrec.getString( lrec.getSSTIndex() ) ;
120                                            String val = ustr.getString();
121    
122                                            // 繧ｫ繝ｩ繝?↓縲＋@繧貞性繧??蜷医?縲〕aynot 縺ｫ髮帛ｽ｢諠??繧定ｿｽ蜉?＠縺ｾ縺吶?
123                                            // 謨ｴ蜷域?繝√ぉ繝?け縺ｯ縲・xcelLayoutData 繧ｯ繝ｩ繧ｹ縺ｧ陦後＞縺ｾ縺吶?
124                                            if( val != null && val.indexOf( "{@" ) >= 0 ) {
125                                                    if( trace ) { System.out.println("String cell id,sheet,row,col=[" + lrec.getSSTIndex() + "," + sheetNo + "," + row + "," + col + "] => " + val ); }
126                                                    laynot.addModel( sheetNo,val,row,col );
127                                            }
128                                    }
129                                    break;
130                            default :
131                                    LogWriter.log( "諠ｳ螳壼､悶?繧､繝吶Φ繝医ｒ蜿励￠蜿悶ｊ縺ｾ縺励◆縲?[" + record.getSid() + "]" );
132                                    break;
133                    }
134            }
135    
136            /**
137             * 髮帛ｽ｢EXCEL繧ｷ繝ｼ繝医? {&#064;繧ｫ繝ｩ繝? 隗｣譫舌ョ繝ｼ繧ｿ邂｡逅?け繝ｩ繧ｹ繧定ｿ斐＠縺ｾ縺吶?
138             *
139             * 髮帛ｽ｢EXCEL繧定ｧ｣譫舌＠邨ゅｏ縺｣縺溷ｾ後〒縲∬ｧ｣譫舌ョ繝ｼ繧ｿ邂｡逅?け繝ｩ繧ｹ繧貞叙繧雁?縺励∪縺吶?
140             * 髮帛ｽ｢EXCEL縺ｨ縲√ョ繝ｼ繧ｿEXCEL縺鯉ｼ大ｯｾ?代〒縺ｯ縺ｪ縺?◆繧√?繝ｪ繧｢繝ｫ繧ｿ繧､繝??逅?′蜃ｺ譚･縺ｾ縺帙ｓ縲?
141             * 荳?率縲√☆縺ｹ縺ｦ縺ｮ髮帛ｽ｢EXCEL繧定ｧ｣譫舌＠邨ゅｏ縺｣縺溷ｾ後〒縲√ョ繝ｼ繧ｿEXCEL蜃ｦ逅?ｒ陦後≧蠢?ｦ√′縺ゅｊ縺ｾ縺吶?
142             *
143             * @return      髮帛ｽ｢EXCEL繧ｷ繝ｼ繝?
144             */
145            public ExcelLayout getLayout() { return laynot; }
146    
147            /**
148             * 髮帛ｽ｢EXCEL 縺ｮ繧ｷ繝ｼ繝域焚繧定ｿ斐＠縺ｾ縺吶?
149             *
150             * @return      髮帛ｽ｢繧ｷ繝ｼ繝域焚
151             */
152            public int getSheetSize() { return sheetSize; }
153    
154            /**
155             * 蜃ｦ逅?ｵ碁℃諠??繧定｡ｨ遉ｺ縺吶ｋ縺九←縺?°[true/false]繧呈欠螳壹＠縺ｾ縺?蛻晄悄蛟､:false)
156             *
157             * 繧､繝吶Φ繝域ｯ弱?迥ｶ豕√ｒ縲∵ｨ呎ｺ門?蜉帙↓蜃ｺ蜉帙☆繧九°縺ｩ縺?°縺ｮ繝輔Λ繧ｰ縺ｧ縺吶?
158             * 蛻晄悄蛟､縺ｯ縲’alse 縺ｧ縺吶?
159             *
160             * @param       flag    蜃ｦ逅?ｵ碁℃諠??繧定｡ｨ遉ｺ縺吶ｋ縺九←縺?°[true/false]
161             */
162            public void setTrace( final boolean flag ) { trace = flag; }
163    
164            /**
165             * EXCEL繝ｬ繧､繧｢繧ｦ繝域ュ蝣ｱ繧貞叙蠕励＠縺ｾ縺吶?
166             *
167             * POI縺ｮ繧､繝吶Φ繝亥?逅?HSSFListener)縺ｧ縲∝推繧､繝吶Φ繝医＃縺ｨ縺ｫ蜃ｦ逅?ｒ陦後＞縺ｾ縺吶?
168             * 繧､繝吶Φ繝医?縲。OFRecord縲。oundSheetRecord縲ヾSTRecord縲´abelSSTRecord 縺ｫ縺､縺?※
169             * 逋ｺ逕溘☆繧九ｈ縺?↓險ｭ螳壹＠縺ｦ縺?∪縺吶?
170             *
171             * @param       file    蜃ｦ逅?☆繧畿XCEL繝輔ぃ繧､繝ｫ
172             * @param       trace   讓呎ｺ門?蜉帙↓繝医Ξ繝ｼ繧ｹ諠??繧貞?蜉帙☆繧九°縺ｩ縺?°繧呈欠螳壹＠縺ｾ縺吶?
173             *
174             * @return      EXCEL繝ｬ繧､繧｢繧ｦ繝域ュ蝣ｱ
175             * @throws IOException 蜈･蜃ｺ蜉帙お繝ｩ繝ｼ縺檎匱逕溘＠縺溷?蜷?
176             */
177            public static ExcelLayout makeExcelLayout( final File file ,final boolean trace ) throws IOException {
178    
179                    FileInputStream fin = null;
180                    InputStream din = null;
181                    final HybsHSSFListener event ;
182                    try {
183                            fin = new FileInputStream( file );
184                            POIFSFileSystem poifs = new POIFSFileSystem( fin );
185                            din = poifs.createDocumentInputStream( "Workbook" );
186                            HSSFRequest req = new HSSFRequest();
187    
188                            event = new HybsHSSFListener();
189                            event.setTrace( trace );                // 讓呎ｺ門?蜉帙↓繝医Ξ繝ｼ繧ｹ諠??繧貞?蜉帙＠縺ｾ縺吶?
190    
191                    //      req.addListenerForAllRecords( event );  // 蜈ｨ繧､繝吶Φ繝医ｒ蜃ｦ逅?
192                            req.addListener( event,BOFRecord.sid );
193                            req.addListener( event,BoundSheetRecord.sid );
194                            req.addListener( event,SSTRecord.sid );
195                            req.addListener( event,LabelSSTRecord.sid );
196    
197                            HSSFEventFactory factory = new HSSFEventFactory();
198                            factory.processEvents( req,din );
199                    }
200                    finally {
201                            Closer.ioClose( fin );          // 4.0.0 (2006/01/31) close 蜃ｦ逅?凾縺ｮ IOException 繧堤┌隕?
202                            Closer.ioClose( din );          // 4.0.0 (2006/01/31) close 蜃ｦ逅?凾縺ｮ IOException 繧堤┌隕?
203            //              fin.close();
204            //              din.close();
205                    }
206    
207                    return event.getLayout();
208            }
209    }