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