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    * HSSFSheet.java
57    *
58    * Created on September 30, 2001, 3:40 PM
59    */
60   package org.apache.poi.hssf.usermodel;
61   
62   import org.apache.poi.hssf.model.Sheet;
63   import org.apache.poi.hssf.model.Workbook;
64   import org.apache.poi.hssf.record.CellValueRecordInterface;
65   import org.apache.poi.hssf.record.Record;
66   import org.apache.poi.hssf.record.RowRecord;
67   import org.apache.poi.hssf.record.VCenterRecord;
68   import org.apache.poi.hssf.record.WSBoolRecord;
69   import org.apache.poi.hssf.util.Region;
70   import org.apache.poi.util.POILogFactory;
71   import org.apache.poi.util.POILogger;
72   
73   import java.util.Iterator;
74   import java.util.TreeMap;
75   
76   /**
77    * High level representation of a worksheet.
78    * @author  Andrew C. Oliver (acoliver at apache dot org)
79    * @author  Glen Stampoultzis (glens at apache.org)
80    * @author  Libin Roman (romal at vistaportal.com)
81    * @author  Shawn Laubach (laubach at acm.org) (Just a little)
82    */
83   
84   public class HSSFSheet
85   {
86       private static final int DEBUG = POILogger.DEBUG;
87   
88       /**
89        * Used for compile-time optimization.  This is the initial size for the collection of
90        * rows.  It is currently set to 20.  If you generate larger sheets you may benefit
91        * by setting this to a higher number and recompiling a custom edition of HSSFSheet.
92        */
93   
94       public final static int INITIAL_CAPACITY = 20;
95   
96       /**
97        * reference to the low level Sheet object
98        */
99   
100      private Sheet sheet;
101      private TreeMap rows;
102      private Workbook book;
103      private int firstrow;
104      private int lastrow;
105      private static POILogger log = POILogFactory.getLogger(HSSFSheet.class);
106  
107      /**
108       * Creates new HSSFSheet   - called by HSSFWorkbook to create a sheet from
109       * scratch.  You should not be calling this from application code (its protected anyhow).
110       *
111       * @param book - lowlevel Workbook object associated with the sheet.
112       * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
113       */
114  
115      protected HSSFSheet(Workbook book)
116      {
117          sheet = Sheet.createSheet();
118          rows = new TreeMap();   // new ArrayList(INITIAL_CAPACITY);
119          this.book = book;
120      }
121  
122      /**
123       * Creates an HSSFSheet representing the given Sheet object.  Should only be
124       * called by HSSFWorkbook when reading in an exisiting file.
125       *
126       * @param book - lowlevel Workbook object associated with the sheet.
127       * @param sheet - lowlevel Sheet object this sheet will represent
128       * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
129       */
130  
131      protected HSSFSheet(Workbook book, Sheet sheet)
132      {
133          this.sheet = sheet;
134          rows = new TreeMap();
135          this.book = book;
136          setPropertiesFromSheet(sheet);
137      }
138  
139  
140      /**
141       * used internally to set the properties given a Sheet object
142       */
143  
144      private void setPropertiesFromSheet(Sheet sheet)
145      {
146          int sloc = sheet.getLoc();
147          RowRecord row = sheet.getNextRow();
148  
149          while (row != null)
150          {
151              createRowFromRecord(row);
152  
153              row = sheet.getNextRow();
154          }
155          sheet.setLoc(sloc);
156          CellValueRecordInterface cval = sheet.getNextValueRecord();
157          long timestart = System.currentTimeMillis();
158  
159          log.log(DEBUG, "Time at start of cell creating in HSSF sheet = ",
160                  new Long(timestart));
161          HSSFRow lastrow = null;
162  
163          while (cval != null)
164          {
165              long cellstart = System.currentTimeMillis();
166              HSSFRow hrow = lastrow;
167  
168              if ((lastrow == null) || (lastrow.getRowNum() != cval.getRow()))
169              {
170                  hrow = getRow(cval.getRow());
171              }
172              lastrow = hrow;
173              log.log(DEBUG, "record id = "+ Integer.toHexString(((Record)cval).getSid()));
174              hrow.createCellFromRecord(cval);
175              cval = sheet.getNextValueRecord();
176              log.log(DEBUG, "record took ",
177                      new Long(System.currentTimeMillis() - cellstart));
178          }
179          log.log(DEBUG, "total sheet cell creation took ",
180                  new Long(System.currentTimeMillis() - timestart));
181      }
182  
183      /**
184       * Create a new row within the sheet and return the high level representation
185       *
186       * @param rownum  row number
187       * @return High level HSSFRow object representing a row in the sheet
188       * @see org.apache.poi.hssf.usermodel.HSSFRow
189       * @see #removeRow(HSSFRow)
190       */
191  
192      public HSSFRow createRow(short rownum)
193      {
194          HSSFRow row = new HSSFRow(book, sheet, rownum);
195  
196          addRow(row, true);
197          return row;
198      }
199  
200      /**
201       * Used internally to create a high level Row object from a low level row object.
202       * USed when reading an existing file
203       * @param row  low level record to represent as a high level Row and add to sheet
204       * @return HSSFRow high level representation
205       */
206  
207      private HSSFRow createRowFromRecord(RowRecord row)
208      {
209          HSSFRow hrow = new HSSFRow(book, sheet, row);
210  
211          addRow(hrow, false);
212          return hrow;
213      }
214  
215      /**
216       * Remove a row from this sheet.  All cells contained in the row are removed as well
217       *
218       * @param row   representing a row to remove.
219       */
220  
221      public void removeRow(HSSFRow row)
222      {
223          sheet.setLoc(sheet.getDimsLoc());
224          if (rows.size() > 0)
225          {
226              rows.remove(row);
227              if (row.getRowNum() == getLastRowNum())
228              {
229                  lastrow = findLastRow(lastrow);
230              }
231              if (row.getRowNum() == getFirstRowNum())
232              {
233                  firstrow = findFirstRow(firstrow);
234              }
235              Iterator iter = row.cellIterator();
236  
237              while (iter.hasNext())
238              {
239                  HSSFCell cell = (HSSFCell) iter.next();
240  
241                  sheet.removeValueRecord(row.getRowNum(),
242                          cell.getCellValueRecord());
243              }
244              sheet.removeRow(row.getRowRecord());
245          }
246      }
247  
248      /**
249       * used internally to refresh the "last row" when the last row is removed.
250       */
251  
252      private int findLastRow(int lastrow)
253      {
254          int rownum = lastrow - 1;
255          HSSFRow r = getRow(rownum);
256  
257          while (r == null && rownum >= 0)
258          {
259              r = getRow(--rownum);
260          }
261          return rownum;
262      }
263  
264      /**
265       * used internally to refresh the "first row" when the first row is removed.
266       */
267  
268      private int findFirstRow(int firstrow)
269      {
270          int rownum = firstrow + 1;
271          HSSFRow r = getRow(rownum);
272  
273          while (r == null && rownum <= getLastRowNum())
274          {
275              r = getRow(++rownum);
276          }
277  
278          if (rownum > getLastRowNum())
279              return -1;
280  
281          return rownum;
282      }
283  
284      /**
285       * add a row to the sheet
286       *
287       * @param addLow whether to add the row to the low level model - false if its already there
288       */
289  
290      private void addRow(HSSFRow row, boolean addLow)
291      {
292          rows.put(row, row);
293          if (addLow)
294          {
295              sheet.addRow(row.getRowRecord());
296          }
297          if (row.getRowNum() > getLastRowNum())
298          {
299              lastrow = row.getRowNum();
300          }
301          if (row.getRowNum() < getFirstRowNum())
302          {
303              firstrow = row.getRowNum();
304          }
305      }
306  
307      /**
308       * Returns the logical row (not physical) 0-based.  If you ask for a row that is not
309       * defined you get a null.  This is to say row 4 represents the fifth row on a sheet.
310       * @param rownum  row to get
311       * @return HSSFRow representing the rownumber or null if its not defined on the sheet
312       */
313  
314      public HSSFRow getRow(int rownum)
315      {
316          HSSFRow row = new HSSFRow();
317  
318          row.setRowNum((short) rownum);
319          return (HSSFRow) rows.get(row);
320      }
321  
322      /**
323       * Returns the number of phsyically defined rows (NOT the number of rows in the sheet)
324       */
325  
326      public int getPhysicalNumberOfRows()
327      {
328          return rows.size();
329      }
330  
331      /**
332       * gets the first row on the sheet
333       * @return the number of the first logical row on the sheet
334       */
335  
336      public int getFirstRowNum()
337      {
338          return firstrow;
339      }
340  
341      /**
342       * gets the last row on the sheet
343       * @return last row contained n this sheet.
344       */
345  
346      public int getLastRowNum()
347      {
348          return lastrow;
349      }
350  
351      /**
352       * set the width (in units of 1/256th of a character width)
353       * @param column - the column to set (0-based)
354       * @param width - the width in units of 1/256th of a character width
355       */
356  
357      public void setColumnWidth(short column, short width)
358      {
359          sheet.setColumnWidth(column, width);
360      }
361  
362      /**
363       * get the width (in units of 1/256th of a character width )
364       * @param column - the column to set (0-based)
365       * @return width - the width in units of 1/256th of a character width
366       */
367  
368      public short getColumnWidth(short column)
369      {
370          return sheet.getColumnWidth(column);
371      }
372  
373      /**
374       * get the default column width for the sheet (if the columns do not define their own width) in
375       * characters
376       * @return default column width
377       */
378  
379      public short getDefaultColumnWidth()
380      {
381          return sheet.getDefaultColumnWidth();
382      }
383  
384      /**
385       * get the default row height for the sheet (if the rows do not define their own height) in
386       * twips (1/20 of  a point)
387       * @return  default row height
388       */
389  
390      public short getDefaultRowHeight()
391      {
392          return sheet.getDefaultRowHeight();
393      }
394  
395      /**
396       * get the default row height for the sheet (if the rows do not define their own height) in
397       * points.
398       * @return  default row height in points
399       */
400  
401      public float getDefaultRowHeightInPoints()
402      {
403          return (sheet.getDefaultRowHeight() / 20);
404      }
405  
406      /**
407       * set the default column width for the sheet (if the columns do not define their own width) in
408       * characters
409       * @param width default column width
410       */
411  
412      public void setDefaultColumnWidth(short width)
413      {
414          sheet.setDefaultColumnWidth(width);
415      }
416  
417      /**
418       * set the default row height for the sheet (if the rows do not define their own height) in
419       * twips (1/20 of  a point)
420       * @param  height default row height
421       */
422  
423      public void setDefaultRowHeight(short height)
424      {
425          sheet.setDefaultRowHeight(height);
426      }
427  
428      /**
429       * set the default row height for the sheet (if the rows do not define their own height) in
430       * points
431       * @param height default row height
432       */
433  
434      public void setDefaultRowHeightInPoints(float height)
435      {
436          sheet.setDefaultRowHeight((short) (height * 20));
437      }
438  
439      /**
440       * get whether gridlines are printed.
441       * @return true if printed
442       */
443  
444      public boolean isGridsPrinted()
445      {
446          return sheet.isGridsPrinted();
447      }
448  
449      /**
450       * set whether gridlines printed.
451       * @param value  false if not printed.
452       */
453  
454      public void setGridsPrinted(boolean value)
455      {
456          sheet.setGridsPrinted(value);
457      }
458  
459      /**
460       * adds a merged region of cells (hence those cells form one)
461       * @param region (rowfrom/colfrom-rowto/colto) to merge
462       * @return index of this region
463       */
464  
465      public int addMergedRegion(Region region)
466      {
467          return sheet.addMergedRegion((short) region.getRowFrom(),
468                  region.getColumnFrom(),
469                  (short) region.getRowTo(),
470                  region.getColumnTo());
471      }
472  
473      /**
474       * determines whether the output is vertically centered on the page.
475       * @param value true to vertically center, false otherwise.
476       */
477  
478      public void setVerticallyCenter(boolean value)
479      {
480          VCenterRecord record =
481                  (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
482  
483          record.setVCenter(value);
484      }
485  
486      /**
487       * Determine whether printed output for this sheet will be vertically centered.
488       */
489  
490      public boolean getVerticallyCenter(boolean value)
491      {
492          VCenterRecord record =
493                  (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
494  
495          return record.getVCenter();
496      }
497  
498      /**
499       * removes a merged region of cells (hence letting them free)
500       * @param index of the region to unmerge
501       */
502  
503      public void removeMergedRegion(int index)
504      {
505          sheet.removeMergedRegion(index);
506      }
507  
508      /**
509       * returns the number of merged regions
510       * @return number of merged regions
511       */
512  
513      public int getNumMergedRegions()
514      {
515          return sheet.getNumMergedRegions();
516      }
517  
518      /**
519       * gets the region at a particular index
520       * @param index of the region to fetch
521       * @return the merged region (simple eh?)
522       */
523  
524      public Region getMergedRegionAt(int index)
525      {
526          return new Region(sheet.getMergedRegionAt(index));
527      }
528  
529      /**
530       * @return an iterator of the PHYSICAL rows.  Meaning the 3rd element may not
531       * be the third row if say for instance the second row is undefined.
532       */
533  
534      public Iterator rowIterator()
535      {
536          return rows.values().iterator();
537      }
538  
539      /**
540       * used internally in the API to get the low level Sheet record represented by this
541       * Object.
542       * @return Sheet - low level representation of this HSSFSheet.
543       */
544  
545      protected Sheet getSheet()
546      {
547          return sheet;
548      }
549  
550      /**
551       * whether alternate expression evaluation is on
552       * @param b  alternative expression evaluation or not
553       */
554  
555      public void setAlternativeExpression(boolean b)
556      {
557          WSBoolRecord record =
558                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
559  
560          record.setAlternateExpression(b);
561      }
562  
563      /**
564       * whether alternative formula entry is on
565       * @param b  alternative formulas or not
566       */
567  
568      public void setAlternativeFormula(boolean b)
569      {
570          WSBoolRecord record =
571                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
572  
573          record.setAlternateFormula(b);
574      }
575  
576      /**
577       * show automatic page breaks or not
578       * @param b  whether to show auto page breaks
579       */
580  
581      public void setAutobreaks(boolean b)
582      {
583          WSBoolRecord record =
584                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
585  
586          record.setAutobreaks(b);
587      }
588  
589      /**
590       * set whether sheet is a dialog sheet or not
591       * @param b  isDialog or not
592       */
593  
594      public void setDialog(boolean b)
595      {
596          WSBoolRecord record =
597                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
598  
599          record.setDialog(b);
600      }
601  
602      /**
603       * set whether to display the guts or not
604       *
605       * @param b  guts or no guts (or glory)
606       */
607  
608      public void setDisplayGuts(boolean b)
609      {
610          WSBoolRecord record =
611                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
612  
613          record.setDisplayGuts(b);
614      }
615  
616      /**
617       * fit to page option is on
618       * @param b  fit or not
619       */
620  
621      public void setFitToPage(boolean b)
622      {
623          WSBoolRecord record =
624                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
625  
626          record.setFitToPage(b);
627      }
628  
629      /**
630       * set if row summaries appear below detail in the outline
631       * @param b  below or not
632       */
633  
634      public void setRowSumsBelow(boolean b)
635      {
636          WSBoolRecord record =
637                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
638  
639          record.setRowSumsBelow(b);
640      }
641  
642      /**
643       * set if col summaries appear right of the detail in the outline
644       * @param b  right or not
645       */
646  
647      public void setRowSumsRight(boolean b)
648      {
649          WSBoolRecord record =
650                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
651  
652          record.setRowSumsRight(b);
653      }
654  
655      /**
656       * whether alternate expression evaluation is on
657       * @return alternative expression evaluation or not
658       */
659  
660      public boolean getAlternateExpression()
661      {
662          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
663                  .getAlternateExpression();
664      }
665  
666      /**
667       * whether alternative formula entry is on
668       * @return alternative formulas or not
669       */
670  
671      public boolean getAlternateFormula()
672      {
673          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
674                  .getAlternateFormula();
675      }
676  
677      /**
678       * show automatic page breaks or not
679       * @return whether to show auto page breaks
680       */
681  
682      public boolean getAutobreaks()
683      {
684          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
685                  .getAutobreaks();
686      }
687  
688      /**
689       * get whether sheet is a dialog sheet or not
690       * @return isDialog or not
691       */
692  
693      public boolean getDialog()
694      {
695          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
696                  .getDialog();
697      }
698  
699      /**
700       * get whether to display the guts or not
701       *
702       * @return guts or no guts (or glory)
703       */
704  
705      public boolean getDisplayGuts()
706      {
707          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
708                  .getDisplayGuts();
709      }
710  
711      /**
712       * fit to page option is on
713       * @return fit or not
714       */
715  
716      public boolean getFitToPage()
717      {
718          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
719                  .getFitToPage();
720      }
721  
722      /**
723       * get if row summaries appear below detail in the outline
724       * @return below or not
725       */
726  
727      public boolean getRowSumsBelow()
728      {
729          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
730                  .getRowSumsBelow();
731      }
732  
733      /**
734       * get if col summaries appear right of the detail in the outline
735       * @return right or not
736       */
737  
738      public boolean getRowSumsRight()
739      {
740          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
741                  .getRowSumsRight();
742      }
743  
744      /**
745       * Returns whether gridlines are printed.
746       * @return Gridlines are printed
747       */
748      public boolean isPrintGridlines() {
749          return getSheet().getPrintGridlines().getPrintGridlines();
750      }
751      
752      /**
753       * Turns on or off the printing of gridlines.
754       * @param newPrintGridlines boolean to turn on or off the printing of
755       * gridlines
756       */
757      public void setPrintGridlines(boolean newPrintGridlines) {
758          getSheet().getPrintGridlines().setPrintGridlines(newPrintGridlines);
759      }
760      
761      /**
762       * Gets the print setup object.
763       * @return The user model for the print setup object.
764       */
765      public HSSFPrintSetup getPrintSetup() {
766  	return new HSSFPrintSetup(getSheet().getPrintSetup());
767      }
768      
769      /**
770       * Gets the user model for the document header.
771       * @return The Document header.
772       */
773      public HSSFHeader getHeader() {
774  	return new HSSFHeader(getSheet().getHeader());
775      }
776      
777      /**
778       * Gets the user model for the document footer.
779       * @return The Document footer.
780       */
781      public HSSFFooter getFooter() {
782          return new HSSFFooter(getSheet().getFooter());
783      }
784  }
785