1
54
55
60 package org.apache.poi.hssf.usermodel;
61
62 import org.apache.poi.util.POILogFactory;
63 import org.apache.poi.hssf.eventmodel.EventRecordFactory;
64 import org.apache.poi.hssf.model.Sheet;
65 import org.apache.poi.hssf.model.Workbook;
66 import org.apache.poi.hssf.record.*;
67 import org.apache.poi.hssf.record.formula.MemFuncPtg;
68 import org.apache.poi.hssf.record.formula.Area3DPtg;
69 import org.apache.poi.hssf.record.formula.UnionPtg;
70 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
71 import org.apache.poi.poifs.filesystem.Entry;
72 import org.apache.poi.poifs.filesystem.DirectoryEntry;
73 import org.apache.poi.poifs.filesystem.DocumentEntry;
74 import org.apache.poi.poifs.filesystem.DocumentInputStream;
75 import org.apache.poi.util.POILogger;
76
77 import java.io.ByteArrayInputStream;
78 import java.io.IOException;
79 import java.io.InputStream;
80 import java.io.OutputStream;
81 import java.util.ArrayList;
82 import java.util.List;
83 import java.util.Iterator;
84 import java.util.Stack;
85
86
98
99 public class HSSFWorkbook
100 extends java.lang.java.lang.Objectvate static final int DEBUG = POILogger.DEBUG;
101
102
108
109 public final static int INITIAL_CAPACITY = 3;
110
111
114
115 private Workbook workbook;
116
117
120
121 private ArrayList sheets;
122
123
126
127 private ArrayList names;
128
129
133 private boolean preserveNodes;
134
135
139 private POIFSFileSystem poifs;
140
141 private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);
142
143
147
148 public HSSFWorkbook()
149 {
150 workbook = Workbook.createWorkbook();
151 sheets = new ArrayList(INITIAL_CAPACITY);
152 names = new ArrayList(INITIAL_CAPACITY);
153 }
154
155 public HSSFWorkbook(POIFSFileSystem fs) throws IOException {
156 this(fs,true);
157 }
158
159
170
171 public HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes)
172 throws IOException
173 {
174 this.preserveNodes = preserveNodes;
175
176 if (preserveNodes) {
177 this.poifs = fs;
178 }
179
180 sheets = new ArrayList(INITIAL_CAPACITY);
181 names = new ArrayList(INITIAL_CAPACITY);
182
183 InputStream stream = fs.createDocumentInputStream("Workbook");
184
185 EventRecordFactory factory = new EventRecordFactory();
186
187
188
189 List records = RecordFactory.createRecords(stream);
190
191 workbook = Workbook.createWorkbook(records);
192 setPropertiesFromWorkbook(workbook);
193 int recOffset = workbook.getNumRecords();
194 int sheetNum = 0;
195
196 while (recOffset < records.size())
197 {
198 Sheet sheet = Sheet.createSheet(records, sheetNum++, recOffset );
199
200 recOffset = sheet.getEofLoc()+1;
201 sheet.convertLabelRecords(
202 workbook);
203 HSSFSheet hsheet = new HSSFSheet(workbook, sheet);
204
205 sheets.add(hsheet);
206
207
208 }
209
210 for (int i = 0 ; i < workbook.getNumNames() ; ++i){
211 HSSFName name = new HSSFName(workbook, workbook.getNameRecord(i));
212 names.add(name);
213 }
214 }
215
216 public HSSFWorkbook(InputStream s) throws IOException {
217 this(s,true);
218 }
219
220
232
233 public HSSFWorkbook(InputStream s, boolean preserveNodes)
234 throws IOException
235 {
236 this(new POIFSFileSystem(s), preserveNodes);
237 }
238
239
242
243 private void setPropertiesFromWorkbook(Workbook book)
244 {
245 this.workbook = book;
246
247
248 }
249
250 public final static byte ENCODING_COMPRESSED_UNICODE = 0;
251 public final static byte ENCODING_UTF_16 = 1;
252
253
258
259 public void setSheetName(int sheet, String name)
260 {
261 workbook.setSheetName( sheet, name, ENCODING_COMPRESSED_UNICODE );
262 }
263
264 public void setSheetName( int sheet, String name, short encoding )
265 {
266 if (sheet > (sheets.size() - 1))
267 {
268 throw new RuntimeException("Sheet out of bounds");
269 }
270
271 switch ( encoding ) {
272 case ENCODING_COMPRESSED_UNICODE:
273 case ENCODING_UTF_16:
274 break;
275
276 default:
277
278 throw new RuntimeException( "Unsupported encoding" );
279 }
280
281 workbook.setSheetName( sheet, name, encoding );
282 }
283
284
289
290 public String getSheetName(int sheet)
291 {
292 if (sheet > (sheets.size() - 1))
293 {
294 throw new RuntimeException("Sheet out of bounds");
295 }
296 return workbook.getSheetName(sheet);
297 }
298
299
304
305
309 public int getSheetIndex(String name)
310 {
311 int retval = workbook.getSheetIndex(name);
312
313 return retval;
314 }
315
316
322
323 public HSSFSheet createSheet()
324 {
325
326
327
328 HSSFSheet sheet = new HSSFSheet(workbook);
329
330 sheets.add(sheet);
331 workbook.setSheetName(sheets.size() - 1,
332 "Sheet" + (sheets.size() - 1));
333 WindowTwoRecord windowTwo = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
334 windowTwo.setSelected(sheets.size() == 1);
335 windowTwo.setPaged(sheets.size() == 1);
336 return sheet;
337 }
338
339
344
345 public HSSFSheet cloneSheet(int sheetNum) {
346 HSSFSheet srcSheet = (HSSFSheet)sheets.get(sheetNum);
347 String srcName = workbook.getSheetName(sheetNum);
348 if (srcSheet != null) {
349 HSSFSheet clonedSheet = srcSheet.cloneSheet(workbook);
350 WindowTwoRecord windowTwo = (WindowTwoRecord) clonedSheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
351 windowTwo.setSelected(sheets.size() == 1);
352 windowTwo.setPaged(sheets.size() == 1);
353
354 sheets.add(clonedSheet);
355 workbook.setSheetName(sheets.size()-1, srcName+"[1]");
356 return clonedSheet;
357 }
358 return null;
359 }
360
361
368
369 public HSSFSheet createSheet(String sheetname)
370 {
371
372
373
374 HSSFSheet sheet = new HSSFSheet(workbook);
375
376 sheets.add(sheet);
377 workbook.setSheetName(sheets.size() - 1, sheetname);
378 WindowTwoRecord windowTwo = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
379 windowTwo.setSelected(sheets.size() == 1);
380 windowTwo.setPaged(sheets.size() == 1);
381 return sheet;
382 }
383
384
388
389 public int getNumberOfSheets()
390 {
391 return sheets.size();
392 }
393
394
399
400 public HSSFSheet getSheetAt(int index)
401 {
402 return (HSSFSheet) sheets.get(index);
403 }
404
405
410
411 public HSSFSheet getSheet(String name)
412 {
413 HSSFSheet retval = null;
414
415 for (int k = 0; k < sheets.size(); k++)
416 {
417 String sheetname = workbook.getSheetName(k);
418
419 if (sheetname.equals(name))
420 {
421 retval = (HSSFSheet) sheets.get(k);
422 }
423 }
424 return retval;
425 }
426
427
431
432 public void removeSheetAt(int index)
433 {
434 sheets.remove(index);
435 workbook.removeSheet(index);
436 }
437
438
443
444 public void setBackupFlag(boolean backupValue)
445 {
446 BackupRecord backupRecord = workbook.getBackupRecord();
447
448 backupRecord.setBackup(backupValue ? (short) 1
449 : (short) 0);
450 }
451
452
457
458 public boolean getBackupFlag()
459 {
460 BackupRecord backupRecord = workbook.getBackupRecord();
461
462 return (backupRecord.getBackup() == 0) ? false
463 : true;
464 }
465
466
491 public void setRepeatingRowsAndColumns(int sheetIndex,
492 int startColumn, int endColumn,
493 int startRow, int endRow)
494 {
495
496 if (startColumn == -1 && endColumn != -1) throw new IllegalArgumentException("Invalid column range specification");
497 if (startRow == -1 && endRow != -1) throw new IllegalArgumentException("Invalid row range specification");
498 if (startColumn < -1 || startColumn >= 0xFF) throw new IllegalArgumentException("Invalid column range specification");
499 if (endColumn < -1 || endColumn >= 0xFF) throw new IllegalArgumentException("Invalid column range specification");
500 if (startRow < -1 || startRow > 65535) throw new IllegalArgumentException("Invalid row range specification");
501 if (endRow < -1 || endRow > 65535) throw new IllegalArgumentException("Invalid row range specification");
502 if (startColumn > endColumn) throw new IllegalArgumentException("Invalid column range specification");
503 if (startRow > endRow) throw new IllegalArgumentException("Invalid row range specification");
504
505 HSSFSheet sheet = getSheetAt(sheetIndex);
506 short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex);
507
508 boolean settingRowAndColumn =
509 startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1;
510 boolean removingRange =
511 startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1;
512
513 boolean isNewRecord = false;
514 NameRecord nameRecord;
515 nameRecord = findExistingRowColHeaderNameRecord(sheetIndex);
516 if (removingRange )
517 {
518 if (nameRecord != null)
519 workbook.removeName(findExistingRowColHeaderNameRecordIdx(sheetIndex));
520 return;
521 }
522 if ( nameRecord == null )
523 {
524 nameRecord = workbook.createName();
525 isNewRecord = true;
526 }
527 nameRecord.setOptionFlag((short)0x20);
528 nameRecord.setKeyboardShortcut((byte)0);
529 short definitionTextLength = settingRowAndColumn ? (short)0x001a : (short)0x000b;
530 nameRecord.setDefinitionTextLength(definitionTextLength);
531 nameRecord.setNameTextLength((byte)1);
532 nameRecord.setNameText(((char)7) + "");
533 nameRecord.setUnused((short)0);
534 nameRecord.setEqualsToIndexToSheet((short)(externSheetIndex+1));
535 nameRecord.setCustomMenuLength((byte)0);
536 nameRecord.setDescriptionTextLength((byte)0);
537 nameRecord.setHelpTopicLength((byte)0);
538 nameRecord.setStatusBarLength((byte)0);
539 Stack ptgs = new Stack();
540
541 if (settingRowAndColumn)
542 {
543 MemFuncPtg memFuncPtg = new MemFuncPtg();
544 memFuncPtg.setLenRefSubexpression(23);
545 ptgs.add(memFuncPtg);
546 }
547 if (startColumn >= 0)
548 {
549 Area3DPtg area3DPtg1 = new Area3DPtg();
550 area3DPtg1.setExternSheetIndex(externSheetIndex);
551 area3DPtg1.setFirstColumn((short)startColumn);
552 area3DPtg1.setLastColumn((short)endColumn);
553 area3DPtg1.setFirstRow((short)0);
554 area3DPtg1.setLastRow((short)0xFFFF);
555 ptgs.add(area3DPtg1);
556 }
557 if (startRow >= 0)
558 {
559 Area3DPtg area3DPtg2 = new Area3DPtg();
560 area3DPtg2.setExternSheetIndex(externSheetIndex);
561 area3DPtg2.setFirstColumn((short)0);
562 area3DPtg2.setLastColumn((short)0x00FF);
563 area3DPtg2.setFirstRow((short)startRow);
564 area3DPtg2.setLastRow((short)endRow);
565 ptgs.add(area3DPtg2);
566 }
567 if (settingRowAndColumn)
568 {
569 UnionPtg unionPtg = new UnionPtg();
570 ptgs.add(unionPtg);
571 }
572 nameRecord.setNameDefinition(ptgs);
573
574 if (isNewRecord)
575 {
576 HSSFName newName = new HSSFName(workbook, nameRecord);
577 names.add(newName);
578 }
579
580 HSSFPrintSetup printSetup = sheet.getPrintSetup();
581 printSetup.setValidSettings(false);
582
583 WindowTwoRecord w2 = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
584 w2.setPaged(true);
585 }
586
587 private NameRecord findExistingRowColHeaderNameRecord( int sheetIndex )
588 {
589 int index = findExistingRowColHeaderNameRecordIdx(sheetIndex);
590 if (index == -1)
591 return null;
592 else
593 return (NameRecord)workbook.findNextRecordBySid(NameRecord.sid, index);
594 }
595
596 private int findExistingRowColHeaderNameRecordIdx( int sheetIndex )
597 {
598 int index = 0;
599 NameRecord r = null;
600 while ((r = (NameRecord) workbook.findNextRecordBySid(NameRecord.sid, index)) != null)
601 {
602 int nameRecordSheetIndex = workbook.getSheetIndexFromExternSheetIndex(r.getEqualsToIndexToSheet() - 1);
603 if (isRowColHeaderRecord( r ) && nameRecordSheetIndex == sheetIndex)
604 {
605 return index;
606 }
607 index++;
608 }
609
610 return -1;
611 }
612
613 private boolean isRowColHeaderRecord( NameRecord r )
614 {
615 return r.getOptionFlag() == 0x20 && ("" + ((char)7)).equals(r.getNameText());
616 }
617
618
622
623 public HSSFFont createFont()
624 {
625 FontRecord font = workbook.createNewFont();
626 short fontindex = (short) (getNumberOfFonts() - 1);
627
628 if (fontindex > 3)
629 {
630 fontindex++;
631 }
632 HSSFFont retval = new HSSFFont(fontindex, font);
633
634 return retval;
635 }
636
637
641
642 public short getNumberOfFonts()
643 {
644 return (short) workbook.getNumberOfFontRecords();
645 }
646
647
652
653 public HSSFFont getFontAt(short idx)
654 {
655 FontRecord font = workbook.getFontRecordAt(idx);
656 HSSFFont retval = new HSSFFont(idx, font);
657
658 return retval;
659 }
660
661
665
666 public HSSFCellStyle createCellStyle()
667 {
668 ExtendedFormatRecord xfr = workbook.createCellXF();
669 short index = (short) (getNumCellStyles() - 1);
670 HSSFCellStyle style = new HSSFCellStyle(index, xfr);
671
672 return style;
673 }
674
675
679
680 public short getNumCellStyles()
681 {
682 return (short) workbook.getNumExFormats();
683 }
684
685
690
691 public HSSFCellStyle getCellStyleAt(short idx)
692 {
693 ExtendedFormatRecord xfr = workbook.getExFormatAt(idx);
694 HSSFCellStyle style = new HSSFCellStyle(idx, xfr);
695
696 return style;
697 }
698
699
709
710 public void write(OutputStream stream)
711 throws IOException
712 {
713 byte[] bytes = getBytes();
714 POIFSFileSystem fs = new POIFSFileSystem();
715
716 fs.createDocument(new ByteArrayInputStream(bytes), "Workbook");
717
718 if (preserveNodes) {
719 List excepts = new ArrayList(1);
720 excepts.add("Workbook");
721 copyNodes(this.poifs,fs,excepts);
722 }
723 fs.writeFilesystem(stream);
724
725 }
726
727
738
739 public byte[] getBytes()
740 {
741 log.log(DEBUG, "HSSFWorkbook.getBytes()");
742 int wbsize = workbook.getSize();
743
744
745
746 int totalsize = wbsize;
747
748 for (int k = 0; k < sheets.size(); k++)
749 {
750 workbook.setSheetBof(k, totalsize);
751
752
753 totalsize += ((HSSFSheet) sheets.get(k)).getSheet().getSize();
754 }
755
759 byte[] retval = new byte[totalsize];
760 int pos = workbook.serialize(0, retval);
761
762
763 for (int k = 0; k < sheets.size(); k++)
764 {
765
766
767
768 pos += ((HSSFSheet) sheets.get(k)).getSheet().serialize(pos,
769 retval);
770 }
771
775 return retval;
776 }
777
778 public int addSSTString(String string)
779 {
780 return workbook.addSSTString(string);
781 }
782
783 public String getSSTString(int index)
784 {
785 return workbook.getSSTString(index);
786 }
787
788 Workbook getWorkbook()
789 {
790 return workbook;
791 }
792
793
796 public int getNumberOfNames(){
797 int result = names.size();
798 return result;
799 }
800
801
805 public HSSFName getNameAt(int index){
806 HSSFName result = (HSSFName) names.get(index);
807
808 return result;
809 }
810
811
815 public String getNameName(int index){
816 String result = getNameAt(index).getNameName();
817
818 return result;
819 }
820
821
822
825 public HSSFName createName(){
826 NameRecord nameRecord = workbook.createName();
827
828 HSSFName newName = new HSSFName(workbook, nameRecord);
829
830 names.add(newName);
831
832 return newName;
833 }
834
835
839 public int getNameIndex(String name)
840 {
841 int retval = -1;
842
843 for (int k = 0; k < names.size(); k++)
844 {
845 String nameName = getNameName(k);
846
847 if (nameName.equals(name))
848 {
849 retval = k;
850 break;
851 }
852 }
853 return retval;
854 }
855
856
857
860 public void removeName(int index){
861 names.remove(index);
862 workbook.removeName(index);
863 }
864
865
871 public HSSFDataFormat createDataFormat() {
872 return new HSSFDataFormat(workbook);
873 }
874
875
878 public void removeName(String name){
879 int index = getNameIndex(name);
880
881 removeName(index);
882
883 }
884
885 public HSSFPalette getCustomPalette()
886 {
887 return new HSSFPalette(workbook.getCustomPalette());
888 }
889
890
896 private void copyNodes(POIFSFileSystem source, POIFSFileSystem target,
897 List excepts) throws IOException {
898
899
900 DirectoryEntry root = source.getRoot();
901 DirectoryEntry newRoot = target.getRoot();
902
903 Iterator entries = root.getEntries();
904
905 while (entries.hasNext()) {
906 Entry entry = (Entry)entries.next();
907 if (!isInList(entry.getName(), excepts)) {
908 copyNodeRecursively(entry,newRoot);
909 }
910 }
911 }
912
913 private boolean isInList(String entry, List list) {
914 for (int k = 0; k < list.size(); k++) {
915 if (list.get(k).equals(entry)) {
916 return true;
917 }
918 }
919 return false;
920 }
921
922 private void copyNodeRecursively(Entry entry, DirectoryEntry target)
923 throws IOException {
924
925
926 DirectoryEntry newTarget = null;
927 if (entry.isDirectoryEntry()) {
928 newTarget = target.createDirectory(entry.getName());
929 Iterator entries = ((DirectoryEntry)entry).getEntries();
930
931 while (entries.hasNext()) {
932 copyNodeRecursively((Entry)entries.next(),newTarget);
933 }
934 } else {
935 DocumentEntry dentry = (DocumentEntry)entry;
936 DocumentInputStream dstream = new DocumentInputStream(dentry);
937 target.createDocument(dentry.getName(),dstream);
938 dstream.close();
939 }
940 }
941
942 public void insertChartRecord()
943 {
944 int loc = workbook.findFirstRecordLocBySid(SSTRecord.sid);
945 byte[] data = {
946 (byte)0x0F, (byte)0x00, (byte)0x00, (byte)0xF0, (byte)0x52,
947 (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x00,
948 (byte)0x06, (byte)0xF0, (byte)0x18, (byte)0x00, (byte)0x00,
949 (byte)0x00, (byte)0x01, (byte)0x08, (byte)0x00, (byte)0x00,
950 (byte)0x02, (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x02,
951 (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x01, (byte)0x00,
952 (byte)0x00, (byte)0x00, (byte)0x01, (byte)0x00, (byte)0x00,
953 (byte)0x00, (byte)0x03, (byte)0x00, (byte)0x00, (byte)0x00,
954 (byte)0x33, (byte)0x00, (byte)0x0B, (byte)0xF0, (byte)0x12,
955 (byte)0x00, (byte)0x00, (byte)0x00, (byte)0xBF, (byte)0x00,
956 (byte)0x08, (byte)0x00, (byte)0x08, (byte)0x00, (byte)0x81,
957 (byte)0x01, (byte)0x09, (byte)0x00, (byte)0x00, (byte)0x08,
958 (byte)0xC0, (byte)0x01, (byte)0x40, (byte)0x00, (byte)0x00,
959 (byte)0x08, (byte)0x40, (byte)0x00, (byte)0x1E, (byte)0xF1,
960 (byte)0x10, (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x0D,
961 (byte)0x00, (byte)0x00, (byte)0x08, (byte)0x0C, (byte)0x00,
962 (byte)0x00, (byte)0x08, (byte)0x17, (byte)0x00, (byte)0x00,
963 (byte)0x08, (byte)0xF7, (byte)0x00, (byte)0x00, (byte)0x10,
964 };
965 UnknownRecord r = new UnknownRecord((short)0x00EB,(short)0x005a, data);
966 workbook.getRecords().add(loc, r);
967 }
968
969
970
971 }
972