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