1    /* ====================================================================
2     * The Apache Software License, Version 1.1
3     *
4     * Copyright (c) 2002 The Apache Software Foundation.  All rights
5     * reserved.
6     *
7     * Redistribution and use in source and binary forms, with or without
8     * modification, are permitted provided that the following conditions
9     * are met:
10    *
11    * 1. Redistributions of source code must retain the above copyright
12    *    notice, this list of conditions and the following disclaimer.
13    *
14    * 2. Redistributions in binary form must reproduce the above copyright
15    *    notice, this list of conditions and the following disclaimer in
16    *    the documentation and/or other materials provided with the
17    *    distribution.
18    *
19    * 3. The end-user documentation included with the redistribution,
20    *    if any, must include the following acknowledgment:
21    *       "This product includes software developed by the
22    *        Apache Software Foundation (http://www.apache.org/)."
23    *    Alternately, this acknowledgment may appear in the software itself,
24    *    if and wherever such third-party acknowledgments normally appear.
25    *
26    * 4. The names "Apache" and "Apache Software Foundation" and
27    *    "Apache POI" must not be used to endorse or promote products
28    *    derived from this software without prior written permission. For
29    *    written permission, please contact apache@apache.org.
30    *
31    * 5. Products derived from this software may not be called "Apache",
32    *    "Apache POI", nor may "Apache" appear in their name, without
33    *    prior written permission of the Apache Software Foundation.
34    *
35    * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
36    * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
37    * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
38    * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
39    * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
40    * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
41    * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
42    * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
43    * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
44    * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
45    * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
46    * SUCH DAMAGE.
47    * ====================================================================
48    *
49    * This software consists of voluntary contributions made by many
50    * individuals on behalf of the Apache Software Foundation.  For more
51    * information on the Apache Software Foundation, please see
52    * <http://www.apache.org/>.
53    */
54   
55   /*
56    * HSSFWorkbook.java
57    *
58    * Created on September 30, 2001, 3:37 PM
59    */
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   /**
87    * High level representation of a workbook.  This is the first object most users
88    * will construct whether they are reading or writing a workbook.  It is also the
89    * top level object for creating new sheets/etc.
90    *
91    * @see org.apache.poi.hssf.model.Workbook
92    * @see org.apache.poi.hssf.usermodel.HSSFSheet
93    * @author  Andrew C. Oliver (acoliver at apache dot org)
94    * @author  Glen Stampoultzis (glens at apache.org)
95    * @author  Shawn Laubach (slaubach at apache dot org)
96    * @version 2.0-pre
97    */
98   
99   public class HSSFWorkbook
100          extends java.lang.java.lang.Objectvate static final int DEBUG = POILogger.DEBUG;
101  
102      /**
103       * used for compile-time performance/memory optimization.  This determines the
104       * initial capacity for the sheet collection.  Its currently set to 3.
105       * Changing it in this release will decrease performance
106       * since you're never allowed to have more or less than three sheets!
107       */
108  
109      public final static int INITIAL_CAPACITY = 3;
110  
111      /**
112       * this is the reference to the low level Workbook object
113       */
114  
115      private Workbook workbook;
116  
117      /**
118       * this holds the HSSFSheet objects attached to this workbook
119       */
120  
121      private ArrayList sheets;
122      
123      /**
124       * this holds the HSSFName objects attached to this workbook
125       */
126  
127      private ArrayList names;
128   
129      /**
130       * holds whether or not to preserve other nodes in the POIFS.  Used
131       * for macros and embedded objects. 
132       */
133      private boolean   preserveNodes;
134  
135      /**
136       * if you do preserve the nodes, you'll need to hold the whole POIFS in
137       * memory.
138       */
139      private POIFSFileSystem poifs;
140      
141      private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);
142  
143      /**
144       * Creates new HSSFWorkbook from scratch (start here!)
145       *
146       */
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      /**
160       * given a POI POIFSFileSystem object, read in its Workbook and populate the high and
161       * low level models.  If you're reading in a workbook...start here.
162       *
163       * @param fs the POI filesystem that contains the Workbook stream.
164       * @param preserveNodes whether to preseve other nodes, such as 
165       *        macros.  This takes more memory, so only say yes if you
166       *        need to.
167       * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
168       * @exception IOException if the stream cannot be read
169       */
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);   // convert all LabelRecord records to LabelSSTRecord
203              HSSFSheet hsheet = new HSSFSheet(workbook, sheet);
204  
205              sheets.add(hsheet);
206  
207              // workbook.setSheetName(sheets.size() -1, "Sheet"+sheets.size());
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      /**
221       * Companion to HSSFWorkbook(POIFSFileSystem), this constructs the POI filesystem around your
222       * inputstream.
223       *
224       * @param s  the POI filesystem that contains the Workbook stream.
225       * @param preserveNodes whether to preseve other nodes, such as 
226       *        macros.  This takes more memory, so only say yes if you
227       *        need to.
228       * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
229       * @see #HSSFWorkbook(POIFSFileSystem)
230       * @exception IOException if the stream cannot be read
231       */
232  
233      public HSSFWorkbook(InputStream s, boolean preserveNodes)
234              throws IOException
235      {
236          this(new POIFSFileSystem(s), preserveNodes);
237      }
238  
239      /**
240       * used internally to set the workbook properties.
241       */
242  
243      private void setPropertiesFromWorkbook(Workbook book)
244      {
245          this.workbook = book;
246  
247          // none currently
248      }
249  
250      public final static byte ENCODING_COMPRESSED_UNICODE = 0;
251      public final static byte ENCODING_UTF_16             = 1;
252      
253      /**
254       * set the sheet name.
255       * @param sheet number (0 based)
256       * @param sheet name
257       */
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              // TODO java.io.UnsupportedEncodingException
278              throw new RuntimeException( "Unsupported encoding" );
279          }
280          
281          workbook.setSheetName( sheet, name, encoding );
282      }
283  
284      /**
285       * get the sheet name
286       * @param sheet Number
287       * @return Sheet name
288       */
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      /*
300       * get the sheet's index
301       * @param name  sheet name
302       * @return sheet index or -1 if it was not found.
303       */
304  
305      /** Returns the index of the sheet by his name
306       * @param name the sheet name
307       * @return index of the sheet (0 based)
308       */    
309      public int getSheetIndex(String name)
310      {
311          int retval = workbook.getSheetIndex(name);
312          
313          return retval;
314      }
315      
316      /**
317       * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns
318       * the high level representation.  Use this to create new sheets.
319       *
320       * @return HSSFSheet representing the new sheet.
321       */
322  
323      public HSSFSheet createSheet()
324      {
325  
326  //        if (getNumberOfSheets() == 3)
327  //            throw new RuntimeException("You cannot have more than three sheets in HSSF 1.0");
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      /**
340       * create an HSSFSheet from an existing sheet in the HSSFWorkbook.
341       *
342       * @return HSSFSheet representing the cloned sheet.
343       */
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      /**
362       * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns
363       * the high level representation.  Use this to create new sheets.
364       *
365       * @param sheetname     sheetname to set for the sheet.
366       * @return HSSFSheet representing the new sheet.
367       */
368  
369      public HSSFSheet createSheet(String sheetname)
370      {
371  
372  //        if (getNumberOfSheets() == 3)
373  //            throw new RuntimeException("You cannot have more than three sheets in HSSF 1.0");
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      /**
385       * get the number of spreadsheets in the workbook (this will be three after serialization)
386       * @return number of sheets
387       */
388  
389      public int getNumberOfSheets()
390      {
391          return sheets.size();
392      }
393  
394      /**
395       * Get the HSSFSheet object at the given index.
396       * @param index of the sheet number (0-based physical & logical)
397       * @return HSSFSheet at the provided index
398       */
399  
400      public HSSFSheet getSheetAt(int index)
401      {
402          return (HSSFSheet) sheets.get(index);
403      }
404  
405      /**
406       * Get sheet with the given name
407       * @param name of the sheet
408       * @return HSSFSheet with the name provided or null if it does not exist
409       */
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      /**
428       * removes sheet at the given index
429       * @param index of the sheet  (0-based)
430       */
431  
432      public void removeSheetAt(int index)
433      {
434          sheets.remove(index);
435          workbook.removeSheet(index);
436      }
437  
438      /**
439       * determine whether the Excel GUI will backup the workbook when saving.
440       *
441       * @param backupValue   true to indicate a backup will be performed.
442       */
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      /**
453       * determine whether the Excel GUI will backup the workbook when saving.
454       *
455       * @return the current setting for backups.
456       */
457  
458      public boolean getBackupFlag()
459      {
460          BackupRecord backupRecord = workbook.getBackupRecord();
461  
462          return (backupRecord.getBackup() == 0) ? false
463                  : true;
464      }
465  
466      /**
467       * Sets the repeating rows and columns for a sheet (as found in
468       * File->PageSetup->Sheet).  This is function is included in the workbook
469       * because it creates/modifies name records which are stored at the
470       * workbook level.
471       * <p>
472       * To set just repeating columns:
473       * <pre>
474       *  workbook.setRepeatingRowsAndColumns(0,0,1,-1-1);
475       * </pre>
476       * To set just repeating rows:
477       * <pre>
478       *  workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
479       * </pre>
480       * To remove all repeating rows and columns for a sheet.
481       * <pre>
482       *  workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);
483       * </pre>
484       *
485       * @param sheetIndex    0 based index to sheet.
486       * @param startColumn   0 based start of repeating columns.
487       * @param endColumn     0 based end of repeating columns.
488       * @param startRow      0 based start of repeating rows.
489       * @param endRow        0 based end of repeating rows.
490       */
491      public void setRepeatingRowsAndColumns(int sheetIndex,
492                                             int startColumn, int endColumn,
493                                             int startRow, int endRow)
494      {
495          // Check arguments
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      /**
619       * create a new Font and add it to the workbook's font table
620       * @return new font object
621       */
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++;   // THERE IS NO FOUR!!
631          }
632          HSSFFont retval = new HSSFFont(fontindex, font);
633  
634          return retval;
635      }
636  
637      /**
638       * get the number of fonts in the font table
639       * @return number of fonts
640       */
641  
642      public short getNumberOfFonts()
643      {
644          return (short) workbook.getNumberOfFontRecords();
645      }
646  
647      /**
648       * get the font at the given index number
649       * @param idx  index number
650       * @return HSSFFont at the index
651       */
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      /**
662       * create a new Cell style and add it to the workbook's style table
663       * @return the new Cell Style object
664       */
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      /**
676       * get the number of styles the workbook contains
677       * @return count of cell styles
678       */
679  
680      public short getNumCellStyles()
681      {
682          return (short) workbook.getNumExFormats();
683      }
684  
685      /**
686       * get the cell style object at the given index
687       * @param idx  index within the set of styles
688       * @return HSSFCellStyle object at the index
689       */
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      /**
700       * Method write - write out this workbook to an Outputstream.  Constructs
701       * a new POI POIFSFileSystem, passes in the workbook binary representation  and
702       * writes it out.
703       *
704       * @param stream - the java OutputStream you wish to write the XLS to
705       *
706       * @exception IOException if anything can't be written.
707       * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
708       */
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          //poifs.writeFilesystem(stream);
725      }
726  
727      /**
728       * Method getBytes - get the bytes of just the HSSF portions of the XLS file.
729       * Use this to construct a POI POIFSFileSystem yourself.
730       *
731       *
732       * @return byte[] array containing the binary representation of this workbook and all contained
733       *         sheets, rows, cells, etc.
734       *
735       * @see org.apache.poi.hssf.model.Workbook
736       * @see org.apache.poi.hssf.model.Sheet
737       */
738  
739      public byte[] getBytes()
740      {
741          log.log(DEBUG, "HSSFWorkbook.getBytes()");
742          int wbsize = workbook.getSize();
743  
744          // log.debug("REMOVEME: old sizing method "+workbook.serialize().length);
745          // ArrayList sheetbytes = new ArrayList(sheets.size());
746          int totalsize = wbsize;
747  
748          for (int k = 0; k < sheets.size(); k++)
749          {
750              workbook.setSheetBof(k, totalsize);
751  
752              // sheetbytes.add((( HSSFSheet ) sheets.get(k)).getSheet().getSize());
753              totalsize += ((HSSFSheet) sheets.get(k)).getSheet().getSize();
754          }
755  /*        if (totalsize < 4096)
756          {
757              totalsize = 4096;
758          }*/
759          byte[] retval = new byte[totalsize];
760          int pos = workbook.serialize(0, retval);
761  
762          // System.arraycopy(wb, 0, retval, 0, wb.length);
763          for (int k = 0; k < sheets.size(); k++)
764          {
765  
766              // byte[] sb = (byte[])sheetbytes.get(k);
767              // System.arraycopy(sb, 0, retval, pos, sb.length);
768              pos += ((HSSFSheet) sheets.get(k)).getSheet().serialize(pos,
769                      retval);   // sb.length;
770          }
771  /*        for (int k = pos; k < totalsize; k++)
772          {
773              retval[k] = 0;
774          }*/
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      /** gets the total number of named ranges in the workboko
794       * @return number of named ranges
795       */    
796      public int getNumberOfNames(){
797          int result = names.size();
798          return result;
799      }
800      
801      /** gets the Named range
802       * @param index position of the named range
803       * @return named range high level
804       */    
805      public HSSFName getNameAt(int index){
806          HSSFName result = (HSSFName) names.get(index);
807          
808          return result;
809      }
810      
811      /** gets the named range name
812       * @param index the named range index (0 based)
813       * @return named range name
814       */    
815      public String getNameName(int index){
816          String result = getNameAt(index).getNameName();
817                  
818          return result;
819      }
820      
821      
822      /** creates a new named range and add it to the model
823       * @return named range high level
824       */    
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      /** gets the named range index by his name
836       * @param name named range name
837       * @return named range index 
838       */    
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      /** remove the named range by his index
858       * @param index named range index (0 based)
859       */    
860      public void removeName(int index){
861          names.remove(index);
862          workbook.removeName(index);        
863      }
864  
865      /**
866       * Creates an instance of HSSFDataFormat.
867       * @return the HSSFDataFormat object
868       * @see org.apache.poi.hssf.record.FormatRecord
869       * @see org.apache.poi.hssf.record.Record
870       */
871      public HSSFDataFormat createDataFormat() {
872          return new HSSFDataFormat(workbook);
873      }
874  	
875      /** remove the named range by his name
876       * @param name named range name
877       */    
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     /**
891      * Copies nodes from one POIFS to the other minus the excepts
892      * @param source is the source POIFS to copy from
893      * @param target is the target POIFS to copy to 
894      * @param excepts is a list of Strings specifying what nodes NOT to copy 
895      */
896     private void copyNodes(POIFSFileSystem source, POIFSFileSystem target, 
897                            List excepts) throws IOException {
898        //System.err.println("CopyNodes called");
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         //System.err.println("copyNodeRecursively called with "+entry.getName()+
925         //                   ","+target.getName());
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