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