|
1
19
20 package jxl.demo;
21
22 import java.io.File  ;
23 import java.io.IOException ;
24 import java.util.Date ;
25 import java.util.Calendar ;
26 import java.util.TimeZone ;
27 import java.util.Locale ;
28 import java.text.SimpleDateFormat ;
29 import java.net.URL ;
30 import java.net.MalformedURLException ;
31
32 import jxl.Workbook;
33 import jxl.WorkbookSettings;
34 import jxl.Range;
35 import jxl.CellView;
36 import jxl.CellReferenceHelper;
37 import jxl.HeaderFooter;
38 import jxl.write.WritableWorkbook;
39 import jxl.write.WritableSheet;
40 import jxl.write.WritableFont;
41 import jxl.write.WritableCellFormat;
42 import jxl.write.NumberFormats;
43 import jxl.write.DateFormats;
44 import jxl.write.Label;
45 import jxl.write.Number;
46 import jxl.write.DateTime;
47 import jxl.write.NumberFormat;
48 import jxl.write.DateFormat;
49 import jxl.write.WriteException;
50 import jxl.write.WritableHyperlink;
51 import jxl.write.Boolean;
52 import jxl.write.Formula;
53 import jxl.write.WritableImage;
54 import jxl.write.WritableCellFeatures;
55 import jxl.format.Alignment;
56 import jxl.format.Border;
57 import jxl.format.BorderLineStyle;
58 import jxl.format.Colour;
59 import jxl.format.UnderlineStyle;
60 import jxl.format.ScriptStyle;
61 import jxl.format.Orientation;
62 import jxl.format.PageOrientation;
63 import jxl.format.PaperSize;
64
65
70 public class Write
71 {
72
75 private String filename;
76
77
80 private WritableWorkbook workbook;
81
82
87 public Write(String fn)
88 {
89 filename = fn;
90 }
91
92
98 public void write() throws IOException , WriteException
99 {
100 WorkbookSettings ws = new WorkbookSettings();
101 ws.setLocale(new Locale ("en", "EN"));
102 workbook = Workbook.createWorkbook(new File (filename), ws);
103
104
105 WritableSheet s2 = workbook.createSheet("Number Formats", 0);
106 WritableSheet s3 = workbook.createSheet("Date Formats", 1);
107 WritableSheet s1 = workbook.createSheet("Label Formats", 2);
108 WritableSheet s4 = workbook.createSheet("Borders", 3);
109 WritableSheet s5 = workbook.createSheet("Labels", 4);
110 WritableSheet s6 = workbook.createSheet("Formulas", 5);
111 WritableSheet s7 = workbook.createSheet("Images", 6);
112
115 writeLabelFormatSheet(s1);
116 writeNumberFormatSheet(s2);
117 writeDateFormatSheet(s3);
118 writeBordersSheet(s4);
119 writeLabelsSheet(s5);
120 writeFormulaSheet(s6);
121 writeImageSheet(s7);
122
123 workbook.setColourRGB(Colour.LIME, 0xff, 0, 0);
125
126 workbook.addNameArea("namedrange", s4, 1, 11, 5, 14);
128
129 workbook.write();
130 workbook.close();
131 }
132
133
138 private void writeNumberFormatSheet(WritableSheet s) throws WriteException
139 {
140 WritableCellFormat wrappedText = new WritableCellFormat
141 (WritableWorkbook.ARIAL_10_PT);
142 wrappedText.setWrap(true);
143
144 s.setColumnView(0,20);
145 s.setColumnView(4,20);
146 s.setColumnView(5,20);
147 s.setColumnView(6,20);
148
149 Label l = new Label(0,0,"+/- Pi - default format", wrappedText);
151 s.addCell(l);
152
153 Number n = new Number (1,0,3.1415926535);
154 s.addCell(n);
155
156 n = new Number (2,0,-3.1415926535);
157 s.addCell(n);
158
159 l = new Label(0,1,"+/- Pi - integer format", wrappedText);
160 s.addCell(l);
161
162 WritableCellFormat cf1 = new WritableCellFormat(NumberFormats.INTEGER);
163 n = new Number (1,1,3.1415926535,cf1);
164 s.addCell(n);
165
166 n = new Number (2,1,-3.1415926535, cf1);
167 s.addCell(n);
168
169 l = new Label(0,2,"+/- Pi - float 2dps", wrappedText);
170 s.addCell(l);
171
172 WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT);
173 n = new Number (1,2,3.1415926535,cf2);
174 s.addCell(n);
175
176 n = new Number (2,2,-3.1415926535, cf2);
177 s.addCell(n);
178
179 l = new Label(0,3,"+/- Pi - custom 3dps",
180 wrappedText);
181 s.addCell(l);
182
183 NumberFormat dp3 = new NumberFormat("#.###");
184 WritableCellFormat dp3cell = new WritableCellFormat(dp3);
185 n = new Number (1,3,3.1415926535,dp3cell);
186 s.addCell(n);
187
188 n = new Number (2,3,-3.1415926535, dp3cell);
189 s.addCell(n);
190
191 l = new Label(0,4,"+/- Pi - custom &3.14",
192 wrappedText);
193 s.addCell(l);
194
195 NumberFormat pounddp2 = new NumberFormat("&#.00");
196 WritableCellFormat pounddp2cell = new WritableCellFormat(pounddp2);
197 n = new Number (1,4,3.1415926535,pounddp2cell);
198 s.addCell(n);
199
200 n = new Number (2,4,-3.1415926535, pounddp2cell);
201 s.addCell(n);
202
203 l = new Label(0,5,"+/- Pi - custom Text #.### Text",
204 wrappedText);
205 s.addCell(l);
206
207 NumberFormat textdp4 = new NumberFormat("Text#.####Text");
208 WritableCellFormat textdp4cell = new WritableCellFormat(textdp4);
209 n = new Number (1,5,3.1415926535, textdp4cell);
210 s.addCell(n);
211
212 n = new Number (2,5,-3.1415926535, textdp4cell);
213 s.addCell(n);
214
215 l = new Label(4,0,"+/- Bilko default format");
217 s.addCell(l);
218 n = new Number (5, 0, 15042699);
219 s.addCell(n);
220 n = new Number (6, 0, -15042699);
221 s.addCell(n);
222
223 l = new Label(4,1,"+/- Bilko float format");
224 s.addCell(l);
225 WritableCellFormat cfi1 = new WritableCellFormat(NumberFormats.FLOAT);
226 n = new Number (5, 1, 15042699, cfi1);
227 s.addCell(n);
228 n = new Number (6, 1, -15042699, cfi1);
229 s.addCell(n);
230
231 l = new Label(4,2,"+/- Thousands separator");
232 s.addCell(l);
233 WritableCellFormat cfi2 = new WritableCellFormat
234 (NumberFormats.THOUSANDS_INTEGER);
235 n = new Number (5, 2, 15042699,cfi2 );
236 s.addCell(n);
237 n = new Number (6, 2, -15042699, cfi2);
238 s.addCell(n);
239
240 l = new Label(4,3,"+/- Accounting red - added 0.01");
241 s.addCell(l);
242 WritableCellFormat cfi3 = new WritableCellFormat
243 (NumberFormats.ACCOUNTING_RED_FLOAT);
244 n = new Number (5, 3, 15042699.01, cfi3);
245 s.addCell(n);
246 n = new Number (6, 3, -15042699.01, cfi3);
247 s.addCell(n);
248
249 l = new Label(4,4,"+/- Percent");
250 s.addCell(l);
251 WritableCellFormat cfi4 = new WritableCellFormat
252 (NumberFormats.PERCENT_INTEGER);
253 n = new Number (5, 4, 15042699, cfi4);
254 s.addCell(n);
255 n = new Number (6, 4, -15042699, cfi4);
256 s.addCell(n);
257
258 l = new Label(4,5,"+/- Exponential - 2dps");
259 s.addCell(l);
260 WritableCellFormat cfi5 = new WritableCellFormat
261 (NumberFormats.EXPONENTIAL);
262 n = new Number (5, 5, 15042699, cfi5);
263 s.addCell(n);
264 n = new Number (6, 5, -15042699, cfi5);
265 s.addCell(n);
266
267 l = new Label(4,6,"+/- Custom exponentional - 3dps", wrappedText);
268 s.addCell(l);
269 NumberFormat edp3 = new NumberFormat("0.000E0");
270 WritableCellFormat edp3Cell = new WritableCellFormat(edp3);
271 n = new Number (5,6,15042699,edp3Cell);
272 s.addCell(n);
273 n = new Number (6,6,-15042699,edp3Cell);
274 s.addCell(n);
275
276 l = new Label(4, 7, "Custom neg brackets", wrappedText);
277 s.addCell(l);
278 NumberFormat negbracks = new NumberFormat("#,##0;(#,##0)");
279 WritableCellFormat negbrackscell = new WritableCellFormat(negbracks);
280 n = new Number (5,7, 15042699, negbrackscell);
281 s.addCell(n);
282 n = new Number (6,7, -15042699, negbrackscell);
283 s.addCell(n);
284
285 l = new Label(4, 8, "Custom neg brackets 2", wrappedText);
286 s.addCell(l);
287 NumberFormat negbracks2 = new NumberFormat("#,##0;(#,##0)a");
288 WritableCellFormat negbrackscell2 = new WritableCellFormat(negbracks2);
289 n = new Number (5,8, 15042699, negbrackscell2);
290 s.addCell(n);
291 n = new Number (6,8, -15042699, negbrackscell2);
292 s.addCell(n);
293
294 l = new Label(4, 9, "Custom percent", wrappedText);
295 s.addCell(l);
296 NumberFormat cuspercent = new NumberFormat("0.0%");
297 WritableCellFormat cuspercentf = new WritableCellFormat(cuspercent);
298 n = new Number (5, 9, 3.14159265, cuspercentf);
299 s.addCell(n);
300
301
302 l = new Label(0,10, "Boolean - TRUE");
304 s.addCell(l);
305 Boolean b = new Boolean (1,10, true);
306 s.addCell(b);
307
308 l = new Label(0,11, "Boolean - FALSE");
309 s.addCell(l);
310 b = new Boolean (1,11,false);
311 s.addCell(b);
312
313 l = new Label(0, 12, "A hidden cell->");
314 s.addCell(l);
315 n = new Number (1, 12, 17, WritableWorkbook.HIDDEN_STYLE);
316 s.addCell(n);
317
318
319 for (int row = 0; row < 100; row++)
321 {
322 for (int col = 8; col < 108; col++)
323 {
324 n = new Number (col, row, col+row);
325 s.addCell(n);
326 }
327 }
328
329 for (int row = 101; row < 3000; row++)
331 {
332 for (int col = 0; col < 25; col++)
333 {
334 n = new Number (col, row, col+row);
335 s.addCell(n);
336 }
337 }
338 }
339
340
345 private void writeDateFormatSheet(WritableSheet s) throws WriteException
346 {
347 WritableCellFormat wrappedText = new WritableCellFormat
348 (WritableWorkbook.ARIAL_10_PT);
349 wrappedText.setWrap(true);
350
351 s.setColumnView(0, 20);
352 s.setColumnView(2, 20);
353 s.setColumnView(3, 20);
354 s.setColumnView(4, 20);
355
356 s.getSettings().setFitWidth(2);
357 s.getSettings().setFitHeight(2);
358
359 Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
360 c.set(1975, 4, 31, 15, 21, 45);
361 c.set(Calendar.MILLISECOND, 660);
362 Date date = c.getTime();
363 c.set(1900, 0, 1, 0, 0, 0);
364 c.set(Calendar.MILLISECOND, 0);
365
366 Date date2 = c.getTime();
367 c.set(1970, 0, 1, 0, 0, 0);
368 Date date3 = c.getTime();
369 c.set(1918, 10, 11, 11, 0, 0);
370 Date date4 = c.getTime();
371 c.set(1900, 0, 2, 0, 0, 0);
372 Date date5 = c.getTime();
373 c.set(1901, 0, 1, 0, 0, 0);
374 Date date6 = c.getTime();
375 c.set(1900, 4, 31, 0, 0, 0);
376 Date date7 = c.getTime();
377 c.set(1900, 1, 1, 0, 0, 0);
378 Date date8 = c.getTime();
379 c.set(1900, 0, 31, 0, 0, 0);
380 Date date9 = c.getTime();
381 c.set(1900, 2, 1, 0, 0, 0);
382 Date date10 = c.getTime();
383 c.set(1900, 1, 27, 0, 0, 0);
384 Date date11 = c.getTime();
385 c.set(1900, 1, 28, 0, 0, 0);
386 Date date12 = c.getTime();
387 c.set(1980, 5, 31, 12, 0, 0);
388 Date date13 = c.getTime();
389 c.set(1066, 9, 14, 0, 0, 0);
390 Date date14 = c.getTime();
391
392 SimpleDateFormat sdf = new SimpleDateFormat ("dd MMM yyyy HH:mm:ss.SSS");
394 sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
395 Label l = new Label(0,0,"All dates are " + sdf.format(date),
396 wrappedText);
397 s.addCell(l);
398
399 l = new Label(0,1,"Built in formats",
400 wrappedText);
401 s.addCell(l);
402
403 l = new Label(2, 1, "Custom formats");
404 s.addCell(l);
405
406 WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);
407 DateTime dt = new DateTime(0,2,date, cf1, DateTime.GMT);
408 s.addCell(dt);
409
410 cf1 = new WritableCellFormat(DateFormats.FORMAT2);
411 dt = new DateTime(0,3,date, cf1,DateTime.GMT);
412 s.addCell(dt);
413
414 cf1 = new WritableCellFormat(DateFormats.FORMAT3);
415 dt = new DateTime(0,4,date, cf1);
416 s.addCell(dt);
417
418 cf1 = new WritableCellFormat(DateFormats.FORMAT4);
419 dt = new DateTime(0,5,date, cf1);
420 s.addCell(dt);
421
422 cf1 = new WritableCellFormat(DateFormats.FORMAT5);
423 dt = new DateTime(0,6,date, cf1);
424 s.addCell(dt);
425
426 cf1 = new WritableCellFormat(DateFormats.FORMAT6);
427 dt = new DateTime(0,7,date, cf1);
428 s.addCell(dt);
429
430 cf1 = new WritableCellFormat(DateFormats.FORMAT7);
431 dt = new DateTime(0,8,date, cf1, DateTime.GMT);
432 s.addCell(dt);
433
434 cf1 = new WritableCellFormat(DateFormats.FORMAT8);
435 dt = new DateTime(0,9,date, cf1, DateTime.GMT);
436 s.addCell(dt);
437
438 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
439 dt = new DateTime(0,10,date, cf1, DateTime.GMT);
440 s.addCell(dt);
441
442 cf1 = new WritableCellFormat(DateFormats.FORMAT10);
443 dt = new DateTime(0,11,date, cf1, DateTime.GMT);
444 s.addCell(dt);
445
446 cf1 = new WritableCellFormat(DateFormats.FORMAT11);
447 dt = new DateTime(0,12,date, cf1, DateTime.GMT);
448 s.addCell(dt);
449
450 cf1 = new WritableCellFormat(DateFormats.FORMAT12);
451 dt = new DateTime(0,13,date, cf1, DateTime.GMT);
452 s.addCell(dt);
453
454 DateFormat df = new DateFormat ("dd MM yyyy");
456 cf1 = new WritableCellFormat(df);
457 l = new Label(2, 2, "dd MM yyyy");
458 s.addCell(l);
459
460 dt = new DateTime(3, 2, date, cf1, DateTime.GMT);
461 s.addCell(dt);
462
463 df = new DateFormat ("dd MMM yyyy");
464 cf1 = new WritableCellFormat(df);
465 l = new Label(2, 3, "dd MMM yyyy");
466 s.addCell(l);
467
468 dt = new DateTime(3, 3, date, cf1, DateTime.GMT);
469 s.addCell(dt);
470
471 df = new DateFormat ("hh:mm");
472 cf1 = new WritableCellFormat(df);
473 l = new Label(2, 4, "hh:mm");
474 s.addCell(l);
475
476 dt = new DateTime(3, 4, date, cf1, DateTime.GMT);
477 s.addCell(dt);
478
479 df = new DateFormat ("hh:mm:ss");
480 cf1 = new WritableCellFormat(df);
481 l = new Label(2, 5, "hh:mm:ss");
482 s.addCell(l);
483
484 dt = new DateTime(3, 5, date, cf1, DateTime.GMT);
485 s.addCell(dt);
486
487 df = new DateFormat ("H:mm:ss a");
488 cf1 = new WritableCellFormat(df);
489 l = new Label(2, 5, "H:mm:ss a");
490 s.addCell(l);
491
492 dt = new DateTime(3, 5, date, cf1, DateTime.GMT);
493 s.addCell(dt);
494 dt = new DateTime(4, 5, date13, cf1, DateTime.GMT);
495 s.addCell(dt);
496
497 df = new DateFormat ("mm:ss.SSS");
498 cf1 = new WritableCellFormat(df);
499 l = new Label(2, 6, "mm:ss.SSS");
500 s.addCell(l);
501
502 dt = new DateTime(3, 6, date, cf1, DateTime.GMT);
503 s.addCell(dt);
504
505 df = new DateFormat ("hh:mm:ss a");
506 cf1 = new WritableCellFormat(df);
507 l = new Label(2, 7, "hh:mm:ss a");
508 s.addCell(l);
509
510 dt = new DateTime(4, 7, date13, cf1, DateTime.GMT);
511 s.addCell(dt);
512
513
514 l = new Label(0,16,"Zero date " + sdf.format(date2),
516 wrappedText);
517 s.addCell(l);
518
519 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
520 dt = new DateTime(0,17,date2, cf1, DateTime.GMT);
521 s.addCell(dt);
522
523 l = new Label(3,16,"Zero date + 1 " + sdf.format(date5),
525 wrappedText);
526 s.addCell(l);
527
528 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
529 dt = new DateTime(3,17,date5, cf1, DateTime.GMT);
530 s.addCell(dt);
531
532 l = new Label(3,19, sdf.format(date6),
534 wrappedText);
535 s.addCell(l);
536
537 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
538 dt = new DateTime(3,20,date6, cf1, DateTime.GMT);
539 s.addCell(dt);
540
541 l = new Label(3,22, sdf.format(date7),
543 wrappedText);
544 s.addCell(l);
545
546 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
547 dt = new DateTime(3,23, date7, cf1, DateTime.GMT);
548 s.addCell(dt);
549
550 l = new Label(3,25, sdf.format(date8),
552 wrappedText);
553 s.addCell(l);
554
555 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
556 dt = new DateTime(3,26, date8, cf1, DateTime.GMT);
557 s.addCell(dt);
558
559 l = new Label(3,28, sdf.format(date9),
561 wrappedText);
562 s.addCell(l);
563
564 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
565 dt = new DateTime(3,29, date9, cf1, DateTime.GMT);
566 s.addCell(dt);
567
568 l = new Label(3,28, sdf.format(date9),
570 wrappedText);
571 s.addCell(l);
572
573 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
574 dt = new DateTime(3,29, date9, cf1, DateTime.GMT);
575 s.addCell(dt);
576
577 l = new Label(3,31, sdf.format(date10),
579 wrappedText);
580 s.addCell(l);
581
582 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
583 dt = new DateTime(3,32, date10, cf1, DateTime.GMT);
584 s.addCell(dt);
585
586 l = new Label(3,34, sdf.format(date11),
588 wrappedText);
589 s.addCell(l);
590
591 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
592 dt = new DateTime(3,35, date11, cf1, DateTime.GMT);
593 s.addCell(dt);
594
595 l = new Label(3,37, sdf.format(date12),
597 wrappedText);
598 s.addCell(l);
599
600 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
601 dt = new DateTime(3,38, date12, cf1, DateTime.GMT);
602 s.addCell(dt);
603
604 l = new Label(0,19,"Zero UTC date " + sdf.format(date3),
606 wrappedText);
607 s.addCell(l);
608
609 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
610 dt = new DateTime(0,20,date3, cf1, DateTime.GMT);
611 s.addCell(dt);
612
613 l = new Label(0,22,"Armistice date " + sdf.format(date4),
615 wrappedText);
616 s.addCell(l);
617
618 cf1 = new WritableCellFormat(DateFormats.FORMAT9);
619 dt = new DateTime(0,23,date4, cf1, DateTime.GMT);
620 s.addCell(dt);
621
622 l = new Label(0,25, "Battle of Hastings " + sdf.format(date14),
624 wrappedText);
625 s.addCell(l);
626
627 cf1 = new WritableCellFormat(DateFormats.FORMAT2);
628 dt = new DateTime(0, 26, date14, cf1, DateTime.GMT);
629 s.addCell(dt);
630 }
631
632
638 private void writeLabelFormatSheet(WritableSheet s1) throws WriteException
639 {
640 s1.setColumnView(0, 60);
641
642 Label lr = new Label(0,0, "Arial Fonts");
643 s1.addCell(lr);
644
645 lr = new Label(1,0, "10pt");
646 s1.addCell(lr);
647
648 lr = new Label(2, 0, "Normal");
649 s1.addCell(lr);
650
651 lr = new Label(3, 0, "12pt");
652 s1.addCell(lr);
653
654 WritableFont arial12pt = new WritableFont(WritableFont.ARIAL, 12);
655 WritableCellFormat arial12format = new WritableCellFormat(arial12pt);
656 arial12format.setWrap(true);
657 lr = new Label(4, 0, "Normal", arial12format);
658 s1.addCell(lr);
659
660 WritableFont arial10ptBold = new WritableFont
661 (WritableFont.ARIAL, 10, WritableFont.BOLD);
662 WritableCellFormat arial10BoldFormat = new WritableCellFormat
663 (arial10ptBold);
664 lr = new Label(2, 2, "BOLD", arial10BoldFormat);
665 s1.addCell(lr);
666
667 WritableFont arial12ptBold = new WritableFont
668 (WritableFont.ARIAL, 12, WritableFont.BOLD);
669 WritableCellFormat arial12BoldFormat = new WritableCellFormat
670 (arial12ptBold);
671 lr = new Label(4, 2, "BOLD", arial12BoldFormat);
672 s1.addCell(lr);
673
674 WritableFont arial10ptItalic = new WritableFont
675 (WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
676 WritableCellFormat arial10ItalicFormat = new WritableCellFormat
677 (arial10ptItalic);
678 lr = new Label(2, 4, "Italic", arial10ItalicFormat);
679 s1.addCell(lr);
680
681 WritableFont arial12ptItalic = new WritableFont
682 (WritableFont.ARIAL, 12, WritableFont.NO_BOLD, true);
683 WritableCellFormat arial12ptItalicFormat = new WritableCellFormat
684 (arial12ptItalic);
685 lr = new Label(4, 4, "Italic", arial12ptItalicFormat);
686 s1.addCell(lr);
687
688 WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
689 WritableCellFormat times10format = new WritableCellFormat(times10pt);
690 lr = new Label(0, 7, "Times Fonts", times10format);
691 s1.addCell(lr);
692
693 lr = new Label(1, 7, "10pt", times10format);
694 s1.addCell(lr);
695
696 lr = new Label(2, 7, "Normal", times10format);
697 s1.addCell(lr);
698
699 lr = new Label(3, 7, "12pt", times10format);
700 s1.addCell(lr);
701
702 WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
703 WritableCellFormat times12format = new WritableCellFormat(times12pt);
704 lr = new Label(4, 7, "Normal", times12format);
705 s1.addCell(lr);
706
707 WritableFont times10ptBold = new WritableFont
708 (WritableFont.TIMES, 10, WritableFont.BOLD);
709 WritableCellFormat times10BoldFormat = new WritableCellFormat
710 (times10ptBold);
711 lr = new Label(2, 9, "BOLD", times10BoldFormat);
712 s1.addCell(lr);
713
714 WritableFont times12ptBold = new WritableFont
715 (WritableFont.TIMES, 12, WritableFont.BOLD);
716 WritableCellFormat times12BoldFormat = new WritableCellFormat
717 (times12ptBold);
718 lr = new Label(4, 9, "BOLD", times12BoldFormat);
719 s1.addCell(lr);
720
721 s1.setColumnView(6, 22);
723 s1.setColumnView(7, 22);
724 s1.setColumnView(8, 22);
725 s1.setColumnView(9, 22);
726
727 lr = new Label(0, 11, "Underlining");
728 s1.addCell(lr);
729
730 WritableFont arial10ptUnderline = new WritableFont
731 (WritableFont.ARIAL,
732 WritableFont.DEFAULT_POINT_SIZE,
733 WritableFont.NO_BOLD,
734 false,
735 UnderlineStyle.SINGLE);
736 WritableCellFormat arialUnderline = new WritableCellFormat
737 (arial10ptUnderline);
738 lr = new Label(6,11, "Underline", arialUnderline);
739 s1.addCell(lr);
740
741 WritableFont arial10ptDoubleUnderline = new WritableFont
742 (WritableFont.ARIAL,
743 WritableFont.DEFAULT_POINT_SIZE,
744 WritableFont.NO_BOLD,
745 false,
746 UnderlineStyle.DOUBLE);
747 WritableCellFormat arialDoubleUnderline = new WritableCellFormat
748 (arial10ptDoubleUnderline);
749 lr = new Label(7,11, "Double Underline", arialDoubleUnderline);
750 s1.addCell(lr);
751
752 WritableFont arial10ptSingleAcc = new WritableFont
753 (WritableFont.ARIAL,
754 WritableFont.DEFAULT_POINT_SIZE,
755 WritableFont.NO_BOLD,
756 false,
757 UnderlineStyle.SINGLE_ACCOUNTING);
758 WritableCellFormat arialSingleAcc = new WritableCellFormat
759 (arial10ptSingleAcc);
760 lr = new Label(8,11, "Single Accounting Underline", arialSingleAcc);
761 s1.addCell(lr);
762
763 WritableFont arial10ptDoubleAcc = new WritableFont
764 (WritableFont.ARIAL,
765 WritableFont.DEFAULT_POINT_SIZE,
766 WritableFont.NO_BOLD,
767 false,
768 UnderlineStyle.DOUBLE_ACCOUNTING);
769 WritableCellFormat arialDoubleAcc = new WritableCellFormat
770 (arial10ptDoubleAcc);
771 lr = new Label(9,11, "Double Accounting Underline", arialDoubleAcc);
772 s1.addCell(lr);
773
774 WritableFont times14ptBoldUnderline = new WritableFont
775 (WritableFont.TIMES,
776 14,
777 WritableFont.BOLD,
778 false,
779 UnderlineStyle.SINGLE);
780 WritableCellFormat timesBoldUnderline = new WritableCellFormat
781 (times14ptBoldUnderline);
782 lr = new Label(6,12, "Times 14 Bold Underline", timesBoldUnderline);
783 s1.addCell(lr);
784
785 WritableFont arial18ptBoldItalicUnderline = new WritableFont
786 (WritableFont.ARIAL,
787 18,
788 WritableFont.BOLD,
789 true,
790 UnderlineStyle.SINGLE);
791 WritableCellFormat arialBoldItalicUnderline = new WritableCellFormat
792 (arial18ptBoldItalicUnderline);
793 lr = new Label(6,13, "Arial 18 Bold Italic Underline",
794 arialBoldItalicUnderline);
795 s1.addCell(lr);
796
797 lr = new Label(0, 15, "Script styles");
798 s1.addCell(lr);
799
800 WritableFont superscript = new WritableFont
801 (WritableFont.ARIAL,
802 WritableFont.DEFAULT_POINT_SIZE,
803 WritableFont.NO_BOLD,
804 false,
805 UnderlineStyle.NO_UNDERLINE,
806 Colour.BLACK,
807 ScriptStyle.SUPERSCRIPT);
808 WritableCellFormat superscriptFormat = new WritableCellFormat
809 (superscript);
810 lr = new Label(1,15, "superscript", superscriptFormat);
811 s1.addCell(lr);
812
813 WritableFont subscript = new WritableFont
814 (WritableFont.ARIAL,
815 WritableFont.DEFAULT_POINT_SIZE,
816 WritableFont.NO_BOLD,
817 false,
818 UnderlineStyle.NO_UNDERLINE,
819 Colour.BLACK,
820 ScriptStyle.SUBSCRIPT);
821 WritableCellFormat subscriptFormat = new WritableCellFormat
822 (subscript);
823 lr = new Label(2,15, "subscript", subscriptFormat);
824 s1.addCell(lr);
825
826 lr = new Label(0, 17, "Colours");
827 s1.addCell(lr);
828
829 WritableFont red = new WritableFont(WritableFont.ARIAL,
830 WritableFont.DEFAULT_POINT_SIZE,
831 WritableFont.NO_BOLD,
832 false,
833 UnderlineStyle.NO_UNDERLINE,
834 Colour.RED);
835 WritableCellFormat redFormat = new WritableCellFormat(red);
836 lr = new Label(2, 17, "Red", redFormat);
837 s1.addCell(lr);
838
839 WritableFont blue = new WritableFont(WritableFont.ARIAL,
840 WritableFont.DEFAULT_POINT_SIZE,
841 WritableFont.NO_BOLD,
842 false,
843 UnderlineStyle.NO_UNDERLINE,
844 Colour.BLUE);
845 WritableCellFormat blueFormat = new WritableCellFormat(blue);
846 lr = new Label(2, 18, "Blue", blueFormat);
847 s1.addCell(lr);
848
849 WritableFont lime = new WritableFont(WritableFont.ARIAL);
850 lime.setColour(Colour.LIME);
851 WritableCellFormat limeFormat = new WritableCellFormat(lime);
852 limeFormat.setWrap(true);
853 lr = new Label(4, 18, "Modified palette - was lime, now red", limeFormat);
854 s1.addCell(lr);
855
856 WritableCellFormat greyBackground = new WritableCellFormat();
857 greyBackground.setWrap(true);
858 greyBackground.setBackground(Colour.GRAY_50);
859 lr = new Label(2, 19, "Grey background", greyBackground);
860 s1.addCell(lr);
861
862 WritableFont yellow = new WritableFont(WritableFont.ARIAL,
863 WritableFont.DEFAULT_POINT_SIZE,
864 WritableFont.NO_BOLD,
865 false,
866 UnderlineStyle.NO_UNDERLINE,
867 Colour.YELLOW);
868 WritableCellFormat yellowOnBlue = new WritableCellFormat(yellow);
869 yellowOnBlue.setWrap(true);
870 yellowOnBlue.setBackground(Colour.BLUE);
871 lr = new Label(2, 20, "Blue background, yellow foreground", yellowOnBlue);
872 s1.addCell(lr);
873
874 lr = new Label(0, 22, "Null label");
875 s1.addCell(lr);
876
877 lr = new Label(2, 22, null);
878 s1.addCell(lr);
879
880 lr = new Label(0, 24,
881 "A very long label, more than 255 characters\012" +
882 "Rejoice O shores\012" +
883 "Sing O bells\012" +
884 "But I with mournful tread\012" +
885 "Walk the deck my captain lies\012" +
886 "Fallen cold and dead\012"+
887 "Summer surprised, coming over the Starnbergersee\012" +
888 "With a shower of rain. We stopped in the Colonnade\012" +
889 "A very long label, more than 255 characters\012" +
890 "Rejoice O shores\012" +
891 "Sing O bells\012" +
892 "But I with mournful tread\012" +
893 "Walk the deck my captain lies\012" +
894 "Fallen cold and dead\012"+
895 "Summer surprised, coming over the Starnbergersee\012" +
896 "With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" +
897 "Rejoice O shores\012" +
898 "Sing O bells\012" +
899 "But I with mournful tread\012" +
900 "Walk the deck my captain lies\012" +
901 "Fallen cold and dead\012"+
902 "Summer surprised, coming over the Starnbergersee\012" +
903 "With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" +
904 "Rejoice O shores\012" +
905 "Sing O bells\012" +
906 "But I with mournful tread\012" +
907 "Walk the deck my captain lies\012" +
908 "Fallen cold and dead\012"+
909 "Summer surprised, coming over the Starnbergersee\012" +
910 "With a shower of rain. We stopped in the Colonnade\012" +
911 "And sat and drank coffee an talked for an hour\012",
912 arial12format);
913 s1.addCell(lr);
914
915 WritableCellFormat vertical = new WritableCellFormat();
916 vertical.setOrientation(Orientation.VERTICAL);
917 lr = new Label(0, 26, "Vertical orientation", vertical);
918 s1.addCell(lr);
919
920
921 WritableCellFormat plus_90 = new WritableCellFormat();
922 plus_90.setOrientation(Orientation.PLUS_90);
923 lr = new Label(1, 26, "Plus 90", plus_90);
924 s1.addCell(lr);
925
926
927 WritableCellFormat minus_90 = new WritableCellFormat();
928 minus_90.setOrientation(Orientation.MINUS_90);
929 lr = new Label(2, 26, "Minus 90", minus_90);
930 s1.addCell(lr);
931
932 lr = new Label(0, 28, "Modified row height");
933 s1.addCell(lr);
934 s1.setRowView(28, 24);
935
936 lr = new Label(0, 29, "Collapsed row");
937 s1.addCell(lr);
938 s1.setRowView(29, true);
939
940 try
942 {
943 Label l = new Label(0, 30, "Hyperlink to home page");
944 s1.addCell(l);
945
946 URL url = new URL ("http://www.andykhan.com/jexcelapi");
947 WritableHyperlink wh = new WritableHyperlink(0, 30, 8, 31, url);
948 s1.addHyperlink(wh);
949
950 WritableHyperlink wh2 = new WritableHyperlink(7, 30, 9, 31, url);
952 s1.addHyperlink(wh2);
953
954 l = new Label(4, 2, "File hyperlink to documentation");
955 s1.addCell(l);
956
957 File file = new File ("../jexcelapi/docs/index.html");
958 wh = new WritableHyperlink(0, 32, 8, 32, file);
959 s1.addHyperlink(wh);
960
961 wh = new WritableHyperlink(0, 34, 8, 34,
963 "Link to another cell",
964 s1,
965 0, 180, 1, 181);
966 s1.addHyperlink(wh);
967
968 file = new File ("\\\\localhost\\file.txt");
969 wh = new WritableHyperlink(0, 36, 8, 36, file);
970 s1.addHyperlink(wh);
971
972 url = new URL ("http://www.amazon.co.uk/exec/obidos/ASIN/0571058086"+
974 "/qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664");
975 wh = new WritableHyperlink(0, 38, 0, 38, url);
976 s1.addHyperlink(wh);
977 }
978 catch (MalformedURLException e)
979 {
980 System.err.println(e.toString());
981 }
982
983 Label l = new Label(5, 35, "Merged cells", timesBoldUnderline);
985 s1.mergeCells(5, 35, 8, 37);
986 s1.addCell(l);
987
988 l = new Label(5, 38, "More merged cells");
989 s1.addCell(l);
990 Range r = s1.mergeCells(5, 38, 8, 41);
991 s1.insertRow(40);
992 s1.removeRow(39);
993 s1.unmergeCells(r);
994
995 WritableCellFormat wcf = new WritableCellFormat();
997 wcf.setAlignment(Alignment.CENTRE);
998 l = new Label(5, 42, "Centred across merged cells", wcf);
999 s1.addCell(l);
1000 s1.mergeCells(5, 42, 10, 42);
1001
1002 wcf = new WritableCellFormat();
1003 wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
1004 wcf.setBackground(Colour.GRAY_25);
1005 l = new Label(3, 44, "Merged with border", wcf);
1006 s1.addCell(l);
1007 s1.mergeCells(3, 44, 4, 46);
1008
1009
1023
1024 WritableFont courier10ptFont = new WritableFont(WritableFont.COURIER, 10);
1025 WritableCellFormat courier10pt = new WritableCellFormat(courier10ptFont);
1026 l = new Label(0, 49, "Courier fonts", courier10pt);
1027 s1.addCell(l);
1028
1029 WritableFont tahoma12ptFont = new WritableFont(WritableFont.TAHOMA, 12);
1030 WritableCellFormat tahoma12pt = new WritableCellFormat(tahoma12ptFont);
1031 l = new Label(0, 50, "Tahoma fonts", tahoma12pt);
1032 s1.addCell(l);
1033
1034 WritableFont.FontName wingdingsFont =
1035 WritableFont.createFont("Wingdings 2");
1036 WritableFont wingdings210ptFont = new WritableFont(wingdingsFont, 10);
1037 WritableCellFormat wingdings210pt = new WritableCellFormat
1038 (wingdings210ptFont);
1039 l = new Label(0,51, "Bespoke Windgdings 2", wingdings210pt);
1040 s1.addCell(l);
1041
1042 WritableCellFormat shrinkToFit = new WritableCellFormat(times12pt);
1043 shrinkToFit.setShrinkToFit(true);
1044 l = new Label(3,53, "Shrunk to fit", shrinkToFit);
1045 s1.addCell(l);
1046
1047 l = new Label(3,55, "Some long wrapped text in a merged cell",
1048 arial12format);
1049 s1.addCell(l);
1050 s1.mergeCells(3,55,4,55);
1051
1052 l = new Label(0, 57, "A cell with a comment");
1053 WritableCellFeatures cellFeatures = new WritableCellFeatures();
1054 cellFeatures.setComment("the cell comment");
1055 l.setCellFeatures(cellFeatures);
1056 s1.addCell(l);
1057
1058 l = new Label(0, 59,
1059 "A cell with a long comment");
1060 cellFeatures = new WritableCellFeatures();
1061 cellFeatures.setComment("a very long cell comment indeed that won't " +
1062 "fit inside a standard comment box, so a " +
1063 "larger comment box is used instead",
1064 5, 6);
1065 l.setCellFeatures(cellFeatures);
1066 s1.addCell(l);
1067
1068 WritableCellFormat indented = new WritableCellFormat(times12pt);
1069 indented.setIndentation(4);
1070 l = new Label(0, 61, "Some indented text", indented);
1071 s1.addCell(l);
1072 }
1073
1074
1080 private void writeBordersSheet(WritableSheet s) throws WriteException
1081 {
1082 s.getSettings().setProtected(true);
1083
1084 s.setColumnView(1, 15);
1085 s.setColumnView(2, 15);
1086 s.setColumnView(4, 15);
1087 WritableCellFormat thickLeft = new WritableCellFormat();
1088 thickLeft.setBorder(Border.LEFT, BorderLineStyle.THICK);
1089 Label lr = new Label(1,0, "Thick left", thickLeft);
1090 s.addCell(lr);
1091
1092 WritableCellFormat dashedRight = new WritableCellFormat();
1093 dashedRight.setBorder(Border.RIGHT, BorderLineStyle.DASHED);
1094 lr = new Label(2, 0, "Dashed right", dashedRight);
1095 s.addCell(lr);
1096
1097 WritableCellFormat doubleTop = new WritableCellFormat();
1098 doubleTop.setBorder(Border.TOP, BorderLineStyle.DOUBLE);
1099 lr = new Label(1, 2, "Double top", doubleTop);
1100 s.addCell(lr);
1101
1102 WritableCellFormat hairBottom = new WritableCellFormat();
1103 hairBottom.setBorder(Border.BOTTOM, BorderLineStyle.HAIR);
1104 lr = new Label(2, 2, "Hair bottom", hairBottom);
1105 s.addCell(lr);
1106
1107 WritableCellFormat allThin = new WritableCellFormat();
1108 allThin.setBorder(Border.ALL, BorderLineStyle.THIN);
1109 lr = new Label(4, 2, "All thin", allThin);
1110 s.addCell(lr);
1111
1112 WritableCellFormat twoBorders = new WritableCellFormat();
1113 twoBorders.setBorder(Border.TOP, BorderLineStyle.THICK);
1114 twoBorders.setBorder(Border.LEFT, BorderLineStyle.THICK);
1115 lr = new Label(6,2, "Two borders", twoBorders);
1116 s.addCell(lr);
1117
1118 lr = new Label(20, 20, "Dislocated cell - after a page break");
1120 s.addCell(lr);
1121
1122 s.getSettings().setPaperSize(PaperSize.A3);
1124 s.getSettings().setOrientation(PageOrientation.LANDSCAPE);
1125 s.getSettings().setHeaderMargin(2);
1126 s.getSettings().setFooterMargin(2);
1127
1128 s.getSettings().setTopMargin(3);
1129 s.getSettings().setBottomMargin(3);
1130
1131 HeaderFooter header = new HeaderFooter();
1133 header.getCentre().append("Page Header");
1134 s.getSettings().setHeader(header);
1135
1136 HeaderFooter footer = new HeaderFooter();
1137 footer.getRight().append("page ");
1138 footer.getRight().appendPageNumber();
1139 s.getSettings().setFooter(footer);
1140
1141 s.addRowPageBreak(18);
1143 s.insertRow(17);
1144 s.insertRow(17);
1145 s.removeRow(17);
1146
1147 s.addRowPageBreak(30);
1149
1150 lr = new Label(10, 1, "Hidden column");
1152 s.addCell(lr);
1153
1154 lr = new Label(3, 8, "Hidden row");
1155 s.addCell(lr);
1156 s.setRowView(8, true);
1157
1158 WritableCellFormat allThickRed = new WritableCellFormat();
1159 allThickRed.setBorder(Border.ALL, BorderLineStyle.THICK, Colour.RED);
1160 lr = new Label(1, 5, "All thick red", allThickRed);
1161 s.addCell(lr);
1162
1163 WritableCellFormat topBottomBlue = new WritableCellFormat();
1164 topBottomBlue.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLUE);
1165 topBottomBlue.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLUE);
1166 lr = new Label(4, 5, "Top and bottom blue", topBottomBlue);
1167 s.addCell(lr);
1168 }
1169
1170
1173 private void writeLabelsSheet(WritableSheet ws) throws WriteException
1174 {
1175 ws.getSettings().setProtected(true);
1176 ws.getSettings().setPassword("jxl");
1177 ws.getSettings().setVerticalFreeze(5);
1178
1179 WritableFont wf = new WritableFont(WritableFont.ARIAL, 12);
1180 wf.setItalic(true);
1181
1182 WritableCellFormat wcf = new WritableCellFormat(wf);
1183
1184 CellView cv = new CellView();
1185 cv.setSize(25 * 256);
1186 cv.setFormat(wcf);
1187 ws.setColumnView(0, cv);
1188 ws.setColumnView(1, 15);
1189
1190 for (int i = 0; i < 61; i++)
1191 {
1192 Label l1 = new Label(0, i, "Common Label");
1193 Label l2 = new Label(1, i, "Distinct label number " + i);
1194 ws.addCell(l1);
1195 ws.addCell(l2);
1196 }
1197
1198
1201 Label l3 = new Label(0, 61, "Common Label", wcf);
1202 Label l4 = new Label(1, 61, "1-1234567890", wcf);
1203 Label l5 = new Label(2, 61, "2-1234567890", wcf);
1204 ws.addCell(l3);
1205 ws.addCell(l4);
1206 ws.addCell(l5);
1207
1208 for (int i = 62; i < 200; i++)
1209 {
1210 Label l1 = new Label(0, i, "Common Label");
1211 Label l2 = new Label(1, i, "Distinct label number " + i);
1212 ws.addCell(l1);
1213 ws.addCell(l2);
1214 }
1215
1216 wf = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
1218 wf.setColour(Colour.RED);
1219 wcf = new WritableCellFormat(wf);
1220 wcf.setWrap(true);
1221 Label l = new Label(0, 205, "Different format", wcf);
1222 ws.addCell(l);
1223 }
1224
1225
1228 private void writeFormulaSheet(WritableSheet ws) throws WriteException
1229 {
1230 Number nc = new Number (0,0,15);
1232 ws.addCell(nc);
1233
1234 nc = new Number (0,1,16);
1235 ws.addCell(nc);
1236
1237 nc = new Number (0,2,10);
1238 ws.addCell(nc);
1239
1240 nc = new Number (0,3, 12);
1241 ws.addCell(nc);
1242
1243 ws.setColumnView(2, 20);
1244 WritableCellFormat wcf = new WritableCellFormat();
1245 wcf.setAlignment(Alignment.RIGHT);
1246 wcf.setWrap(true);
1247 CellView cv = new CellView();
1248 cv.setSize(25 * 256);
1249 cv.setFormat(wcf);
1250 ws.setColumnView(3, cv);
1251
1252 Formula f = null;
1254 Label l = null;
1255
1256 f = new Formula(2,0, "A1+A2");
1257 ws.addCell(f);
1258 l = new Label(3, 0, "a1+a2");
1259 ws.addCell(l);
1260
1261 f = new Formula(2,1, "A2 * 3");
1262 ws.addCell(f);
1263 l = new Label(3,1, "A2 * 3");
1264 ws.addCell(l);
1265
1266 f = new Formula(2,2, "A2+A1/2.5");
1267 ws.addCell(f);
1268 l = new Label(3,2, "A2+A1/2.5");
1269 ws.addCell(l);
1270
1271 f = new Formula(2,3, "3+(a1+a2)/2.5");
1272 ws.addCell(f);
1273 l = new Label(3,3, "3+(a1+a2)/2.5");
1274 ws.addCell(l);
1275
1276 f = new Formula(2,4, "(a1+a2)/2.5");
1277 ws.addCell(f);
1278 l = new Label(3,4, "(a1+a2)/2.5");
1279 ws.addCell(l);
1280
1281 f = new Formula(2,5, "15+((a1+a2)/2.5)*17");
1282 ws.addCell(f);
1283 l = new Label(3,5, "15+((a1+a2)/2.5)*17");
1284 ws.addCell(l);
1285
1286 f = new Formula(2, 6, "SUM(a1:a4)");
1287 ws.addCell(f);
1288 l = new Label(3, 6, "SUM(a1:a4)");
1289 ws.addCell(l);
1290
1291 f = new Formula(2, 7, "SUM(a1:a4)/4");
1292 ws.addCell(f);
1293 l = new Label(3, 7, "SUM(a1:a4)/4");
1294 ws.addCell(l);
1295
1296 f = new Formula(2, 8, "AVERAGE(A1:A4)");
1297 ws.addCell(f);
1298 l = new Label(3, 8, "AVERAGE(a1:a4)");
1299 ws.addCell(l);
1300
1301 f = new Formula(2, 9, "MIN(5,4,1,2,3)");
1302 ws.addCell(f);
1303 l = new Label(3, 9, "MIN(5,4,1,2,3)");
1304 ws.addCell(l);
1305
1306 f = new Formula(2, 10, "ROUND(3.14159265, 3)");
1307 ws.addCell(f);
1308 l = new Label(3, 10, "ROUND(3.14159265, 3)");
1309 ws.addCell(l);
1310
1311 f = new Formula(2, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
1312 ws.addCell(f);
1313 l = new Label(3, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
1314 ws.addCell(l);
1315
1316 f = new Formula(2,12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
1317 ws.addCell(f);
1318 l = new Label(3,12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
1319 ws.addCell(l);
1320
1321 f = new Formula(2,13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
1322 ws.addCell(f);
1323 l = new Label(3,13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
1324 ws.addCell(l);
1325
1326 f = new Formula(2,14, "IF(A3<=10, \"<= 10\")");
1327 ws.addCell(f);
1328 l = new Label(3,14, "IF(A3<=10, \"<= 10\")");
1329 ws.addCell(l);
1330
1331 f = new Formula(2, 15, "SUM(1,2,3,4,5)");
1332 ws.addCell(f);
1333 l = new Label(3, 15, "SUM(1,2,3,4,5)");
1334 ws.addCell(l);
1335
1336 f = new Formula(2, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
1337 ws.addCell(f);
1338 l = new Label(3, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
1339 ws.addCell(l);
1340
1341 f = new Formula(2, 17, "3*4+5");
1342 ws.addCell(f);
1343 l = new Label(3, 17, "3*4+5");
1344 ws.addCell(l);
1345
1346 f = new Formula(2, 18, "\"Plain text formula\"");
1347 ws.addCell(f);
1348 l = new Label(3, 18, "Plain text formula");
1349 ws.addCell(l);
1350
1351 f = new Formula(2, 19, "SUM(a1,a2,-a3,a4)");
1352 ws.addCell(f);
1353 l = new Label(3, 19, "SUM(a1,a2,-a3,a4)");
1354 ws.addCell(l);
1355
1356 f = new Formula(2, 20, "2*-(a1+a2)");
1357 ws.addCell(f);
1358 l = new Label(3, 20, "2*-(a1+a2)");
1359 ws.addCell(l);
1360
1361 f = new Formula(2, 21, "Number Formats!B1/2");
1362 ws.addCell(f);
1363 l = new Label(3, 21, "Number Formats!B1/2");
1364 ws.addCell(l);
1365
1366 f = new Formula(2, 22, "IF(F22=0, 0, F21/F22)");
1367 ws.addCell(f);
1368 l = new Label(3, 22, "IF(F22=0, 0, F21/F22)");
1369 ws.addCell(l);
1370
1371 f = new Formula(2, 23, "RAND()");
1372 ws.addCell(f);
1373 l = new Label(3, 23, "RAND()");
1374 ws.addCell(l);
1375
1376 StringBuffer buf = new StringBuffer ();
1377 buf.append("'");
1378 buf.append(workbook.getSheet(0).getName());
1379 buf.append("'!");
1380 buf.append(CellReferenceHelper.getCellReference(9, 18));
1381 buf.append("*25");
1382 f = new Formula(2, 24, buf.toString());
1383 ws.addCell(f);
1384 l = new Label(3, 24, buf.toString());
1385 ws.addCell(l);
1386
1387 wcf = new WritableCellFormat(DateFormats.DEFAULT);
1388 f = new Formula(2, 25, "NOW()", wcf);
1389 ws.addCell(f);
1390 l = new Label(3, 25, "NOW()");
1391 ws.addCell(l);
1392
1393 f = new Formula(2, 26, "$A$2+A3");
1394 ws.addCell(f);
1395 l = new Label(3, 26, "$A$2+A3");
1396 ws.addCell(l);
1397
1398 f = new Formula(2, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
1399 ws.addCell(f);
1400 l = new Label(3, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
1401 ws.addCell(l);
1402
1403 f = new Formula(2, 28, "SUM(A1,A2,A3,A4)");
1404 ws.addCell(f);
1405 l = new Label(3, 28, "SUM(A1,A2,A3,A4)");
1406 ws.addCell(l);
1407
1408 l = new Label(1, 29, "a1");
1409 ws.addCell(l);
1410 f = new Formula(2, 29, "SUM(INDIRECT(ADDRESS(2,29)):A4)");
1411 ws.addCell(f);
1412 l = new Label(3, 29, "SUM(INDIRECT(ADDRESS(2,29):A4)");
1413 ws.addCell(l);
1414
1415 f = new Formula(2, 30, "COUNTIF(A1:A4, \">=12\")");
1416 ws.addCell(f);
1417 l = new Label(3, 30, "COUNTIF(A1:A4, \">=12\")");
1418 ws.addCell(l);
1419
1420 f = new Formula(2, 31, "MAX($A$1:$A$4)");
1421 ws.addCell(f);
1422 l = new Label(3, 31, "MAX($A$1:$A$4)");
1423 ws.addCell(l);
1424
1425 f = new Formula(2, 32, "OR(A1,TRUE)");
1426 ws.addCell(f);
1427 l = new Label(3, 32, "OR(A1,TRUE)");
1428 ws.addCell(l);
1429
1430 f = new Formula(2, 33, "ROWS(A1:C14)");
1431 ws.addCell(f);
1432 l = new Label(3, 33, "ROWS(A1:C14)");
1433 ws.addCell(l);
1434
1435 f = new Formula(2, 34, "COUNTBLANK(A1:C14)");
1436 ws.addCell(f);
1437 l = new Label(3, 34, "COUNTBLANK(A1:C14)");
1438 ws.addCell(l);
1439
1440 f = new Formula(2, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
1441 ws.addCell(f);
1442 l = new Label(3, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
1443 ws.addCell(l);
1444
1445 f = new Formula(2, 36,
1446 "HYPERLINK(\"http://www.amazon.co.uk/exec/obidos/ASIN/0571058086qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664\", \"Long hyperlink\")");
1447 ws.addCell(f);
1448
1449 f = new Formula(2, 37, "1234567+2699");
1450 ws.addCell(f);
1451 l = new Label(3, 37, "1234567+2699");
1452 ws.addCell(l);
1453
1454
1455
1472 }
1473
1474
1477 private void writeImageSheet(WritableSheet ws) throws WriteException
1478 {
1479 Label l = new Label(0, 0, "Weald & Downland Open Air Museum, Sussex");
1480 ws.addCell(l);
1481
1482 WritableImage wi = new WritableImage
1483 (0, 3, 5, 7, new File ("resources/wealdanddownland.png"));
1484 ws.addImage(wi);
1485
1486 l = new Label(0, 12, "Merchant Adventurers Hall, York");
1487 ws.addCell(l);
1488
1489 wi = new WritableImage(5, 12, 4, 10,
1490 new File ("resources/merchantadventurers.png"));
1491 ws.addImage(wi);
1492
1493
1498 }
1499}
1500
1501
1502
1503
1504
1505
1506
1507
1508 |
Popular Tags |
Java API By Example, From Geeks To Geeks.