KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > poi > hssf > dev > FormulaViewer


1 /* ====================================================================
2    Copyright 2004 Apache Software Foundation
3
4    Licensed under the Apache License, Version 2.0 (the "License");
5    you may not use this file except in compliance with the License.
6    You may obtain a copy of the License at
7
8        http://www.apache.org/licenses/LICENSE-2.0
9
10    Unless required by applicable law or agreed to in writing, software
11    distributed under the License is distributed on an "AS IS" BASIS,
12    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13    See the License for the specific language governing permissions and
14    limitations under the License.
15 ==================================================================== */
16
17
18 /*
19  * FormulaViewer.java - finds formulas in a BIFF8 file and attempts to parse them and
20  * display info about them.
21  *
22  * Created on November 18, 2001, 7:58 AM
23  */
24 package org.apache.poi.hssf.dev;
25
26 import java.io.InputStream JavaDoc;
27 import java.io.IOException JavaDoc;
28 import java.io.ByteArrayInputStream JavaDoc;
29 import java.io.FileInputStream JavaDoc;
30 import java.io.FileOutputStream JavaDoc;
31
32 //import java.io.*;
33 import java.util.List JavaDoc;
34
35 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
36 import org.apache.poi.util.LittleEndian;
37 import org.apache.poi.util.HexDump;
38 import org.apache.poi.hssf.record.*;
39 import org.apache.poi.hssf.record.formula.*;
40 import org.apache.poi.hssf.model.*;
41 import org.apache.poi.hssf.usermodel.*;
42
43 /**
44  * FormulaViewer - finds formulas in a BIFF8 file and attempts to read them/display
45  * data from them. Only works if Formulas are enabled in "RecordFactory"
46  * @author andy
47  * @author Avik
48  */
49
50 public class FormulaViewer
51 {
52     private String JavaDoc file;
53     private boolean list=false;
54
55     /** Creates new FormulaViewer */
56
57     public FormulaViewer()
58     {
59     }
60
61     /**
62      * Method run
63      *
64      *
65      * @exception Exception
66      *
67      */
68
69     public void run()
70         throws Exception JavaDoc
71     {
72         POIFSFileSystem fs =
73             new POIFSFileSystem(new FileInputStream JavaDoc(file));
74         List JavaDoc records =
75             RecordFactory
76                 .createRecords(fs.createDocumentInputStream("Workbook"));
77
78         for (int k = 0; k < records.size(); k++)
79         {
80             Record record = ( Record ) records.get(k);
81
82             if (record.getSid() == FormulaRecord.sid)
83             {
84                if (list) {
85                     listFormula((FormulaRecord) record);
86                }else {
87                     parseFormulaRecord(( FormulaRecord ) record);
88                }
89             }
90         }
91     }
92     
93     private void listFormula(FormulaRecord record) {
94         String JavaDoc sep="~";
95         List JavaDoc tokens= record.getParsedExpression();
96         int numptgs = record.getNumberOfExpressionTokens();
97         Ptg token = null;
98         String JavaDoc name,numArg;
99         if (tokens != null) {
100             token = (Ptg) tokens.get(numptgs-1);
101             if (token instanceof FuncPtg) {
102                 numArg = String.valueOf(numptgs-1);
103             } else { numArg = String.valueOf(-1);}
104             
105             StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
106             
107             if (token instanceof ExpPtg) return;
108             buf.append(name=((OperationPtg) token).toFormulaString((Workbook)null));
109             buf.append(sep);
110             switch (token.getPtgClass()) {
111                 case Ptg.CLASS_REF :
112                     buf.append("REF");
113                     break;
114                 case Ptg.CLASS_VALUE :
115                     buf.append("VALUE");
116                     break;
117                 case Ptg.CLASS_ARRAY :
118                     buf.append("ARRAY");
119                     break;
120             }
121             
122             buf.append(sep);
123             if (numptgs>1) {
124                 token = (Ptg) tokens.get(numptgs-2);
125                 switch (token.getPtgClass()) {
126                     case Ptg.CLASS_REF :
127                         buf.append("REF");
128                         break;
129                     case Ptg.CLASS_VALUE :
130                         buf.append("VALUE");
131                         break;
132                     case Ptg.CLASS_ARRAY :
133                         buf.append("ARRAY");
134                         break;
135                 }
136             }else {
137                 buf.append("VALUE");
138             }
139             buf.append(sep);
140             buf.append(numArg);
141             System.out.println(buf.toString());
142         } else {
143             System.out.println("#NAME");
144         }
145     }
146
147     /**
148      * Method parseFormulaRecord
149      *
150      *
151      * @param record
152      *
153      */
154
155     public void parseFormulaRecord(FormulaRecord record)
156     {
157         System.out.println("==============================");
158         System.out.print("row = " + record.getRow());
159         System.out.println(", col = " + record.getColumn());
160         System.out.println("value = " + record.getValue());
161         System.out.print("xf = " + record.getXFIndex());
162         System.out.print(", number of ptgs = "
163                            + record.getNumberOfExpressionTokens());
164         System.out.println(", options = " + record.getOptions());
165         System.out.println("RPN List = "+formulaString(record));
166         System.out.println("Formula text = "+ composeFormula(record));
167     }
168
169     private String JavaDoc formulaString(FormulaRecord record) {
170         StringBuffer JavaDoc formula = new StringBuffer JavaDoc("=");
171         int numptgs = record.getNumberOfExpressionTokens();
172         List JavaDoc tokens = record.getParsedExpression();
173         Ptg token;
174         StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
175            for (int i=0;i<numptgs;i++) {
176            token = (Ptg) tokens.get(i);
177             buf.append( token.toFormulaString((Workbook)null));
178             switch (token.getPtgClass()) {
179                 case Ptg.CLASS_REF :
180                     buf.append("(R)");
181                     break;
182                 case Ptg.CLASS_VALUE :
183                     buf.append("(V)");
184                     break;
185                 case Ptg.CLASS_ARRAY :
186                     buf.append("(A)");
187                     break;
188             }
189             buf.append(' ');
190         }
191         return buf.toString();
192     }
193     
194     
195     private String JavaDoc composeFormula(FormulaRecord record)
196     {
197        return org.apache.poi.hssf.model.FormulaParser.toFormulaString((Workbook)null,record.getParsedExpression());
198     }
199
200     /**
201      * Method setFile
202      *
203      *
204      * @param file
205      *
206      */
207
208     public void setFile(String JavaDoc file)
209     {
210         this.file = file;
211     }
212     
213     public void setList(boolean list) {
214         this.list=list;
215     }
216
217     /**
218      * Method main
219      *
220      * pass me a filename and I'll try and parse the formulas from it
221      *
222      * @param args pass one argument with the filename or --help
223      *
224      */
225
226     public static void main(String JavaDoc args[])
227     {
228         if ((args == null) || (args.length >2 )
229                 || args[ 0 ].equals("--help"))
230         {
231             System.out.println(
232                 "FormulaViewer .8 proof that the devil lies in the details (or just in BIFF8 files in general)");
233             System.out.println("usage: Give me a big fat file name");
234         } else if (args[0].equals("--listFunctions")) { // undocumented attribute to research functions!~
235 try {
236                 FormulaViewer viewer = new FormulaViewer();
237                 viewer.setFile(args[1]);
238                 viewer.setList(true);
239                 viewer.run();
240             }
241             catch (Exception JavaDoc e) {
242                 System.out.println("Whoops!");
243                 e.printStackTrace();
244             }
245         }
246         else
247         {
248             try
249             {
250                 FormulaViewer viewer = new FormulaViewer();
251
252                 viewer.setFile(args[ 0 ]);
253                 viewer.run();
254             }
255             catch (Exception JavaDoc e)
256             {
257                 System.out.println("Whoops!");
258                 e.printStackTrace();
259             }
260         }
261     }
262 }
263
Popular Tags