1 /* ====================================================================
2 Licensed to the Apache Software Foundation (ASF) under one or more
3 contributor license agreements. See the NOTICE file distributed with
4 this work for additional information regarding copyright ownership.
5 The ASF licenses this file to You under the Apache License, Version 2.0
6 (the "License"); you may not use this file except in compliance with
7 the License. You may obtain a copy of the License at
8
9 http://www.apache.org/licenses/LICENSE-2.0
10
11 Unless required by applicable law or agreed to in writing, software
12 distributed under the License is distributed on an "AS IS" BASIS,
13 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 See the License for the specific language governing permissions and
15 limitations under the License.
16 ==================================================================== */
17
18 package org.apache.poi.hssf.usermodel;
19
20 import java.awt.font.FontRenderContext;
21 import java.awt.font.TextAttribute;
22 import java.awt.font.TextLayout;
23 import java.awt.geom.AffineTransform;
24 import java.io.PrintWriter;
25 import java.text.AttributedString;
26 import java.text.DecimalFormat;
27 import java.text.NumberFormat;
28 import java.util.ArrayList;
29 import java.util.Iterator;
30 import java.util.List;
31 import java.util.Stack;
32 import java.util.TreeMap;
33
34 import org.apache.poi.ddf.EscherRecord;
35 import org.apache.poi.hssf.model.FormulaParser;
36 import org.apache.poi.hssf.model.Sheet;
37 import org.apache.poi.hssf.model.Workbook;
38 import org.apache.poi.hssf.record;
39 import org.apache.poi.hssf.record.formula.Ptg;
40 import org.apache.poi.hssf.record.formula.RefPtg;
41 import org.apache.poi.hssf.util.HSSFCellRangeAddress;
42 import org.apache.poi.hssf.util.HSSFDataValidation;
43 import org.apache.poi.hssf.util.PaneInformation;
44 import org.apache.poi.hssf.util.Region;
45 import org.apache.poi.util.POILogFactory;
46 import org.apache.poi.util.POILogger;
47
48 /**
49 * High level representation of a worksheet.
50 * @author Andrew C. Oliver (acoliver at apache dot org)
51 * @author Glen Stampoultzis (glens at apache.org)
52 * @author Libin Roman (romal at vistaportal.com)
53 * @author Shawn Laubach (slaubach at apache dot org) (Just a little)
54 * @author Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too)
55 * @author Yegor Kozlov (yegor at apache.org) (Autosizing columns)
56 */
57 public final class HSSFSheet {
58 private static final int DEBUG = POILogger.DEBUG;
59
60 /* Constants for margins */
61 public static final short LeftMargin = Sheet.LeftMargin;
62 public static final short RightMargin = Sheet.RightMargin;
63 public static final short TopMargin = Sheet.TopMargin;
64 public static final short BottomMargin = Sheet.BottomMargin;
65
66 public static final byte PANE_LOWER_RIGHT = (byte)0;
67 public static final byte PANE_UPPER_RIGHT = (byte)1;
68 public static final byte PANE_LOWER_LEFT = (byte)2;
69 public static final byte PANE_UPPER_LEFT = (byte)3;
70
71
72 /**
73 * Used for compile-time optimization. This is the initial size for the collection of
74 * rows. It is currently set to 20. If you generate larger sheets you may benefit
75 * by setting this to a higher number and recompiling a custom edition of HSSFSheet.
76 */
77
78 public final static int INITIAL_CAPACITY = 20;
79
80 /**
81 * reference to the low level Sheet object
82 */
83
84 private Sheet sheet;
85 private TreeMap rows;
86 protected Workbook book;
87 protected HSSFWorkbook workbook;
88 private int firstrow;
89 private int lastrow;
90 private static POILogger log = POILogFactory.getLogger(HSSFSheet.class);
91
92 /**
93 * Creates new HSSFSheet - called by HSSFWorkbook to create a sheet from
94 * scratch. You should not be calling this from application code (its protected anyhow).
95 *
96 * @param workbook - The HSSF Workbook object associated with the sheet.
97 * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
98 */
99
100 protected HSSFSheet(HSSFWorkbook workbook)
101 {
102 sheet = Sheet.createSheet();
103 rows = new TreeMap(); // new ArrayList(INITIAL_CAPACITY);
104 this.workbook = workbook;
105 this.book = workbook.getWorkbook();
106 }
107
108 /**
109 * Creates an HSSFSheet representing the given Sheet object. Should only be
110 * called by HSSFWorkbook when reading in an exisiting file.
111 *
112 * @param workbook - The HSSF Workbook object associated with the sheet.
113 * @param sheet - lowlevel Sheet object this sheet will represent
114 * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
115 */
116
117 protected HSSFSheet(HSSFWorkbook workbook, Sheet sheet)
118 {
119 this.sheet = sheet;
120 rows = new TreeMap();
121 this.workbook = workbook;
122 this.book = workbook.getWorkbook();
123 setPropertiesFromSheet(sheet);
124 }
125
126 HSSFSheet cloneSheet(HSSFWorkbook workbook) {
127 return new HSSFSheet(workbook, sheet.cloneSheet());
128 }
129
130
131 /**
132 * used internally to set the properties given a Sheet object
133 */
134
135 private void setPropertiesFromSheet(Sheet sheet)
136 {
137 int sloc = sheet.getLoc();
138 RowRecord row = sheet.getNextRow();
139 boolean rowRecordsAlreadyPresent = row!=null;
140
141 while (row != null)
142 {
143 createRowFromRecord(row);
144
145 row = sheet.getNextRow();
146 }
147 sheet.setLoc(sloc);
148 CellValueRecordInterface cval = sheet.getNextValueRecord();
149 long timestart = System.currentTimeMillis();
150
151 if (log.check( POILogger.DEBUG ))
152 log.log(DEBUG, "Time at start of cell creating in HSSF sheet = ",
153 new Long(timestart));
154 HSSFRow lastrow = null;
155
156 while (cval != null)
157 {
158 long cellstart = System.currentTimeMillis();
159 HSSFRow hrow = lastrow;
160
161 if ( ( lastrow == null ) || ( lastrow.getRowNum() != cval.getRow() ) )
162 {
163 hrow = getRow( cval.getRow() );
164 if (hrow == null) {
165 // Some tools (like Perl module Spreadsheet::WriteExcel - bug 41187) skip the RowRecords
166 // Excel, OpenOffice.org and GoogleDocs are all OK with this, so POI should be too.
167 if (rowRecordsAlreadyPresent) {
168 // if at least one row record is present, all should be present.
169 throw new RuntimeException("Unexpected missing row when some rows already present");
170 }
171 // create the row record on the fly now.
172 RowRecord rowRec = new RowRecord(cval.getRow());
173 sheet.addRow(rowRec);
174 hrow = createRowFromRecord(rowRec);
175 }
176 }
177 if ( hrow != null )
178 {
179 lastrow = hrow;
180 if (log.check( POILogger.DEBUG ))
181 log.log( DEBUG, "record id = " + Integer.toHexString( ( (Record) cval ).getSid() ) );
182 hrow.createCellFromRecord( cval );
183 cval = sheet.getNextValueRecord();
184 if (log.check( POILogger.DEBUG ))
185 log.log( DEBUG, "record took ",
186 new Long( System.currentTimeMillis() - cellstart ) );
187 }
188 else
189 {
190 cval = null;
191 }
192 }
193 if (log.check( POILogger.DEBUG ))
194 log.log(DEBUG, "total sheet cell creation took ",
195 new Long(System.currentTimeMillis() - timestart));
196 }
197
198 /**
199 * Create a new row within the sheet and return the high level representation
200 *
201 * @param rownum row number
202 * @return High level HSSFRow object representing a row in the sheet
203 * @see org.apache.poi.hssf.usermodel.HSSFRow
204 * @see #removeRow(HSSFRow)
205 */
206 public HSSFRow createRow(int rownum)
207 {
208 HSSFRow row = new HSSFRow(workbook, sheet, rownum);
209
210 addRow(row, true);
211 return row;
212 }
213
214 /**
215 * Used internally to create a high level Row object from a low level row object.
216 * USed when reading an existing file
217 * @param row low level record to represent as a high level Row and add to sheet
218 * @return HSSFRow high level representation
219 */
220
221 private HSSFRow createRowFromRecord(RowRecord row)
222 {
223 HSSFRow hrow = new HSSFRow(workbook, sheet, row);
224
225 addRow(hrow, false);
226 return hrow;
227 }
228
229 /**
230 * Remove a row from this sheet. All cells contained in the row are removed as well
231 *
232 * @param row representing a row to remove.
233 */
234
235 public void removeRow(HSSFRow row)
236 {
237 sheet.setLoc(sheet.getDimsLoc());
238 if (rows.size() > 0)
239 {
240 rows.remove(row);
241 if (row.getRowNum() == getLastRowNum())
242 {
243 lastrow = findLastRow(lastrow);
244 }
245 if (row.getRowNum() == getFirstRowNum())
246 {
247 firstrow = findFirstRow(firstrow);
248 }
249 Iterator iter = row.cellIterator();
250
251 while (iter.hasNext())
252 {
253 HSSFCell cell = (HSSFCell) iter.next();
254
255 sheet.removeValueRecord(row.getRowNum(),
256 cell.getCellValueRecord());
257 }
258 sheet.removeRow(row.getRowRecord());
259 }
260 }
261
262 /**
263 * used internally to refresh the "last row" when the last row is removed.
264 */
265
266 private int findLastRow(int lastrow)
267 {
268 int rownum = lastrow - 1;
269 HSSFRow r = getRow(rownum);
270
271 while (r == null && rownum > 0)
272 {
273 r = getRow(--rownum);
274 }
275 if (r == null)
276 return -1;
277 return rownum;
278 }
279
280 /**
281 * used internally to refresh the "first row" when the first row is removed.
282 */
283
284 private int findFirstRow(int firstrow)
285 {
286 int rownum = firstrow + 1;
287 HSSFRow r = getRow(rownum);
288
289 while (r == null && rownum <= getLastRowNum())
290 {
291 r = getRow(++rownum);
292 }
293
294 if (rownum > getLastRowNum())
295 return -1;
296
297 return rownum;
298 }
299
300 /**
301 * add a row to the sheet
302 *
303 * @param addLow whether to add the row to the low level model - false if its already there
304 */
305
306 private void addRow(HSSFRow row, boolean addLow)
307 {
308 rows.put(row, row);
309 if (addLow)
310 {
311 sheet.addRow(row.getRowRecord());
312 }
313 if (row.getRowNum() > getLastRowNum())
314 {
315 lastrow = row.getRowNum();
316 }
317 if (row.getRowNum() < getFirstRowNum())
318 {
319 firstrow = row.getRowNum();
320 }
321 }
322
323 /**
324 * Returns the logical row (not physical) 0-based. If you ask for a row that is not
325 * defined you get a null. This is to say row 4 represents the fifth row on a sheet.
326 * @param rownum row to get
327 * @return HSSFRow representing the rownumber or null if its not defined on the sheet
328 */
329
330 public HSSFRow getRow(int rownum)
331 {
332 HSSFRow row = new HSSFRow();
333
334 //row.setRowNum((short) rownum);
335 row.setRowNum( rownum);
336 return (HSSFRow) rows.get(row);
337 }
338
339 /**
340 * Returns the number of phsyically defined rows (NOT the number of rows in the sheet)
341 */
342
343 public int getPhysicalNumberOfRows()
344 {
345 return rows.size();
346 }
347
348 /**
349 * gets the first row on the sheet
350 * @return the number of the first logical row on the sheet
351 */
352
353 public int getFirstRowNum()
354 {
355 return firstrow;
356 }
357
358 /**
359 * gets the last row on the sheet
360 * @return last row contained n this sheet.
361 */
362
363 public int getLastRowNum()
364 {
365 return lastrow;
366 }
367
368 /**
369 * Creates a data validation object
370 * @param obj_validation The Data validation object settings
371 */
372 public void addValidationData(HSSFDataValidation obj_validation)
373 {
374 if ( obj_validation == null )
375 {
376 return;
377 }
378 DVALRecord dvalRec = (DVALRecord)sheet.findFirstRecordBySid( DVALRecord.sid );
379 int eofLoc = sheet.findFirstRecordLocBySid( EOFRecord.sid );
380 if ( dvalRec == null )
381 {
382 dvalRec = new DVALRecord();
383 sheet.getRecords().add( eofLoc, dvalRec );
384 }
385 int curr_dvRecNo = dvalRec.getDVRecNo();
386 dvalRec.setDVRecNo(curr_dvRecNo+1);
387
388 //create dv record
389 DVRecord dvRecord = new DVRecord();
390
391 //dv record's option flags
392 dvRecord.setDataType( obj_validation.getDataValidationType() );
393 dvRecord.setErrorStyle(obj_validation.getErrorStyle());
394 dvRecord.setEmptyCellAllowed(obj_validation.getEmptyCellAllowed());
395 dvRecord.setSurppresDropdownArrow(obj_validation.getSurppressDropDownArrow());
396 dvRecord.setShowPromptOnCellSelected(obj_validation.getShowPromptBox());
397 dvRecord.setShowErrorOnInvalidValue(obj_validation.getShowErrorBox());
398 dvRecord.setConditionOperator(obj_validation.getOperator());
399
400 //string fields
401 dvRecord.setStringField( DVRecord.STRING_PROMPT_TITLE,obj_validation.getPromptBoxTitle());
402 dvRecord.setStringField( DVRecord.STRING_PROMPT_TEXT, obj_validation.getPromptBoxText());
403 dvRecord.setStringField( DVRecord.STRING_ERROR_TITLE, obj_validation.getErrorBoxTitle());
404 dvRecord.setStringField( DVRecord.STRING_ERROR_TEXT, obj_validation.getErrorBoxText());
405
406 //formula fields ( size and data )
407 String str_formula = obj_validation.getFirstFormula();
408 FormulaParser fp = new FormulaParser(str_formula, workbook);
409 fp.parse();
410 Stack ptg_arr = new Stack();
411 Ptg[] ptg = fp.getRPNPtg();
412 int size = 0;
413 for (int k = 0; k < ptg.length; k++)
414 {
415 if ( ptg[k] instanceof org.apache.poi.hssf.record.formula.AreaPtg )
416 {
417 //we should set ptgClass to Ptg.CLASS_REF and explicit formula string to false
418 ptg[k].setClass(Ptg.CLASS_REF);
419 obj_validation.setExplicitListFormula(false);
420 }
421 size += ptg[k].getSize();
422 ptg_arr.push(ptg[k]);
423 }
424 dvRecord.setFirstFormulaRPN(ptg_arr);
425 dvRecord.setFirstFormulaSize((short)size);
426
427 dvRecord.setListExplicitFormula(obj_validation.getExplicitListFormula());
428
429 if ( obj_validation.getSecondFormula() != null )
430 {
431 str_formula = obj_validation.getSecondFormula();
432 fp = new FormulaParser(str_formula, workbook);
433 fp.parse();
434 ptg_arr = new Stack();
435 ptg = fp.getRPNPtg();
436 size = 0;
437 for (int k = 0; k < ptg.length; k++)
438 {
439 size += ptg[k].getSize();
440 ptg_arr.push(ptg[k]);
441 }
442 dvRecord.setSecFormulaRPN(ptg_arr);
443 dvRecord.setSecFormulaSize((short)size);
444 }
445
446 //dv records cell range field
447 HSSFCellRangeAddress cell_range = new HSSFCellRangeAddress();
448 cell_range.addADDRStructure(obj_validation.getFirstRow(), obj_validation.getFirstColumn(), obj_validation.getLastRow(), obj_validation.getLastColumn());
449 dvRecord.setCellRangeAddress(cell_range);
450
451 //add dv record
452 eofLoc = sheet.findFirstRecordLocBySid( EOFRecord.sid );
453 sheet.getRecords().add( eofLoc, dvRecord );
454 }
455
456 /**
457 * Get the visibility state for a given column.
458 * @param column - the column to get (0-based)
459 * @param hidden - the visiblity state of the column
460 */
461
462 public void setColumnHidden(short column, boolean hidden)
463 {
464 sheet.setColumnHidden(column, hidden);
465 }
466
467 /**
468 * Get the hidden state for a given column.
469 * @param column - the column to set (0-based)
470 * @return hidden - the visiblity state of the column
471 */
472
473 public boolean isColumnHidden(short column)
474 {
475 return sheet.isColumnHidden(column);
476 }
477
478 /**
479 * set the width (in units of 1/256th of a character width)
480 * @param column - the column to set (0-based)
481 * @param width - the width in units of 1/256th of a character width
482 */
483
484 public void setColumnWidth(short column, short width)
485 {
486 sheet.setColumnWidth(column, width);
487 }
488
489 /**
490 * get the width (in units of 1/256th of a character width )
491 * @param column - the column to set (0-based)
492 * @return width - the width in units of 1/256th of a character width
493 */
494
495 public short getColumnWidth(short column)
496 {
497 return sheet.getColumnWidth(column);
498 }
499
500 /**
501 * get the default column width for the sheet (if the columns do not define their own width) in
502 * characters
503 * @return default column width
504 */
505
506 public short getDefaultColumnWidth()
507 {
508 return sheet.getDefaultColumnWidth();
509 }
510
511 /**
512 * get the default row height for the sheet (if the rows do not define their own height) in
513 * twips (1/20 of a point)
514 * @return default row height
515 */
516
517 public short getDefaultRowHeight()
518 {
519 return sheet.getDefaultRowHeight();
520 }
521
522 /**
523 * get the default row height for the sheet (if the rows do not define their own height) in
524 * points.
525 * @return default row height in points
526 */
527
528 public float getDefaultRowHeightInPoints()
529 {
530 return (sheet.getDefaultRowHeight() / 20);
531 }
532
533 /**
534 * set the default column width for the sheet (if the columns do not define their own width) in
535 * characters
536 * @param width default column width
537 */
538
539 public void setDefaultColumnWidth(short width)
540 {
541 sheet.setDefaultColumnWidth(width);
542 }
543
544 /**
545 * set the default row height for the sheet (if the rows do not define their own height) in
546 * twips (1/20 of a point)
547 * @param height default row height
548 */
549
550 public void setDefaultRowHeight(short height)
551 {
552 sheet.setDefaultRowHeight(height);
553 }
554
555 /**
556 * set the default row height for the sheet (if the rows do not define their own height) in
557 * points
558 * @param height default row height
559 */
560
561 public void setDefaultRowHeightInPoints(float height)
562 {
563 sheet.setDefaultRowHeight((short) (height * 20));
564 }
565
566 /**
567 * get whether gridlines are printed.
568 * @return true if printed
569 */
570
571 public boolean isGridsPrinted()
572 {
573 return sheet.isGridsPrinted();
574 }
575
576 /**
577 * set whether gridlines printed.
578 * @param value false if not printed.
579 */
580
581 public void setGridsPrinted(boolean value)
582 {
583 sheet.setGridsPrinted(value);
584 }
585
586 /**
587 * adds a merged region of cells (hence those cells form one)
588 * @param region (rowfrom/colfrom-rowto/colto) to merge
589 * @return index of this region
590 */
591
592 public int addMergedRegion(Region region)
593 {
594 //return sheet.addMergedRegion((short) region.getRowFrom(),
595 return sheet.addMergedRegion( region.getRowFrom(),
596 region.getColumnFrom(),
597 //(short) region.getRowTo(),
598 region.getRowTo(),
599 region.getColumnTo());
600 }
601
602 /**
603 * Whether a record must be inserted or not at generation to indicate that
604 * formula must be recalculated when workbook is opened.
605 * @param value true if an uncalced record must be inserted or not at generation
606 */
607 public void setForceFormulaRecalculation(boolean value)
608 {
609 sheet.setUncalced(value);
610 }
611 /**
612 * Whether a record must be inserted or not at generation to indicate that
613 * formula must be recalculated when workbook is opened.
614 * @return true if an uncalced record must be inserted or not at generation
615 */
616 public boolean getForceFormulaRecalculation()
617 {
618 return sheet.getUncalced();
619 }
620
621
622 /**
623 * determines whether the output is vertically centered on the page.
624 * @param value true to vertically center, false otherwise.
625 */
626
627 public void setVerticallyCenter(boolean value)
628 {
629 VCenterRecord record =
630 (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
631
632 record.setVCenter(value);
633 }
634
635 /**
636 * TODO: Boolean not needed, remove after next release
637 * @deprecated use getVerticallyCenter() instead
638 */
639 public boolean getVerticallyCenter(boolean value) {
640 return getVerticallyCenter();
641 }
642
643 /**
644 * Determine whether printed output for this sheet will be vertically centered.
645 */
646 public boolean getVerticallyCenter()
647 {
648 VCenterRecord record =
649 (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
650
651 return record.getVCenter();
652 }
653
654 /**
655 * determines whether the output is horizontally centered on the page.
656 * @param value true to horizontally center, false otherwise.
657 */
658
659 public void setHorizontallyCenter(boolean value)
660 {
661 HCenterRecord record =
662 (HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
663
664 record.setHCenter(value);
665 }
666
667 /**
668 * Determine whether printed output for this sheet will be horizontally centered.
669 */
670
671 public boolean getHorizontallyCenter()
672 {
673 HCenterRecord record =
674 (HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
675
676 return record.getHCenter();
677 }
678
679
680
681 /**
682 * removes a merged region of cells (hence letting them free)
683 * @param index of the region to unmerge
684 */
685
686 public void removeMergedRegion(int index)
687 {
688 sheet.removeMergedRegion(index);
689 }
690
691 /**
692 * returns the number of merged regions
693 * @return number of merged regions
694 */
695
696 public int getNumMergedRegions()
697 {
698 return sheet.getNumMergedRegions();
699 }
700
701 /**
702 * gets the region at a particular index
703 * @param index of the region to fetch
704 * @return the merged region (simple eh?)
705 */
706
707 public Region getMergedRegionAt(int index)
708 {
709 return new Region(sheet.getMergedRegionAt(index));
710 }
711
712 /**
713 * @return an iterator of the PHYSICAL rows. Meaning the 3rd element may not
714 * be the third row if say for instance the second row is undefined.
715 * Call getRowNum() on each row if you care which one it is.
716 */
717 public Iterator rowIterator()
718 {
719 return rows.values().iterator();
720 }
721 /**
722 * Alias for {@link #rowIterator()} to allow
723 * foreach loops
724 */
725 public Iterator iterator() {
726 return rowIterator();
727 }
728
729
730 /**
731 * used internally in the API to get the low level Sheet record represented by this
732 * Object.
733 * @return Sheet - low level representation of this HSSFSheet.
734 */
735
736 protected Sheet getSheet()
737 {
738 return sheet;
739 }
740
741 /**
742 * whether alternate expression evaluation is on
743 * @param b alternative expression evaluation or not
744 */
745
746 public void setAlternativeExpression(boolean b)
747 {
748 WSBoolRecord record =
749 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
750
751 record.setAlternateExpression(b);
752 }
753
754 /**
755 * whether alternative formula entry is on
756 * @param b alternative formulas or not
757 */
758
759 public void setAlternativeFormula(boolean b)
760 {
761 WSBoolRecord record =
762 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
763
764 record.setAlternateFormula(b);
765 }
766
767 /**
768 * show automatic page breaks or not
769 * @param b whether to show auto page breaks
770 */
771
772 public void setAutobreaks(boolean b)
773 {
774 WSBoolRecord record =
775 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
776
777 record.setAutobreaks(b);
778 }
779
780 /**
781 * set whether sheet is a dialog sheet or not
782 * @param b isDialog or not
783 */
784
785 public void setDialog(boolean b)
786 {
787 WSBoolRecord record =
788 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
789
790 record.setDialog(b);
791 }
792
793 /**
794 * set whether to display the guts or not
795 *
796 * @param b guts or no guts (or glory)
797 */
798
799 public void setDisplayGuts(boolean b)
800 {
801 WSBoolRecord record =
802 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
803
804 record.setDisplayGuts(b);
805 }
806
807 /**
808 * fit to page option is on
809 * @param b fit or not
810 */
811
812 public void setFitToPage(boolean b)
813 {
814 WSBoolRecord record =
815 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
816
817 record.setFitToPage(b);
818 }
819
820 /**
821 * set if row summaries appear below detail in the outline
822 * @param b below or not
823 */
824
825 public void setRowSumsBelow(boolean b)
826 {
827 WSBoolRecord record =
828 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
829
830 record.setRowSumsBelow(b);
831 }
832
833 /**
834 * set if col summaries appear right of the detail in the outline
835 * @param b right or not
836 */
837
838 public void setRowSumsRight(boolean b)
839 {
840 WSBoolRecord record =
841 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
842
843 record.setRowSumsRight(b);
844 }
845
846 /**
847 * whether alternate expression evaluation is on
848 * @return alternative expression evaluation or not
849 */
850
851 public boolean getAlternateExpression()
852 {
853 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
854 .getAlternateExpression();
855 }
856
857 /**
858 * whether alternative formula entry is on
859 * @return alternative formulas or not
860 */
861
862 public boolean getAlternateFormula()
863 {
864 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
865 .getAlternateFormula();
866 }
867
868 /**
869 * show automatic page breaks or not
870 * @return whether to show auto page breaks
871 */
872
873 public boolean getAutobreaks()
874 {
875 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
876 .getAutobreaks();
877 }
878
879 /**
880 * get whether sheet is a dialog sheet or not
881 * @return isDialog or not
882 */
883
884 public boolean getDialog()
885 {
886 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
887 .getDialog();
888 }
889
890 /**
891 * get whether to display the guts or not
892 *
893 * @return guts or no guts (or glory)
894 */
895
896 public boolean getDisplayGuts()
897 {
898 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
899 .getDisplayGuts();
900 }
901
902 /**
903 * fit to page option is on
904 * @return fit or not
905 */
906
907 public boolean getFitToPage()
908 {
909 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
910 .getFitToPage();
911 }
912
913 /**
914 * get if row summaries appear below detail in the outline
915 * @return below or not
916 */
917
918 public boolean getRowSumsBelow()
919 {
920 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
921 .getRowSumsBelow();
922 }
923
924 /**
925 * get if col summaries appear right of the detail in the outline
926 * @return right or not
927 */
928
929 public boolean getRowSumsRight()
930 {
931 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
932 .getRowSumsRight();
933 }
934
935 /**
936 * Returns whether gridlines are printed.
937 * @return Gridlines are printed
938 */
939 public boolean isPrintGridlines() {
940 return getSheet().getPrintGridlines().getPrintGridlines();
941 }
942
943 /**
944 * Turns on or off the printing of gridlines.
945 * @param newPrintGridlines boolean to turn on or off the printing of
946 * gridlines
947 */
948 public void setPrintGridlines( boolean newPrintGridlines )
949 {
950 getSheet().getPrintGridlines().setPrintGridlines( newPrintGridlines );
951 }
952
953 /**
954 * Gets the print setup object.
955 * @return The user model for the print setup object.
956 */
957 public HSSFPrintSetup getPrintSetup()
958 {
959 return new HSSFPrintSetup( getSheet().getPrintSetup() );
960 }
961
962 /**
963 * Gets the user model for the document header.
964 * @return The Document header.
965 */
966 public HSSFHeader getHeader()
967 {
968 return new HSSFHeader( getSheet().getHeader() );
969 }
970
971 /**
972 * Gets the user model for the document footer.
973 * @return The Document footer.
974 */
975 public HSSFFooter getFooter()
976 {
977 return new HSSFFooter( getSheet().getFooter() );
978 }
979
980 /**
981 * Note - this is not the same as whether the sheet is focused (isActive)
982 * @return <code>true</code> if this sheet is currently selected
983 */
984 public boolean isSelected() {
985 return getSheet().getWindowTwo().getSelected();
986 }
987 /**
988 * Sets whether sheet is selected.
989 * @param sel Whether to select the sheet or deselect the sheet.
990 */
991 public void setSelected( boolean sel )
992 {
993 getSheet().getWindowTwo().setSelected(sel);
994 }
995 /**
996 * @return <code>true</code> if this sheet is currently focused
997 */
998 public boolean isActive() {
999 return getSheet().getWindowTwo().isActive();
1000 }
1001 /**
1002 * Sets whether sheet is selected.
1003 * @param sel Whether to select the sheet or deselect the sheet.
1004 */
1005 public void setActive(boolean sel )
1006 {
1007 getSheet().getWindowTwo().setActive(sel);
1008 }
1009
1010 /**
1011 * Gets the size of the margin in inches.
1012 * @param margin which margin to get
1013 * @return the size of the margin
1014 */
1015 public double getMargin( short margin )
1016 {
1017 return getSheet().getMargin( margin );
1018 }
1019
1020 /**
1021 * Sets the size of the margin in inches.
1022 * @param margin which margin to get
1023 * @param size the size of the margin
1024 */
1025 public void setMargin( short margin, double size )
1026 {
1027 getSheet().setMargin( margin, size );
1028 }
1029
1030 /**
1031 * Answer whether protection is enabled or disabled
1032 * @return true => protection enabled; false => protection disabled
1033 */
1034 public boolean getProtect() {
1035 return getSheet().isProtected()[0];
1036 }
1037
1038 /**
1039 * @return hashed password
1040 */
1041 public short getPassword() {
1042 return getSheet().getPassword().getPassword();
1043 }
1044
1045 /**
1046 * Answer whether object protection is enabled or disabled
1047 * @return true => protection enabled; false => protection disabled
1048 */
1049 public boolean getObjectProtect() {
1050 return getSheet().isProtected()[1];
1051 }
1052
1053 /**
1054 * Answer whether scenario protection is enabled or disabled
1055 * @return true => protection enabled; false => protection disabled
1056 */
1057 public boolean getScenarioProtect() {
1058 return getSheet().isProtected()[2];
1059 }
1060
1061 /**
1062 * Sets the protection on enabled or disabled
1063 * @param protect true => protection enabled; false => protection disabled
1064 * @deprecated use protectSheet(String, boolean, boolean)
1065 */
1066 public void setProtect(boolean protect) {
1067 getSheet().getProtect().setProtect(protect);
1068 }
1069
1070 /**
1071 * Sets the protection enabled as well as the password
1072 * @param password to set for protection
1073 */
1074 public void protectSheet(String password) {
1075 getSheet().protectSheet(password, true, true); //protect objs&scenarios(normal)
1076 }
1077
1078 /**
1079 * Sets the zoom magnication for the sheet. The zoom is expressed as a
1080 * fraction. For example to express a zoom of 75% use 3 for the numerator
1081 * and 4 for the denominator.
1082 *
1083 * @param numerator The numerator for the zoom magnification.
1084 * @param denominator The denominator for the zoom magnification.
1085 */
1086 public void setZoom( int numerator, int denominator)
1087 {
1088 if (numerator < 1 || numerator > 65535)
1089 throw new IllegalArgumentException("Numerator must be greater than 1 and less than 65536");
1090 if (denominator < 1 || denominator > 65535)
1091 throw new IllegalArgumentException("Denominator must be greater than 1 and less than 65536");
1092
1093 SCLRecord sclRecord = new SCLRecord();
1094 sclRecord.setNumerator((short)numerator);
1095 sclRecord.setDenominator((short)denominator);
1096 getSheet().setSCLRecord(sclRecord);
1097 }
1098
1099 /**
1100 * The top row in the visible view when the sheet is
1101 * first viewed after opening it in a viewer
1102 * @return short indicating the rownum (0 based) of the top row
1103 */
1104 public short getTopRow()
1105 {
1106 return sheet.getTopRow();
1107 }
1108
1109 /**
1110 * The left col in the visible view when the sheet is
1111 * first viewed after opening it in a viewer
1112 * @return short indicating the rownum (0 based) of the top row
1113 */
1114 public short getLeftCol()
1115 {
1116 return sheet.getLeftCol();
1117 }
1118
1119 /**
1120 * Sets desktop window pane display area, when the
1121 * file is first opened in a viewer.
1122 * @param toprow the top row to show in desktop window pane
1123 * @param leftcol the left column to show in desktop window pane
1124 */
1125 public void showInPane(short toprow, short leftcol){
1126 this.sheet.setTopRow(toprow);
1127 this.sheet.setLeftCol(leftcol);
1128 }
1129
1130 /**
1131 * Shifts the merged regions left or right depending on mode
1132 * <p>
1133 * TODO: MODE , this is only row specific
1134 * @param startRow
1135 * @param endRow
1136 * @param n
1137 * @param isRow
1138 */
1139 protected void shiftMerged(int startRow, int endRow, int n, boolean isRow) {
1140 List shiftedRegions = new ArrayList();
1141 //move merged regions completely if they fall within the new region boundaries when they are shifted
1142 for (int i = 0; i < this.getNumMergedRegions(); i++) {
1143 Region merged = this.getMergedRegionAt(i);
1144
1145 boolean inStart = (merged.getRowFrom() >= startRow || merged.getRowTo() >= startRow);
1146 boolean inEnd = (merged.getRowTo() <= endRow || merged.getRowFrom() <= endRow);
1147
1148 //dont check if it's not within the shifted area
1149 if (! (inStart && inEnd)) continue;
1150
1151 //only shift if the region outside the shifted rows is not merged too
1152 if (!merged.contains(startRow-1, (short)0) && !merged.contains(endRow+1, (short)0)){
1153 merged.setRowFrom(merged.getRowFrom()+n);
1154 merged.setRowTo(merged.getRowTo()+n);
1155 //have to remove/add it back
1156 shiftedRegions.add(merged);
1157 this.removeMergedRegion(i);
1158 i = i -1; // we have to back up now since we removed one
1159
1160 }
1161
1162 }
1163
1164 //readd so it doesn't get shifted again
1165 Iterator iterator = shiftedRegions.iterator();
1166 while (iterator.hasNext()) {
1167 Region region = (Region)iterator.next();
1168
1169 this.addMergedRegion(region);
1170 }
1171
1172 }
1173
1174 /**
1175 * Shifts rows between startRow and endRow n number of rows.
1176 * If you use a negative number, it will shift rows up.
1177 * Code ensures that rows don't wrap around.
1178 *
1179 * Calls shiftRows(startRow, endRow, n, false, false);
1180 *
1181 * <p>
1182 * Additionally shifts merged regions that are completely defined in these
1183 * rows (ie. merged 2 cells on a row to be shifted).
1184 * @param startRow the row to start shifting
1185 * @param endRow the row to end shifting
1186 * @param n the number of rows to shift
1187 */
1188 public void shiftRows( int startRow, int endRow, int n ) {
1189 shiftRows(startRow, endRow, n, false, false);
1190 }
1191
1192 /**
1193 * Shifts rows between startRow and endRow n number of rows.
1194 * If you use a negative number, it will shift rows up.
1195 * Code ensures that rows don't wrap around
1196 *
1197 * <p>
1198 * Additionally shifts merged regions that are completely defined in these
1199 * rows (ie. merged 2 cells on a row to be shifted).
1200 * <p>
1201 * TODO Might want to add bounds checking here
1202 * @param startRow the row to start shifting
1203 * @param endRow the row to end shifting
1204 * @param n the number of rows to shift
1205 * @param copyRowHeight whether to copy the row height during the shift
1206 * @param resetOriginalRowHeight whether to set the original row's height to the default
1207 */
1208 public void shiftRows( int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight)
1209 {
1210 int s, e, inc;
1211 if ( n < 0 )
1212 {
1213 s = startRow;
1214 e = endRow;
1215 inc = 1;
1216 }
1217 else
1218 {
1219 s = endRow;
1220 e = startRow;
1221 inc = -1;
1222 }
1223
1224 shiftMerged(startRow, endRow, n, true);
1225 sheet.shiftRowBreaks(startRow, endRow, n);
1226
1227 for ( int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum += inc )
1228 {
1229 HSSFRow row = getRow( rowNum );
1230 HSSFRow row2Replace = getRow( rowNum + n );
1231 if ( row2Replace == null )
1232 row2Replace = createRow( rowNum + n );
1233
1234 HSSFCell cell;
1235
1236
1237
1238
1239 // Removes the cells before over writting them.
1240 for ( short col = row2Replace.getFirstCellNum(); col <= row2Replace.getLastCellNum(); col++ )
1241 {
1242 cell = row2Replace.getCell( col );
1243 if ( cell != null )
1244 row2Replace.removeCell( cell );
1245 }
1246 if (row == null) continue; // Nothing to do for this row
1247 else {
1248 if (copyRowHeight) {
1249 row2Replace.setHeight(row.getHeight());
1250 }
1251
1252 if (resetOriginalRowHeight) {
1253 row.setHeight((short)0xff);
1254 }
1255 }
1256 for ( short col = row.getFirstCellNum(); col <= row.getLastCellNum(); col++ )
1257 {
1258 cell = row.getCell( col );
1259 if ( cell != null )
1260 {
1261 row.removeCell( cell );
1262 CellValueRecordInterface cellRecord = cell.getCellValueRecord();
1263 cellRecord.setRow( rowNum + n );
1264 row2Replace.createCellFromRecord( cellRecord );
1265 sheet.addValueRecord( rowNum + n, cellRecord );
1266 }
1267
1268 // move comments if exist (can exist even if cell is null)
1269 HSSFComment comment = getCellComment(rowNum, col);
1270 if (comment != null) {
1271 comment.setRow(rowNum + n);
1272 }
1273 }
1274 }
1275 if ( endRow == lastrow || endRow + n > lastrow ) lastrow = Math.min( endRow + n, 65535 );
1276 if ( startRow == firstrow || startRow + n < firstrow ) firstrow = Math.max( startRow + n, 0 );
1277
1278 // Update any formulas on this sheet that point to
1279 // rows which have been moved
1280 updateFormulasAfterShift(startRow, endRow, n);
1281 }
1282
1283 /**
1284 * Called by shiftRows to update formulas on this sheet
1285 * to point to the new location of moved rows
1286 */
1287 private void updateFormulasAfterShift(int startRow, int endRow, int n) {
1288 // Need to look at every cell on the sheet
1289 // Not just those that were moved
1290 Iterator ri = rowIterator();
1291 while(ri.hasNext()) {
1292 HSSFRow r = (HSSFRow)ri.next();
1293 Iterator ci = r.cellIterator();
1294 while(ci.hasNext()) {
1295 HSSFCell c = (HSSFCell)ci.next();
1296 if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
1297 // Since it's a formula cell, process the
1298 // formula string, and look to see if
1299 // it contains any references
1300 FormulaParser fp = new FormulaParser(c.getCellFormula(), workbook);
1301 fp.parse();
1302
1303 // Look for references, and update if needed
1304 Ptg[] ptgs = fp.getRPNPtg();
1305 boolean changed = false;
1306 for(int i=0; i<ptgs.length; i++) {
1307 if(ptgs[i] instanceof RefPtg) {
1308 RefPtg rptg = (RefPtg)ptgs[i];
1309 if(startRow <= rptg.getRowAsInt() &&
1310 rptg.getRowAsInt() <= endRow) {
1311 // References a row that moved
1312 rptg.setRow(rptg.getRowAsInt() + n);
1313 changed = true;
1314 }
1315 }
1316 }
1317 // If any references were changed, then
1318 // re-create the formula string
1319 if(changed) {
1320 c.setCellFormula(
1321 fp.toFormulaString(ptgs)
1322 );
1323 }
1324 }
1325 }
1326 }
1327 }
1328
1329 protected void insertChartRecords( List records )
1330 {
1331 int window2Loc = sheet.findFirstRecordLocBySid( WindowTwoRecord.sid );
1332 sheet.getRecords().addAll( window2Loc, records );
1333 }
1334
1335 /**
1336 * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
1337 * @param colSplit Horizonatal position of split.
1338 * @param rowSplit Vertical position of split.
1339 * @param topRow Top row visible in bottom pane
1340 * @param leftmostColumn Left column visible in right pane.
1341 */
1342 public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow )
1343 {
1344 if (colSplit < 0 || colSplit > 255) throw new IllegalArgumentException("Column must be between 0 and 255");
1345 if (rowSplit < 0 || rowSplit > 65535) throw new IllegalArgumentException("Row must be between 0 and 65535");
1346 if (leftmostColumn < colSplit) throw new IllegalArgumentException("leftmostColumn parameter must not be less than colSplit parameter");
1347 if (topRow < rowSplit) throw new IllegalArgumentException("topRow parameter must not be less than leftmostColumn parameter");
1348 getSheet().createFreezePane( colSplit, rowSplit, topRow, leftmostColumn );
1349 }
1350
1351 /**
1352 * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
1353 * @param colSplit Horizonatal position of split.
1354 * @param rowSplit Vertical position of split.
1355 */
1356 public void createFreezePane( int colSplit, int rowSplit )
1357 {
1358 createFreezePane( colSplit, rowSplit, colSplit, rowSplit );
1359 }
1360
1361 /**
1362 * Creates a split pane. Any existing freezepane or split pane is overwritten.
1363 * @param xSplitPos Horizonatal position of split (in 1/20th of a point).
1364 * @param ySplitPos Vertical position of split (in 1/20th of a point).
1365 * @param topRow Top row visible in bottom pane
1366 * @param leftmostColumn Left column visible in right pane.
1367 * @param activePane Active pane. One of: PANE_LOWER_RIGHT,
1368 * PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT
1369 * @see #PANE_LOWER_LEFT
1370 * @see #PANE_LOWER_RIGHT
1371 * @see #PANE_UPPER_LEFT
1372 * @see #PANE_UPPER_RIGHT
1373 */
1374 public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane )
1375 {
1376 getSheet().createSplitPane( xSplitPos, ySplitPos, topRow, leftmostColumn, activePane );
1377 }
1378
1379 /**
1380 * Returns the information regarding the currently configured pane (split or freeze).
1381 * @return null if no pane configured, or the pane information.
1382 */
1383 public PaneInformation getPaneInformation() {
1384 return getSheet().getPaneInformation();
1385 }
1386
1387 /**
1388 * Sets whether the gridlines are shown in a viewer.
1389 * @param show whether to show gridlines or not
1390 */
1391 public void setDisplayGridlines(boolean show) {
1392 sheet.setDisplayGridlines(show);
1393 }
1394
1395 /**
1396 * Returns if gridlines are displayed.
1397 * @return whether gridlines are displayed
1398 */
1399 public boolean isDisplayGridlines() {
1400 return sheet.isDisplayGridlines();
1401 }
1402
1403 /**
1404 * Sets whether the formulas are shown in a viewer.
1405 * @param show whether to show formulas or not
1406 */
1407 public void setDisplayFormulas(boolean show) {
1408 sheet.setDisplayFormulas(show);
1409 }
1410
1411 /**
1412 * Returns if formulas are displayed.
1413 * @return whether formulas are displayed
1414 */
1415 public boolean isDisplayFormulas() {
1416 return sheet.isDisplayFormulas();
1417 }
1418
1419 /**
1420 * Sets whether the RowColHeadings are shown in a viewer.
1421 * @param show whether to show RowColHeadings or not
1422 */
1423 public void setDisplayRowColHeadings(boolean show) {
1424 sheet.setDisplayRowColHeadings(show);
1425 }
1426
1427 /**
1428 * Returns if RowColHeadings are displayed.
1429 * @return whether RowColHeadings are displayed
1430 */
1431 public boolean isDisplayRowColHeadings() {
1432 return sheet.isDisplayRowColHeadings();
1433 }
1434
1435 /**
1436 * Sets a page break at the indicated row
1437 * @param row FIXME: Document this!
1438 */
1439 public void setRowBreak(int row) {
1440 validateRow(row);
1441 sheet.setRowBreak(row, (short)0, (short)255);
1442 }
1443
1444 /**
1445 * Determines if there is a page break at the indicated row
1446 * @param row FIXME: Document this!
1447 * @return FIXME: Document this!
1448 */
1449 public boolean isRowBroken(int row) {
1450 return sheet.isRowBroken(row);
1451 }
1452
1453 /**
1454 * Removes the page break at the indicated row
1455 * @param row
1456 */
1457 public void removeRowBreak(int row) {
1458 sheet.removeRowBreak(row);
1459 }
1460
1461 /**
1462 * Retrieves all the horizontal page breaks
1463 * @return all the horizontal page breaks, or null if there are no row page breaks
1464 */
1465 public int[] getRowBreaks(){
1466 //we can probably cache this information, but this should be a sparsely used function
1467 int count = sheet.getNumRowBreaks();
1468 if (count > 0) {
1469 int[] returnValue = new int[count];
1470 Iterator iterator = sheet.getRowBreaks();
1471 int i = 0;
1472 while (iterator.hasNext()) {
1473 PageBreakRecord.Break breakItem = (PageBreakRecord.Break)iterator.next();
1474 returnValue[i++] = breakItem.main;
1475 }
1476 return returnValue;
1477 }
1478 return null;
1479 }
1480
1481 /**
1482 * Retrieves all the vertical page breaks
1483 * @return all the vertical page breaks, or null if there are no column page breaks
1484 */
1485 public short[] getColumnBreaks(){
1486 //we can probably cache this information, but this should be a sparsely used function
1487 int count = sheet.getNumColumnBreaks();
1488 if (count > 0) {
1489 short[] returnValue = new short[count];
1490 Iterator iterator = sheet.getColumnBreaks();
1491 int i = 0;
1492 while (iterator.hasNext()) {
1493 PageBreakRecord.Break breakItem = (PageBreakRecord.Break)iterator.next();
1494 returnValue[i++] = breakItem.main;
1495 }
1496 return returnValue;
1497 }
1498 return null;
1499 }
1500
1501
1502 /**
1503 * Sets a page break at the indicated column
1504 * @param column
1505 */
1506 public void setColumnBreak(short column) {
1507 validateColumn(column);
1508 sheet.setColumnBreak(column, (short)0, (short)65535);
1509 }
1510
1511 /**
1512 * Determines if there is a page break at the indicated column
1513 * @param column FIXME: Document this!
1514 * @return FIXME: Document this!
1515 */
1516 public boolean isColumnBroken(short column) {
1517 return sheet.isColumnBroken(column);
1518 }
1519
1520 /**
1521 * Removes a page break at the indicated column
1522 * @param column
1523 */
1524 public void removeColumnBreak(short column) {
1525 sheet.removeColumnBreak(column);
1526 }
1527
1528 /**
1529 * Runs a bounds check for row numbers
1530 * @param row
1531 */
1532 protected void validateRow(int row) {
1533 if (row > 65535) throw new IllegalArgumentException("Maximum row number is 65535");
1534 if (row < 0) throw new IllegalArgumentException("Minumum row number is 0");
1535 }
1536
1537 /**
1538 * Runs a bounds check for column numbers
1539 * @param column
1540 */
1541 protected void validateColumn(short column) {
1542 if (column > 255) throw new IllegalArgumentException("Maximum column number is 255");
1543 if (column < 0) throw new IllegalArgumentException("Minimum column number is 0");
1544 }
1545
1546 /**
1547 * Aggregates the drawing records and dumps the escher record hierarchy
1548 * to the standard output.
1549 */
1550 public void dumpDrawingRecords(boolean fat)
1551 {
1552 sheet.aggregateDrawingRecords(book.getDrawingManager(), false);
1553
1554 EscherAggregate r = (EscherAggregate) getSheet().findFirstRecordBySid(EscherAggregate.sid);
1555 List escherRecords = r.getEscherRecords();
1556 PrintWriter w = new PrintWriter(System.out);
1557 for ( Iterator iterator = escherRecords.iterator(); iterator.hasNext(); )
1558 {
1559 EscherRecord escherRecord = (EscherRecord) iterator.next();
1560 if (fat)
1561 System.out.println(escherRecord.toString());
1562 else
1563 escherRecord.display(w, 0);
1564 }
1565 w.flush();
1566 }
1567
1568 /**
1569 * Creates the top-level drawing patriarch. This will have
1570 * the effect of removing any existing drawings on this
1571 * sheet.
1572 * This may then be used to add graphics or charts
1573 * @return The new patriarch.
1574 */
1575 public HSSFPatriarch createDrawingPatriarch()
1576 {
1577 // Create the drawing group if it doesn't already exist.
1578 book.createDrawingGroup();
1579
1580 sheet.aggregateDrawingRecords(book.getDrawingManager(), true);
1581 EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
1582 HSSFPatriarch patriarch = new HSSFPatriarch(this, agg);
1583 agg.clear(); // Initially the behaviour will be to clear out any existing shapes in the sheet when
1584 // creating a new patriarch.
1585 agg.setPatriarch(patriarch);
1586 return patriarch;
1587 }
1588
1589 /**
1590 * Returns the agregate escher records for this sheet,
1591 * it there is one.
1592 * WARNING - calling this will trigger a parsing of the
1593 * associated escher records. Any that aren't supported
1594 * (such as charts and complex drawing types) will almost
1595 * certainly be lost or corrupted when written out.
1596 */
1597 public EscherAggregate getDrawingEscherAggregate() {
1598 book.findDrawingGroup();
1599
1600 // If there's now no drawing manager, then there's
1601 // no drawing escher records on the workbook
1602 if(book.getDrawingManager() == null) {
1603 return null;
1604 }
1605
1606 int found = sheet.aggregateDrawingRecords(
1607 book.getDrawingManager(), false
1608 );
1609 if(found == -1) {
1610 // Workbook has drawing stuff, but this sheet doesn't
1611 return null;
1612 }
1613
1614 // Grab our aggregate record, and wire it up
1615 EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
1616 return agg;
1617 }
1618
1619 /**
1620 * Returns the top-level drawing patriach, if there is
1621 * one.
1622 * This will hold any graphics or charts for the sheet.
1623 * WARNING - calling this will trigger a parsing of the
1624 * associated escher records. Any that aren't supported
1625 * (such as charts and complex drawing types) will almost
1626 * certainly be lost or corrupted when written out. Only
1627 * use this with simple drawings, otherwise call
1628 * {@link HSSFSheet#createDrawingPatriarch()} and
1629 * start from scratch!
1630 */
1631 public HSSFPatriarch getDrawingPatriarch() {
1632 EscherAggregate agg = getDrawingEscherAggregate();
1633 if(agg == null) return null;
1634
1635 HSSFPatriarch patriarch = new HSSFPatriarch(this, agg);
1636 agg.setPatriarch(patriarch);
1637
1638 // Have it process the records into high level objects
1639 // as best it can do (this step may eat anything
1640 // that isn't supported, you were warned...)
1641 agg.convertRecordsToUserModel();
1642
1643 // Return what we could cope with
1644 return patriarch;
1645 }
1646
1647 /**
1648 * Expands or collapses a column group.
1649 *
1650 * @param columnNumber One of the columns in the group.
1651 * @param collapsed true = collapse group, false = expand group.
1652 */
1653 public void setColumnGroupCollapsed( short columnNumber, boolean collapsed )
1654 {
1655 sheet.setColumnGroupCollapsed( columnNumber, collapsed );
1656 }
1657
1658 /**
1659 * Create an outline for the provided column range.
1660 *
1661 * @param fromColumn beginning of the column range.
1662 * @param toColumn end of the column range.
1663 */
1664 public void groupColumn(short fromColumn, short toColumn)
1665 {
1666 sheet.groupColumnRange( fromColumn, toColumn, true );
1667 }
1668
1669 public void ungroupColumn( short fromColumn, short toColumn )
1670 {
1671 sheet.groupColumnRange( fromColumn, toColumn, false );
1672 }
1673
1674 public void groupRow(int fromRow, int toRow)
1675 {
1676 sheet.groupRowRange( fromRow, toRow, true );
1677 }
1678
1679 public void ungroupRow(int fromRow, int toRow)
1680 {
1681 sheet.groupRowRange( fromRow, toRow, false );
1682 }
1683
1684 public void setRowGroupCollapsed( int row, boolean collapse )
1685 {
1686 sheet.setRowGroupCollapsed( row, collapse );
1687 }
1688
1689 /**
1690 * Sets the default column style for a given column. POI will only apply this style to new cells added to the sheet.
1691 *
1692 * @param column the column index
1693 * @param style the style to set
1694 */
1695 public void setDefaultColumnStyle(short column, HSSFCellStyle style) {
1696 sheet.setColumn(column, new Short(style.getIndex()), null, null, null, null);
1697 }
1698
1699 /**
1700 * Adjusts the column width to fit the contents.
1701 *
1702 * This process can be relatively slow on large sheets, so this should
1703 * normally only be called once per column, at the end of your
1704 * processing.
1705 *
1706 * @param column the column index
1707 */
1708 public void autoSizeColumn(short column) {
1709 autoSizeColumn(column, false);
1710 }
1711
1712 /**
1713 * Adjusts the column width to fit the contents.
1714 *
1715 * This process can be relatively slow on large sheets, so this should
1716 * normally only be called once per column, at the end of your
1717 * processing.
1718 *
1719 * You can specify whether the content of merged cells should be considered or ignored.
1720 * Default is to ignore merged cells.
1721 *
1722 * @param column the column index
1723 * @param useMergedCells whether to use the contents of merged cells when calculating the width of the column
1724 */
1725 public void autoSizeColumn(short column, boolean useMergedCells) {
1726 AttributedString str;
1727 TextLayout layout;
1728 /**
1729 * Excel measures columns in units of 1/256th of a character width
1730 * but the docs say nothing about what particular character is used.
1731 * '0' looks to be a good choice.
1732 */
1733 char defaultChar = '0';
1734
1735 /**
1736 * This is the multiple that the font height is scaled by when determining the
1737 * boundary of rotated text.
1738 */
1739 double fontHeightMultiple = 2.0;
1740
1741 FontRenderContext frc = new FontRenderContext(null, true, true);
1742
1743 HSSFWorkbook wb = new HSSFWorkbook(book);
1744 HSSFFont defaultFont = wb.getFontAt((short) 0);
1745
1746 str = new AttributedString("" + defaultChar);
1747 copyAttributes(defaultFont, str, 0, 1);
1748 layout = new TextLayout(str.getIterator(), frc);
1749 int defaultCharWidth = (int)layout.getAdvance();
1750
1751 double width = -1;
1752 rows:
1753 for (Iterator it = rowIterator(); it.hasNext();) {
1754 HSSFRow row = (HSSFRow) it.next();
1755 HSSFCell cell = row.getCell(column);
1756
1757 if (cell == null) continue;
1758
1759 int colspan = 1;
1760 for (int i = 0 ; i < getNumMergedRegions(); i++) {
1761 if (getMergedRegionAt(i).contains(row.getRowNum(), column)) {
1762 if (!useMergedCells) {
1763 // If we're not using merged cells, skip this one and move on to the next.
1764 continue rows;
1765 }
1766 cell = row.getCell(getMergedRegionAt(i).getColumnFrom());
1767 colspan = 1+ getMergedRegionAt(i).getColumnTo() - getMergedRegionAt(i).getColumnFrom();
1768 }
1769 }
1770
1771 HSSFCellStyle style = cell.getCellStyle();
1772 HSSFFont font = wb.getFontAt(style.getFontIndex());
1773
1774 if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
1775 HSSFRichTextString rt = cell.getRichStringCellValue();
1776 String[] lines = rt.getString().split("\\n");
1777 for (int i = 0; i < lines.length; i++) {
1778 String txt = lines[i] + defaultChar;
1779 str = new AttributedString(txt);
1780 copyAttributes(font, str, 0, txt.length());
1781
1782 if (rt.numFormattingRuns() > 0) {
1783 for (int j = 0; j < lines[i].length(); j++) {
1784 int idx = rt.getFontAtIndex(j);
1785 if (idx != 0) {
1786 HSSFFont fnt = wb.getFontAt((short) idx);
1787 copyAttributes(fnt, str, j, j + 1);
1788 }
1789 }
1790 }
1791
1792 layout = new TextLayout(str.getIterator(), frc);
1793 if(style.getRotation() != 0){
1794 /*
1795 * Transform the text using a scale so that it's height is increased by a multiple of the leading,
1796 * and then rotate the text before computing the bounds. The scale results in some whitespace around
1797 * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
1798 * is added by the standard Excel autosize.
1799 */
1800 AffineTransform trans = new AffineTransform();
1801 trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
1802 trans.concatenate(
1803 AffineTransform.getScaleInstance(1, fontHeightMultiple)
1804 );
1805 width = Math.max(width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1806 } else {
1807 width = Math.max(width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1808 }
1809 }
1810 } else {
1811 String sval = null;
1812 if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
1813 HSSFDataFormat dataformat = wb.createDataFormat();
1814 short idx = style.getDataFormat();
1815 String format = dataformat.getFormat(idx).replaceAll("\"", "");
1816 double value = cell.getNumericCellValue();
1817 try {
1818 NumberFormat fmt;
1819 if ("General".equals(format))
1820 sval = "" + value;
1821 else
1822 {
1823 fmt = new DecimalFormat(format);
1824 sval = fmt.format(value);
1825 }
1826 } catch (Exception e) {
1827 sval = "" + value;
1828 }
1829 } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
1830 sval = String.valueOf(cell.getBooleanCellValue());
1831 }
1832 if(sval != null) {
1833 String txt = sval + defaultChar;
1834 str = new AttributedString(txt);
1835 copyAttributes(font, str, 0, txt.length());
1836
1837 layout = new TextLayout(str.getIterator(), frc);
1838 if(style.getRotation() != 0){
1839 /*
1840 * Transform the text using a scale so that it's height is increased by a multiple of the leading,
1841 * and then rotate the text before computing the bounds. The scale results in some whitespace around
1842 * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
1843 * is added by the standard Excel autosize.
1844 */
1845 AffineTransform trans = new AffineTransform();
1846 trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
1847 trans.concatenate(
1848 AffineTransform.getScaleInstance(1, fontHeightMultiple)
1849 );
1850 width = Math.max(width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1851 } else {
1852 width = Math.max(width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1853 }
1854 }
1855 }
1856
1857 }
1858 if (width != -1) {
1859 if (width > Short.MAX_VALUE) { //width can be bigger that Short.MAX_VALUE!
1860 width = Short.MAX_VALUE;
1861 }
1862 sheet.setColumnWidth(column, (short) (width * 256));
1863 }
1864 }
1865
1866 /**
1867 * Copy text attributes from the supplied HSSFFont to Java2D AttributedString
1868 */
1869 private void copyAttributes(HSSFFont font, AttributedString str, int startIdx, int endIdx) {
1870 str.addAttribute(TextAttribute.FAMILY, font.getFontName(), startIdx, endIdx);
1871 str.addAttribute(TextAttribute.SIZE, new Float(font.getFontHeightInPoints()));
1872 if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) str.addAttribute(TextAttribute.WEIGHT, TextAttribute.WEIGHT_BOLD, startIdx, endIdx);
1873 if (font.getItalic() ) str.addAttribute(TextAttribute.POSTURE, TextAttribute.POSTURE_OBLIQUE, startIdx, endIdx);
1874 if (font.getUnderline() == HSSFFont.U_SINGLE ) str.addAttribute(TextAttribute.UNDERLINE, TextAttribute.UNDERLINE_ON, startIdx, endIdx);
1875 }
1876
1877 /**
1878 * Returns cell comment for the specified row and column
1879 *
1880 * @return cell comment or <code>null</code> if not found
1881 */
1882 public HSSFComment getCellComment(int row, int column) {
1883 // Don't call findCellComment directly, otherwise
1884 // two calls to this method will result in two
1885 // new HSSFComment instances, which is bad
1886 HSSFRow r = getRow(row);
1887 if(r != null) {
1888 HSSFCell c = r.getCell((short)column);
1889 if(c != null) {
1890 return c.getCellComment();
1891 } else {
1892 // No cell, so you will get new
1893 // objects every time, sorry...
1894 return HSSFCell.findCellComment(sheet, row, column);
1895 }
1896 }
1897 return null;
1898 }
1899
1900 public HSSFSheetConditionalFormatting getSheetConditionalFormatting() {
1901 return new HSSFSheetConditionalFormatting(workbook, sheet);
1902 }
1903 }
Save This Page