1 /
55
56
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
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 import org.apache.poi.hssf.util.SheetReferences;
81
82
88
89 public class FormulaViewer
90 {
91 private String file;
92 private boolean list=false;
93
94
95
96 public FormulaViewer()
97 {
98 }
99
100
107
108 public void run()
109 throws Exception
110 {
111 POIFSFileSystem fs =
112 new POIFSFileSystem(new FileInputStream(file));
113 List records =
114 RecordFactory
115 .createRecords(fs.createDocumentInputStream("Workbook"));
116
117 for (int k = 0; k < records.size(); k++)
118 {
119 Record record = ( Record ) records.get(k);
120
121 if (record.getSid() == FormulaRecord.sid)
122 {
123 if (list) {
124 listFormula((FormulaRecord) record);
125 }else {
126 parseFormulaRecord(( FormulaRecord ) record);
127 }
128 }
129 }
130 }
131
132 private void listFormula(FormulaRecord record) {
133 String sep="~";
134 List tokens= record.getParsedExpression();
135 int numptgs = record.getNumberOfExpressionTokens();
136 Ptg token = null;
137 String name,numArg;
138 if (tokens != null) {
139 token = (Ptg) tokens.get(numptgs-1);
140 if (token instanceof FuncPtg) {
141 numArg = String.valueOf(numptgs-1);
142 } else { numArg = String.valueOf(-1);}
143
144 StringBuffer buf = new StringBuffer();
145
146 if (token instanceof ExpPtg) return;
147 buf.append(name=((OperationPtg) token).toFormulaString((SheetReferences)null));
148 buf.append(sep);
149 switch (token.getPtgClass()) {
150 case Ptg.CLASS_REF :
151 buf.append("REF");
152 break;
153 case Ptg.CLASS_VALUE :
154 buf.append("VALUE");
155 break;
156 case Ptg.CLASS_ARRAY :
157 buf.append("ARRAY");
158 break;
159 }
160
161 buf.append(sep);
162 if (numptgs>1) {
163 token = (Ptg) tokens.get(numptgs-2);
164 switch (token.getPtgClass()) {
165 case Ptg.CLASS_REF :
166 buf.append("REF");
167 break;
168 case Ptg.CLASS_VALUE :
169 buf.append("VALUE");
170 break;
171 case Ptg.CLASS_ARRAY :
172 buf.append("ARRAY");
173 break;
174 }
175 }else {
176 buf.append("VALUE");
177 }
178 buf.append(sep);
179 buf.append(numArg);
180 System.out.println(buf.toString());
181 } else {
182 System.out.println("#NAME");
183 }
184 }
185
186 /**
187 * Method parseFormulaRecord
188 *
189 *
190 * @param record
191 *
192 */
193
194 public void parseFormulaRecord(FormulaRecord record)
195 {
196 System.out.println("==============================");
197 System.out.print("row = " + record.getRow());
198 System.out.println(", col = " + record.getColumn());
199 System.out.println("value = " + record.getValue());
200 System.out.print("xf = " + record.getXFIndex());
201 System.out.print(", number of ptgs = "
202 + record.getNumberOfExpressionTokens());
203 System.out.println(", options = " + record.getOptions());
204 System.out.println("RPN List = "+formulaString(record));
205 System.out.println("Formula text = "+ composeFormula(record));
206 }
207
208 private String formulaString(FormulaRecord record) {
209 StringBuffer formula = new StringBuffer("=");
210 int numptgs = record.getNumberOfExpressionTokens();
211 List tokens = record.getParsedExpression();
212 Ptg token;
213 StringBuffer buf = new StringBuffer();
214 for (int i=0;i<numptgs;i++) {
215 token = (Ptg) tokens.get(i);
216 buf.append( token.toFormulaString((SheetReferences)null));
217 switch (token.getPtgClass()) {
218 case Ptg.CLASS_REF :
219 buf.append("(R)");
220 break;
221 case Ptg.CLASS_VALUE :
222 buf.append("(V)");
223 break;
224 case Ptg.CLASS_ARRAY :
225 buf.append("(A)");
226 break;
227 }
228 buf.append(' ');
229 }
230 return buf.toString();
231 }
232
233
234 private String composeFormula(FormulaRecord record)
235 {
236 return org.apache.poi.hssf.model.FormulaParser.toFormulaString((SheetReferences)null,record.getParsedExpression());
237 }
238
239 /**
240 * Method setFile
241 *
242 *
243 * @param file
244 *
245 */
246
247 public void setFile(String file)
248 {
249 this.file = file;
250 }
251
252 public void setList(boolean list) {
253 this.list=list;
254 }
255
256 /**
257 * Method main
258 *
259 * pass me a filename and I'll try and parse the formulas from it
260 *
261 * @param args pass one argument with the filename or --help
262 *
263 */
264
265 public static void main(String args[])
266 {
267 if ((args == null) || (args.length >2 )
268 || args[ 0 ].equals("--help"))
269 {
270 System.out.println(
271 "FormulaViewer .8 proof that the devil lies in the details (or just in BIFF8 files in general)");
272 System.out.println("usage: Give me a big fat file name");
273 } else if (args[0].equals("--listFunctions")) { // undocumented attribute to research functions!~
274 try {
275 FormulaViewer viewer = new FormulaViewer();
276 viewer.setFile(args[1]);
277 viewer.setList(true);
278 viewer.run();
279 }
280 catch (Exception e) {
281 System.out.println("Whoops!");
282 e.printStackTrace();
283 }
284 }
285 else
286 {
287 try
288 {
289 FormulaViewer viewer = new FormulaViewer();
290
291 viewer.setFile(args[ 0 ]);
292 viewer.run();
293 }
294 catch (Exception e)
295 {
296 System.out.println("Whoops!");
297 e.printStackTrace();
298 }
299 }
300 }
301 }
302 createRecordsfscreateDocumentInputStreamkrecordskRecordRecordrecordskrecordgetSidFormulaRecordsidlistlistFormulaFormulaRecordrecordparseFormulaRecordFormulaRecordrecordlistFormulaFormulaRecordrecordgetParsedExpressionrecordgetNumberOfExpressionTokensPtgtokenstokenPtgtokensnumptgstokennumArgnumptgsnumArgtokenbufnameOperationPtgtokenSheetReferencesbufseptokengetPtgClassPtgCLASS_REFbufPtgCLASS_VALUEbufPtgCLASS_ARRAYbufbufsepnumptgstokenPtgtokensnumptgstokengetPtgClassPtgCLASS_REFbufPtgCLASS_VALUEbufPtgCLASS_ARRAYbufbufbufsepbufnumArgbufparseFormulaRecordFormulaRecordrecordgetRowrecordgetColumnrecordgetValuerecordgetXFIndexrecordgetNumberOfExpressionTokensrecordgetOptionsformulaStringrecordcomposeFormularecordformulaStringFormulaRecordrecordgetNumberOfExpressionTokensrecordgetParsedExpressionPtginumptgsitokenPtgtokensibuftokentoFormulaStringSheetReferencestokengetPtgClassPtgCLASS_REFbufPtgCLASS_VALUEbufPtgCLASS_ARRAYbufbufbufcomposeFormulaFormulaRecordFormulaParsertoFormulaStringSheetReferencesrecordgetParsedExpressionsetFilefilesetListlistmainargsargsargsargsFormulaViewerFormulaViewerviewersetFileargsviewersetListviewerruneFormulaViewerFormulaViewerviewersetFileargsviewerrune