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 * HSSFWorkbook.java 57 * 58 * Created on September 30, 2001, 3:37 PM 59 */ 60 package org.apache.poi.hssf.usermodel; 61 62 import org.apache.poi.util.POILogFactory; 63 import org.apache.poi.hssf.model.Sheet; 64 import org.apache.poi.hssf.model.Workbook; 65 import org.apache.poi.hssf.record.*; 66 import org.apache.poi.poifs.filesystem.POIFSFileSystem; 67 import org.apache.poi.util.POILogger; 68 69 import java.io.ByteArrayInputStream; 70 import java.io.IOException; 71 import java.io.InputStream; 72 import java.io.OutputStream; 73 import java.util.ArrayList; 74 import java.util.List; 75 76 /** 77 * High level representation of a workbook. This is the first object most users 78 * will construct whether they are reading or writing a workbook. It is also the 79 * top level object for creating new sheets/etc. 80 * 81 * @see org.apache.poi.hssf.model.Workbook 82 * @see org.apache.poi.hssf.usermodel.HSSFSheet 83 * @author Andrew C. Oliver (acoliver at apache dot org) 84 * @author Glen Stampoultzis (glens at apache.org) 85 * @version 2.0-pre 86 */ 87 88 public class HSSFWorkbook 89 extends java.lang.java.lang.Objectvate static final int DEBUG = POILogger.DEBUG; 90 91 /** 92 * used for compile-time performance/memory optimization. This determines the 93 * initial capacity for the sheet collection. Its currently set to 3. 94 * Changing it in this release will decrease performance 95 * since you're never allowed to have more or less than three sheets! 96 */ 97 98 public final static int INITIAL_CAPACITY = 3; 99 100 /** 101 * this is the reference to the low level Workbook object 102 */ 103 104 private Workbook workbook; 105 106 /** 107 * this holds the HSSFSheet objects attached to this workbook 108 */ 109 110 private ArrayList sheets; 111 112 /** 113 * this holds the HSSFName objects attached to this workbook 114 */ 115 116 private ArrayList names; 117 118 private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class); 119 120 /** 121 * Creates new HSSFWorkbook from scratch (start here!) 122 * 123 */ 124 125 public HSSFWorkbook() 126 { 127 workbook = Workbook.createWorkbook(); 128 sheets = new ArrayList(INITIAL_CAPACITY); 129 names = new ArrayList(INITIAL_CAPACITY); 130 } 131 132 /** 133 * given a POI POIFSFileSystem object, read in its Workbook and populate the high and 134 * low level models. If you're reading in a workbook...start here. 135 * 136 * @param fs the POI filesystem that contains the Workbook stream. 137 * @see org.apache.poi.poifs.filesystem.POIFSFileSystem 138 * @exception IOException if the stream cannot be read 139 */ 140 141 public HSSFWorkbook(POIFSFileSystem fs) 142 throws IOException 143 { 144 sheets = new ArrayList(INITIAL_CAPACITY); 145 names = new ArrayList(INITIAL_CAPACITY); 146 147 InputStream stream = fs.createDocumentInputStream("Workbook"); 148 List records = RecordFactory.createRecords(stream); 149 150 workbook = Workbook.createWorkbook(records); 151 setPropertiesFromWorkbook(workbook); 152 int numRecords = workbook.getNumRecords(); 153 int sheetNum = 0; 154 155 while (numRecords < records.size()) 156 { 157 Sheet sheet = Sheet.createSheet(records, sheetNum++, numRecords); 158 159 numRecords += sheet.getNumRecords(); 160 sheet.convertLabelRecords( 161 workbook); // convert all LabelRecord records to LabelSSTRecord 162 HSSFSheet hsheet = new HSSFSheet(workbook, sheet); 163 164 sheets.add(hsheet); 165 166 // workbook.setSheetName(sheets.size() -1, "Sheet"+sheets.size()); 167 } 168 169 for (int i = 0 ; i < workbook.getNumNames() ; ++i){ 170 HSSFName name = new HSSFName(workbook, workbook.getNameRecord(i)); 171 names.add(name); 172 } 173 } 174 175 /** 176 * Companion to HSSFWorkbook(POIFSFileSystem), this constructs the POI filesystem around your 177 * inputstream. 178 * 179 * @param s the POI filesystem that contains the Workbook stream. 180 * @see org.apache.poi.poifs.filesystem.POIFSFileSystem 181 * @see #HSSFWorkbook(POIFSFileSystem) 182 * @exception IOException if the stream cannot be read 183 */ 184 185 public HSSFWorkbook(InputStream s) 186 throws IOException 187 { 188 this((new POIFSFileSystem(s))); 189 } 190 191 /** 192 * used internally to set the workbook properties. 193 */ 194 195 private void setPropertiesFromWorkbook(Workbook book) 196 { 197 this.workbook = book; 198 199 // none currently 200 } 201 202 /** 203 * set the sheet name. 204 * @param sheet number (0 based) 205 * @param sheet name 206 */ 207 208 public void setSheetName(int sheet, String name) 209 { 210 if (sheet > (sheets.size() - 1)) 211 { 212 throw new RuntimeException("Sheet out of bounds"); 213 } 214 workbook.setSheetName(sheet, name); 215 } 216 217 /** 218 * get the sheet name 219 * @param sheet Number 220 * @return Sheet name 221 */ 222 223 public String getSheetName(int sheet) 224 { 225 if (sheet > (sheets.size() - 1)) 226 { 227 throw new RuntimeException("Sheet out of bounds"); 228 } 229 return workbook.getSheetName(sheet); 230 } 231 232 /* 233 * get the sheet's index 234 * @param name sheet name 235 * @return sheet index or -1 if it was not found. 236 */ 237 238 /** Returns the index of the sheet by his name 239 * @param name the sheet name 240 * @return index of the sheet (0 based) 241 */ 242 public int getSheetIndex(String name) 243 { 244 int retval = workbook.getSheetIndex(name); 245 246 return retval; 247 } 248 249 /** 250 * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns 251 * the high level representation. Use this to create new sheets. 252 * 253 * @return HSSFSheet representing the new sheet. 254 */ 255 256 public HSSFSheet createSheet() 257 { 258 259 // if (getNumberOfSheets() == 3) 260 // throw new RuntimeException("You cannot have more than three sheets in HSSF 1.0"); 261 HSSFSheet sheet = new HSSFSheet(workbook); 262 263 sheets.add(sheet); 264 workbook.setSheetName(sheets.size() - 1, 265 "Sheet" + (sheets.size() - 1)); 266 WindowTwoRecord windowTwo = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid); 267 windowTwo.setSelected(sheets.size() == 1); 268 windowTwo.setPaged(sheets.size() == 1); 269 return sheet; 270 } 271 272 /** 273 * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns 274 * the high level representation. Use this to create new sheets. 275 * 276 * @param sheetname sheetname to set for the sheet. 277 * @return HSSFSheet representing the new sheet. 278 */ 279 280 public HSSFSheet createSheet(String sheetname) 281 { 282 283 // if (getNumberOfSheets() == 3) 284 // throw new RuntimeException("You cannot have more than three sheets in HSSF 1.0"); 285 HSSFSheet sheet = new HSSFSheet(workbook); 286 287 sheets.add(sheet); 288 workbook.setSheetName(sheets.size() - 1, sheetname); 289 WindowTwoRecord windowTwo = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid); 290 windowTwo.setSelected(sheets.size() == 1); 291 windowTwo.setPaged(sheets.size() == 1); 292 return sheet; 293 } 294 295 /** 296 * get the number of spreadsheets in the workbook (this will be three after serialization) 297 * @return number of sheets 298 */ 299 300 public int getNumberOfSheets() 301 { 302 return sheets.size(); 303 } 304 305 /** 306 * Get the HSSFSheet object at the given index. 307 * @param index of the sheet number (0-based physical & logical) 308 * @return HSSFSheet at the provided index 309 */ 310 311 public HSSFSheet getSheetAt(int index) 312 { 313 return (HSSFSheet) sheets.get(index); 314 } 315 316 /** 317 * Get sheet with the given name 318 * @param name of the sheet 319 * @return HSSFSheet with the name provided or null if it does not exist 320 */ 321 322 public HSSFSheet getSheet(String name) 323 { 324 HSSFSheet retval = null; 325 326 for (int k = 0; k < sheets.size(); k++) 327 { 328 String sheetname = workbook.getSheetName(k); 329 330 if (sheetname.equals(name)) 331 { 332 retval = (HSSFSheet) sheets.get(k); 333 } 334 } 335 return retval; 336 } 337 338 /** 339 * removes sheet at the given index 340 * @param index of the sheet (0-based) 341 */ 342 343 public void removeSheetAt(int index) 344 { 345 sheets.remove(index); 346 workbook.removeSheet(index); 347 } 348 349 /** 350 * determine whether the Excel GUI will backup the workbook when saving. 351 * 352 * @param backupValue true to indicate a backup will be performed. 353 */ 354 355 public void setBackupFlag(boolean backupValue) 356 { 357 BackupRecord backupRecord = workbook.getBackupRecord(); 358 359 backupRecord.setBackup(backupValue ? (short) 1 360 : (short) 0); 361 } 362 363 /** 364 * determine whether the Excel GUI will backup the workbook when saving. 365 * 366 * @return the current setting for backups. 367 */ 368 369 public boolean getBackupFlag() 370 { 371 BackupRecord backupRecord = workbook.getBackupRecord(); 372 373 return (backupRecord.getBackup() == 0) ? false 374 : true; 375 } 376 377 /** 378 * create a new Font and add it to the workbook's font table 379 * @return new font object 380 */ 381 382 public HSSFFont createFont() 383 { 384 FontRecord font = workbook.createNewFont(); 385 short fontindex = (short) (getNumberOfFonts() - 1); 386 387 if (fontindex > 3) 388 { 389 fontindex++; // THERE IS NO FOUR!! 390 } 391 HSSFFont retval = new HSSFFont(fontindex, font); 392 393 return retval; 394 } 395 396 /** 397 * get the number of fonts in the font table 398 * @return number of fonts 399 */ 400 401 public short getNumberOfFonts() 402 { 403 return (short) workbook.getNumberOfFontRecords(); 404 } 405 406 /** 407 * get the font at the given index number 408 * @param idx index number 409 * @return HSSFFont at the index 410 */ 411 412 public HSSFFont getFontAt(short idx) 413 { 414 FontRecord font = workbook.getFontRecordAt(idx); 415 HSSFFont retval = new HSSFFont(idx, font); 416 417 return retval; 418 } 419 420 /** 421 * create a new Cell style and add it to the workbook's style table 422 * @return the new Cell Style object 423 */ 424 425 public HSSFCellStyle createCellStyle() 426 { 427 ExtendedFormatRecord xfr = workbook.createCellXF(); 428 short index = (short) (getNumCellStyles() - 1); 429 HSSFCellStyle style = new HSSFCellStyle(index, xfr); 430 431 return style; 432 } 433 434 /** 435 * get the number of styles the workbook contains 436 * @return count of cell styles 437 */ 438 439 public short getNumCellStyles() 440 { 441 return (short) workbook.getNumExFormats(); 442 } 443 444 /** 445 * get the cell style object at the given index 446 * @param idx index within the set of styles 447 * @return HSSFCellStyle object at the index 448 */ 449 450 public HSSFCellStyle getCellStyleAt(short idx) 451 { 452 ExtendedFormatRecord xfr = workbook.getExFormatAt(idx); 453 HSSFCellStyle style = new HSSFCellStyle(idx, xfr); 454 455 return style; 456 } 457 458 /** 459 * Method write - write out this workbook to an Outputstream. Constructs 460 * a new POI POIFSFileSystem, passes in the workbook binary representation and 461 * writes it out. 462 * 463 * @param stream - the java OutputStream you wish to write the XLS to 464 * 465 * @exception IOException if anything can't be written. 466 * @see org.apache.poi.poifs.filesystem.POIFSFileSystem 467 */ 468 469 public void write(OutputStream stream) 470 throws IOException 471 { 472 byte[] bytes = getBytes(); 473 POIFSFileSystem fs = new POIFSFileSystem(); 474 475 fs.createDocument(new ByteArrayInputStream(bytes), "Workbook"); 476 fs.writeFilesystem(stream); 477 } 478 479 /** 480 * Method getBytes - get the bytes of just the HSSF portions of the XLS file. 481 * Use this to construct a POI POIFSFileSystem yourself. 482 * 483 * 484 * @return byte[] array containing the binary representation of this workbook and all contained 485 * sheets, rows, cells, etc. 486 * 487 * @see org.apache.poi.hssf.model.Workbook 488 * @see org.apache.poi.hssf.model.Sheet 489 */ 490 491 public byte[] getBytes() 492 { 493 log.log(DEBUG, "HSSFWorkbook.getBytes()"); 494 int wbsize = workbook.getSize(); 495 496 // log.debug("REMOVEME: old sizing method "+workbook.serialize().length); 497 // ArrayList sheetbytes = new ArrayList(sheets.size()); 498 int totalsize = wbsize; 499 500 for (int k = 0; k < sheets.size(); k++) 501 { 502 workbook.setSheetBof(k, totalsize); 503 504 // sheetbytes.add((( HSSFSheet ) sheets.get(k)).getSheet().getSize()); 505 totalsize += ((HSSFSheet) sheets.get(k)).getSheet().getSize(); 506 } 507 if (totalsize < 4096) 508 { 509 totalsize = 4096; 510 } 511 byte[] retval = new byte[totalsize]; 512 int pos = workbook.serialize(0, retval); 513 514 // System.arraycopy(wb, 0, retval, 0, wb.length); 515 for (int k = 0; k < sheets.size(); k++) 516 { 517 518 // byte[] sb = (byte[])sheetbytes.get(k); 519 // System.arraycopy(sb, 0, retval, pos, sb.length); 520 pos += ((HSSFSheet) sheets.get(k)).getSheet().serialize(pos, 521 retval); // sb.length; 522 } 523 for (int k = pos; k < totalsize; k++) 524 { 525 retval[k] = 0; 526 } 527 return retval; 528 } 529 530 public int addSSTString(String string) 531 { 532 return workbook.addSSTString(string); 533 } 534 535 public String getSSTString(int index) 536 { 537 return workbook.getSSTString(index); 538 } 539 540 Workbook getWorkbook() 541 { 542 return workbook; 543 } 544 545 /** gets the total number of named ranges in the workboko 546 * @return number of named ranges 547 */ 548 public int getNumberOfNames(){ 549 int result = names.size(); 550 return result; 551 } 552 553 /** gets the Named range 554 * @param index position of the named range 555 * @return named range high level 556 */ 557 public HSSFName getNameAt(int index){ 558 HSSFName result = (HSSFName) names.get(index); 559 560 return result; 561 } 562 563 /** gets the named range name 564 * @param index the named range index (0 based) 565 * @return named range name 566 */ 567 public String getNameName(int index){ 568 String result = getNameAt(index).getNameName(); 569 570 return result; 571 } 572 573 574 /** creates a new named range and add it to the model 575 * @return named range high level 576 */ 577 public HSSFName createName(){ 578 NameRecord nameRecord = workbook.createName(); 579 580 HSSFName newName = new HSSFName(workbook, nameRecord); 581 582 names.add(newName); 583 584 return newName; 585 } 586 587 /** gets the named range index by his name 588 * @param name named range name 589 * @return named range index 590 */ 591 public int getNameIndex(String name) 592 { 593 int retval = -1; 594 595 for (int k = 0; k < names.size(); k++) 596 { 597 String nameName = getNameName(k); 598 599 if (nameName.equals(name)) 600 { 601 retval = k; 602 break; 603 } 604 } 605 return retval; 606 } 607 608 609 /** remove the named range by his index 610 * @param index named range index (0 based) 611 */ 612 public void removeName(int index){ 613 names.remove(index); 614 workbook.removeName(index); 615 } 616 617 /** remove the named range by his name 618 * @param name named range name 619 */ 620 public void removeName(String name){ 621 int index = getNameIndex(name); 622 623 removeName(index); 624 625 } 626 627 } 628