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