HSSFSheet


1   /* ====================================================================
2      Copyright 2002-2004   Apache Software Foundation
3   
4      Licensed under the Apache License, Version 2.0 (the "License");
5      you may not use this file except in compliance with the License.
6      You may obtain a copy of the License at
7   
8          http://www.apache.org/licenses/LICENSE-2.0
9   
10     Unless required by applicable law or agreed to in writing, software
11     distributed under the License is distributed on an "AS IS" BASIS,
12     WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13     See the License for the specific language governing permissions and
14     limitations under the License.
15  ==================================================================== */
16  
17  /*
18   * HSSFSheet.java
19   *
20   * Created on September 30, 2001, 3:40 PM
21   */
22  package org.apache.poi.hssf.usermodel;
23  
24  import org.apache.poi.ddf.EscherRecord;
25  import org.apache.poi.hssf.model.Sheet;
26  import org.apache.poi.hssf.model.Workbook;
27  import org.apache.poi.hssf.record.*;
28  import org.apache.poi.hssf.util.Region;
29  import org.apache.poi.util.POILogFactory;
30  import org.apache.poi.util.POILogger;
31  
32  import java.io.PrintWriter  ;
33  import java.util.ArrayList  ;
34  import java.util.Iterator  ;
35  import java.util.List  ;
36  import java.util.TreeMap  ;
37  
38  /**
39   * High level representation of a worksheet.
40   * @author  Andrew C. Oliver (acoliver at apache dot org)
41   * @author  Glen Stampoultzis (glens at apache.org)
42   * @author  Libin Roman (romal at vistaportal.com)
43   * @author  Shawn Laubach (slaubach at apache dot org) (Just a little)
44   */
45  
46  public class HSSFSheet
47  {
48      private static final int DEBUG = POILogger.DEBUG;
49  
50      /* Constants for margins */
51      public static final short LeftMargin = Sheet.LeftMargin;
52      public static final short RightMargin = Sheet.RightMargin;
53      public static final short TopMargin = Sheet.TopMargin;
54      public static final short BottomMargin = Sheet.BottomMargin;
55  
56      public static final byte PANE_LOWER_RIGHT = (byte)0;
57      public static final byte PANE_UPPER_RIGHT = (byte)1;
58      public static final byte PANE_LOWER_LEFT = (byte)2;
59      public static final byte PANE_UPPER_LEFT = (byte)3;
60  
61  
62      /**
63       * Used for compile-time optimization.  This is the initial size for the collection of
64       * rows.  It is currently set to 20.  If you generate larger sheets you may benefit
65       * by setting this to a higher number and recompiling a custom edition of HSSFSheet.
66       */
67  
68      public final static int INITIAL_CAPACITY = 20;
69  
70      /**
71       * reference to the low level Sheet object
72       */
73  
74      private Sheet sheet;
75      private TreeMap   rows;
76      private Workbook book;
77      private int firstrow;
78      private int lastrow;
79      private static POILogger log = POILogFactory.getLogger(HSSFSheet.class);
80  
81      /**
82       * Creates new HSSFSheet   - called by HSSFWorkbook to create a sheet from
83       * scratch.  You should not be calling this from application code (its protected anyhow).
84       *
85       * @param book - lowlevel Workbook object associated with the sheet.
86       * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
87       */
88  
89      protected HSSFSheet(Workbook book)
90      {
91          sheet = Sheet.createSheet();
92          rows = new TreeMap  ();   // new ArrayList(INITIAL_CAPACITY);
93          this.book = book;
94      }
95  
96      /**
97       * Creates an HSSFSheet representing the given Sheet object.  Should only be
98       * called by HSSFWorkbook when reading in an exisiting file.
99       *
100      * @param book - lowlevel Workbook object associated with the sheet.
101      * @param sheet - lowlevel Sheet object this sheet will represent
102      * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
103      */
104 
105     protected HSSFSheet(Workbook book, Sheet sheet)
106     {
107         this.sheet = sheet;
108         rows = new TreeMap  ();
109         this.book = book;
110         setPropertiesFromSheet(sheet);
111     }
112 
113     HSSFSheet cloneSheet(Workbook book) {
114       return new HSSFSheet(book, sheet.cloneSheet());
115     }
116 
117 
118     /**
119      * used internally to set the properties given a Sheet object
120      */
121 
122     private void setPropertiesFromSheet(Sheet sheet)
123     {
124         int sloc = sheet.getLoc();
125         RowRecord row = sheet.getNextRow();
126 
127         while (row != null)
128         {
129             createRowFromRecord(row);
130 
131             row = sheet.getNextRow();
132         }
133         sheet.setLoc(sloc);
134         CellValueRecordInterface cval = sheet.getNextValueRecord();
135         long timestart = System.currentTimeMillis();
136 
137         if (log.check( POILogger.DEBUG ))
138             log.log(DEBUG, "Time at start of cell creating in HSSF sheet = ",
139                 new Long  (timestart));
140         HSSFRow lastrow = null;
141 
142         while (cval != null)
143         {
144             long cellstart = System.currentTimeMillis();
145             HSSFRow hrow = lastrow;
146 
147             if ( ( lastrow == null ) || ( lastrow.getRowNum() != cval.getRow() ) )
148             {
149                 hrow = getRow( cval.getRow() );
150             }
151             if ( hrow != null )
152             {
153                 lastrow = hrow;
154                 if (log.check( POILogger.DEBUG ))
155                     log.log( DEBUG, "record id = " + Integer.toHexString( ( (Record) cval ).getSid() ) );
156                 hrow.createCellFromRecord( cval );
157                 cval = sheet.getNextValueRecord();
158                 if (log.check( POILogger.DEBUG ))
159                     log.log( DEBUG, "record took ",
160                         new Long  ( System.currentTimeMillis() - cellstart ) );
161             }
162             else
163             {
164                 cval = null;
165             }
166         }
167         if (log.check( POILogger.DEBUG ))
168             log.log(DEBUG, "total sheet cell creation took ",
169                 new Long  (System.currentTimeMillis() - timestart));
170     }
171 
172     /**
173      * Create a new row within the sheet and return the high level representation
174      *
175      * @param rownum  row number
176      * @return High level HSSFRow object representing a row in the sheet
177      * @see org.apache.poi.hssf.usermodel.HSSFRow
178      * @see #removeRow(HSSFRow)
179      */
180     public HSSFRow createRow(int rownum)
181     {
182         HSSFRow row = new HSSFRow(book, sheet, rownum);
183 
184         addRow(row, true);
185         return row;
186     }
187 
188     /**
189      * Used internally to create a high level Row object from a low level row object.
190      * USed when reading an existing file
191      * @param row  low level record to represent as a high level Row and add to sheet
192      * @return HSSFRow high level representation
193      */
194 
195     private HSSFRow createRowFromRecord(RowRecord row)
196     {
197         HSSFRow hrow = new HSSFRow(book, sheet, row);
198 
199         addRow(hrow, false);
200         return hrow;
201     }
202 
203     /**
204      * Remove a row from this sheet.  All cells contained in the row are removed as well
205      *
206      * @param row   representing a row to remove.
207      */
208 
209     public void removeRow(HSSFRow row)
210     {
211         sheet.setLoc(sheet.getDimsLoc());
212         if (rows.size() > 0)
213         {
214             rows.remove(row);
215             if (row.getRowNum() == getLastRowNum())
216             {
217                 lastrow = findLastRow(lastrow);
218             }
219             if (row.getRowNum() == getFirstRowNum())
220             {
221                 firstrow = findFirstRow(firstrow);
222             }
223             Iterator   iter = row.cellIterator();
224 
225             while (iter.hasNext())
226             {
227                 HSSFCell cell = (HSSFCell) iter.next();
228 
229                 sheet.removeValueRecord(row.getRowNum(),
230                         cell.getCellValueRecord());
231             }
232             sheet.removeRow(row.getRowRecord());
233         }
234     }
235 
236     /**
237      * used internally to refresh the "last row" when the last row is removed.
238      */
239 
240     private int findLastRow(int lastrow)
241     {
242         int rownum = lastrow - 1;
243         HSSFRow r = getRow(rownum);
244 
245         while (r == null && rownum > 0)
246         {
247             r = getRow(--rownum);
248         }
249         if (r == null)
250           return -1;
251         return rownum;
252     }
253 
254     /**
255      * used internally to refresh the "first row" when the first row is removed.
256      */
257 
258     private int findFirstRow(int firstrow)
259     {
260         int rownum = firstrow + 1;
261         HSSFRow r = getRow(rownum);
262 
263         while (r == null && rownum <= getLastRowNum())
264         {
265             r = getRow(++rownum);
266         }
267 
268         if (rownum > getLastRowNum())
269             return -1;
270 
271         return rownum;
272     }
273 
274     /**
275      * add a row to the sheet
276      *
277      * @param addLow whether to add the row to the low level model - false if its already there
278      */
279 
280     private void addRow(HSSFRow row, boolean addLow)
281     {
282         rows.put(row, row);
283         if (addLow)
284         {
285             sheet.addRow(row.getRowRecord());
286         }
287         if (row.getRowNum() > getLastRowNum())
288         {
289             lastrow = row.getRowNum();
290         }
291         if (row.getRowNum() < getFirstRowNum())
292         {
293             firstrow = row.getRowNum();
294         }
295     }
296 
297     /**
298      * Returns the logical row (not physical) 0-based.  If you ask for a row that is not
299      * defined you get a null.  This is to say row 4 represents the fifth row on a sheet.
300      * @param rownum  row to get
301      * @return HSSFRow representing the rownumber or null if its not defined on the sheet
302      */
303 
304     public HSSFRow getRow(int rownum)
305     {
306         HSSFRow row = new HSSFRow();
307 
308         //row.setRowNum((short) rownum);
309         row.setRowNum( rownum);
310         return (HSSFRow) rows.get(row);
311     }
312 
313     /**
314      * Returns the number of phsyically defined rows (NOT the number of rows in the sheet)
315      */
316 
317     public int getPhysicalNumberOfRows()
318     {
319         return rows.size();
320     }
321 
322     /**
323      * gets the first row on the sheet
324      * @return the number of the first logical row on the sheet
325      */
326 
327     public int getFirstRowNum()
328     {
329         return firstrow;
330     }
331 
332     /**
333      * gets the last row on the sheet
334      * @return last row contained n this sheet.
335      */
336 
337     public int getLastRowNum()
338     {
339         return lastrow;
340     }
341 
342     /**
343      * set the width (in units of 1/256th of a character width)
344      * @param column - the column to set (0-based)
345      * @param width - the width in units of 1/256th of a character width
346      */
347 
348     public void setColumnWidth(short column, short width)
349     {
350         sheet.setColumnWidth(column, width);
351     }
352 
353     /**
354      * get the width (in units of 1/256th of a character width )
355      * @param column - the column to set (0-based)
356      * @return width - the width in units of 1/256th of a character width
357      */
358 
359     public short getColumnWidth(short column)
360     {
361         return sheet.getColumnWidth(column);
362     }
363 
364     /**
365      * get the default column width for the sheet (if the columns do not define their own width) in
366      * characters
367      * @return default column width
368      */
369 
370     public short getDefaultColumnWidth()
371     {
372         return sheet.getDefaultColumnWidth();
373     }
374 
375     /**
376      * get the default row height for the sheet (if the rows do not define their own height) in
377      * twips (1/20 of  a point)
378      * @return  default row height
379      */
380 
381     public short getDefaultRowHeight()
382     {
383         return sheet.getDefaultRowHeight();
384     }
385 
386     /**
387      * get the default row height for the sheet (if the rows do not define their own height) in
388      * points.
389      * @return  default row height in points
390      */
391 
392     public float getDefaultRowHeightInPoints()
393     {
394         return (sheet.getDefaultRowHeight() / 20);
395     }
396 
397     /**
398      * set the default column width for the sheet (if the columns do not define their own width) in
399      * characters
400      * @param width default column width
401      */
402 
403     public void setDefaultColumnWidth(short width)
404     {
405         sheet.setDefaultColumnWidth(width);
406     }
407 
408     /**
409      * set the default row height for the sheet (if the rows do not define their own height) in
410      * twips (1/20 of  a point)
411      * @param  height default row height
412      */
413 
414     public void setDefaultRowHeight(short height)
415     {
416         sheet.setDefaultRowHeight(height);
417     }
418 
419     /**
420      * set the default row height for the sheet (if the rows do not define their own height) in
421      * points
422      * @param height default row height
423      */
424 
425     public void setDefaultRowHeightInPoints(float height)
426     {
427         sheet.setDefaultRowHeight((short) (height * 20));
428     }
429 
430     /**
431      * get whether gridlines are printed.
432      * @return true if printed
433      */
434 
435     public boolean isGridsPrinted()
436     {
437         return sheet.isGridsPrinted();
438     }
439 
440     /**
441      * set whether gridlines printed.
442      * @param value  false if not printed.
443      */
444 
445     public void setGridsPrinted(boolean value)
446     {
447         sheet.setGridsPrinted(value);
448     }
449 
450     /**
451      * adds a merged region of cells (hence those cells form one)
452      * @param region (rowfrom/colfrom-rowto/colto) to merge
453      * @return index of this region
454      */
455 
456     public int addMergedRegion(Region region)
457     {
458         //return sheet.addMergedRegion((short) region.getRowFrom(),
459         return sheet.addMergedRegion( region.getRowFrom(),
460                 region.getColumnFrom(),
461                 //(short) region.getRowTo(),
462                 region.getRowTo(),
463                 region.getColumnTo());
464     }
465 
466     /**
467      * determines whether the output is vertically centered on the page.
468      * @param value true to vertically center, false otherwise.
469      */
470 
471     public void setVerticallyCenter(boolean value)
472     {
473         VCenterRecord record =
474                 (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
475 
476         record.setVCenter(value);
477     }
478 
479     /**
480      * Determine whether printed output for this sheet will be vertically centered.
481      */
482 
483     public boolean getVerticallyCenter(boolean value)
484     {
485         VCenterRecord record =
486                 (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
487 
488         return record.getVCenter();
489     }
490 
491     /**
492      * determines whether the output is horizontally centered on the page.
493      * @param value true to horizontally center, false otherwise.
494      */
495 
496     public void setHorizontallyCenter(boolean value)
497     {
498         HCenterRecord record =
499                 (HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
500 
501         record.setHCenter(value);
502     }
503 
504     /**
505      * Determine whether printed output for this sheet will be horizontally centered.
506      */
507 
508     public boolean getHorizontallyCenter()
509     {
510         HCenterRecord record =
511                 (HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
512 
513         return record.getHCenter();
514     }
515 
516 
517 
518     /**
519      * removes a merged region of cells (hence letting them free)
520      * @param index of the region to unmerge
521      */
522 
523     public void removeMergedRegion(int index)
524     {
525         sheet.removeMergedRegion(index);
526     }
527 
528     /**
529      * returns the number of merged regions
530      * @return number of merged regions
531      */
532 
533     public int getNumMergedRegions()
534     {
535         return sheet.getNumMergedRegions();
536     }
537 
538     /**
539      * gets the region at a particular index
540      * @param index of the region to fetch
541      * @return the merged region (simple eh?)
542      */
543 
544     public Region getMergedRegionAt(int index)
545     {
546         return new Region(sheet.getMergedRegionAt(index));
547     }
548 
549     /**
550      * @return an iterator of the PHYSICAL rows.  Meaning the 3rd element may not
551      * be the third row if say for instance the second row is undefined.
552      */
553 
554     public Iterator   rowIterator()
555     {
556         return rows.values().iterator();
557     }
558 
559     /**
560      * used internally in the API to get the low level Sheet record represented by this
561      * Object.
562      * @return Sheet - low level representation of this HSSFSheet.
563      */
564 
565     protected Sheet getSheet()
566     {
567         return sheet;
568     }
569 
570     /**
571      * whether alternate expression evaluation is on
572      * @param b  alternative expression evaluation or not
573      */
574 
575     public void setAlternativeExpression(boolean b)
576     {
577         WSBoolRecord record =
578                 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
579 
580         record.setAlternateExpression(b);
581     }
582 
583     /**
584      * whether alternative formula entry is on
585      * @param b  alternative formulas or not
586      */
587 
588     public void setAlternativeFormula(boolean b)
589     {
590         WSBoolRecord record =
591                 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
592 
593         record.setAlternateFormula(b);
594     }
595 
596     /**
597      * show automatic page breaks or not
598      * @param b  whether to show auto page breaks
599      */
600 
601     public void setAutobreaks(boolean b)
602     {
603         WSBoolRecord record =
604                 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
605 
606         record.setAutobreaks(b);
607     }
608 
609     /**
610      * set whether sheet is a dialog sheet or not
611      * @param b  isDialog or not
612      */
613 
614     public void setDialog(boolean b)
615     {
616         WSBoolRecord record =
617                 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
618 
619         record.setDialog(b);
620     }
621 
622     /**
623      * set whether to display the guts or not
624      *
625      * @param b  guts or no guts (or glory)
626      */
627 
628     public void setDisplayGuts(boolean b)
629     {
630         WSBoolRecord record =
631                 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
632 
633         record.setDisplayGuts(b);
634     }
635 
636     /**
637      * fit to page option is on
638      * @param b  fit or not
639      */
640 
641     public void setFitToPage(boolean b)
642     {
643         WSBoolRecord record =
644                 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
645 
646         record.setFitToPage(b);
647     }
648 
649     /**
650      * set if row summaries appear below detail in the outline
651      * @param b  below or not
652      */
653 
654     public void setRowSumsBelow(boolean b)
655     {
656         WSBoolRecord record =
657                 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
658 
659         record.setRowSumsBelow(b);
660     }
661 
662     /**
663      * set if col summaries appear right of the detail in the outline
664      * @param b  right or not
665      */
666 
667     public void setRowSumsRight(boolean b)
668     {
669         WSBoolRecord record =
670                 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
671 
672         record.setRowSumsRight(b);
673     }
674 
675     /**
676      * whether alternate expression evaluation is on
677      * @return alternative expression evaluation or not
678      */
679 
680     public boolean getAlternateExpression()
681     {
682         return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
683                 .getAlternateExpression();
684     }
685 
686     /**
687      * whether alternative formula entry is on
688      * @return alternative formulas or not
689      */
690 
691     public boolean getAlternateFormula()
692     {
693         return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
694                 .getAlternateFormula();
695     }
696 
697     /**
698      * show automatic page breaks or not
699      * @return whether to show auto page breaks
700      */
701 
702     public boolean getAutobreaks()
703     {
704         return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
705                 .getAutobreaks();
706     }
707 
708     /**
709      * get whether sheet is a dialog sheet or not
710      * @return isDialog or not
711      */
712 
713     public boolean getDialog()
714     {
715         return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
716                 .getDialog();
717     }
718 
719     /**
720      * get whether to display the guts or not
721      *
722      * @return guts or no guts (or glory)
723      */
724 
725     public boolean getDisplayGuts()
726     {
727         return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
728                 .getDisplayGuts();
729     }
730 
731     /**
732      * fit to page option is on
733      * @return fit or not
734      */
735 
736     public boolean getFitToPage()
737     {
738         return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
739                 .getFitToPage();
740     }
741 
742     /**
743      * get if row summaries appear below detail in the outline
744      * @return below or not
745      */
746 
747     public boolean getRowSumsBelow()
748     {
749         return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
750                 .getRowSumsBelow();
751     }
752 
753     /**
754      * get if col summaries appear right of the detail in the outline
755      * @return right or not
756      */
757 
758     public boolean getRowSumsRight()
759     {
760         return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
761                 .getRowSumsRight();
762     }
763 
764     /**
765      * Returns whether gridlines are printed.
766      * @return Gridlines are printed
767      */
768     public boolean isPrintGridlines() {
769         return getSheet().getPrintGridlines().getPrintGridlines();
770     }
771 
772     /**
773      * Turns on or off the printing of gridlines.
774      * @param newPrintGridlines boolean to turn on or off the printing of
775      * gridlines
776      */
777     public void setPrintGridlines( boolean newPrintGridlines )
778     {
779         getSheet().getPrintGridlines().setPrintGridlines( newPrintGridlines );
780     }
781 
782     /**
783      * Gets the print setup object.
784      * @return The user model for the print setup object.
785      */
786     public HSSFPrintSetup getPrintSetup()
787     {
788         return new HSSFPrintSetup( getSheet().getPrintSetup() );
789     }
790 
791     /**
792      * Gets the user model for the document header.
793      * @return The Document header.
794      */
795     public HSSFHeader getHeader()
796     {
797         return new HSSFHeader( getSheet().getHeader() );
798     }
799 
800     /**
801      * Gets the user model for the document footer.
802      * @return The Document footer.
803      */
804     public HSSFFooter getFooter()
805     {
806         return new HSSFFooter( getSheet().getFooter() );
807     }
808 
809     /**
810      * Sets whether sheet is selected.
811      * @param sel Whether to select the sheet or deselect the sheet.
812      */
813     public void setSelected( boolean sel )
814     {
815         getSheet().setSelected( sel );
816     }
817 
818     /**
819      * Gets the size of the margin in inches.
820      * @param margin which margin to get
821      * @return the size of the margin
822      */
823     public double getMargin( short margin )
824     {
825         return getSheet().getMargin( margin );
826     }
827 
828     /**
829      * Sets the size of the margin in inches.
830      * @param margin which margin to get
831      * @param size the size of the margin
832      */
833     public void setMargin( short margin, double size )
834     {
835         getSheet().setMargin( margin, size );
836     }
837 
838     /**
839      * Answer whether protection is enabled or disabled
840      * @return true => protection enabled; false => protection disabled
841      */
842     public boolean getProtect() {
843         return getSheet().getProtect().getProtect();
844     }
845 
846     /**
847      * Sets the protection on enabled or disabled
848      * @param protect true => protection enabled; false => protection disabled
849      */
850     public void setProtect(boolean protect) {
851         getSheet().getProtect().setProtect(protect);
852     }
853 
854     /**
855      * Sets the zoom magnication for the sheet.  The zoom is expressed as a
856      * fraction.  For example to express a zoom of 75% use 3 for the numerator
857      * and 4 for the denominator.
858      *
859      * @param numerator     The numerator for the zoom magnification.
860      * @param denominator   The denominator for the zoom magnification.
861      */
862     public void setZoom( int numerator, int denominator)
863     {
864         if (numerator < 1 || numerator > 65535)
865             throw new IllegalArgumentException  ("Numerator must be greater than 1 and less than 65536");
866         if (denominator < 1 || denominator > 65535)
867             throw new IllegalArgumentException  ("Denominator must be greater than 1 and less than 65536");
868 
869         SCLRecord sclRecord = new SCLRecord();
870         sclRecord.setNumerator((short)numerator);
871         sclRecord.setDenominator((short)denominator);
872         getSheet().setSCLRecord(sclRecord);
873     }
874 
875     /**
876      * Shifts the merged regions left or right depending on mode
877      * <p>
878      * TODO: MODE , this is only row specific
879      * @param startRow
880      * @param endRow
881      * @param n
882      * @param isRow
883      */
884     protected void shiftMerged(int startRow, int endRow, int n, boolean isRow) {
885         List   shiftedRegions = new ArrayList  ();
886         //move merged regions completely if they fall within the new region boundaries when they are shifted
887         for (int i = 0; i < this.getNumMergedRegions(); i++) {
888              Region merged = this.getMergedRegionAt(i);
889 
890              boolean inStart = (merged.getRowFrom() >= startRow || merged.getRowTo() >= startRow);
891              boolean inEnd =  (merged.getRowTo() <= endRow || merged.getRowFrom() <= endRow);
892 
893              //dont check if it's not within the shifted area
894              if (! (inStart && inEnd)) continue;
895 
896              //only shift if the region outside the shifted rows is not merged too
897              if (!merged.contains(startRow-1, (short)0) && !merged.contains(endRow+1, (short)0)){
898                  merged.setRowFrom(merged.getRowFrom()+n);
899                  merged.setRowTo(merged.getRowTo()+n);
900                  //have to remove/add it back
901                  shiftedRegions.add(merged);
902                  this.removeMergedRegion(i);
903                  i = i -1; // we have to back up now since we removed one
904 
905              }
906 
907         }
908 
909         //readd so it doesn't get shifted again
910         Iterator   iterator = shiftedRegions.iterator();
911         while (iterator.hasNext()) {
912             Region region = (Region)iterator.next();
913 
914             this.addMergedRegion(region);
915         }
916 
917     }
918 
919     /**
920      * Shifts rows between startRow and endRow n number of rows.
921      * If you use a negative number, it will shift rows up.
922      * Code ensures that rows don't wrap around.
923      *
924      * Calls shiftRows(startRow, endRow, n, false, false);
925      *
926      * <p>
927      * Additionally shifts merged regions that are completely defined in these
928      * rows (ie. merged 2 cells on a row to be shifted).
929      * @param startRow the row to start shifting
930      * @param endRow the row to end shifting
931      * @param n the number of rows to shift
932      */
933     public void shiftRows( int startRow, int endRow, int n ) {
934         shiftRows(startRow, endRow, n, false, false);
935     }
936 
937     /**
938      * Shifts rows between startRow and endRow n number of rows.
939      * If you use a negative number, it will shift rows up.
940      * Code ensures that rows don't wrap around
941      *
942      * <p>
943      * Additionally shifts merged regions that are completely defined in these
944      * rows (ie. merged 2 cells on a row to be shifted).
945      * <p>
946      * TODO Might want to add bounds checking here
947      * @param startRow the row to start shifting
948      * @param endRow the row to end shifting
949      * @param n the number of rows to shift
950      * @param copyRowHeight whether to copy the row height during the shift
951      * @param resetOriginalRowHeight whether to set the original row's height to the default
952      */
953     public void shiftRows( int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight)
954     {
955         int s, e, inc;
956         if ( n < 0 )
957         {
958             s = startRow;
959             e = endRow;
960             inc = 1;
961         }
962         else
963         {
964             s = endRow;
965             e = startRow;
966             inc = -1;
967         }
968 
969         shiftMerged(startRow, endRow, n, true);
970         sheet.shiftRowBreaks(startRow, endRow, n);
971             
972         for ( int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum += inc )
973         {
974             HSSFRow row = getRow( rowNum );
975             HSSFRow row2Replace = getRow( rowNum + n );
976             if ( row2Replace == null )
977                 row2Replace = createRow( rowNum + n );
978 
979             HSSFCell cell;
980 
981 
982 
983 
984         // Removes the cells before over writting them.
985             for ( short col = row2Replace.getFirstCellNum(); col <= row2Replace.getLastCellNum(); col++ )
986             {
987                 cell = row2Replace.getCell( col );
988                 if ( cell != null )
989                     row2Replace.removeCell( cell );
990             }
991         if (row == null) continue; // Nothing to do for this row
992         else {
993         if (copyRowHeight) {
994             row2Replace.setHeight(row.getHeight());
995         }
996 
997         if (resetOriginalRowHeight) {
998             row.setHeight((short)0xff);
999         }
1000        }
1001            for ( short col = row.getFirstCellNum(); col <= row.getLastCellNum(); col++ )
1002            {
1003                cell = row.getCell( col );
1004                if ( cell != null )
1005                {
1006                    row.removeCell( cell );
1007                    CellValueRecordInterface cellRecord = cell.getCellValueRecord();
1008                    cellRecord.setRow( rowNum + n );
1009                    row2Replace.createCellFromRecord( cellRecord );
1010                    sheet.addValueRecord( rowNum + n, cellRecord );
1011                }
1012            }
1013        }
1014        if ( endRow == lastrow || endRow + n > lastrow ) lastrow = Math.min( endRow + n, 65535 );
1015        if ( startRow == firstrow || startRow + n < firstrow ) firstrow = Math.max( startRow + n, 0 );
1016    }
1017
1018    protected void insertChartRecords( List   records )
1019    {
1020        int window2Loc = sheet.findFirstRecordLocBySid( WindowTwoRecord.sid );
1021        sheet.getRecords().addAll( window2Loc, records );
1022    }
1023
1024    /**
1025     * Creates a split (freezepane).
1026     * @param colSplit      Horizonatal position of split.
1027     * @param rowSplit      Vertical position of split.
1028     * @param topRow        Top row visible in bottom pane
1029     * @param leftmostColumn   Left column visible in right pane.
1030     */
1031    public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow )
1032    {
1033        if (colSplit < 0 || colSplit > 255) throw new IllegalArgumentException  ("Column must be between 0 and 255");
1034        if (rowSplit < 0 || rowSplit > 65535) throw new IllegalArgumentException  ("Row must be between 0 and 65535");
1035        if (leftmostColumn < colSplit) throw new IllegalArgumentException  ("leftmostColumn parameter must not be less than colSplit parameter");
1036        if (topRow < rowSplit) throw new IllegalArgumentException  ("topRow parameter must not be less than leftmostColumn parameter");
1037        getSheet().createFreezePane( colSplit, rowSplit, topRow, leftmostColumn );
1038    }
1039
1040    /**
1041     * Creates a split (freezepane).
1042     * @param colSplit      Horizonatal position of split.
1043     * @param rowSplit      Vertical position of split.
1044     */
1045    public void createFreezePane( int colSplit, int rowSplit )
1046    {
1047        createFreezePane( colSplit, rowSplit, colSplit, rowSplit );
1048    }
1049
1050    /**
1051     * Creates a split pane.
1052     * @param xSplitPos      Horizonatal position of split (in 1/20th of a point).
1053     * @param ySplitPos      Vertical position of split (in 1/20th of a point).
1054     * @param topRow        Top row visible in bottom pane
1055     * @param leftmostColumn   Left column visible in right pane.
1056     * @param activePane    Active pane.  One of: PANE_LOWER_RIGHT,
1057     *                      PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT
1058     * @see #PANE_LOWER_LEFT
1059     * @see #PANE_LOWER_RIGHT
1060     * @see #PANE_UPPER_LEFT
1061     * @see #PANE_UPPER_RIGHT
1062     */
1063    public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane )
1064    {
1065        getSheet().createSplitPane( xSplitPos, ySplitPos, topRow, leftmostColumn, activePane );
1066    }
1067
1068    /**
1069     * Sets whether the gridlines are shown in a viewer.
1070     * @param show whether to show gridlines or not
1071     */
1072    public void setDisplayGridlines(boolean show) {
1073        sheet.setDisplayGridlines(show);
1074    }
1075
1076    /**
1077     * Returns if gridlines are displayed.
1078     * @return whether gridlines are displayed
1079     */
1080    public boolean isDisplayGridlines() {
1081    return sheet.isDisplayGridlines();
1082    }
1083
1084    /**
1085     * Sets whether the formulas are shown in a viewer.
1086     * @param show whether to show formulas or not
1087     */
1088    public void setDisplayFormulas(boolean show) {
1089        sheet.setDisplayFormulas(show);
1090    }
1091
1092    /**
1093     * Returns if formulas are displayed.
1094     * @return whether formulas are displayed
1095     */
1096    public boolean isDisplayFormulas() {
1097        return sheet.isDisplayFormulas();
1098    }
1099
1100    /**
1101     * Sets whether the RowColHeadings are shown in a viewer.
1102     * @param show whether to show RowColHeadings or not
1103     */
1104    public void setDisplayRowColHeadings(boolean show) {
1105        sheet.setDisplayRowColHeadings(show);
1106    }
1107
1108    /**
1109     * Returns if RowColHeadings are displayed.
1110     * @return whether RowColHeadings are displayed
1111     */
1112    public boolean isDisplayRowColHeadings() {
1113        return sheet.isDisplayRowColHeadings();
1114    }
1115    
1116    /**
1117     * Sets a page break at the indicated row
1118     * @param row FIXME: Document this!
1119     */
1120    public void setRowBreak(int row) {
1121        validateRow(row);
1122        sheet.setRowBreak(row, (short)0, (short)255);
1123    }
1124
1125    /**
1126     * Determines if there is a page break at the indicated row
1127     * @param row FIXME: Document this!
1128     * @return FIXME: Document this!
1129     */
1130    public boolean isRowBroken(int row) {
1131        return sheet.isRowBroken(row);
1132    }
1133    
1134    /**
1135     * Removes the page break at the indicated row
1136     * @param row
1137     */
1138    public void removeRowBreak(int row) {
1139        sheet.removeRowBreak(row);
1140    }
1141    
1142    /**
1143     * Retrieves all the horizontal page breaks
1144     * @return all the horizontal page breaks
1145     */
1146    public int[] getRowBreaks(){
1147        //we can probably cache this information, but this should be a sparsely used function 
1148        int[] returnValue = new int[sheet.getNumRowBreaks()];
1149        Iterator   iterator = sheet.getRowBreaks();
1150        int i = 0;
1151        while (iterator.hasNext()) {
1152            PageBreakRecord.Break breakItem = (PageBreakRecord.Break)iterator.next();
1153            returnValue[i++] = (int)breakItem.main;
1154        }
1155        return returnValue;
1156    }
1157
1158    /**
1159     * Retrieves all the vertical page breaks
1160     * @return all the vertical page breaks
1161     */
1162    public short[] getColumnBreaks(){
1163        //we can probably cache this information, but this should be a sparsely used function 
1164        short[] returnValue = new short[sheet.getNumColumnBreaks()];
1165        Iterator   iterator = sheet.getColumnBreaks();
1166        int i = 0;
1167        while (iterator.hasNext()) {
1168            PageBreakRecord.Break breakItem = (PageBreakRecord.Break)iterator.next();
1169            returnValue[i++] = breakItem.main;
1170        }
1171        return returnValue;
1172    }
1173    
1174    
1175    /**
1176     * Sets a page break at the indicated column
1177     * @param column
1178     */
1179    public void setColumnBreak(short column) {
1180        validateColumn(column);
1181        sheet.setColumnBreak(column, (short)0, (short)65535);
1182    }
1183
1184    /**
1185     * Determines if there is a page break at the indicated column
1186     * @param column FIXME: Document this!
1187     * @return FIXME: Document this!
1188     */
1189    public boolean isColumnBroken(short column) {
1190        return sheet.isColumnBroken(column);
1191    }
1192    
1193    /**
1194     * Removes a page break at the indicated column
1195     * @param column
1196     */
1197    public void removeColumnBreak(short column) {
1198        sheet.removeColumnBreak(column);
1199    }
1200    
1201    /**
1202     * Runs a bounds check for row numbers
1203     * @param row
1204     */
1205    protected void validateRow(int row) {
1206        if (row > 65535) throw new IllegalArgumentException  ("Maximum row number is 65535");
1207        if (row < 0) throw new IllegalArgumentException  ("Minumum row number is 0");
1208    }
1209    
1210    /**
1211     * Runs a bounds check for column numbers
1212     * @param column
1213     */
1214    protected void validateColumn(short column) {
1215        if (column > 255) throw new IllegalArgumentException  ("Maximum column number is 255");
1216        if (column < 0) throw new IllegalArgumentException  ("Minimum column number is 0");
1217    }
1218
1219    /**
1220     * Aggregates the drawing records and dumps the escher record hierarchy
1221     * to the standard output.
1222     */
1223    public void dumpDrawingRecords()
1224    {
1225        sheet.aggregateDrawingRecords(book.getDrawingManager());
1226
1227        EscherAggregate r = (EscherAggregate) getSheet().findFirstRecordBySid(EscherAggregate.sid);
1228        List   escherRecords = r.getEscherRecords();
1229        for ( Iterator   iterator = escherRecords.iterator(); iterator.hasNext(); )
1230        {
1231            EscherRecord escherRecord = (EscherRecord) iterator.next();
1232            PrintWriter   w = new PrintWriter  (System.out);
1233            escherRecord.display(w, 0);
1234            w.close();
1235        }
1236    }
1237
1238    /**
1239     * Creates the toplevel drawing patriarch.  This will have the effect of
1240     * removing any existing drawings on this sheet.
1241     *
1242     * @return  The new patriarch.
1243     */
1244    public HSSFPatriarch createDrawingPatriarch()
1245    {
1246        // Create the drawing group if it doesn't already exist.
1247        book.createDrawingGroup();
1248
1249        sheet.aggregateDrawingRecords(book.getDrawingManager());
1250        EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
1251        HSSFPatriarch patriarch = new HSSFPatriarch(this);
1252        agg.clear();     // Initially the behaviour will be to clear out any existing shapes in the sheet when
1253                         // creating a new patriarch.
1254        agg.setPatriarch(patriarch);
1255        return patriarch;
1256    }
1257
1258    /**
1259     * Expands or collapses a column group.
1260     *
1261     * @param columnNumber      One of the columns in the group.
1262     * @param collapsed         true = collapse group, false = expand group.
1263     */
1264    public void setColumnGroupCollapsed( short columnNumber, boolean collapsed )
1265    {
1266        sheet.setColumnGroupCollapsed( columnNumber, collapsed );
1267    }
1268
1269    /**
1270     * Create an outline for the provided column range.
1271     *
1272     * @param fromColumn        beginning of the column range.
1273     * @param toColumn          end of the column range.
1274     */
1275    public void groupColumn(short fromColumn, short toColumn)
1276    {
1277        sheet.groupColumnRange( fromColumn, toColumn, true );
1278    }
1279
1280    public void ungroupColumn( short fromColumn, short toColumn )
1281    {
1282        sheet.groupColumnRange( fromColumn, toColumn, false );
1283    }
1284
1285    public void groupRow(int fromRow, int toRow)
1286    {
1287        sheet.groupRowRange( fromRow, toRow, true );
1288    }
1289
1290    public void ungroupRow(int fromRow, int toRow)
1291    {
1292        sheet.groupRowRange( fromRow, toRow, false );
1293    }
1294
1295    public void setRowGroupCollapsed( int row, boolean collapse )
1296    {
1297        sheet.setRowGroupCollapsed( row, collapse );
1298    }
1299
1300
1301}
1302
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags