KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > jxl > demo > Formulas


1 /*********************************************************************
2 *
3 * Copyright (C) 2002 Andrew Khan
4 *
5 * This library is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU Lesser General Public
7 * License as published by the Free Software Foundation; either
8 * version 2.1 of the License, or (at your option) any later version.
9 *
10 * This library is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 * Lesser General Public License for more details.
14 *
15 * You should have received a copy of the GNU Lesser General Public
16 * License along with this library; if not, write to the Free Software
17 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18 ***************************************************************************/
19
20 package jxl.demo;
21
22 import java.io.File JavaDoc;
23 import java.io.OutputStream JavaDoc;
24 import java.io.OutputStreamWriter JavaDoc;
25 import java.io.BufferedWriter JavaDoc;
26 import java.io.IOException JavaDoc;
27 import java.io.UnsupportedEncodingException JavaDoc;
28
29 import java.util.ArrayList JavaDoc;
30 import java.util.Iterator JavaDoc;
31
32 import jxl.Workbook;
33 import jxl.Sheet;
34 import jxl.Cell;
35 import jxl.CellType;
36 import jxl.FormulaCell;
37 import jxl.biff.CellReferenceHelper;
38 import jxl.biff.formula.FormulaParser;
39 import jxl.biff.formula.ExternalSheet;
40 import jxl.biff.formula.FormulaException;
41
42 /**
43  * Goes through each cell in the workbook, and if the contents of that
44  * cell is a formula, it prints out the last calculated value and
45  * the formula string
46  */
47 public class Formulas
48 {
49   /**
50    * Constructor
51    *
52    * @param w The workbook to interrogate
53    * @param out The output stream to which the CSV values are written
54    * @param encoding The encoding used by the output stream. Null or
55    * unrecognized values cause the encoding to default to UTF8
56    * @exception java.io.IOException
57    */
58   public Formulas(Workbook w, OutputStream JavaDoc out, String JavaDoc encoding)
59     throws IOException JavaDoc
60   {
61     if (encoding == null || !encoding.equals("UnicodeBig"))
62     {
63       encoding = "UTF8";
64     }
65
66     try
67     {
68       OutputStreamWriter JavaDoc osw = new OutputStreamWriter JavaDoc(out, encoding);
69       BufferedWriter JavaDoc bw = new BufferedWriter JavaDoc(osw);
70
71       ArrayList JavaDoc parseErrors = new ArrayList JavaDoc();
72       
73       for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++)
74       {
75         Sheet s = w.getSheet(sheet);
76
77         bw.write(s.getName());
78         bw.newLine();
79       
80         Cell[] row = null;
81         Cell c = null;
82       
83         for (int i = 0 ; i < s.getRows() ; i++)
84         {
85           row = s.getRow(i);
86
87           for (int j = 0; j < row.length; j++)
88           {
89             c = row[j];
90             if (c.getType() == CellType.NUMBER_FORMULA ||
91                 c.getType() == CellType.STRING_FORMULA ||
92                 c.getType() == CellType.BOOLEAN_FORMULA ||
93                 c.getType() == CellType.DATE_FORMULA ||
94                 c.getType() == CellType.FORMULA_ERROR)
95             {
96               FormulaCell nfc = (FormulaCell) c;
97               StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
98               CellReferenceHelper.getCellReference
99                  (c.getColumn(), c.getRow(), sb);
100
101               try
102               {
103                 bw.write("Formula in " + sb.toString() +
104                          " value: " + c.getContents());
105                 bw.flush();
106                 bw.write(" formula: " + nfc.getFormula());
107                 bw.flush();
108                 bw.newLine();
109               }
110               catch (FormulaException e)
111               {
112                 bw.newLine();
113                 parseErrors.add(s.getName() + '!' +
114                                 sb.toString() + ": " + e.getMessage());
115               }
116             }
117           }
118         }
119       }
120       bw.flush();
121       bw.close();
122
123       if (parseErrors.size() > 0)
124       {
125         System.err.println();
126         System.err.println("There were " + parseErrors.size() + " errors");
127
128         Iterator JavaDoc i = parseErrors.iterator();
129         while (i.hasNext())
130         {
131           System.err.println(i.next());
132         }
133       }
134     }
135     catch (UnsupportedEncodingException JavaDoc e)
136     {
137       System.err.println(e.toString());
138     }
139   }
140
141 }
142
143
Popular Tags