1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
|
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>The New Halloween Document</title>
</head>
<body bgcolor="#ffffff" marginheight="4" marginwidth="4" leftmargin="4" topmargin="4" alink="#023264" vlink="#023264" link="#525D76" text="#000000">
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td align="left" valign="top"><a href="http://jakarta.apache.org/index.html"><img src="images/jakarta-logo.gif" border="0" vspace="0" hspace="0"></a></td><td bgcolor="#ffffff" align="left" valign="top" width="100%"><img src="images/header.gif" align="right" border="0" vspace="0" hspace="0"></td>
</tr>
<tr>
<td colspan="2" height="2" width="100%">
<hr size="1" noshade="">
</td>
</tr>
</table>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td valign="top" width="1%"></td><td nowrap="1" valign="top" width="14%">
<br>
<font face="arial,helvetica,sanserif">
<br>
<font size="+1" color="#000000">Navigation</font><font size="-1">
<ul>
<li>
<a href="../index.html"><font size="-1">Main</font></a>
</li>
</ul>
</font>
<br>
<font size="+1" color="#000000">HSSF</font><font size="-1">
<ul>
<li>
<a href="how-to.html"><font size="-1">HOWTO</font></a>
</li>
<li>
<a href="use-case.html"><font size="-1">Use Case</font></a>
</li>
<li>
<a href="diagrams.html"><font size="-1">Pictorial Docs</font></a>
</li>
</ul>
</font>
<br>
<font size="+1" color="#000000">Contributer's Guide</font><font size="-1">
<ul>
<li>
<a href="record-generator.html"><font size="-1">Record Generator</font></a>
</li>
</ul>
</font>
<br>
<br>
</font></td><td align="left" valign="top" width="*">
<title>The New Halloween Document</title>
<center>
<table width="80%">
<tr>
<td bgcolor="#F3DD61"><font face="Arial,sans-serif" size="+1">
<center>
<b>The New Halloween Document</b>
</center>
</font></td>
</tr>
</table>
</center>
<font size="-2" color="#000000">
<p>
<a href="mailto:"></a>
</p>
</font>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="100%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="+1"><font face="Arial,sans-serif"><b>How to use the HSSF prototype API</b></font></font></td>
</tr>
<tr>
<td>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="99%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="+0"><font face="Arial,sans-serif"><b>Recent revision history</b></font></font></td>
</tr>
<tr>
<td>
<br>
<ul>
<li>12.30.2001 - revised for poi 1.0-final - minor revisions</li>
<li>01.03.2001 - revised for poi 1.1-devel</li>
</ul>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="99%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="+0"><font face="Arial,sans-serif"><b>Capabilities</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">This release of the how-to outlines functionality included in a
development build of HSSF. Those looking for information on the
release edition should look in the poi-src for the release or at a
previous edition in CVS tagged for that release.</p>
<p align="justify">
This release allows numeric and string cell values to be written to
or read from an XLS file. Also in this release is row and column
sizing, cell styling (bold, italics, borders,etc), and support for
built-in data formats. New to this release is an event-based API
for reading XLS files. It differs greatly from the read/write API
and is intended for intermediate developers who need a smaller
memory footprint. It will also serve as the basis for the HSSF
Generator.</p>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="99%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="+0"><font face="Arial,sans-serif"><b>Target Audience</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">This release is intended for developers, java-fanatics and the
just generally all around impatient. HSSF has not yet been
extensively tested in a high load multi-threaded situation. This
release is not considered to be "golden" as it has new
features that have not been extensively tested, and is an early 2.0
build that could be restructured significantly in the future (not
that there are necessarily plans to do so, just that you're better
off basing your code on 1.0 and sticking with it if you don't need
2.0 stuff bad enough to deal with us pulling the rug out from under
you regularly).</p>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="99%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="+0"><font face="Arial,sans-serif"><b>General Use</b></font></font></td>
</tr>
<tr>
<td>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="98%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-1"><font face="Arial,sans-serif"><b>User API</b></font></font></td>
</tr>
<tr>
<td>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>Writing a new one</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">The high level API (package: org.apache.poi.hssf.usermodel)
is what most people should use. Usage is very simple.
</p>
<p align="justify">Workbooks are created by creating an instance of
org.apache.poi.hssf.usermodel.HSSFWorkbook.
</p>
<p align="justify">Sheets are created by calling createSheet() from an existing
instance of HSSFWorkbook, the created sheet is automatically added in
sequence to the workbook. In this release there will always be at
least three sheets generated regardless of whether you have three
sheets. More than three sheets is probably not supported. Sheets do
not in themselves have a sheet name (the tab at the bottom); you set
the name associated with a sheet by calling
HSSFWorkbook.setSheetName(sheetindex,"SheetName").</p>
<p align="justify">Rows are created by calling createRow(rowNumber) from an existing
instance of HSSFSheet. Only rows that have cell values should be
added to the sheet. To set the row's height, you just call
setRowHeight(height) on the row object. The height must be given in
twips, or 1/20th of a point. If you prefer, there is also a
setRowHeightInPoints method.
</p>
<p align="justify">Cells are created by calling createCell(column, type) from an
existing HSSFRow. Only cells that have values should be added to the
row. Cells should have their cell type set to either
HSSFCell.CELL_TYPE_NUMERIC or HSSFCell.CELL_TYPE_STRING depending on
whether they contain a numeric or textual value. Cells must also have
a value set. Set the value by calling setCellValue with either a
String or double as a parameter. Individual cells do not have a
width; you must call setColumnWidth(colindex, width) (use units of
1/256th of a character) on the HSSFSheet object. (You can't do it on
an individual basis in the GUI either).</p>
<p align="justify">Cells are styled with HSSFCellStyle objects which in turn contain
a reference to an HSSFFont object. These are created via the
HSSFWorkbook object by calling createCellStyle() and createFont().
Once you create the object you must set its parameters (colors,
borders, etc). To set a font for an HSSFCellStyle call
setFont(fontobj).
</p>
<p align="justify">Once you have generated your workbook, you can write it out by
calling write(outputStream) from your instance of Workbook, passing
it an OutputStream (for instance, a FileOutputStream or
ServletOutputStream). You must close the OutputStream yourself. HSSF
does not close it for you.
</p>
<p align="justify">Here is some example code (excerpted and adapted from
org.apache.poi.hssf.dev.HSSF test class):</p>
<div align="center">
<table cellspacing="2" cellpadding="2" border="1">
<tr>
<td>
<pre> // create a new file
FileOutputStream out = new FileOutputStream("/home/me/myfile.xls");
// create a new workbook
HSSFWorkbook wb = new HSSFWorkbook();
// create a new sheet
HSSFSheet s = wb.createSheet();
// declare a row object reference
HSSFRow r = null;
// declare a cell object reference
HSSFCell c = null;
// create 3 cell styles
HSSFCellStyle cs = wb.createCellStyle();
HSSFCellStyle cs2 = wb.createCellStyle();
HSSFCellStyle cs3 = wb.createCellStyle();
// create 2 fonts objects
HSSFFont f = wb.createFont();
HSSFFont f2 = wb.createFont();
//set font 1 to 12 point type
f.setFontHeightInPoints((short)12);
//make it red
f.setColor((short)0xA);
// make it bold
//arial is the default font
f.setBoldweight(f.BOLDWEIGHT_BOLD);
//set font 2 to 10 point type
f2.setFontHeightInPoints((short)10);
//make it the color at palette index 0xf (white)
f2.setColor((short)0xf);
//make it bold
f2.setBoldweight(f2.BOLDWEIGHT_BOLD);
//set cell stlye
cs.setFont(f);
//set the cell format see HSSFDataFromat for a full list
cs.setDataFormat(HSSFDataFormat.getFormat("($#,##0_);[Red]($#,##0)"));
//set a thin border
cs2.setBorderBottom(cs2.BORDER_THIN);
//fill w fg fill color
cs2.setFillPattern((short)1);
// set foreground fill to red
cs2.setFillForegroundColor((short)0xA);
// set the font
cs2.setFont(f2);
// set the sheet name to HSSF Test
wb.setSheetName(0,"HSSF Test");
// create a sheet with 300 rows (0-299)
for (rownum = (short)0; rownum < 300; rownum++)
{
// create a row
r = s.createRow(rownum);
// on every other row
if ( (rownum % 2) == 0) {
// make the row height bigger (in twips - 1/20 of a point)
r.setHeight((short)0x249);
}
//r.setRowNum(( short ) rownum);
// create 50 cells (0-49) (the += 2 becomes apparent later
for (short cellnum = (short)0; cellnum < 50; cellnum += 2)
{
// create a numeric cell
c = r.createCell(cellnum,HSSFCell.CELL_TYPE_NUMERIC);
// do some goofy math to demonstrate decimals
c.setCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
// on every other row
if ( (rownum % 2) == 0) {
// set this cell to the first cell style we defined
c.setCellStyle(cs);
}
// create a string cell (see why += 2 in the
c = r.createCell((short)(cellnum+1),HSSFCell.CELL_TYPE_STRING);
// set the cell's string value to "TEST"
c.setCellValue("TEST");
// make this column a bit wider
s.setColumnWidth((short)(cellnum+1), (short)((50*8) / ((double)1/20)) );
// on every other row
if ( (rownum % 2) == 0) {
// set this to the white on red cell style
// we defined above
c.setCellStyle(cs2);
}
}
}
//draw a thick black border on the row at the bottom using BLANKS
// advance 2 rows
rownum++;
rownum++;
r = s.createRow(rownum);
// define the third style to be the default
// except with a thick black border at the bottom
cs3.setBorderBottom(cs3.BORDER_THICK);
//create 50 cells
for (short cellnum = (short)0; cellnum < 50; cellnum++) {
//create a blank type cell (no value)
c = r.createCell(cellnum,HSSFCell.CELL_TYPE_BLANK);
// set it to the thick black border style
c.setCellStyle(cs3);
}
//end draw thick black border
// demonstrate adding/naming and deleting a sheet
// create a sheet, set its title then delete it
s = wb.createSheet();
wb.setSheetName(1,"DeletedSheet");
wb.removeSheetAt(1);
//end deleted sheet
// write the workbook to the output stream
// close our file (don't blow out our file handles
wb.write(out);
out.close();
</pre>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>Reading or modifying an existing file</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">Reading in a file is equally simple. To read in a file, create a
new instance of org.apache.poi.poifs.Filesystem, passing in an open InputStream, such as a FileInputStream
for your XLS, to the constructor. Construct a new instance of
org.apache.poi.hssf.usermodel.HSSFWorkbook passing the
Filesystem instance to the constructor. From there you have access to
all of the high level model objects through their assessor methods
(workbook.getSheet(sheetNum), sheet.getRow(rownum), etc).
</p>
<p align="justify">Modifying the file you have read in is simple. You retrieve the
object via an assessor method, remove it via a parent object's remove
method (sheet.removeRow(hssfrow)) and create objects just as you
would if creating a new xls. When you are done modifying cells just
call workbook.write(outputstream) just as you did above.</p>
<p align="justify">An example of this can be seen in
<a href="http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/*checkout*/poi/poi/production/src/net/sourceforge/poi/hssf/dev/HSSF.java?rev=HEAD&content-type=text/plain">org.apache.poi.hssf.dev.HSSF</a>.</p>
</td>
</tr>
</table>
</div>
<br>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="98%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-1"><font face="Arial,sans-serif"><b>Event API</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">The event API is brand new. It is intended for intermediate
developers who are willing to learn a little bit of the low level API
structures. Its relatively simple to use, but requires a basic
understanding of the parts of an Excel file (or willingness to
learn). The advantage provided is that you can read an XLS with a
relatively small memory footprint.
</p>
<p align="justify">To use this API you construct an instance of
org.apache.poi.hssf.eventmodel.HSSFRequest. Register a class you
create that supports the
org.apache.poi.hssf.eventmodel.HSSFListener interface using the
HSSFRequest.addListener(yourlistener, recordsid). The recordsid
should be a static reference number (such as BOFRecord.sid) contained
in the classes in org.apache.poi.hssf.record. The trick is you
have to know what these records are. Alternatively you can call
HSSFRequest.addListenerForAllRecords(mylistener). In order to learn
about these records you can either read all of the javadoc in the
org.apache.poi.hssf.record package or you can just hack up a
copy of org.apache.poi.hssf.dev.EFHSSF and adapt it to your
needs. TODO: better documentation on records.</p>
<p align="justify">Once you've registered your listeners in the HSSFRequest object
you can construct an instance of
org.apache.poi.poifs.filesystem.FileSystem (see POIFS howto) and
pass it your XLS file inputstream. You can either pass this, along
with the request you constructed, to an instance of HSSFEventFactory
via the HSSFEventFactory.processWorkbookEvents(request, Filesystem)
method, or you can get an instance of DocumentInputStream from
Filesystem.createDocumentInputStream("Workbook") and pass
it to HSSFEventFactory.processEvents(request, inputStream). Once you
make this call, the listeners that you constructed receive calls to
their processRecord(Record) methods with each Record they are
registered to listen for until the file has been completely read.
</p>
<p align="justify">A code excerpt from org.apache.poi.hssf.dev.EFHSSF (which is
in CVS or the source distribution) is reprinted below with excessive
comments:</p>
<div align="center">
<table cellspacing="2" cellpadding="2" border="1">
<tr>
<td>
<pre>
//this non-public class implements the required interface
// we construct it with a copy of its container class...this is cheap but effective
class EFHSSFListener implements HSSFListener {
EFHSSF efhssf;
public EFHSSFListener(EFHSSF efhssf) {
this.efhssf = efhssf;
}
// we just use this as an adapter so we pass the record to the method in the container class
public void processRecord(Record record) {
efhssf.recordHandler(record);
}
}
//here is an excerpt of the main line execution code from EFHSSF
public void run() throws IOException {
// create a new file input stream with the input file specified
// at the command line
FileInputStream fin = new FileInputStream(infile);
// create a new org.apache.poi.poifs.filesystem.Filesystem
Filesystem poifs = new Filesystem(fin);
// get the Workbook (excel part) stream in a InputStream
InputStream din = poifs.createDocumentInputStream("Workbook");
// construct out HSSFRequest object
HSSFRequest req = new HSSFRequest();
// lazy listen for ALL records with the listener shown above
req.addListenerForAllRecords(new EFHSSFListener(this));
// create our event factory
HSSFEventFactory factory = new HSSFEventFactory();
// process our events based on the document input stream
factory.processEvents(req,din);
// once all the events are processed close our file input stream
fin.close();
// and our document input stream (don't want to leak these!)
din.close();
// create a new output stream from filename specified at the command line
FileOutputStream fout = new FileOutputStream(outfile);
// write the HSSFWorkbook (class member) we created out to the file.
workbook.write(fout);
// close our file output stream
fout.close();
// print done. Go enjoy your copy of the file.
System.out.println("done.");
}
//here is an excerpt of the recordHander called from our listener.
// the record handler in the container class is intent on just rewriting the file
public void recordHandler(Record record) {
HSSFRow row = null;
HSSFCell cell = null;
int sheetnum = -1;
switch (record.getSid()) {
// the BOFRecord can represent either the beginning of a sheet or the workbook
case BOFRecord.sid:
BOFRecord bof = (BOFRecord) record;
if (bof.getType() == bof.TYPE_WORKBOOK) {
//if its the workbook then create a new HSSFWorkbook
workbook = new HSSFWorkbook();
// assigned to the class level member
} else if (bof.getType() == bof.TYPE_WORKSHEET) {
sheetnum++;
// otherwise if its a sheet increment the sheetnum index
cursheet = workbook.getSheetAt(sheetnum);
}
break;
// get the sheet at that index and assign it to method variable
// cursheet (the sheet was created when the BoundSheetRecord record occurred
case BoundSheetRecord.sid:
// when we find a boundsheet record create a new sheet in the workbook and
BoundSheetRecord bsr = (BoundSheetRecord) record;
// assign it the name specified in this record.
workbook.createSheet(bsr.getSheetname());
break;
// if this is a row record add the row to the current sheet
case RowRecord.sid:
RowRecord rowrec = (RowRecord) record;
// assign our row the rownumber specified in the Row Record
cursheet.createRow(rowrec.getRowNumber());
break;
// if this is a NumberRecord (RKRecord, MulRKRecord get converted to Number
// records) then get the row specified in the number record from the current
// sheet. With this instance of HSSFRow create a new HSSFCell with the column
// number specified in the record and assign it type NUMERIC
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
row = cursheet.getRow(numrec.getRow());
cell = row.createCell(numrec.getColumn(),HSSFCell.CELL_TYPE_NUMERIC);
// set the HSSFCell's value to the value stored in the NumberRecord
cell.setCellValue(numrec.getValue());
break;
// if this is the SSTRecord (occurs once in the workbook) then add all of its
// strings to our workbook. We'll look them up later when we add LABELSST records.
case SSTRecord.sid:
SSTRecord sstrec = (SSTRecord) record;
for (int k = 0; k < sstrec.getNumUniqueStrings(); k++) {
workbook.addSSTString(sstrec.getString(k));
}
break;
// if this is a LabelSSTRecord then get the row specified in the LabelSSTRecord from
// the current sheet. With this instance of HSSFRow create a new HSSFCell with the
// column nubmer specified in the record and set the type to type STRING.
case LabelSSTRecord.sid:
LabelSSTRecord lrec = (LabelSSTRecord) record;
row = cursheet.getRow(lrec.getRow());
cell = row.createCell(lrec.getColumn(),HSSFCell.CELL_TYPE_STRING);
//set the cells value to the string in our workbook object (added in the case
//above) at the index specified by the LabelSSTRecord.
cell.setCellValue(workbook.getSSTString(lrec.getSSTIndex()));
break;
}
}</pre>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="98%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-1"><font face="Arial,sans-serif"><b>Low Level APIs</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">The low level API is not much to look at. It consists of lots of
"Records" in the org.apache.poi.hssf.record.* package,
and set of helper classes in org.apache.poi.hssf.model.*. The
record classes are consistent with the low level binary structures
inside a BIFF8 file (which is embedded in a POIFS file system). You
probably need the book: "Microsoft Excel 97 Developer's Kit"
from Microsoft Press in order to understand how these fit together
(out of print but easily obtainable from Amazon's used books). In
order to gain a good understanding of how to use the low level APIs
should view the source in org.apache.poi.hssf.usermodel.* and
the classes in org.apache.poi.hssf.model.*. You should read the
documentation for the POIFS libraries as well.</p>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="98%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-1"><font face="Arial,sans-serif"><b>HSSF Class/Test Application</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">The HSSF application is nothing more than a test for the high
level API (and indirectly the low level support). The main body of
its code is repeated above. To run it:
</p>
<ul>
<li>download the poi-alpha build and untar it (tar xvzf
tarball.tar.gz)
</li>
<li>set up your classpath as follows:
<code>export HSSFDIR={wherever you put HSSF's jar files}
export LOG4JDIR={wherever you put LOG4J's jar files}
export CLASSPATH=$CLASSPATH:$HSSFDIR/hssf.jar:$HSSFDIR/poi-poifs.jar:$HSSFDIR/poi-util.jar:$LOG4JDIR/jog4j.jar</code>
</li>
<li>type:
<code>java org.apache.poi.hssf.dev.HSSF ~/myxls.xls write</code>
</li>
</ul>
<p align="justify">This should generate a test sheet in your home directory called <code>"myxls.xls"</code>. </p>
<ul>
<li>Type:
<code>java org.apache.poi.hssf.dev.HSSF ~/input.xls output.xls
This is the read/write/modify test. It reads in the spreadsheet, modifies a cell, and writes it back out.
Failing this test is not necessarily a bad thing. If HSSF tries to modify a non-existant sheet then this will
most likely fail. No big deal. </code>
</li>
</ul>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="98%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-1"><font face="Arial,sans-serif"><b>HSSF Logging facility</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">HSSF now has a logging facility (using log4j - thanks jakarta!)
that will record massive amounts of debugging information. Its mostly
useful to us hssf-developing geeks, but might be useful in tracking
down problems. By default we turn this off because it results in
unnecessary performance degradation when fully turned on! Using it is
simple. You need an hssflog.properties file (example listed below,
those familiar with log4j can customize this as they wish). You can
either put this in your home directory (or wherever the default
directory is on windows which I suspect is c:\windows) or you can put
it wherever you want and set the HSSF.log to the path ending in "/"
(or "\\" on windows). If for any reason HSSF can't find it,
you get no logging. If the log configuration dictates the logging be
turned off, you get no logging.</p>
<p align="justify">Here is an example hssflog.properties (actually its not an example
its mine):
<div align="center">
<table cellspacing="2" cellpadding="2" border="1">
<tr>
<td>
<pre># Set root category priority to DEBUG and its only appender to A1.
log4j.rootCategory=DEBUG, A1
# A1 is set to be a ConsoleAppender.
log4j.appender.A1=org.apache.log4j.ConsoleAppender
# A1 uses PatternLayout.
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n
#uncomment below to change the level to WARN to disable debugging information. This effectively turns off logging.
#the default level is DEBUG (and changing it to DEBUG is the basically the same thing as leaving it commented out).
#log4j.category.org.apache.poi=WARN</pre>
</td>
</tr>
</table>
</div>
</p>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="98%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-1"><font face="Arial,sans-serif"><b>HSSF Developer's tools</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">HSSF has a number of tools useful for developers to debug/develop
stuff using HSSF (and more generally XLS files). We've already
discussed the app for testing HSSF read/write/modify capabilities;
now we'll talk a bit about BiffViewer. Early on in the development of
HSSF, it was decided that knowing what was in a record, what was
wrong with it, etc. was virtually impossible with the available
tools. So we developed BiffViewer. You can find it at
org.apache.poi.hssf.dev.BiffViewer. It performs two basic
functions and a derivative.
</p>
<p align="justify">The first is "biffview". To do this you run it (assumes
you have everything setup in your classpath and that you know what
you're doing enough to be thinking about this) with an xls file as a
parameter. It will give you a listing of all understood records with
their data and a list of not-yet-understood records with no data
(because it doesn't know how to interpret them). This listing is
useful for several things. First, you can look at the values and SEE
what is wrong in quasi-English. Second, you can send the output to a
file and compare it.
</p>
<p align="justify">The second function is "big freakin dump", just pass a
file and a second argument matching "bfd" exactly. This
will just make a big hexdump of the file.
</p>
<p align="justify">Lastly, there is "mixed" mode which does the same as
regular biffview, only it includes hex dumps of certain records
intertwined. To use that just pass a file with a second argument
matching "on" exactly.</p>
<p align="justify">In the next release cycle we'll also have something called a
FormulaViewer. The class is already there, but its not very useful
yet. When it does something, I'll document it.</p>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="98%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-1"><font face="Arial,sans-serif"><b>What's Next?</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">This release contains code that supports "internationalization"
or more accurately non-US/UK languages; however, it has not been
tested with the new API changes (please help us with this). We've
shifted focus a bit for this release in recognition of the
international support we've gotten. We're going to focus on western
European languages for our first beta. We're more than happy to
accept help in supporting non-Western European languages if someone
who knows what they're doing in this area is willing to pitch in!
(There is next to no documentation on what is necessary to support
such a move and its really hard to support a language when you don't even
know the alphabet).</p>
<p align="justify">This release of HSSF does not yet support Formulas. I've been
focusing on the requests I've gotten in. That being said, if we get
more user feedback on what is most useful first we'll aim for that.
As a general principal, HSSF's goal is to support HSSF-Serializer
(meaning an emphasis on write). We would like to hear from you! How
are you using HSSF/POIFS? How would you like to use it? What features
are most important first?
</p>
<p align="justify">This release is near feature freeze for the 1.0-beta. All
priorities refer to things we'll be adding in the next release
(probably 2.0). The 1.0-beta is scheduled for release in the mid to
late December timeframe. While it's way to early to say when the
2.0-beta will be released, my "gut" feeling is to aim for
around March and have at least the first three items.</p>
<p align="justify">Current list of priorities:</p>
<ol>
<li>Helper class for fonts, etc.</li>
<li>Add Formulas.</li>
<li>Implement more record types (for other things ... not sure
what this will mean yet).</li>
<li>Add more dummy checks (for when API user's do things they
"can't" do)</li>
<li>Add support for embedded graphics and stuff like that.</li>
<li>Create new adapter object for handling MulBlank, MulRk, Rk
records.</li>
</ol>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="98%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-1"><font face="Arial,sans-serif"><b>Changes</b></font></font></td>
</tr>
<tr>
<td>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>1.1.0</b></font></font></td>
</tr>
<tr>
<td>
<br>
<ol>
<li>Created new event model</li>
<li>Optimizations made to HSSF including aggregate records for
values, rows, etc.</li>
<li>predictive sizing, offset based writing (instead of lots of
array copies)</li>
<li>minor re-factoring and bug fixes.</li>
</ol>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>1.0.0</b></font></font></td>
</tr>
<tr>
<td>
<br>
<ol>
<li>Minor documentation updates.</li>
</ol>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>0.14.0</b></font></font></td>
</tr>
<tr>
<td>
<br>
<ol>
<ol>
<li>Added DataFormat helper class and exposed set and get format
on HSSFCellStyle</li>
<li>Fixed column width apis (unit wise) and various javadoc on
the subject</li>
<li>Fix for Dimensions record (again)... (one of these days I'll
write a unit test for this ;-p).</li>
<li>Some optimization on sheet creation.</li>
</ol>
</ol>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>0.13.0</b></font></font></td>
</tr>
<tr>
<td>
<br>
<p align="justify">- NO WAY!</p>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>0.12.0</b></font></font></td>
</tr>
<tr>
<td>
<br>
<ol>
<li>Added MulBlank, Blank, ColInfo</li>
<li>Added log4j facility and removed all sys.out type logging</li>
<li>Added support for adding font's, styles and corresponding
high level api for styling cells</li>
<li>added support for changing row height, cell width and default
row height/cell width.</li>
<li>Added fixes for internationalization (UTF-16 should work now
from HSSFCell.setStringValue, etc when the encoding is set)</li>
<li>added support for adding/removing and naming sheets.</li>
</ol>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>0.11.0</b></font></font></td>
</tr>
<tr>
<td>
<br>
<ol>
<li>Bugfix release. We were throwing an exception when reading
RKRecord objects.</li>
</ol>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>0.10.0</b></font></font></td>
</tr>
<tr>
<td>
<br>
<ol>
<li>Got continuation records to work (read/write)</li>
<li>Added various pre-support for formulas</li>
<li>Massive API reorganization, repackaging.</li>
<li>BiffViewer class added for validating HSSF & POI and/or
HSSF Output.</li>
<li>Better API support for modification.</li>
</ol>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>0.7 (and interim releases)</b></font></font></td>
</tr>
<tr>
<td>
<br>
<ol>
<li>Added encoding flag to high and low level api to use utf-16
when needed (HSSFCell.setEncoding())</li>
<li>added read only support for Label records (which are
reinterpreted as LabelSST when written)</li>
<li>Broken continuation record implementation (oops)</li>
<li>BiffViewer class added for validating HSSF & POI and/or
HSSF Output.</li>
</ol>
</td>
</tr>
</table>
</div>
<br>
<div align="right">
<table cellspacing="0" cellpadding="2" border="0" width="97%">
<tr>
<td bgcolor="#525D76"><font color="#ffffff" size="-2"><font face="Arial,sans-serif"><b>0.6 (release)</b></font></font></td>
</tr>
<tr>
<td>
<br>
<ol>
<li>Support for read/write and modify.</li>
<li>Read only support for MulRK records (converted to Number when
writing)
</li>
</ol>
</td>
</tr>
</table>
</div>
<br>
</td>
</tr>
</table>
</div>
<br>
</td>
</tr>
</table>
</div>
<br>
</td>
</tr>
</table>
</div>
<br>
</td>
</tr>
</table>
<br>
<table cellpadding="0" cellspacing="0" border="0" width="100%">
<tr>
<td>
<hr size="1" noshade="">
</td>
</tr>
<tr>
<td align="center"><font color="#525D76" size="-1" face="arial,helvetica,sanserif"><i>
Copyright ©2002 Apache Software Foundation
</i></font></td><td width="5%" align="right"><img src="images/cocoon2-small.jpg" align="right" border="0" vspace="0" hspace="0"></td>
</tr>
</table>
</body>
</html>
|
