KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > jxl > demo > ReadWrite


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.UnsupportedEncodingException JavaDoc;
27 import java.io.IOException JavaDoc;
28 import java.util.Date JavaDoc;
29 import java.util.Calendar JavaDoc;
30 import java.util.TimeZone JavaDoc;
31 import java.net.URL JavaDoc;
32 import java.net.MalformedURLException JavaDoc;
33
34 import common.Logger;
35
36 import jxl.Workbook;
37 import jxl.CellType;
38 import jxl.Cell;
39 import jxl.Sheet;
40 import jxl.FormulaCell;
41 import jxl.Range;
42 import jxl.biff.formula.FormulaException;
43 import jxl.format.CellFormat;
44 import jxl.format.UnderlineStyle;
45 import jxl.format.Colour;
46 import jxl.read.biff.BiffException;
47 import jxl.write.WritableWorkbook;
48 import jxl.write.WritableSheet;
49 import jxl.write.WritableCell;
50 import jxl.write.WritableCellFormat;
51 import jxl.write.WritableFont;
52 import jxl.write.WritableHyperlink;
53 import jxl.write.Label;
54 import jxl.write.NumberFormat;
55 import jxl.write.NumberFormats;
56 import jxl.write.Number;
57 import jxl.write.DateFormat;
58 import jxl.write.DateFormats;
59 import jxl.write.DateTime;
60 import jxl.write.Formula;
61 import jxl.write.WriteException;
62 import jxl.write.WritableImage;
63 import jxl.write.WritableCellFeatures;
64
65 /**
66  * Demo class which uses the api to read in a spreadsheet and generate a clone
67  * of that spreadsheet which contains the same data. If the spreadsheet read
68  * in is the spreadsheet called jxlrwtest.xls (provided with the distribution)
69  * then this class will modify certain fields in the copy of that spreadsheet.
70  * This is illustrating that it is possible to read in a spreadsheet, modify
71  * a few values, and write it under a new name.
72  */
73 public class ReadWrite
74 {
75   /**
76    * The logger
77    */
78   private static Logger logger = Logger.getLogger(ReadWrite.class);
79
80   /**
81    * The spreadsheet to read in
82    */
83   private File JavaDoc inputWorkbook;
84   /**
85    * The spreadsheet to output
86    */
87   private File JavaDoc outputWorkbook;
88
89   /**
90    * Constructor
91    *
92    * @param output
93    * @param input
94    */
95   public ReadWrite(String JavaDoc input, String JavaDoc output)
96   {
97     inputWorkbook = new File JavaDoc(input);
98     outputWorkbook = new File JavaDoc(output);
99     logger.setSuppressWarnings(Boolean.getBoolean("jxl.nowarnings"));
100     logger.info("Input file: " + input);
101     logger.info("Output file: " + output);
102   }
103
104   /**
105    * Reads in the inputFile and creates a writable copy of it called outputFile
106    *
107    * @exception IOException
108    * @exception BiffException
109    */
110   public void readWrite() throws IOException JavaDoc, BiffException, WriteException
111   {
112     logger.info("Reading...");
113     Workbook w1 = Workbook.getWorkbook(inputWorkbook);
114
115     Sheet s = w1.getSheet(0);
116
117     logger.info("Copying...");
118     WritableWorkbook w2 = Workbook.createWorkbook(outputWorkbook, w1);
119
120     if (inputWorkbook.getName().equals("jxlrwtest.xls"))
121     {
122       modify(w2);
123     }
124
125     w2.write();
126     w2.close();
127     logger.info("Done");
128   }
129
130   /**
131    * If the inputFile was the test spreadsheet, then it modifies certain fields
132    * of the writable copy
133    *
134    * @param w
135    */
136   private void modify(WritableWorkbook w) throws WriteException
137   {
138     logger.info("Modifying...");
139
140     WritableSheet sheet = w.getSheet("modified");
141     
142     WritableCell cell = null;
143     CellFormat cf = null;
144
145     // Change the format of cell B4 to be emboldened
146 cell = sheet.getWritableCell(1,3);
147     WritableFont bold = new WritableFont(WritableFont.ARIAL,
148                                          WritableFont.DEFAULT_POINT_SIZE,
149                                          WritableFont.BOLD);
150     cf = new WritableCellFormat(bold);
151     cell.setCellFormat(cf);
152
153     // Change the format of cell B5 to be underlined
154 cell = sheet.getWritableCell(1,4);
155     WritableFont underline = new WritableFont(WritableFont.ARIAL,
156                                               WritableFont.DEFAULT_POINT_SIZE,
157                                               WritableFont.NO_BOLD,
158                                               false,
159                                               UnderlineStyle.SINGLE);
160     cf = new WritableCellFormat(underline);
161     cell.setCellFormat(cf);
162
163     // Change the point size of cell B6 to be 10 point
164 cell = sheet.getWritableCell(1,5);
165     WritableFont tenpoint = new WritableFont(WritableFont.ARIAL, 10);
166     cf = new WritableCellFormat(tenpoint);
167     cell.setCellFormat(cf);
168
169     // Change the contents of cell B7 to read "Label - mod"
170 cell = sheet.getWritableCell(1,6);
171     if (cell.getType() == CellType.LABEL)
172     {
173       Label lc = (Label) cell;
174       lc.setString(lc.getString() + " - mod");
175     }
176
177     // Change cell B10 to display 7 dps
178 cell = sheet.getWritableCell(1,9);
179     NumberFormat sevendps = new NumberFormat("#.0000000");
180     cf = new WritableCellFormat(sevendps);
181     cell.setCellFormat(cf);
182
183     
184     // Change cell B11 to display in the format 1e4
185 cell = sheet.getWritableCell(1,10);
186     NumberFormat exp4 = new NumberFormat("0.####E0");
187     cf = new WritableCellFormat(exp4);
188     cell.setCellFormat(cf);
189     
190     // Change cell B12 to be normal display
191 cell = sheet.getWritableCell(1,11);
192     cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);
193
194     // Change the contents of cell B13 to 42
195 cell = sheet.getWritableCell(1,12);
196     if (cell.getType() == CellType.NUMBER)
197     {
198       Number JavaDoc n = (Number JavaDoc) cell;
199       n.setValue(42);
200     }
201
202     // Add 0.1 to the contents of cell B14
203 cell = sheet.getWritableCell(1,13);
204     if (cell.getType() == CellType.NUMBER)
205     {
206       Number JavaDoc n = (Number JavaDoc) cell;
207       n.setValue(n.getValue() + 0.1);
208     }
209
210     // Change the date format of cell B17 to be a custom format
211 cell = sheet.getWritableCell(1,16);
212     DateFormat df = new DateFormat("dd MMM yyyy HH:mm:ss");
213     cf = new WritableCellFormat(df);
214     cell.setCellFormat(cf);
215
216     // Change the date format of cell B18 to be a standard format
217 cell = sheet.getWritableCell(1,17);
218     cf = new WritableCellFormat(DateFormats.FORMAT9);
219     cell.setCellFormat(cf);
220
221     // Change the date in cell B19 to be 18 Feb 1998, 11:23:28
222 cell = sheet.getWritableCell(1,18);
223     if (cell.getType() == CellType.DATE)
224     {
225       DateTime dt = (DateTime) cell;
226       Calendar JavaDoc cal = Calendar.getInstance();
227       cal.set(1998, 1, 18, 11, 23, 28);
228       Date JavaDoc d = cal.getTime();
229       dt.setDate(d);
230     }
231
232     // Change the value in B23 to be 6.8. This should recalculate the
233 // formula
234 cell = sheet.getWritableCell(1,22);
235     if (cell.getType() == CellType.NUMBER)
236     {
237       Number JavaDoc n = (Number JavaDoc) cell;
238       n.setValue(6.8);
239     }
240
241     // Change the label in B30. This will have the effect of making
242 // the original string unreferenced
243 cell = sheet.getWritableCell(1, 29);
244     if (cell.getType() == CellType.LABEL)
245     {
246       Label l = (Label) cell;
247       l.setString("Modified string contents");
248     }
249     // Insert a new row (number 35)
250 sheet.insertRow(34);
251
252     // Delete row 38 (39 after row has been inserted)
253 sheet.removeRow(38);
254
255     // Insert a new column (J)
256 sheet.insertColumn(9);
257
258     // Remove a column (L - M after column has been inserted)
259 sheet.removeColumn(11);
260
261     // Remove row 44 (contains a hyperlink), and then insert an empty
262 // row just to keep the numbers consistent
263 sheet.removeRow(43);
264     sheet.insertRow(43);
265
266     // Modify the hyperlinks
267 WritableHyperlink hyperlinks[] = sheet.getWritableHyperlinks();
268
269     for (int i = 0; i < hyperlinks.length; i++)
270     {
271       WritableHyperlink wh = hyperlinks[i];
272       if (wh.getColumn() == 1 && wh.getRow() == 39)
273       {
274         try
275         {
276           // Change the hyperlink that begins in cell B40 to be a different API
277 wh.setURL(new URL JavaDoc("http://www.andykhan.com/jexcelapi/index.html"));
278         }
279         catch (MalformedURLException JavaDoc e)
280         {
281           logger.warn(e.toString());
282         }
283       }
284       else if (wh.getColumn() == 1 && wh.getRow() == 40)
285       {
286         wh.setFile(new File JavaDoc("../jexcelapi/docs/overview-summary.html"));
287       }
288       else if (wh.getColumn() == 1 && wh.getRow() == 41)
289       {
290         wh.setFile(new File JavaDoc("d:/home/jexcelapi/docs/jxl/package-summary.html"));
291       }
292       else if (wh.getColumn() == 1 && wh.getRow() == 44)
293       {
294         // Remove the hyperlink at B45
295 sheet.removeHyperlink(wh);
296       }
297     }
298
299     // Change the background of cell F31 from blue to red
300 WritableCell c = sheet.getWritableCell(5,30);
301     WritableCellFormat newFormat = new WritableCellFormat(c.getCellFormat());
302     newFormat.setBackground(Colour.RED);
303     c.setCellFormat(newFormat);
304
305     // Modify the contents of the merged cell
306 Label l = new Label(0, 49, "Modified merged cells");
307     sheet.addCell(l);
308
309     // Modify the chart data
310 Number JavaDoc n = (Number JavaDoc) sheet.getWritableCell(0, 70);
311     n.setValue(9);
312     
313     n = (Number JavaDoc) sheet.getWritableCell(0, 71);
314     n.setValue(10);
315
316     n = (Number JavaDoc) sheet.getWritableCell(0, 73);
317     n.setValue(4);
318
319     // Add in a cross sheet formula
320 Formula f = new Formula(1, 80, "ROUND(COS(original!B10),2)");
321     sheet.addCell(f);
322
323     // Add in a formula from the named cells
324 f = new Formula(1, 83, "value1+value2");
325     sheet.addCell(f);
326
327     // Add in a function formula using named cells
328 f = new Formula(1, 84, "AVERAGE(value1,value1*4,value2)");
329     sheet.addCell(f);
330
331     // Copy sheet 1 to sheet 3
332 // w.copySheet(0, "copy", 2);
333
334     // Use the cell deep copy feature
335 Label label = new Label(0, 88, "Some copied cells", cf);
336     sheet.addCell(label);
337
338     label = new Label(0,89, "Number from B9");
339     sheet.addCell(label);
340
341     WritableCell wc = sheet.getWritableCell(1, 9).copyTo(1,89);
342     sheet.addCell(wc);
343
344     label = new Label(0, 90, "Label from B4 (modified format)");
345     sheet.addCell(label);
346
347     wc = sheet.getWritableCell(1, 3).copyTo(1,90);
348     sheet.addCell(wc);
349
350     label = new Label(0, 91, "Date from B17");
351     sheet.addCell(label);
352
353     wc = sheet.getWritableCell(1, 16).copyTo(1,91);
354     sheet.addCell(wc);
355
356     label = new Label(0, 92, "Boolean from E16");
357     sheet.addCell(label);
358
359     wc = sheet.getWritableCell(4, 15).copyTo(1,92);
360     sheet.addCell(wc);
361
362     label = new Label(0, 93, "URL from B40");
363     sheet.addCell(label);
364
365     wc = sheet.getWritableCell(1, 39).copyTo(1,93);
366     sheet.addCell(wc);
367
368     // Add some numbers for the formula copy
369 for (int i = 0 ; i < 6; i++)
370     {
371       Number JavaDoc number = new Number JavaDoc(1,94+i, i + 1 + i/8.0);
372       sheet.addCell(number);
373     }
374
375     label = new Label(0,100, "Formula from B27");
376     sheet.addCell(label);
377
378     wc = sheet.getWritableCell(1, 26).copyTo(1,100);
379     sheet.addCell(wc);
380
381     label = new Label(0,101, "A brand new formula");
382     sheet.addCell(label);
383     
384     Formula formula = new Formula(1, 101, "SUM(B94:B96)");
385     sheet.addCell(formula);
386
387     label = new Label(0,102, "A copy of it");
388     sheet.addCell(label);
389
390     wc = sheet.getWritableCell(1,101).copyTo(1, 102);
391     sheet.addCell(wc);
392
393     // Remove the second image from the sheet
394 WritableImage wi = sheet.getImage(1);
395     sheet.removeImage(wi);
396
397     wi = new WritableImage(1, 116, 2, 9,
398                            new File JavaDoc("resources/littlemoretonhall.png"));
399     sheet.addImage(wi);
400
401     // Modify the text in the first cell with a comment
402 cell = sheet.getWritableCell(0, 156);
403     l = (Label) cell;
404     l.setString("Label text modified");
405
406     cell = sheet.getWritableCell(0, 157);
407     WritableCellFeatures wcf = cell.getWritableCellFeatures();
408     wcf.setComment("modified comment text");
409   }
410 }
411
412
413
414
415
416
417
418
419
420
Popular Tags