Java > Open Source Codes > org > apache > poi > hssf > usermodel > HSSFWorkbook


1 /* ====================================================================
2    Copyright 2002-2004 Apache Software Foundation
3
4    Licensed under the Apache License, Version 2.0 (the "License");
5    you may not use this file except in compliance with the License.
6    You may obtain a copy of the License at
7
8        http://www.apache.org/licenses/LICENSE-2.0
9
10    Unless required by applicable law or agreed to in writing, software
11    distributed under the License is distributed on an "AS IS" BASIS,
12    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13    See the License for the specific language governing permissions and
14    limitations under the License.
15 ==================================================================== */
16
17
18 /*
19  * HSSFWorkbook.java
20  *
21  * Created on September 30, 2001, 3:37 PM
22  */
23 package org.apache.poi.hssf.usermodel;
24
25 import org.apache.poi.hssf.eventmodel.EventRecordFactory;
26 import org.apache.poi.hssf.model.Sheet;
27 import org.apache.poi.hssf.model.Workbook;
28 import org.apache.poi.hssf.record.*;
29 import org.apache.poi.hssf.record.formula.Area3DPtg;
30 import org.apache.poi.hssf.record.formula.MemFuncPtg;
31 import org.apache.poi.hssf.record.formula.UnionPtg;
32 import org.apache.poi.hssf.util.CellReference;
33 import org.apache.poi.poifs.filesystem.*;
34 import org.apache.poi.util.POILogFactory;
35 import org.apache.poi.util.POILogger;
36
37 import java.io.ByteArrayInputStream ;
38 import java.io.IOException ;
39 import java.io.InputStream ;
40 import java.io.OutputStream ;
41 import java.util.ArrayList ;
42 import java.util.Iterator ;
43 import java.util.List ;
44 import java.util.Stack ;
45
46 /**
47  * High level representation of a workbook. This is the first object most users
48  * will construct whether they are reading or writing a workbook. It is also the
49  * top level object for creating new sheets/etc.
50  *
51  * @see org.apache.poi.hssf.model.Workbook
52  * @see org.apache.poi.hssf.usermodel.HSSFSheet
53  * @author Andrew C. Oliver (acoliver at apache dot org)
54  * @author Glen Stampoultzis (glens at apache.org)
55  * @author Shawn Laubach (slaubach at apache dot org)
56  * @version 2.0-pre
57  */
58
59 public class HSSFWorkbook
60         extends java.lang.Object
61 {
62     private static final int DEBUG = POILogger.DEBUG;
63
64     /**
65      * used for compile-time performance/memory optimization. This determines the
66      * initial capacity for the sheet collection. Its currently set to 3.
67      * Changing it in this release will decrease performance
68      * since you're never allowed to have more or less than three sheets!
69      */
70
71     public final static int INITIAL_CAPACITY = 3;
72
73     /**
74      * this is the reference to the low level Workbook object
75      */
76
77     private Workbook workbook;
78
79     /**
80      * this holds the HSSFSheet objects attached to this workbook
81      */
82
83     protected ArrayList sheets;
84
85     /**
86      * this holds the HSSFName objects attached to this workbook
87      */
88
89     private ArrayList names;
90
91     /**
92      * holds whether or not to preserve other nodes in the POIFS. Used
93      * for macros and embedded objects.
94      */
95     private boolean preserveNodes;
96
97     /**
98      * if you do preserve the nodes, you'll need to hold the whole POIFS in
99      * memory.
100      */
101     private POIFSFileSystem poifs;
102
103     /**
104      * Used to keep track of the data formatter so that all
105      * createDataFormatter calls return the same one for a given
106      * book. This ensures that updates from one places is visible
107      * someplace else.
108      */
109     private HSSFDataFormat formatter;
110
111     private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);
112
113     /**
114      * Creates new HSSFWorkbook from scratch (start here!)
115      *
116      */
117
118     public HSSFWorkbook()
119     {
120         this(Workbook.createWorkbook());
121     }
122
123     protected HSSFWorkbook( Workbook book )
124     {
125         workbook = book;
126         sheets = new ArrayList ( INITIAL_CAPACITY );
127         names = new ArrayList ( INITIAL_CAPACITY );
128     }
129
130     public HSSFWorkbook(POIFSFileSystem fs) throws IOException {
131       this(fs,true);
132     }
133
134     /**
135      * given a POI POIFSFileSystem object, read in its Workbook and populate the high and
136      * low level models. If you're reading in a workbook...start here.
137      *
138      * @param fs the POI filesystem that contains the Workbook stream.
139      * @param preserveNodes whether to preseve other nodes, such as
140      * macros. This takes more memory, so only say yes if you
141      * need to.
142      * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
143      * @exception IOException if the stream cannot be read
144      */
145
146     public HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes)
147             throws IOException
148     {
149         this.preserveNodes = preserveNodes;
150
151         if (preserveNodes) {
152            this.poifs = fs;
153         }
154
155         sheets = new ArrayList (INITIAL_CAPACITY);
156         names = new ArrayList (INITIAL_CAPACITY);
157
158         InputStream stream = fs.createDocumentInputStream("Workbook");
159
160         EventRecordFactory factory = new EventRecordFactory();
161
162
163
164         List records = RecordFactory.createRecords(stream);
165
166         workbook = Workbook.createWorkbook(records);
167         setPropertiesFromWorkbook(workbook);
168         int recOffset = workbook.getNumRecords();
169         int sheetNum = 0;
170
171         while (recOffset < records.size())
172         {
173             Sheet sheet = Sheet.createSheet(records, sheetNum++, recOffset );
174
175             recOffset = sheet.getEofLoc()+1;
176             sheet.convertLabelRecords(
177                     workbook); // convert all LabelRecord records to LabelSSTRecord
178 HSSFSheet hsheet = new HSSFSheet(workbook, sheet);
179
180             sheets.add(hsheet);
181
182             // workbook.setSheetName(sheets.size() -1, "Sheet"+sheets.size());
183 }
184
185         for (int i = 0 ; i < workbook.getNumNames() ; ++i){
186             HSSFName name = new HSSFName(workbook, workbook.getNameRecord(i));
187             names.add(name);
188         }
189     }
190
191      public HSSFWorkbook(InputStream s) throws IOException {
192          this(s,true);
193      }
194
195     /**
196      * Companion to HSSFWorkbook(POIFSFileSystem), this constructs the POI filesystem around your
197      * inputstream.
198      *
199      * @param s the POI filesystem that contains the Workbook stream.
200      * @param preserveNodes whether to preseve other nodes, such as
201      * macros. This takes more memory, so only say yes if you
202      * need to.
203      * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
204      * @see #HSSFWorkbook(POIFSFileSystem)
205      * @exception IOException if the stream cannot be read
206      */
207
208     public HSSFWorkbook(InputStream s, boolean preserveNodes)
209             throws IOException
210     {
211         this(new POIFSFileSystem(s), preserveNodes);
212     }
213
214     /**
215      * used internally to set the workbook properties.
216      */
217
218     private void setPropertiesFromWorkbook(Workbook book)
219     {
220         this.workbook = book;
221
222         // none currently
223 }
224
225     /**
226      * sets the order of appearance for a given sheet.
227      *
228      * @param sheetname the name of the sheet to reorder
229      * @param pos the position that we want to insert the sheet into (0 based)
230      */
231
232     public void setSheetOrder(String sheetname, int pos ) {
233         workbook.setSheetOrder(sheetname, pos);
234     }
235
236     public final static byte ENCODING_COMPRESSED_UNICODE = 0;
237     public final static byte ENCODING_UTF_16 = 1;
238
239
240     /**
241      * set the sheet name.
242      * Will throw IllegalArgumentException if the name is greater than 31 chars
243      * or contains /\?*[]
244      * @param sheet number (0 based)
245      */
246     public void setSheetName(int sheet, String name)
247     {
248         if (workbook.doesContainsSheetName( name, sheet ))
249             throw new IllegalArgumentException ( "The workbook already contains a sheet with this name" );
250
251         workbook.setSheetName( sheet, name, ENCODING_COMPRESSED_UNICODE );
252     }
253
254     public void setSheetName( int sheet, String name, short encoding )
255     {
256         if (workbook.doesContainsSheetName( name, sheet ))
257             throw new IllegalArgumentException ( "The workbook already contains a sheet with this name" );
258
259         if (sheet > (sheets.size() - 1))
260         {
261             throw new RuntimeException ("Sheet out of bounds");
262         }
263
264         switch ( encoding ) {
265         case ENCODING_COMPRESSED_UNICODE:
266         case ENCODING_UTF_16:
267             break;
268
269         default:
270             // TODO java.io.UnsupportedEncodingException
271 throw new RuntimeException ( "Unsupported encoding" );
272         }
273
274         workbook.setSheetName( sheet, name, encoding );
275     }
276
277     /**
278      * get the sheet name
279      * @param sheet Number
280      * @return Sheet name
281      */
282
283     public String getSheetName(int sheet)
284     {
285         if (sheet > (sheets.size() - 1))
286         {
287             throw new RuntimeException ("Sheet out of bounds");
288         }
289         return workbook.getSheetName(sheet);
290     }
291
292     /*
293      * get the sheet's index
294      * @param name sheet name
295      * @return sheet index or -1 if it was not found.
296      */
297
298     /** Returns the index of the sheet by his name
299      * @param name the sheet name
300      * @return index of the sheet (0 based)
301      */
302     public int getSheetIndex(String name)
303     {
304         int retval = workbook.getSheetIndex(name);
305
306         return retval;
307     }
308
309     /**
310      * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns
311      * the high level representation. Use this to create new sheets.
312      *
313      * @return HSSFSheet representing the new sheet.
314      */
315
316     public HSSFSheet createSheet()
317     {
318
319 // if (getNumberOfSheets() == 3)
320 // throw new RuntimeException("You cannot have more than three sheets in HSSF 1.0");
321 HSSFSheet sheet = new HSSFSheet(workbook);
322
323         sheets.add(sheet);
324         workbook.setSheetName(sheets.size() - 1,
325                 "Sheet" + (sheets.size() - 1));
326         WindowTwoRecord windowTwo = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
327         windowTwo.setSelected(sheets.size() == 1);
328         windowTwo.setPaged(sheets.size() == 1);
329         return sheet;
330     }
331
332     /**
333      * create an HSSFSheet from an existing sheet in the HSSFWorkbook.
334      *
335      * @return HSSFSheet representing the cloned sheet.
336      */
337
338     public HSSFSheet cloneSheet(int sheetNum) {
339       HSSFSheet srcSheet = (HSSFSheet)sheets.get(sheetNum);
340       String srcName = workbook.getSheetName(sheetNum);
341       if (srcSheet != null) {
342         HSSFSheet clonedSheet = srcSheet.cloneSheet(workbook);
343         WindowTwoRecord windowTwo = (WindowTwoRecord) clonedSheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
344         windowTwo.setSelected(sheets.size() == 1);
345         windowTwo.setPaged(sheets.size() == 1);
346
347         sheets.add(clonedSheet);
348         if (srcName.length()<28) {
349             workbook.setSheetName(sheets.size()-1, srcName+"(2)");
350         }else {
351             workbook.setSheetName(sheets.size()-1,srcName.substring(0,28)+"(2)");
352         }
353         return clonedSheet;
354       }
355       return null;
356     }
357
358     /**
359      * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns
360      * the high level representation. Use this to create new sheets.
361      *
362      * @param sheetname sheetname to set for the sheet.
363      * @return HSSFSheet representing the new sheet.
364      */
365
366     public HSSFSheet createSheet(String sheetname)
367     {
368         if (workbook.doesContainsSheetName( sheetname, -1 ))
369             throw new IllegalArgumentException ( "The workbook already contains a sheet of this name" );
370
371         HSSFSheet sheet = new HSSFSheet(workbook);
372
373         sheets.add(sheet);
374         workbook.setSheetName(sheets.size() - 1, sheetname);
375         WindowTwoRecord windowTwo = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
376         windowTwo.setSelected(sheets.size() == 1);
377         windowTwo.setPaged(sheets.size() == 1);
378         return sheet;
379     }
380
381     /**
382      * get the number of spreadsheets in the workbook (this will be three after serialization)
383      * @return number of sheets
384      */
385
386     public int getNumberOfSheets()
387     {
388         return sheets.size();
389     }
390
391     /**
392      * Get the HSSFSheet object at the given index.
393      * @param index of the sheet number (0-based physical & logical)
394      * @return HSSFSheet at the provided index
395      */
396
397     public HSSFSheet getSheetAt(int index)
398     {
399         return (HSSFSheet) sheets.get(index);
400     }
401
402     /**
403      * Get sheet with the given name
404      * @param name of the sheet
405      * @return HSSFSheet with the name provided or null if it does not exist
406      */
407
408     public HSSFSheet getSheet(String name)
409     {
410         HSSFSheet retval = null;
411
412         for (int k = 0; k < sheets.size(); k++)
413         {
414             String sheetname = workbook.getSheetName(k);
415
416             if (sheetname.equals(name))
417             {
418                 retval = (HSSFSheet) sheets.get(k);
419             }
420         }
421         return retval;
422     }
423
424     /**
425      * removes sheet at the given index
426      * @param index of the sheet (0-based)
427      */
428
429     public void removeSheetAt(int index)
430     {
431         sheets.remove(index);
432         workbook.removeSheet(index);
433     }
434
435     /**
436      * determine whether the Excel GUI will backup the workbook when saving.
437      *
438      * @param backupValue true to indicate a backup will be performed.
439      */
440
441     public void setBackupFlag(boolean backupValue)
442     {
443         BackupRecord backupRecord = workbook.getBackupRecord();
444
445         backupRecord.setBackup(backupValue ? (short) 1
446                 : (short) 0);
447     }
448
449     /**
450      * determine whether the Excel GUI will backup the workbook when saving.
451      *
452      * @return the current setting for backups.
453      */
454
455     public boolean getBackupFlag()
456     {
457         BackupRecord backupRecord = workbook.getBackupRecord();
458
459         return (backupRecord.getBackup() == 0) ? false
460                 : true;
461     }
462
463     /**
464      * Sets the repeating rows and columns for a sheet (as found in
465      * File->PageSetup->Sheet). This is function is included in the workbook
466      * because it creates/modifies name records which are stored at the
467      * workbook level.
468      * <p>
469      * To set just repeating columns:
470      * <pre>
471      * workbook.setRepeatingRowsAndColumns(0,0,1,-1-1);
472      * </pre>
473      * To set just repeating rows:
474      * <pre>
475      * workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
476      * </pre>
477      * To remove all repeating rows and columns for a sheet.
478      * <pre>
479      * workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);
480      * </pre>
481      *
482      * @param sheetIndex 0 based index to sheet.
483      * @param startColumn 0 based start of repeating columns.
484      * @param endColumn 0 based end of repeating columns.
485      * @param startRow 0 based start of repeating rows.
486      * @param endRow 0 based end of repeating rows.
487      */
488     public void setRepeatingRowsAndColumns(int sheetIndex,
489                                            int startColumn, int endColumn,
490                                            int startRow, int endRow)
491     {
492         // Check arguments
493 if (startColumn == -1 && endColumn != -1) throw new IllegalArgumentException ("Invalid column range specification");
494         if (startRow == -1 && endRow != -1) throw new IllegalArgumentException ("Invalid row range specification");
495         if (startColumn < -1 || startColumn >= 0xFF) throw new IllegalArgumentException ("Invalid column range specification");
496         if (endColumn < -1 || endColumn >= 0xFF) throw new IllegalArgumentException ("Invalid column range specification");
497         if (startRow < -1 || startRow > 65535) throw new IllegalArgumentException ("Invalid row range specification");
498         if (endRow < -1 || endRow > 65535) throw new IllegalArgumentException ("Invalid row range specification");
499         if (startColumn > endColumn) throw new IllegalArgumentException ("Invalid column range specification");
500         if (startRow > endRow) throw new IllegalArgumentException ("Invalid row range specification");
501
502         HSSFSheet sheet = getSheetAt(sheetIndex);
503         short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex);
504
505         boolean settingRowAndColumn =
506                 startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1;
507         boolean removingRange =
508                 startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1;
509
510         boolean isNewRecord = false;
511         NameRecord nameRecord;
512         nameRecord = findExistingRowColHeaderNameRecord(sheetIndex);
513         if (removingRange )
514         {
515             if (nameRecord != null)
516                 workbook.removeName(findExistingRowColHeaderNameRecordIdx(sheetIndex+1));
517             return;
518         }
519         if ( nameRecord == null )
520         {
521             nameRecord = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex+1);
522             //does a lot of the house keeping for builtin records, like setting lengths to zero etc
523 isNewRecord = true;
524         }
525
526         short definitionTextLength = settingRowAndColumn ? (short)0x001a : (short)0x000b;
527         nameRecord.setDefinitionTextLength(definitionTextLength);
528
529         Stack ptgs = new Stack ();
530
531         if (settingRowAndColumn)
532         {
533             MemFuncPtg memFuncPtg = new MemFuncPtg();
534             memFuncPtg.setLenRefSubexpression(23);
535             ptgs.add(memFuncPtg);
536         }
537         if (startColumn >= 0)
538         {
539             Area3DPtg area3DPtg1 = new Area3DPtg();
540             area3DPtg1.setExternSheetIndex(externSheetIndex);
541             area3DPtg1.setFirstColumn((short)startColumn);
542             area3DPtg1.setLastColumn((short)endColumn);
543             area3DPtg1.setFirstRow((short)0);
544             area3DPtg1.setLastRow((short)0xFFFF);
545             ptgs.add(area3DPtg1);
546         }
547         if (startRow >= 0)
548         {
549             Area3DPtg area3DPtg2 = new Area3DPtg();
550             area3DPtg2.setExternSheetIndex(externSheetIndex);
551             area3DPtg2.setFirstColumn((short)0);
552             area3DPtg2.setLastColumn((short)0x00FF);
553             area3DPtg2.setFirstRow((short)startRow);
554             area3DPtg2.setLastRow((short)endRow);
555             ptgs.add(area3DPtg2);
556         }
557         if (settingRowAndColumn)
558         {
559             UnionPtg unionPtg = new UnionPtg();
560             ptgs.add(unionPtg);
561         }
562         nameRecord.setNameDefinition(ptgs);
563
564         if (isNewRecord)
565         {
566             HSSFName newName = new HSSFName(workbook, nameRecord);
567             names.add(newName);
568         }
569
570         HSSFPrintSetup printSetup = sheet.getPrintSetup();
571         printSetup.setValidSettings(false);
572
573         WindowTwoRecord w2 = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
574         w2.setPaged(true);
575     }
576
577     private NameRecord findExistingRowColHeaderNameRecord( int sheetIndex )
578     {
579         int index = findExistingRowColHeaderNameRecordIdx(sheetIndex);
580         if (index == -1)
581             return null;
582         else
583             return (NameRecord)workbook.findNextRecordBySid(NameRecord.sid, index);
584     }
585
586     private int findExistingRowColHeaderNameRecordIdx( int sheetIndex )
587     {
588         int index = 0;
589         NameRecord r = null;
590         while ((r = (NameRecord) workbook.findNextRecordBySid(NameRecord.sid, index)) != null)
591         {
592             int nameRecordSheetIndex = workbook.getSheetIndexFromExternSheetIndex(r.getEqualsToIndexToSheet() - 1);
593             if (isRowColHeaderRecord( r ) && nameRecordSheetIndex == sheetIndex)
594             {
595                 return index;
596             }
597             index++;
598         }
599
600         return -1;
601     }
602
603     private boolean isRowColHeaderRecord( NameRecord r )
604     {
605         return r.getOptionFlag() == 0x20 && ("" + ((char)7)).equals(r.getNameText());
606     }
607
608     /**
609      * create a new Font and add it to the workbook's font table
610      * @return new font object
611      */
612
613     public HSSFFont createFont()
614     {
615         FontRecord font = workbook.createNewFont();
616         short fontindex = (short) (getNumberOfFonts() - 1);
617
618         if (fontindex > 3)
619         {
620             fontindex++; // THERE IS NO FOUR!!
621 }
622         HSSFFont retval = new HSSFFont(fontindex, font);
623
624         return retval;
625     }
626
627     /**
628      * Finds a font that matches the one with the supplied attributes
629      */
630     public HSSFFont findFont(short boldWeight, short color, short fontHeight,
631                              String name, boolean italic, boolean strikeout,
632                              short typeOffset, byte underline)
633     {
634 // System.out.println( boldWeight + ", " + color + ", " + fontHeight + ", " + name + ", " + italic + ", " + strikeout + ", " + typeOffset + ", " + underline );
635 for (short i = 0; i < workbook.getNumberOfFontRecords(); i++)
636         {
637             if (i == 4)
638                 continue;
639
640             FontRecord font = workbook.getFontRecordAt(i);
641             HSSFFont hssfFont = new HSSFFont(i, font);
642 // System.out.println( hssfFont.getBoldweight() + ", " + hssfFont.getColor() + ", " + hssfFont.getFontHeight() + ", " + hssfFont.getFontName() + ", " + hssfFont.getItalic() + ", " + hssfFont.getStrikeout() + ", " + hssfFont.getTypeOffset() + ", " + hssfFont.getUnderline() );
643 if (hssfFont.getBoldweight() == boldWeight
644                     && hssfFont.getColor() == color
645                     && hssfFont.getFontHeight() == fontHeight
646                     && hssfFont.getFontName().equals(name)
647                     && hssfFont.getItalic() == italic
648                     && hssfFont.getStrikeout() == strikeout
649                     && hssfFont.getTypeOffset() == typeOffset
650                     && hssfFont.getUnderline() == underline)
651             {
652 // System.out.println( "Found font" );
653 return hssfFont;
654             }
655         }
656
657 // System.out.println( "No font found" );
658 return null;
659     }
660
661     /**
662      * get the number of fonts in the font table
663      * @return number of fonts
664      */
665
666     public short getNumberOfFonts()
667     {
668         return (short) workbook.getNumberOfFontRecords();
669     }
670
671     /**
672      * get the font at the given index number
673      * @param idx index number
674      * @return HSSFFont at the index
675      */
676
677     public HSSFFont getFontAt(short idx)
678     {
679         FontRecord font = workbook.getFontRecordAt(idx);
680         HSSFFont retval = new HSSFFont(idx, font);
681
682         return retval;
683     }
684
685     /**
686      * create a new Cell style and add it to the workbook's style table
687      * @return the new Cell Style object
688      */
689
690     public HSSFCellStyle createCellStyle()
691     {
692         ExtendedFormatRecord xfr = workbook.createCellXF();
693         short index = (short) (getNumCellStyles() - 1);
694         HSSFCellStyle style = new HSSFCellStyle(index, xfr);
695
696         return style;
697     }
698
699     /**
700      * get the number of styles the workbook contains
701      * @return count of cell styles
702      */
703
704     public short getNumCellStyles()
705     {
706         return (short) workbook.getNumExFormats();
707     }
708
709     /**
710      * get the cell style object at the given index
711      * @param idx index within the set of styles
712      * @return HSSFCellStyle object at the index
713      */
714
715     public HSSFCellStyle getCellStyleAt(short idx)
716     {
717         ExtendedFormatRecord xfr = workbook.getExFormatAt(idx);
718         HSSFCellStyle style = new HSSFCellStyle(idx, xfr);
719
720         return style;
721     }
722
723     /**
724      * Method write - write out this workbook to an Outputstream. Constructs
725      * a new POI POIFSFileSystem, passes in the workbook binary representation and
726      * writes it out.
727      *
728      * @param stream - the java OutputStream you wish to write the XLS to
729      *
730      * @exception IOException if anything can't be written.
731      * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
732      */
733
734     public void write(OutputStream stream)
735             throws IOException
736     {
737         byte[] bytes = getBytes();
738         POIFSFileSystem fs = new POIFSFileSystem();
739
740         fs.createDocument(new ByteArrayInputStream (bytes), "Workbook");
741
742         if (preserveNodes) {
743             List excepts = new ArrayList (1);
744             excepts.add("Workbook");
745             copyNodes(this.poifs,fs,excepts);
746         }
747         fs.writeFilesystem(stream);
748         //poifs.writeFilesystem(stream);
749 }
750
751     /**
752      * Method getBytes - get the bytes of just the HSSF portions of the XLS file.
753      * Use this to construct a POI POIFSFileSystem yourself.
754      *
755      *
756      * @return byte[] array containing the binary representation of this workbook and all contained
757      * sheets, rows, cells, etc.
758      *
759      * @see org.apache.poi.hssf.model.Workbook
760      * @see org.apache.poi.hssf.model.Sheet
761      */
762
763     public byte[] getBytes()
764     {
765         if (log.check( POILogger.DEBUG ))
766             log.log(DEBUG, "HSSFWorkbook.getBytes()");
767
768         // before getting the workbook size we must tell the sheets that
769 // serialization is about to occur.
770 for (int k = 0; k < sheets.size(); k++)
771             ((HSSFSheet) sheets.get(k)).getSheet().preSerialize();
772
773         int wbsize = workbook.getSize();
774
775         // log.debug("REMOVEME: old sizing method "+workbook.serialize().length);
776 // ArrayList sheetbytes = new ArrayList(sheets.size());
777 int totalsize = wbsize;
778
779         for (int k = 0; k < sheets.size(); k++)
780         {
781             workbook.setSheetBof(k, totalsize);
782             totalsize += ((HSSFSheet) sheets.get(k)).getSheet().getSize();
783         }
784
785
786 /* if (totalsize < 4096)
787         {
788             totalsize = 4096;
789         }*/
790         byte[] retval = new byte[totalsize];
791         int pos = workbook.serialize(0, retval);
792
793         // System.arraycopy(wb, 0, retval, 0, wb.length);
794 for (int k = 0; k < sheets.size(); k++)
795         {
796
797             // byte[] sb = (byte[])sheetbytes.get(k);
798 // System.arraycopy(sb, 0, retval, pos, sb.length);
799 pos += ((HSSFSheet) sheets.get(k)).getSheet().serialize(pos,
800                     retval); // sb.length;
801 }
802 /* for (int k = pos; k < totalsize; k++)
803         {
804             retval[k] = 0;
805         }*/
806         return retval;
807     }
808
809     public int addSSTString(String string)
810     {
811         return workbook.addSSTString(string);
812     }
813
814     public String getSSTString(int index)
815     {
816         return workbook.getSSTString(index);
817     }
818
819     Workbook getWorkbook()
820     {
821         return workbook;
822     }
823
824     /** gets the total number of named ranges in the workboko
825      * @return number of named ranges
826      */
827     public int getNumberOfNames(){
828         int result = names.size();
829         return result;
830     }
831
832     /** gets the Named range
833      * @param index position of the named range
834      * @return named range high level
835      */
836     public HSSFName getNameAt(int index){
837         HSSFName result = (HSSFName) names.get(index);
838
839         return result;
840     }
841
842     /** gets the named range name
843      * @param index the named range index (0 based)
844      * @return named range name
845      */
846     public String getNameName(int index){
847         String result = getNameAt(index).getNameName();
848
849         return result;
850     }
851
852     /**
853      * Sets the printarea for the sheet provided
854      * <p>
855      * i.e. Reference = $A$1:$B$2
856      * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
857      * @param reference Valid name Reference for the Print Area
858      */
859     public void setPrintArea(int sheetIndex, String reference)
860     {
861         NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
862
863
864         if (name == null)
865             name = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
866        //adding one here because 0 indicates a global named region; doesnt make sense for print areas
867
868         short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex);
869         name.setExternSheetNumber(externSheetIndex);
870         name.setAreaReference(reference);
871
872
873     }
874
875     /**
876      * For the Convenience of Java Programmers maintaining pointers.
877      * @see #setPrintArea(int, String)
878      * @param sheetIndex Zero-based sheet index (0 = First Sheet)
879      * @param startColumn Column to begin printarea
880      * @param endColumn Column to end the printarea
881      * @param startRow Row to begin the printarea
882      * @param endRow Row to end the printarea
883      */
884     public void setPrintArea(int sheetIndex, int startColumn, int endColumn,
885                               int startRow, int endRow) {
886
887         //using absolute references because they dont get copied and pasted anyway
888 CellReference cell = new CellReference(startRow, startColumn, true, true);
889         String reference = cell.toString();
890
891         cell = new CellReference(endRow, endColumn, true, true);
892         reference = reference+":"+cell.toString();
893
894         setPrintArea(sheetIndex, reference);
895     }
896
897
898     /**
899      * Retrieves the reference for the printarea of the specified sheet, the sheet name is appended to the reference even if it was not specified.
900      * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
901      * @return String Null if no print area has been defined
902      */
903     public String getPrintArea(int sheetIndex)
904     {
905         NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
906         if (name == null) return null;
907         //adding one here because 0 indicates a global named region; doesnt make sense for print areas
908
909         return name.getAreaReference(workbook);
910     }
911
912     /**
913      * Delete the printarea for the sheet specified
914      * @param sheetIndex Zero-based sheet index (0 = First Sheet)
915      */
916     public void removePrintArea(int sheetIndex) {
917         getWorkbook().removeBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
918     }
919
920     /** creates a new named range and add it to the model
921      * @return named range high level
922      */
923     public HSSFName createName(){
924         NameRecord nameRecord = workbook.createName();
925
926         HSSFName newName = new HSSFName(workbook, nameRecord);
927
928         names.add(newName);
929
930         return newName;
931     }
932
933     /** gets the named range index by his name
934      * @param name named range name
935      * @return named range index
936      */
937     public int getNameIndex(String name)
938     {
939         int retval = -1;
940
941         for (int k = 0; k < names.size(); k++)
942         {
943             String nameName = getNameName(k);
944
945             if (nameName.equals(name))
946             {
947                 retval = k;
948                 break;
949             }
950         }
951         return retval;
952     }
953
954
955     /** remove the named range by his index
956      * @param index named range index (0 based)
957      */
958     public void removeName(int index){
959         names.remove(index);
960         workbook.removeName(index);
961     }
962
963     /**
964      * Returns the instance of HSSFDataFormat for this workbook.
965      * @return the HSSFDataFormat object
966      * @see org.apache.poi.hssf.record.FormatRecord
967      * @see org.apache.poi.hssf.record.Record
968      */
969     public HSSFDataFormat createDataFormat() {
970     if (formatter == null)
971         formatter = new HSSFDataFormat(workbook);
972     return formatter;
973     }
974
975     /** remove the named range by his name
976      * @param name named range name
977      */
978     public void removeName(String name){
979         int index = getNameIndex(name);
980
981         removeName(index);
982
983     }
984
985     public HSSFPalette getCustomPalette()
986     {
987         return new HSSFPalette(workbook.getCustomPalette());
988     }
989
990    /**
991     * Copies nodes from one POIFS to the other minus the excepts
992     * @param source is the source POIFS to copy from
993     * @param target is the target POIFS to copy to
994     * @param excepts is a list of Strings specifying what nodes NOT to copy
995     */
996    private void copyNodes(POIFSFileSystem source, POIFSFileSystem target,
997                           List excepts) throws IOException {
998       //System.err.println("CopyNodes called");
999
1000      DirectoryEntry root = source.getRoot();
1001      DirectoryEntry newRoot = target.getRoot();
1002
1003      Iterator entries = root.getEntries();
1004
1005      while (entries.hasNext()) {
1006         Entry entry = (Entry)entries.next();
1007         if (!isInList(entry.getName(), excepts)) {
1008             copyNodeRecursively(entry,newRoot);
1009         }
1010      }
1011   }
1012
1013   private boolean isInList(String entry, List list) {
1014       for (int k = 0; k < list.size(); k++) {
1015          if (list.get(k).equals(entry)) {
1016            return true;
1017          }
1018       }
1019       return false;
1020   }
1021
1022   private void copyNodeRecursively(Entry entry, DirectoryEntry target)
1023   throws IOException {
1024       //System.err.println("copyNodeRecursively called with "+entry.getName()+
1025 // ","+target.getName());
1026 DirectoryEntry newTarget = null;
1027       if (entry.isDirectoryEntry()) {
1028           newTarget = target.createDirectory(entry.getName());
1029           Iterator entries = ((DirectoryEntry)entry).getEntries();
1030
1031           while (entries.hasNext()) {
1032              copyNodeRecursively((Entry)entries.next(),newTarget);
1033           }
1034       } else {
1035         DocumentEntry dentry = (DocumentEntry)entry;
1036         DocumentInputStream dstream = new DocumentInputStream(dentry);
1037         target.createDocument(dentry.getName(),dstream);
1038         dstream.close();
1039       }
1040   }
1041
1042    /** Test only. Do not use */
1043    public void insertChartRecord()
1044    {
1045        int loc = workbook.findFirstRecordLocBySid(SSTRecord.sid);
1046        byte[] data = {
1047           (byte)0x0F, (byte)0x00, (byte)0x00, (byte)0xF0, (byte)0x52,
1048           (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x00,
1049           (byte)0x06, (byte)0xF0, (byte)0x18, (byte)0x00, (byte)0x00,
1050           (byte)0x00, (byte)0x01, (byte)0x08, (byte)0x00, (byte)0x00,
1051           (byte)0x02, (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x02,
1052           (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x01, (byte)0x00,
1053           (byte)0x00, (byte)0x00, (byte)0x01, (byte)0x00, (byte)0x00,
1054           (byte)0x00, (byte)0x03, (byte)0x00, (byte)0x00, (byte)0x00,
1055           (byte)0x33, (byte)0x00, (byte)0x0B, (byte)0xF0, (byte)0x12,
1056           (byte)0x00, (byte)0x00, (byte)0x00, (byte)0xBF, (byte)0x00,
1057           (byte)0x08, (byte)0x00, (byte)0x08, (byte)0x00, (byte)0x81,
1058           (byte)0x01, (byte)0x09, (byte)0x00, (byte)0x00, (byte)0x08,
1059           (byte)0xC0, (byte)0x01, (byte)0x40, (byte)0x00, (byte)0x00,
1060           (byte)0x08, (byte)0x40, (byte)0x00, (byte)0x1E, (byte)0xF1,
1061           (byte)0x10, (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x0D,
1062           (byte)0x00, (byte)0x00, (byte)0x08, (byte)0x0C, (byte)0x00,
1063           (byte)0x00, (byte)0x08, (byte)0x17, (byte)0x00, (byte)0x00,
1064           (byte)0x08, (byte)0xF7, (byte)0x00, (byte)0x00, (byte)0x10,
1065        };
1066        UnknownRecord r = new UnknownRecord((short)0x00EB,(short)0x005a, data);
1067        workbook.getRecords().add(loc, r);
1068    }
1069
1070
1071
1072}
1073

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags