1    
2    /* ====================================================================
3     * The Apache Software License, Version 1.1
4     *
5     * Copyright (c) 2002 The Apache Software Foundation.  All rights
6     * reserved.
7     *
8     * Redistribution and use in source and binary forms, with or without
9     * modification, are permitted provided that the following conditions
10    * are met:
11    *
12    * 1. Redistributions of source code must retain the above copyright
13    *    notice, this list of conditions and the following disclaimer.
14    *
15    * 2. Redistributions in binary form must reproduce the above copyright
16    *    notice, this list of conditions and the following disclaimer in
17    *    the documentation and/or other materials provided with the
18    *    distribution.
19    *
20    * 3. The end-user documentation included with the redistribution,
21    *    if any, must include the following acknowledgment:
22    *       "This product includes software developed by the
23    *        Apache Software Foundation (http://www.apache.org/)."
24    *    Alternately, this acknowledgment may appear in the software itself,
25    *    if and wherever such third-party acknowledgments normally appear.
26    *
27    * 4. The names "Apache" and "Apache Software Foundation" and
28    *    "Apache POI" must not be used to endorse or promote products
29    *    derived from this software without prior written permission. For
30    *    written permission, please contact apache@apache.org.
31    *
32    * 5. Products derived from this software may not be called "Apache",
33    *    "Apache POI", nor may "Apache" appear in their name, without
34    *    prior written permission of the Apache Software Foundation.
35    *
36    * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
37    * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
38    * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
39    * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
40    * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
41    * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
42    * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
43    * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
44    * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
45    * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
46    * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
47    * SUCH DAMAGE.
48    * ====================================================================
49    *
50    * This software consists of voluntary contributions made by many
51    * individuals on behalf of the Apache Software Foundation.  For more
52    * information on the Apache Software Foundation, please see
53    * <http://www.apache.org/>.
54    */
55   
56   /*
57    * FormulaViewer.java - finds formulas in a BIFF8 file and attempts to parse them and
58    * display info about them.
59    *
60    * Created on November 18, 2001, 7:58 AM
61    */
62   package org.apache.poi.hssf.dev;
63   
64   import java.io.InputStream;
65   import java.io.IOException;
66   import java.io.ByteArrayInputStream;
67   import java.io.FileInputStream;
68   import java.io.FileOutputStream;
69   
70   //import java.io.*;
71   import java.util.List;
72   
73   import org.apache.poi.poifs.filesystem.POIFSFileSystem;
74   import org.apache.poi.util.LittleEndian;
75   import org.apache.poi.util.HexDump;
76   import org.apache.poi.hssf.record.*;
77   import org.apache.poi.hssf.record.formula.*;
78   import org.apache.poi.hssf.model.*;
79   import org.apache.poi.hssf.usermodel.*;
80   
81   /**
82    * FormulaViewer - finds formulas in a BIFF8 file and attempts to read them/display
83    * data from them. Only works if Formulas are enabled in "RecordFactory"
84    * @author  andy
85    * @author Avik
86    */
87   
88   public class FormulaViewer
89   {
90       private String file;
91       private boolean list=false;
92   
93       /** Creates new FormulaViewer */
94   
95       public FormulaViewer()
96       {
97       }
98   
99       /**
100       * Method run
101       *
102       *
103       * @exception Exception
104       *
105       */
106  
107      public void run()
108          throws Exception
109      {
110          POIFSFileSystem fs      =
111              new POIFSFileSystem(new FileInputStream(file));
112          List            records =
113              RecordFactory
114                  .createRecords(fs.createDocumentInputStream("Workbook"));
115  
116          for (int k = 0; k < records.size(); k++)
117          {
118              Record record = ( Record ) records.get(k);
119  
120              if (record.getSid() == FormulaRecord.sid)
121              {
122                 if (list) {
123                      listFormula((FormulaRecord) record);
124                 }else {
125                      parseFormulaRecord(( FormulaRecord ) record);
126                 }
127              }
128          }
129      }
130      
131      private void listFormula(FormulaRecord record) {
132          String sep="~";
133          List tokens= record.getParsedExpression();
134          int numptgs = record.getNumberOfExpressionTokens();
135          Ptg token = null;
136          String name,numArg;
137          if (tokens != null) {
138              token = (Ptg) tokens.get(numptgs-1);
139              if (token instanceof FuncPtg) {
140                  numArg = String.valueOf(numptgs-1);
141              } else { numArg = String.valueOf(-1);}
142              
143              StringBuffer buf = new StringBuffer();
144              
145              if (token instanceof ExpPtg) return;
146              buf.append(name=((OperationPtg) token).toFormulaString());
147              buf.append(sep);
148              switch (token.getPtgClass()) {
149                  case Ptg.CLASS_REF :
150                      buf.append("REF");
151                      break;
152                  case Ptg.CLASS_VALUE :
153                      buf.append("VALUE");
154                      break;
155                  case Ptg.CLASS_ARRAY :
156                      buf.append("ARRAY");
157                      break;
158              }
159              
160              buf.append(sep);
161              if (numptgs>1) {
162                  token = (Ptg) tokens.get(numptgs-2);
163                  switch (token.getPtgClass()) {
164                      case Ptg.CLASS_REF :
165                          buf.append("REF");
166                          break;
167                      case Ptg.CLASS_VALUE :
168                          buf.append("VALUE");
169                          break;
170                      case Ptg.CLASS_ARRAY :
171                          buf.append("ARRAY");
172                          break;
173                  }
174              }else {
175                  buf.append("VALUE");
176              }
177              buf.append(sep);
178              buf.append(numArg);
179              System.out.println(buf.toString());
180          } else  {
181              System.out.println("#NAME");
182          }
183      }
184  
185      /**
186       * Method parseFormulaRecord
187       *
188       *
189       * @param record
190       *
191       */
192  
193      public void parseFormulaRecord(FormulaRecord record)
194      {
195          System.out.println("==============================");
196          System.out.print("row = " + record.getRow());
197          System.out.println(", col = " + record.getColumn());
198          System.out.println("value = " + record.getValue());
199          System.out.print("xf = " + record.getXFIndex());
200          System.out.print(", number of ptgs = "
201                             + record.getNumberOfExpressionTokens());
202          System.out.println(", options = " + record.getOptions());
203          System.out.println("RPN List = "+formulaString(record));
204          System.out.println("Formula text = "+ composeFormula(record));
205      }
206  
207      private String formulaString(FormulaRecord record) {
208          StringBuffer formula = new StringBuffer("=");
209          int          numptgs = record.getNumberOfExpressionTokens();
210          List         tokens    = record.getParsedExpression();
211          Ptg token;
212          StringBuffer buf = new StringBuffer();
213             for (int i=0;i<numptgs;i++) {
214             token = (Ptg) tokens.get(i);
215              buf.append( token.toFormulaString());
216              switch (token.getPtgClass()) {
217                  case Ptg.CLASS_REF :
218                      buf.append("(R)");
219                      break;
220                  case Ptg.CLASS_VALUE :
221                      buf.append("(V)");
222                      break;
223                  case Ptg.CLASS_ARRAY :
224                      buf.append("(A)");
225                      break;
226              }
227              buf.append(' ');
228          } 
229          return buf.toString();
230      }
231      
232      
233      private String composeFormula(FormulaRecord record)
234      {
235         return  FormulaParser.toFormulaString(record.getParsedExpression());
236      }
237  
238      /**
239       * Method setFile
240       *
241       *
242       * @param file
243       *
244       */
245  
246      public void setFile(String file)
247      {
248          this.file = file;
249      }
250      
251      public void setList(boolean list) {
252          this.list=list;
253      }
254  
255      /**
256       * Method main
257       *
258       * pass me a filename and I'll try and parse the formulas from it
259       *
260       * @param args pass one argument with the filename or --help
261       *
262       */
263  
264      public static void main(String args[])
265      {
266          if ((args == null) || (args.length >2 )
267                  || args[ 0 ].equals("--help"))
268          {
269              System.out.println(
270                  "FormulaViewer .8 proof that the devil lies in the details (or just in BIFF8 files in general)");
271              System.out.println("usage: Give me a big fat file name");
272          } else if (args[0].equals("--listFunctions")) { // undocumented attribute to research functions!~
273              try {
274                  FormulaViewer viewer = new FormulaViewer();
275                  viewer.setFile(args[1]);
276                  viewer.setList(true);
277                  viewer.run();
278              }
279              catch (Exception e) {
280                  System.out.println("Whoops!");
281                  e.printStackTrace();
282              }
283          }
284          else
285          {
286              try
287              {
288                  FormulaViewer viewer = new FormulaViewer();
289  
290                  viewer.setFile(args[ 0 ]);
291                  viewer.run();
292              }
293              catch (Exception e)
294              {
295                  System.out.println("Whoops!");
296                  e.printStackTrace();
297              }
298          }
299      }
300  }
301  ??????????????????createRecords????????????????????????????????fs???????????????????????????????????createDocumentInputStream?????????????????????????k?????????????????????????????records?????????????????????????????????????????????k?????????????Record???????????????????????????????Record????????????????????????????????????????records????????????????????????????????????????????????????k?????????????????record????????????????????????getSid????????????????????????????????????FormulaRecord??????????????????????????????????????????????????sid????????????????????list?????????????????????listFormula??????????????????????????????????FormulaRecord?????????????????????????????????????????????????record?????????????????????parseFormulaRecord??????????????????????????????????????????FormulaRecord??????????????????????????????????????????????????????????record??????????????????listFormula??????????????????????????????FormulaRecord??????????????????????record?????????????????????????????getParsedExpression???????????????????????record??????????????????????????????getNumberOfExpressionTokens?????????Ptg?????????????tokens?????????????token??????????????????????Ptg???????????????????????????tokens??????????????????????????????????????numptgs?????????????????token?????????????????numArg?????????????????????????????????????????numptgs??????????????????????numArg?????????????????token?????????????buf????????????????????????name???????????????????????????????OperationPtg?????????????????????????????????????????????token?????????????buf????????????????????????sep?????????????????????token???????????????????????????getPtgClass??????????????????????Ptg??????????????????????????CLASS_REF?????????????????????buf??????????????????????Ptg??????????????????????????CLASS_VALUE?????????????????????buf??????????????????????Ptg??????????????????????????CLASS_ARRAY?????????????????????buf?????????????buf????????????????????????sep?????????????????numptgs?????????????????token??????????????????????????Ptg???????????????????????????????tokens??????????????????????????????????????????numptgs?????????????????????????token???????????????????????????????getPtgClass??????????????????????????Ptg??????????????????????????????CLASS_REF?????????????????????????buf??????????????????????????Ptg??????????????????????????????CLASS_VALUE?????????????????????????buf??????????????????????????Ptg??????????????????????????????CLASS_ARRAY?????????????????????????buf?????????????????buf?????????????buf????????????????????????sep?????????????buf????????????????????????numArg????????????????????????????????buf????????????????????????????????????????????????????????????????????????????????????????????????????????????parseFormulaRecord????????????????????????????????????FormulaRecord?????????????????????????????????????record????????????????????????????????????????????getRow?????????????????????????????????????????record????????????????????????????????????????????????getColumn?????????????????????????????????????????record????????????????????????????????????????????????getValue????????????????????????????????????record???????????????????????????????????????????getXFIndex??????????????????????????????record?????????????????????????????????????getNumberOfExpressionTokens?????????????????????????????????????????????record????????????????????????????????????????????????????getOptions??????????????????????????????????????????formulaString????????????????????????????????????????????????????????record???????????????????????????????????????????????composeFormula??????????????????????????????????????????????????????????????record????????????????????formulaString??????????????????????????????????FormulaRecord????????????????????????????????record???????????????????????????????????????getNumberOfExpressionTokens??????????????????????????????????record?????????????????????????????????????????getParsedExpression?????????Ptg?????????????????????????i???????????????????????????numptgs???????????????????????????????????i????????????token?????????????????????Ptg??????????????????????????tokens?????????????????????????????????????i?????????????buf?????????????????????????token???????????????????????????????toFormulaString?????????????????????token???????????????????????????getPtgClass??????????????????????Ptg??????????????????????????CLASS_REF?????????????????????buf??????????????????????Ptg??????????????????????????CLASS_VALUE?????????????????????buf??????????????????????Ptg??????????????????????????CLASS_ARRAY?????????????????????buf?????????????buf????????????????buf????????????????????composeFormula???????????????????????????????????FormulaRecord????????????????FormulaParser??????????????????????????????toFormulaString??????????????????????????????????????????????record?????????????????????????????????????????????????????getParsedExpression???????????????????????????????????????????????????????????????????????????????????????????????setFile?????????????????????file?????????????????setList???????????????????list???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????main??????????????args????????????????????????????????args????????????????????args????????????????????args???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????FormulaViewer????????????????????????????????????????????FormulaViewer?????????????????viewer????????????????????????setFile????????????????????????????????args?????????????????viewer????????????????????????setList?????????????????viewer????????????????????????run?????????????????e?????????????????FormulaViewer????????????????????????????????????????????FormulaViewer?????????????????viewer????????????????????????setFile????????????????????????????????args?????????????????viewer????????????????????????run?????????????????e