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      public HSSFRow createRow(int rownum)
194      {
195          HSSFRow row = new HSSFRow(book, sheet, rownum);
196  
197          addRow(row, true);
198          return row;
199      }
200  
201      /**
202       * Used internally to create a high level Row object from a low level row object.
203       * USed when reading an existing file
204       * @param row  low level record to represent as a high level Row and add to sheet
205       * @return HSSFRow high level representation
206       */
207  
208      private HSSFRow createRowFromRecord(RowRecord row)
209      {
210          HSSFRow hrow = new HSSFRow(book, sheet, row);
211  
212          addRow(hrow, false);
213          return hrow;
214      }
215  
216      /**
217       * Remove a row from this sheet.  All cells contained in the row are removed as well
218       *
219       * @param row   representing a row to remove.
220       */
221  
222      public void removeRow(HSSFRow row)
223      {
224          sheet.setLoc(sheet.getDimsLoc());
225          if (rows.size() > 0)
226          {
227              rows.remove(row);
228              if (row.getRowNum() == getLastRowNum())
229              {
230                  lastrow = findLastRow(lastrow);
231              }
232              if (row.getRowNum() == getFirstRowNum())
233              {
234                  firstrow = findFirstRow(firstrow);
235              }
236              Iterator iter = row.cellIterator();
237  
238              while (iter.hasNext())
239              {
240                  HSSFCell cell = (HSSFCell) iter.next();
241  
242                  sheet.removeValueRecord(row.getRowNum(),
243                          cell.getCellValueRecord());
244              }
245              sheet.removeRow(row.getRowRecord());
246          }
247      }
248  
249      /**
250       * used internally to refresh the "last row" when the last row is removed.
251       */
252  
253      private int findLastRow(int lastrow)
254      {
255          int rownum = lastrow - 1;
256          HSSFRow r = getRow(rownum);
257  
258          while (r == null && rownum >= 0)
259          {
260              r = getRow(--rownum);
261          }
262          return rownum;
263      }
264  
265      /**
266       * used internally to refresh the "first row" when the first row is removed.
267       */
268  
269      private int findFirstRow(int firstrow)
270      {
271          int rownum = firstrow + 1;
272          HSSFRow r = getRow(rownum);
273  
274          while (r == null && rownum <= getLastRowNum())
275          {
276              r = getRow(++rownum);
277          }
278  
279          if (rownum > getLastRowNum())
280              return -1;
281  
282          return rownum;
283      }
284  
285      /**
286       * add a row to the sheet
287       *
288       * @param addLow whether to add the row to the low level model - false if its already there
289       */
290  
291      private void addRow(HSSFRow row, boolean addLow)
292      {
293          rows.put(row, row);
294          if (addLow)
295          {
296              sheet.addRow(row.getRowRecord());
297          }
298          if (row.getRowNum() > getLastRowNum())
299          {
300              lastrow = row.getRowNum();
301          }
302          if (row.getRowNum() < getFirstRowNum())
303          {
304              firstrow = row.getRowNum();
305          }
306      }
307  
308      /**
309       * Returns the logical row (not physical) 0-based.  If you ask for a row that is not
310       * defined you get a null.  This is to say row 4 represents the fifth row on a sheet.
311       * @param rownum  row to get
312       * @return HSSFRow representing the rownumber or null if its not defined on the sheet
313       */
314  
315      public HSSFRow getRow(int rownum)
316      {
317          HSSFRow row = new HSSFRow();
318  
319          //row.setRowNum((short) rownum);
320          row.setRowNum( rownum);
321          return (HSSFRow) rows.get(row);
322      }
323  
324      /**
325       * Returns the number of phsyically defined rows (NOT the number of rows in the sheet)
326       */
327  
328      public int getPhysicalNumberOfRows()
329      {
330          return rows.size();
331      }
332  
333      /**
334       * gets the first row on the sheet
335       * @return the number of the first logical row on the sheet
336       */
337  
338      public int getFirstRowNum()
339      {
340          return firstrow;
341      }
342  
343      /**
344       * gets the last row on the sheet
345       * @return last row contained n this sheet.
346       */
347  
348      public int getLastRowNum()
349      {
350          return lastrow;
351      }
352  
353      /**
354       * set the width (in units of 1/256th of a character width)
355       * @param column - the column to set (0-based)
356       * @param width - the width in units of 1/256th of a character width
357       */
358  
359      public void setColumnWidth(short column, short width)
360      {
361          sheet.setColumnWidth(column, width);
362      }
363  
364      /**
365       * get the width (in units of 1/256th of a character width )
366       * @param column - the column to set (0-based)
367       * @return width - the width in units of 1/256th of a character width
368       */
369  
370      public short getColumnWidth(short column)
371      {
372          return sheet.getColumnWidth(column);
373      }
374  
375      /**
376       * get the default column width for the sheet (if the columns do not define their own width) in
377       * characters
378       * @return default column width
379       */
380  
381      public short getDefaultColumnWidth()
382      {
383          return sheet.getDefaultColumnWidth();
384      }
385  
386      /**
387       * get the default row height for the sheet (if the rows do not define their own height) in
388       * twips (1/20 of  a point)
389       * @return  default row height
390       */
391  
392      public short getDefaultRowHeight()
393      {
394          return sheet.getDefaultRowHeight();
395      }
396  
397      /**
398       * get the default row height for the sheet (if the rows do not define their own height) in
399       * points.
400       * @return  default row height in points
401       */
402  
403      public float getDefaultRowHeightInPoints()
404      {
405          return (sheet.getDefaultRowHeight() / 20);
406      }
407  
408      /**
409       * set the default column width for the sheet (if the columns do not define their own width) in
410       * characters
411       * @param width default column width
412       */
413  
414      public void setDefaultColumnWidth(short width)
415      {
416          sheet.setDefaultColumnWidth(width);
417      }
418  
419      /**
420       * set the default row height for the sheet (if the rows do not define their own height) in
421       * twips (1/20 of  a point)
422       * @param  height default row height
423       */
424  
425      public void setDefaultRowHeight(short height)
426      {
427          sheet.setDefaultRowHeight(height);
428      }
429  
430      /**
431       * set the default row height for the sheet (if the rows do not define their own height) in
432       * points
433       * @param height default row height
434       */
435  
436      public void setDefaultRowHeightInPoints(float height)
437      {
438          sheet.setDefaultRowHeight((short) (height * 20));
439      }
440  
441      /**
442       * get whether gridlines are printed.
443       * @return true if printed
444       */
445  
446      public boolean isGridsPrinted()
447      {
448          return sheet.isGridsPrinted();
449      }
450  
451      /**
452       * set whether gridlines printed.
453       * @param value  false if not printed.
454       */
455  
456      public void setGridsPrinted(boolean value)
457      {
458          sheet.setGridsPrinted(value);
459      }
460  
461      /**
462       * adds a merged region of cells (hence those cells form one)
463       * @param region (rowfrom/colfrom-rowto/colto) to merge
464       * @return index of this region
465       */
466  
467      public int addMergedRegion(Region region)
468      {
469          //return sheet.addMergedRegion((short) region.getRowFrom(),
470          return sheet.addMergedRegion( region.getRowFrom(),
471                  region.getColumnFrom(),
472                  //(short) region.getRowTo(),
473                  region.getRowTo(),
474                  region.getColumnTo());
475      }
476  
477      /**
478       * determines whether the output is vertically centered on the page.
479       * @param value true to vertically center, false otherwise.
480       */
481  
482      public void setVerticallyCenter(boolean value)
483      {
484          VCenterRecord record =
485                  (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
486  
487          record.setVCenter(value);
488      }
489  
490      /**
491       * Determine whether printed output for this sheet will be vertically centered.
492       */
493  
494      public boolean getVerticallyCenter(boolean value)
495      {
496          VCenterRecord record =
497                  (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
498  
499          return record.getVCenter();
500      }
501  
502      /**
503       * removes a merged region of cells (hence letting them free)
504       * @param index of the region to unmerge
505       */
506  
507      public void removeMergedRegion(int index)
508      {
509          sheet.removeMergedRegion(index);
510      }
511  
512      /**
513       * returns the number of merged regions
514       * @return number of merged regions
515       */
516  
517      public int getNumMergedRegions()
518      {
519          return sheet.getNumMergedRegions();
520      }
521  
522      /**
523       * gets the region at a particular index
524       * @param index of the region to fetch
525       * @return the merged region (simple eh?)
526       */
527  
528      public Region getMergedRegionAt(int index)
529      {
530          return new Region(sheet.getMergedRegionAt(index));
531      }
532  
533      /**
534       * @return an iterator of the PHYSICAL rows.  Meaning the 3rd element may not
535       * be the third row if say for instance the second row is undefined.
536       */
537  
538      public Iterator rowIterator()
539      {
540          return rows.values().iterator();
541      }
542  
543      /**
544       * used internally in the API to get the low level Sheet record represented by this
545       * Object.
546       * @return Sheet - low level representation of this HSSFSheet.
547       */
548  
549      protected Sheet getSheet()
550      {
551          return sheet;
552      }
553  
554      /**
555       * whether alternate expression evaluation is on
556       * @param b  alternative expression evaluation or not
557       */
558  
559      public void setAlternativeExpression(boolean b)
560      {
561          WSBoolRecord record =
562                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
563  
564          record.setAlternateExpression(b);
565      }
566  
567      /**
568       * whether alternative formula entry is on
569       * @param b  alternative formulas or not
570       */
571  
572      public void setAlternativeFormula(boolean b)
573      {
574          WSBoolRecord record =
575                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
576  
577          record.setAlternateFormula(b);
578      }
579  
580      /**
581       * show automatic page breaks or not
582       * @param b  whether to show auto page breaks
583       */
584  
585      public void setAutobreaks(boolean b)
586      {
587          WSBoolRecord record =
588                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
589  
590          record.setAutobreaks(b);
591      }
592  
593      /**
594       * set whether sheet is a dialog sheet or not
595       * @param b  isDialog or not
596       */
597  
598      public void setDialog(boolean b)
599      {
600          WSBoolRecord record =
601                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
602  
603          record.setDialog(b);
604      }
605  
606      /**
607       * set whether to display the guts or not
608       *
609       * @param b  guts or no guts (or glory)
610       */
611  
612      public void setDisplayGuts(boolean b)
613      {
614          WSBoolRecord record =
615                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
616  
617          record.setDisplayGuts(b);
618      }
619  
620      /**
621       * fit to page option is on
622       * @param b  fit or not
623       */
624  
625      public void setFitToPage(boolean b)
626      {
627          WSBoolRecord record =
628                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
629  
630          record.setFitToPage(b);
631      }
632  
633      /**
634       * set if row summaries appear below detail in the outline
635       * @param b  below or not
636       */
637  
638      public void setRowSumsBelow(boolean b)
639      {
640          WSBoolRecord record =
641                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
642  
643          record.setRowSumsBelow(b);
644      }
645  
646      /**
647       * set if col summaries appear right of the detail in the outline
648       * @param b  right or not
649       */
650  
651      public void setRowSumsRight(boolean b)
652      {
653          WSBoolRecord record =
654                  (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
655  
656          record.setRowSumsRight(b);
657      }
658  
659      /**
660       * whether alternate expression evaluation is on
661       * @return alternative expression evaluation or not
662       */
663  
664      public boolean getAlternateExpression()
665      {
666          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
667                  .getAlternateExpression();
668      }
669  
670      /**
671       * whether alternative formula entry is on
672       * @return alternative formulas or not
673       */
674  
675      public boolean getAlternateFormula()
676      {
677          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
678                  .getAlternateFormula();
679      }
680  
681      /**
682       * show automatic page breaks or not
683       * @return whether to show auto page breaks
684       */
685  
686      public boolean getAutobreaks()
687      {
688          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
689                  .getAutobreaks();
690      }
691  
692      /**
693       * get whether sheet is a dialog sheet or not
694       * @return isDialog or not
695       */
696  
697      public boolean getDialog()
698      {
699          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
700                  .getDialog();
701      }
702  
703      /**
704       * get whether to display the guts or not
705       *
706       * @return guts or no guts (or glory)
707       */
708  
709      public boolean getDisplayGuts()
710      {
711          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
712                  .getDisplayGuts();
713      }
714  
715      /**
716       * fit to page option is on
717       * @return fit or not
718       */
719  
720      public boolean getFitToPage()
721      {
722          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
723                  .getFitToPage();
724      }
725  
726      /**
727       * get if row summaries appear below detail in the outline
728       * @return below or not
729       */
730  
731      public boolean getRowSumsBelow()
732      {
733          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
734                  .getRowSumsBelow();
735      }
736  
737      /**
738       * get if col summaries appear right of the detail in the outline
739       * @return right or not
740       */
741  
742      public boolean getRowSumsRight()
743      {
744          return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
745                  .getRowSumsRight();
746      }
747  
748      /**
749       * Returns whether gridlines are printed.
750       * @return Gridlines are printed
751       */
752      public boolean isPrintGridlines() {
753          return getSheet().getPrintGridlines().getPrintGridlines();
754      }
755      
756      /**
757       * Turns on or off the printing of gridlines.
758       * @param newPrintGridlines boolean to turn on or off the printing of
759       * gridlines
760       */
761      public void setPrintGridlines(boolean newPrintGridlines) {
762          getSheet().getPrintGridlines().setPrintGridlines(newPrintGridlines);
763      }
764      
765      /**
766       * Gets the print setup object.
767       * @return The user model for the print setup object.
768       */
769      public HSSFPrintSetup getPrintSetup() {
770  	return new HSSFPrintSetup(getSheet().getPrintSetup());
771      }
772      
773      /**
774       * Gets the user model for the document header.
775       * @return The Document header.
776       */
777      public HSSFHeader getHeader() {
778  	return new HSSFHeader(getSheet().getHeader());
779      }
780      
781      /**
782       * Gets the user model for the document footer.
783       * @return The Document footer.
784       */
785      public HSSFFooter getFooter() {
786          return new HSSFFooter(getSheet().getFooter());
787      }
788  }
789