001: /* ====================================================================
002: Licensed to the Apache Software Foundation (ASF) under one or more
003: contributor license agreements. See the NOTICE file distributed with
004: this work for additional information regarding copyright ownership.
005: The ASF licenses this file to You under the Apache License, Version 2.0
006: (the "License"); you may not use this file except in compliance with
007: the License. You may obtain a copy of the License at
008:
009: http://www.apache.org/licenses/LICENSE-2.0
010:
011: Unless required by applicable law or agreed to in writing, software
012: distributed under the License is distributed on an "AS IS" BASIS,
013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: See the License for the specific language governing permissions and
015: limitations under the License.
016: ==================================================================== */
017:
018: /*
019: * FormulaViewer.java - finds formulas in a BIFF8 file and attempts to parse them and
020: * display info about them.
021: *
022: * Created on November 18, 2001, 7:58 AM
023: */
024: package org.apache.poi.hssf.dev;
025:
026: import java.io.FileInputStream;
027:
028: //import java.io.*;
029: import java.util.List;
030:
031: import org.apache.poi.poifs.filesystem.POIFSFileSystem;
032: import org.apache.poi.hssf.record.*;
033: import org.apache.poi.hssf.record.formula.*;
034: import org.apache.poi.hssf.model.*;
035:
036: /**
037: * FormulaViewer - finds formulas in a BIFF8 file and attempts to read them/display
038: * data from them. Only works if Formulas are enabled in "RecordFactory"
039: * @author andy
040: * @author Avik
041: */
042:
043: public class FormulaViewer {
044: private String file;
045: private boolean list = false;
046:
047: /** Creates new FormulaViewer */
048:
049: public FormulaViewer() {
050: }
051:
052: /**
053: * Method run
054: *
055: *
056: * @exception Exception
057: *
058: */
059:
060: public void run() throws Exception {
061: POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
062: file));
063: List records = RecordFactory.createRecords(fs
064: .createDocumentInputStream("Workbook"));
065:
066: for (int k = 0; k < records.size(); k++) {
067: Record record = (Record) records.get(k);
068:
069: if (record.getSid() == FormulaRecord.sid) {
070: if (list) {
071: listFormula((FormulaRecord) record);
072: } else {
073: parseFormulaRecord((FormulaRecord) record);
074: }
075: }
076: }
077: }
078:
079: private void listFormula(FormulaRecord record) {
080: String sep = "~";
081: List tokens = record.getParsedExpression();
082: int numptgs = record.getNumberOfExpressionTokens();
083: Ptg token = null;
084: String name, numArg;
085: if (tokens != null) {
086: token = (Ptg) tokens.get(numptgs - 1);
087: if (token instanceof FuncPtg) {
088: numArg = String.valueOf(numptgs - 1);
089: } else {
090: numArg = String.valueOf(-1);
091: }
092:
093: StringBuffer buf = new StringBuffer();
094:
095: if (token instanceof ExpPtg)
096: return;
097: buf.append(name = ((OperationPtg) token)
098: .toFormulaString((Workbook) null));
099: buf.append(sep);
100: switch (token.getPtgClass()) {
101: case Ptg.CLASS_REF:
102: buf.append("REF");
103: break;
104: case Ptg.CLASS_VALUE:
105: buf.append("VALUE");
106: break;
107: case Ptg.CLASS_ARRAY:
108: buf.append("ARRAY");
109: break;
110: }
111:
112: buf.append(sep);
113: if (numptgs > 1) {
114: token = (Ptg) tokens.get(numptgs - 2);
115: switch (token.getPtgClass()) {
116: case Ptg.CLASS_REF:
117: buf.append("REF");
118: break;
119: case Ptg.CLASS_VALUE:
120: buf.append("VALUE");
121: break;
122: case Ptg.CLASS_ARRAY:
123: buf.append("ARRAY");
124: break;
125: }
126: } else {
127: buf.append("VALUE");
128: }
129: buf.append(sep);
130: buf.append(numArg);
131: System.out.println(buf.toString());
132: } else {
133: System.out.println("#NAME");
134: }
135: }
136:
137: /**
138: * Method parseFormulaRecord
139: *
140: *
141: * @param record
142: *
143: */
144:
145: public void parseFormulaRecord(FormulaRecord record) {
146: System.out.println("==============================");
147: System.out.print("row = " + record.getRow());
148: System.out.println(", col = " + record.getColumn());
149: System.out.println("value = " + record.getValue());
150: System.out.print("xf = " + record.getXFIndex());
151: System.out.print(", number of ptgs = "
152: + record.getNumberOfExpressionTokens());
153: System.out.println(", options = " + record.getOptions());
154: System.out.println("RPN List = " + formulaString(record));
155: System.out.println("Formula text = " + composeFormula(record));
156: }
157:
158: private String formulaString(FormulaRecord record) {
159: StringBuffer formula = new StringBuffer("=");
160: int numptgs = record.getNumberOfExpressionTokens();
161: List tokens = record.getParsedExpression();
162: Ptg token;
163: StringBuffer buf = new StringBuffer();
164: for (int i = 0; i < numptgs; i++) {
165: token = (Ptg) tokens.get(i);
166: buf.append(token.toFormulaString((Workbook) null));
167: switch (token.getPtgClass()) {
168: case Ptg.CLASS_REF:
169: buf.append("(R)");
170: break;
171: case Ptg.CLASS_VALUE:
172: buf.append("(V)");
173: break;
174: case Ptg.CLASS_ARRAY:
175: buf.append("(A)");
176: break;
177: }
178: buf.append(' ');
179: }
180: return buf.toString();
181: }
182:
183: private String composeFormula(FormulaRecord record) {
184: return org.apache.poi.hssf.model.FormulaParser.toFormulaString(
185: (Workbook) null, record.getParsedExpression());
186: }
187:
188: /**
189: * Method setFile
190: *
191: *
192: * @param file
193: *
194: */
195:
196: public void setFile(String file) {
197: this .file = file;
198: }
199:
200: public void setList(boolean list) {
201: this .list = list;
202: }
203:
204: /**
205: * Method main
206: *
207: * pass me a filename and I'll try and parse the formulas from it
208: *
209: * @param args pass one argument with the filename or --help
210: *
211: */
212:
213: public static void main(String args[]) {
214: if ((args == null) || (args.length > 2)
215: || args[0].equals("--help")) {
216: System.out
217: .println("FormulaViewer .8 proof that the devil lies in the details (or just in BIFF8 files in general)");
218: System.out.println("usage: Give me a big fat file name");
219: } else if (args[0].equals("--listFunctions")) { // undocumented attribute to research functions!~
220: try {
221: FormulaViewer viewer = new FormulaViewer();
222: viewer.setFile(args[1]);
223: viewer.setList(true);
224: viewer.run();
225: } catch (Exception e) {
226: System.out.println("Whoops!");
227: e.printStackTrace();
228: }
229: } else {
230: try {
231: FormulaViewer viewer = new FormulaViewer();
232:
233: viewer.setFile(args[0]);
234: viewer.run();
235: } catch (Exception e) {
236: System.out.println("Whoops!");
237: e.printStackTrace();
238: }
239: }
240: }
241: }
|