/
HSSFWorkbook.java
1703 lines (1488 loc) · 58.2 KB
/
HSSFWorkbook.java
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
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.hssf.usermodel;
import java.io.ByteArrayInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.POIDocument;
import org.apache.poi.ddf.EscherBSERecord;
import org.apache.poi.ddf.EscherBitmapBlip;
import org.apache.poi.ddf.EscherBlipRecord;
import org.apache.poi.ddf.EscherRecord;
import org.apache.poi.hssf.model.RecordStream;
import org.apache.poi.hssf.model.Sheet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.AbstractEscherHolderRecord;
import org.apache.poi.hssf.record.BackupRecord;
import org.apache.poi.hssf.record.DrawingGroupRecord;
import org.apache.poi.hssf.record.EmbeddedObjectRefSubRecord;
import org.apache.poi.hssf.record.ExtendedFormatRecord;
import org.apache.poi.hssf.record.FontRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NameRecord;
import org.apache.poi.hssf.record.ObjRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RecordFactory;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.UnicodeString;
import org.apache.poi.hssf.record.UnknownRecord;
import org.apache.poi.hssf.record.aggregates.RecordAggregate.RecordVisitor;
import org.apache.poi.hssf.record.formula.Area3DPtg;
import org.apache.poi.hssf.record.formula.MemFuncPtg;
import org.apache.poi.hssf.record.formula.NameXPtg;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.record.formula.Ref3DPtg;
import org.apache.poi.hssf.record.formula.UnionPtg;
import org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.DirectoryNode;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;
/**
* High level representation of a workbook. This is the first object most users
* will construct whether they are reading or writing a workbook. It is also the
* top level object for creating new sheets/etc.
*
* @see org.apache.poi.hssf.model.Workbook
* @see org.apache.poi.hssf.usermodel.HSSFSheet
* @author Andrew C. Oliver (acoliver at apache dot org)
* @author Glen Stampoultzis (glens at apache.org)
* @author Shawn Laubach (slaubach at apache dot org)
* @version 2.0-pre
*/
public class HSSFWorkbook extends POIDocument
{
private static final int MAX_ROW = 0xFFFF;
private static final short MAX_COLUMN = (short)0x00FF;
private static final int DEBUG = POILogger.DEBUG;
/**
* used for compile-time performance/memory optimization. This determines the
* initial capacity for the sheet collection. Its currently set to 3.
* Changing it in this release will decrease performance
* since you're never allowed to have more or less than three sheets!
*/
public final static int INITIAL_CAPACITY = 3;
/**
* this is the reference to the low level Workbook object
*/
private Workbook workbook;
/**
* this holds the HSSFSheet objects attached to this workbook
*/
protected List _sheets;
/**
* this holds the HSSFName objects attached to this workbook
*/
private ArrayList names;
/**
* this holds the HSSFFont objects attached to this workbook.
* We only create these from the low level records as required.
*/
private Hashtable fonts;
/**
* holds whether or not to preserve other nodes in the POIFS. Used
* for macros and embedded objects.
*/
private boolean preserveNodes;
/**
* Used to keep track of the data formatter so that all
* createDataFormatter calls return the same one for a given
* book. This ensures that updates from one places is visible
* someplace else.
*/
private HSSFDataFormat formatter;
/**
* The policy to apply in the event of missing or
* blank cells when fetching from a row.
* See {@link MissingCellPolicy}
*/
private MissingCellPolicy missingCellPolicy = HSSFRow.RETURN_NULL_AND_BLANK;
/** Extended windows meta file */
public static final int PICTURE_TYPE_EMF = 2;
/** Windows Meta File */
public static final int PICTURE_TYPE_WMF = 3;
/** Mac PICT format */
public static final int PICTURE_TYPE_PICT = 4;
/** JPEG format */
public static final int PICTURE_TYPE_JPEG = 5;
/** PNG format */
public static final int PICTURE_TYPE_PNG = 6;
/** Device independant bitmap */
public static final int PICTURE_TYPE_DIB = 7;
private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);
/**
* Creates new HSSFWorkbook from scratch (start here!)
*
*/
public HSSFWorkbook()
{
this(Workbook.createWorkbook());
}
protected HSSFWorkbook( Workbook book )
{
super(null, null);
workbook = book;
_sheets = new ArrayList( INITIAL_CAPACITY );
names = new ArrayList( INITIAL_CAPACITY );
}
public HSSFWorkbook(POIFSFileSystem fs) throws IOException {
this(fs,true);
}
/**
* given a POI POIFSFileSystem object, read in its Workbook and populate the high and
* low level models. If you're reading in a workbook...start here.
*
* @param fs the POI filesystem that contains the Workbook stream.
* @param preserveNodes whether to preseve other nodes, such as
* macros. This takes more memory, so only say yes if you
* need to. If set, will store all of the POIFSFileSystem
* in memory
* @see org.apache.poi.poifs.filesystem.POIFSFileSystem
* @exception IOException if the stream cannot be read
*/
public HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes)
throws IOException
{
this(fs.getRoot(), fs, preserveNodes);
}
/**
* Normally, the Workbook will be in a POIFS Stream
* called "Workbook". However, some weird XLS generators use "WORKBOOK"
*/
private static final String[] WORKBOOK_DIR_ENTRY_NAMES = {
"Workbook", // as per BIFF8 spec
"WORKBOOK",
};
private static String getWorkbookDirEntryName(DirectoryNode directory) {
String[] potentialNames = WORKBOOK_DIR_ENTRY_NAMES;
for (int i = 0; i < potentialNames.length; i++) {
String wbName = potentialNames[i];
try {
directory.getEntry(wbName);
return wbName;
} catch (FileNotFoundException e) {
// continue - to try other options
}
}
// check for previous version of file format
try {
directory.getEntry("Book");
throw new IllegalArgumentException("The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. "
+ "POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)");
} catch (FileNotFoundException e) {
// fall through
}
throw new IllegalArgumentException("The supplied POIFSFileSystem does not contain a BIFF8 'Workbook' entry. "
+ "Is it really an excel file?");
}
/**
* given a POI POIFSFileSystem object, and a specific directory
* within it, read in its Workbook and populate the high and
* low level models. If you're reading in a workbook...start here.
*
* @param directory the POI filesystem directory to process from
* @param fs the POI filesystem that contains the Workbook stream.
* @param preserveNodes whether to preseve other nodes, such as
* macros. This takes more memory, so only say yes if you
* need to. If set, will store all of the POIFSFileSystem
* in memory
* @see org.apache.poi.poifs.filesystem.POIFSFileSystem
* @exception IOException if the stream cannot be read
*/
public HSSFWorkbook(DirectoryNode directory, POIFSFileSystem fs, boolean preserveNodes)
throws IOException
{
this(directory, preserveNodes);
}
/**
* given a POI POIFSFileSystem object, and a specific directory
* within it, read in its Workbook and populate the high and
* low level models. If you're reading in a workbook...start here.
*
* @param directory the POI filesystem directory to process from
* @param preserveNodes whether to preseve other nodes, such as
* macros. This takes more memory, so only say yes if you
* need to. If set, will store all of the POIFSFileSystem
* in memory
* @see org.apache.poi.poifs.filesystem.POIFSFileSystem
* @exception IOException if the stream cannot be read
*/
public HSSFWorkbook(DirectoryNode directory, boolean preserveNodes)
throws IOException
{
super(directory);
String workbookName = getWorkbookDirEntryName(directory);
this.preserveNodes = preserveNodes;
// If we're not preserving nodes, don't track the
// POIFS any more
if(! preserveNodes) {
this.directory = null;
}
_sheets = new ArrayList(INITIAL_CAPACITY);
names = new ArrayList(INITIAL_CAPACITY);
// Grab the data from the workbook stream, however
// it happens to be spelled.
InputStream stream = directory.createDocumentInputStream(workbookName);
List records = RecordFactory.createRecords(stream);
workbook = Workbook.createWorkbook(records);
setPropertiesFromWorkbook(workbook);
int recOffset = workbook.getNumRecords();
int sheetNum = 0;
// convert all LabelRecord records to LabelSSTRecord
convertLabelRecords(records, recOffset);
RecordStream rs = new RecordStream(records, recOffset);
while (rs.hasNext()) {
Sheet sheet = Sheet.createSheet(rs);
_sheets.add(new HSSFSheet(this, sheet));
}
for (int i = 0 ; i < workbook.getNumNames() ; ++i){
HSSFName name = new HSSFName(this, workbook.getNameRecord(i));
names.add(name);
}
}
public HSSFWorkbook(InputStream s) throws IOException {
this(s,true);
}
/**
* Companion to HSSFWorkbook(POIFSFileSystem), this constructs the POI filesystem around your
* inputstream.
*
* @param s the POI filesystem that contains the Workbook stream.
* @param preserveNodes whether to preseve other nodes, such as
* macros. This takes more memory, so only say yes if you
* need to.
* @see org.apache.poi.poifs.filesystem.POIFSFileSystem
* @see #HSSFWorkbook(POIFSFileSystem)
* @exception IOException if the stream cannot be read
*/
public HSSFWorkbook(InputStream s, boolean preserveNodes)
throws IOException
{
this(new POIFSFileSystem(s), preserveNodes);
}
/**
* used internally to set the workbook properties.
*/
private void setPropertiesFromWorkbook(Workbook book)
{
this.workbook = book;
// none currently
}
/**
* This is basically a kludge to deal with the now obsolete Label records. If
* you have to read in a sheet that contains Label records, be aware that the rest
* of the API doesn't deal with them, the low level structure only provides read-only
* semi-immutable structures (the sets are there for interface conformance with NO
* impelmentation). In short, you need to call this function passing it a reference
* to the Workbook object. All labels will be converted to LabelSST records and their
* contained strings will be written to the Shared String tabel (SSTRecord) within
* the Workbook.
*
* @param wb sheet's matching low level Workbook structure containing the SSTRecord.
* @see org.apache.poi.hssf.record.LabelRecord
* @see org.apache.poi.hssf.record.LabelSSTRecord
* @see org.apache.poi.hssf.record.SSTRecord
*/
private void convertLabelRecords(List records, int offset)
{
if (log.check( POILogger.DEBUG ))
log.log(POILogger.DEBUG, "convertLabelRecords called");
for (int k = offset; k < records.size(); k++)
{
Record rec = ( Record ) records.get(k);
if (rec.getSid() == LabelRecord.sid)
{
LabelRecord oldrec = ( LabelRecord ) rec;
records.remove(k);
LabelSSTRecord newrec = new LabelSSTRecord();
int stringid =
workbook.addSSTString(new UnicodeString(oldrec.getValue()));
newrec.setRow(oldrec.getRow());
newrec.setColumn(oldrec.getColumn());
newrec.setXFIndex(oldrec.getXFIndex());
newrec.setSSTIndex(stringid);
records.add(k, newrec);
}
}
if (log.check( POILogger.DEBUG ))
log.log(POILogger.DEBUG, "convertLabelRecords exit");
}
/**
* Retrieves the current policy on what to do when
* getting missing or blank cells from a row.
* The default is to return blank and null cells.
* {@link MissingCellPolicy}
*/
public MissingCellPolicy getMissingCellPolicy() {
return missingCellPolicy;
}
/**
* Sets the policy on what to do when
* getting missing or blank cells from a row.
* This will then apply to all calls to
* {@link HSSFRow.getCell()}. See
* {@link MissingCellPolicy}.
* Note that this has no effect on any
* iterators, only on when fetching Cells
* by their column index.
*/
public void setMissingCellPolicy(MissingCellPolicy missingCellPolicy) {
this.missingCellPolicy = missingCellPolicy;
}
/**
* sets the order of appearance for a given sheet.
*
* @param sheetname the name of the sheet to reorder
* @param pos the position that we want to insert the sheet into (0 based)
*/
public void setSheetOrder(String sheetname, int pos ) {
_sheets.add(pos,_sheets.remove(getSheetIndex(sheetname)));
workbook.setSheetOrder(sheetname, pos);
}
private void validateSheetIndex(int index) {
int lastSheetIx = _sheets.size() - 1;
if (index < 0 || index > lastSheetIx) {
throw new IllegalArgumentException("Sheet index ("
+ index +") is out of range (0.." + lastSheetIx + ")");
}
}
/**
* Selects a single sheet. This may be different to
* the 'active' sheet (which is the sheet with focus).
*/
public void setSelectedTab(int index) {
validateSheetIndex(index);
int nSheets = _sheets.size();
for (int i=0; i<nSheets; i++) {
getSheetAt(i).setSelected(i == index);
}
workbook.getWindowOne().setNumSelectedTabs((short)1);
}
/**
* deprecated May 2008
* @deprecated use setSelectedTab(int)
*/
public void setSelectedTab(short index) {
setSelectedTab((int)index);
}
public void setSelectedTabs(int[] indexes) {
for (int i = 0; i < indexes.length; i++) {
validateSheetIndex(indexes[i]);
}
int nSheets = _sheets.size();
for (int i=0; i<nSheets; i++) {
boolean bSelect = false;
for (int j = 0; j < indexes.length; j++) {
if (indexes[j] == i) {
bSelect = true;
break;
}
}
getSheetAt(i).setSelected(bSelect);
}
workbook.getWindowOne().setNumSelectedTabs((short)indexes.length);
}
/**
* Convenience method to set the active sheet. The active sheet is is the sheet
* which is currently displayed when the workbook is viewed in Excel.
* 'Selected' sheet(s) is a distinct concept.
*/
public void setActiveSheet(int index) {
validateSheetIndex(index);
int nSheets = _sheets.size();
for (int i=0; i<nSheets; i++) {
getSheetAt(i).setActive(i == index);
}
workbook.getWindowOne().setActiveSheetIndex(index);
}
/**
* gets the tab whose data is actually seen when the sheet is opened.
* This may be different from the "selected sheet" since excel seems to
* allow you to show the data of one sheet when another is seen "selected"
* in the tabs (at the bottom).
* @see org.apache.poi.hssf.usermodel.HSSFSheet#setSelected(boolean)
*/
public int getActiveSheetIndex() {
return workbook.getWindowOne().getActiveSheetIndex();
}
/**
* deprecated May 2008
* @deprecated - Misleading name - use getActiveSheetIndex()
*/
public short getSelectedTab() {
return (short) getActiveSheetIndex();
}
/**
* sets the first tab that is displayed in the list of tabs
* in excel.
* @param index
*/
public void setFirstVisibleTab(int index) {
workbook.getWindowOne().setFirstVisibleTab(index);
}
/**
* deprecated May 2008
* @deprecated - Misleading name - use setFirstVisibleTab()
*/
public void setDisplayedTab(short index) {
setFirstVisibleTab(index);
}
/**
* sets the first tab that is displayed in the list of tabs in excel.
*/
public int getFirstVisibleTab() {
return workbook.getWindowOne().getFirstVisibleTab();
}
/**
* deprecated May 2008
* @deprecated - Misleading name - use getFirstVisibleTab()
*/
public short getDisplayedTab() {
return (short) getFirstVisibleTab();
}
/**
* Sets the sheet name.
* Will throw IllegalArgumentException if the name is duplicated or contains /\?*[]
* Note - Excel allows sheet names up to 31 chars in length but other applications allow more.
* Excel does not crash with names longer than 31 chars, but silently truncates such names to
* 31 chars. POI enforces uniqueness on the first 31 chars.
*
* @param sheetIx number (0 based)
*/
public void setSheetName(int sheetIx, String name) {
if (workbook.doesContainsSheetName(name, sheetIx)) {
throw new IllegalArgumentException("The workbook already contains a sheet with this name");
}
validateSheetIndex(sheetIx);
workbook.setSheetName(sheetIx, name);
}
/**
* get the sheet name
* @param sheetIx Number
* @return Sheet name
*/
public String getSheetName(int sheetIx)
{
validateSheetIndex(sheetIx);
return workbook.getSheetName(sheetIx);
}
/**
* Check whether a sheet is hidden.
* Note that a sheet could instead be
* set to be very hidden, which is different
* ({@link #isSheetVeryHidden(int)})
* @param sheetIx Number
* @return True if sheet is hidden
*/
public boolean isSheetHidden(int sheetIx) {
validateSheetIndex(sheetIx);
return workbook.isSheetHidden(sheetIx);
}
/**
* Check whether a sheet is very hidden.
* This is different from the normal
* hidden status
* ({@link #isSheetHidden(int)})
* @param sheetIx Number
* @return True if sheet is very hidden
*/
public boolean isSheetVeryHidden(int sheetIx) {
validateSheetIndex(sheetIx);
return workbook.isSheetVeryHidden(sheetIx);
}
/**
* Hide or unhide a sheet
*
* @param sheetIx The sheet index
* @param hidden True to mark the sheet as hidden, false otherwise
*/
public void setSheetHidden(int sheetIx, boolean hidden) {
validateSheetIndex(sheetIx);
workbook.setSheetHidden(sheetIx, hidden);
}
/**
* Hide or unhide a sheet.
* 0 = not hidden
* 1 = hidden
* 2 = very hidden.
*
* @param sheetIx The sheet number
* @param hidden 0 for not hidden, 1 for hidden, 2 for very hidden
*/
public void setSheetHidden(int sheetIx, int hidden) {
validateSheetIndex(sheetIx);
workbook.setSheetHidden(sheetIx, hidden);
}
/*
* get the sheet's index
* @param name sheet name
* @return sheet index or -1 if it was not found.
*/
/** Returns the index of the sheet by his name
* @param name the sheet name
* @return index of the sheet (0 based)
*/
public int getSheetIndex(String name)
{
int retval = workbook.getSheetIndex(name);
return retval;
}
/** Returns the index of the given sheet
* @param sheet the sheet to look up
* @return index of the sheet (0 based). <tt>-1</tt> if not found
*/
public int getSheetIndex(HSSFSheet sheet)
{
for(int i=0; i<_sheets.size(); i++) {
if(_sheets.get(i) == sheet) {
return i;
}
}
return -1;
}
/**
* create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns
* the high level representation. Use this to create new sheets.
*
* @return HSSFSheet representing the new sheet.
*/
public HSSFSheet createSheet()
{
HSSFSheet sheet = new HSSFSheet(this);
_sheets.add(sheet);
workbook.setSheetName(_sheets.size() - 1, "Sheet" + (_sheets.size() - 1));
boolean isOnlySheet = _sheets.size() == 1;
sheet.setSelected(isOnlySheet);
sheet.setActive(isOnlySheet);
return sheet;
}
/**
* create an HSSFSheet from an existing sheet in the HSSFWorkbook.
*
* @return HSSFSheet representing the cloned sheet.
*/
public HSSFSheet cloneSheet(int sheetIndex) {
validateSheetIndex(sheetIndex);
HSSFSheet srcSheet = (HSSFSheet) _sheets.get(sheetIndex);
String srcName = workbook.getSheetName(sheetIndex);
HSSFSheet clonedSheet = srcSheet.cloneSheet(this);
clonedSheet.setSelected(false);
clonedSheet.setActive(false);
String name = getUniqueSheetName(srcName);
int newSheetIndex = _sheets.size();
_sheets.add(clonedSheet);
workbook.setSheetName(newSheetIndex, name);
// Check this sheet has an autofilter, (which has a built-in NameRecord at workbook level)
int filterDbNameIndex = findExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_FILTER_DB);
if (filterDbNameIndex >=0) {
NameRecord origNameRecord = workbook.getNameRecord(filterDbNameIndex);
// copy original formula but adjust 3D refs to the new external sheet index
int newExtSheetIx = workbook.checkExternSheet(newSheetIndex);
Ptg[] ptgs = origNameRecord.getNameDefinition();
for (int i=0; i< ptgs.length; i++) {
Ptg ptg = ptgs[i];
ptg = ptg.copy();
if (ptg instanceof Area3DPtg) {
Area3DPtg a3p = (Area3DPtg) ptg;
a3p.setExternSheetIndex(newExtSheetIx);
} else if (ptg instanceof Ref3DPtg) {
Ref3DPtg r3p = (Ref3DPtg) ptg;
r3p.setExternSheetIndex(newExtSheetIx);
}
ptgs[i] = ptg;
}
NameRecord newNameRecord = workbook.createBuiltInName(NameRecord.BUILTIN_FILTER_DB, newSheetIndex+1);
newNameRecord.setNameDefinition(ptgs);
newNameRecord.setHidden(true);
HSSFName newName = new HSSFName(this, newNameRecord);
names.add(newName);
workbook.cloneDrawings(clonedSheet.getSheet());
}
// TODO - maybe same logic required for other/all built-in name records
return clonedSheet;
}
private String getUniqueSheetName(String srcName) {
int uniqueIndex = 2;
String baseName = srcName;
int bracketPos = srcName.lastIndexOf('(');
if (bracketPos > 0 && srcName.endsWith(")")) {
String suffix = srcName.substring(bracketPos + 1, srcName.length() - ")".length());
try {
uniqueIndex = Integer.parseInt(suffix.trim());
uniqueIndex++;
baseName=srcName.substring(0, bracketPos).trim();
} catch (NumberFormatException e) {
// contents of brackets not numeric
}
}
while (true) {
// Try and find the next sheet name that is unique
String index = Integer.toString(uniqueIndex++);
String name;
if (baseName.length() + index.length() + 2 < 31) {
name = baseName + " (" + index + ")";
} else {
name = baseName.substring(0, 31 - index.length() - 2) + "(" + index + ")";
}
//If the sheet name is unique, then set it otherwise move on to the next number.
if (workbook.getSheetIndex(name) == -1) {
return name;
}
}
}
/**
* create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and
* returns the high level representation. Use this to create new sheets.
*
* @param sheetname the name for the new sheet. Note - certain length limits
* apply. See {@link #setSheetName(int, String)}.
*
* @return HSSFSheet representing the new sheet.
* @throws IllegalArgumentException
* if there is already a sheet present with a case-insensitive
* match for the specified name.
*/
public HSSFSheet createSheet(String sheetname)
{
if (workbook.doesContainsSheetName( sheetname, _sheets.size() ))
throw new IllegalArgumentException( "The workbook already contains a sheet of this name" );
HSSFSheet sheet = new HSSFSheet(this);
_sheets.add(sheet);
workbook.setSheetName(_sheets.size() - 1, sheetname);
boolean isOnlySheet = _sheets.size() == 1;
sheet.setSelected(isOnlySheet);
sheet.setActive(isOnlySheet);
return sheet;
}
/**
* get the number of spreadsheets in the workbook (this will be three after serialization)
* @return number of sheets
*/
public int getNumberOfSheets()
{
return _sheets.size();
}
private HSSFSheet[] getSheets() {
HSSFSheet[] result = new HSSFSheet[_sheets.size()];
_sheets.toArray(result);
return result;
}
/**
* Get the HSSFSheet object at the given index.
* @param index of the sheet number (0-based physical & logical)
* @return HSSFSheet at the provided index
*/
public HSSFSheet getSheetAt(int index)
{
return (HSSFSheet) _sheets.get(index);
}
/**
* Get sheet with the given name (case insensitive match)
* @param name of the sheet
* @return HSSFSheet with the name provided or <code>null</code> if it does not exist
*/
public HSSFSheet getSheet(String name)
{
HSSFSheet retval = null;
for (int k = 0; k < _sheets.size(); k++)
{
String sheetname = workbook.getSheetName(k);
if (sheetname.equalsIgnoreCase(name))
{
retval = (HSSFSheet) _sheets.get(k);
}
}
return retval;
}
/**
* Removes sheet at the given index.<p/>
*
* Care must be taken if the removed sheet is the currently active or only selected sheet in
* the workbook. There are a few situations when Excel must have a selection and/or active
* sheet. (For example when printing - see Bug 40414).<br/>
*
* This method makes sure that if the removed sheet was active, another sheet will become
* active in its place. Furthermore, if the removed sheet was the only selected sheet, another
* sheet will become selected. The newly active/selected sheet will have the same index, or
* one less if the removed sheet was the last in the workbook.
*
* @param index of the sheet (0-based)
*/
public void removeSheetAt(int index) {
validateSheetIndex(index);
boolean wasActive = getSheetAt(index).isActive();
boolean wasSelected = getSheetAt(index).isSelected();
_sheets.remove(index);
workbook.removeSheet(index);
// set the remaining active/selected sheet
int nSheets = _sheets.size();
if (nSheets < 1) {
// nothing more to do if there are no sheets left
return;
}
// the index of the closest remaining sheet to the one just deleted
int newSheetIndex = index;
if (newSheetIndex >= nSheets) {
newSheetIndex = nSheets-1;
}
if (wasActive) {
setActiveSheet(newSheetIndex);
}
if (wasSelected) {
boolean someOtherSheetIsStillSelected = false;
for (int i =0; i < nSheets; i++) {
if (getSheetAt(i).isSelected()) {
someOtherSheetIsStillSelected = true;
break;
}
}
if (!someOtherSheetIsStillSelected) {
setSelectedTab(newSheetIndex);
}
}
}
/**
* determine whether the Excel GUI will backup the workbook when saving.
*
* @param backupValue true to indicate a backup will be performed.
*/
public void setBackupFlag(boolean backupValue)
{
BackupRecord backupRecord = workbook.getBackupRecord();
backupRecord.setBackup(backupValue ? (short) 1
: (short) 0);
}
/**
* determine whether the Excel GUI will backup the workbook when saving.
*
* @return the current setting for backups.
*/
public boolean getBackupFlag()
{
BackupRecord backupRecord = workbook.getBackupRecord();
return (backupRecord.getBackup() == 0) ? false
: true;
}
/**
* Sets the repeating rows and columns for a sheet (as found in
* 2003:File->PageSetup->Sheet, 2007:Page Layout->Print Titles).
* This is function is included in the workbook
* because it creates/modifies name records which are stored at the
* workbook level.
* <p>
* To set just repeating columns:
* <pre>
* workbook.setRepeatingRowsAndColumns(0,0,1,-1-1);
* </pre>
* To set just repeating rows:
* <pre>
* workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
* </pre>
* To remove all repeating rows and columns for a sheet.
* <pre>
* workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);
* </pre>
*
* @param sheetIndex 0 based index to sheet.
* @param startColumn 0 based start of repeating columns.
* @param endColumn 0 based end of repeating columns.
* @param startRow 0 based start of repeating rows.
* @param endRow 0 based end of repeating rows.
*/
public void setRepeatingRowsAndColumns(int sheetIndex,
int startColumn, int endColumn,
int startRow, int endRow)
{
// Check arguments
if (startColumn == -1 && endColumn != -1) throw new IllegalArgumentException("Invalid column range specification");
if (startRow == -1 && endRow != -1) throw new IllegalArgumentException("Invalid row range specification");
if (startColumn < -1 || startColumn >= MAX_COLUMN) throw new IllegalArgumentException("Invalid column range specification");
if (endColumn < -1 || endColumn >= MAX_COLUMN) throw new IllegalArgumentException("Invalid column range specification");
if (startRow < -1 || startRow > MAX_ROW) throw new IllegalArgumentException("Invalid row range specification");
if (endRow < -1 || endRow > MAX_ROW) throw new IllegalArgumentException("Invalid row range specification");
if (startColumn > endColumn) throw new IllegalArgumentException("Invalid column range specification");
if (startRow > endRow) throw new IllegalArgumentException("Invalid row range specification");
HSSFSheet sheet = getSheetAt(sheetIndex);
short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex);
boolean settingRowAndColumn =
startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1;
boolean removingRange =
startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1;
int rowColHeaderNameIndex = findExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_PRINT_TITLE);
if (removingRange) {
if (rowColHeaderNameIndex >= 0) {
workbook.removeName(rowColHeaderNameIndex);
}
return;
}
boolean isNewRecord;
NameRecord nameRecord;
if (rowColHeaderNameIndex < 0) {
//does a lot of the house keeping for builtin records, like setting lengths to zero etc
nameRecord = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex+1);
isNewRecord = true;
} else {
nameRecord = workbook.getNameRecord(rowColHeaderNameIndex);
isNewRecord = false;
}
List temp = new ArrayList();
if (settingRowAndColumn) {
final int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE
temp.add(new MemFuncPtg(exprsSize));
}
if (startColumn >= 0) {
Area3DPtg colArea = new Area3DPtg(0, MAX_ROW, startColumn, endColumn,
false, false, false, false, externSheetIndex);
temp.add(colArea);
}
if (startRow >= 0) {
Area3DPtg rowArea = new Area3DPtg(startRow, endRow, 0, MAX_COLUMN,
false, false, false, false, externSheetIndex);
temp.add(rowArea);
}
if (settingRowAndColumn) {
temp.add(UnionPtg.instance);
}
Ptg[] ptgs = new Ptg[temp.size()];
temp.toArray(ptgs);
nameRecord.setNameDefinition(ptgs);
if (isNewRecord)
{
HSSFName newName = new HSSFName(this, nameRecord);
names.add(newName);
}
HSSFPrintSetup printSetup = sheet.getPrintSetup();
printSetup.setValidSettings(false);
sheet.setActive(true);
}
private int findExistingBuiltinNameRecordIdx(int sheetIndex, byte builtinCode) {