org.apache.poi.hssf.model.InternalWorkbook Java Examples

protected static void cleanupAndSaveExcel97(POIFSFileSystem fs, OutputStream outputStream)
    throws IOException {
  Workbook wb = WorkbookFactory.create(fs);

  if (wb instanceof HSSFWorkbook) {
    HSSFWorkbook hwb = (HSSFWorkbook) wb;
    InternalWorkbook internal = hwb.getInternalWorkbook();
    if (internal != null) {
      LOGGER.trace("# of Records: {}", internal.getNumRecords());
      removeObProjRecord(internal.getRecords());
      LOGGER.trace("# of Records: {}", internal.getNumRecords());
    }
  }

  wb.write(outputStream);
}
 

/**
 * Return picture data for this shape
 *
 * @return picture data for this shape or {@code null} if picture wasn't embedded, i.e. external linked
 */
@Override
public HSSFPictureData getPictureData(){
    int picIdx = getPictureIndex();
    if (picIdx == -1) {
        return null;
    }
    
    HSSFPatriarch patriarch = getPatriarch();
    HSSFShape parent = getParent();
    while(patriarch == null && parent != null) {
        patriarch = parent.getPatriarch();
        parent = parent.getParent();
    }
    if(patriarch == null) {
        throw new IllegalStateException("Could not find a patriarch for a HSSPicture");
    }

    InternalWorkbook iwb = patriarch.getSheet().getWorkbook().getWorkbook();
    EscherBSERecord bse = iwb.getBSERecord(picIdx);
	EscherBlipRecord blipRecord = bse.getBlipRecord();
	return new HSSFPictureData(blipRecord);
}
 

/**
 * Constructs a new data formatter.  It takes a workbook to have
 * access to the workbooks format records.
 * @param workbook the workbook the formats are tied to.
 */
HSSFDataFormat(InternalWorkbook workbook) {
	_workbook = workbook;

	Iterator<FormatRecord> i = workbook.getFormats().iterator();
	while (i.hasNext()) {
		FormatRecord r = i.next();
		ensureFormatsSize(r.getIndexCode());
		_formats.set(r.getIndexCode(), r.getFormatString());
	}
}
 

public static void cloneFont(HSSFCellStyle source, HSSFCellStyle target) {
    // Handle matching things if we cross workbooks
    InternalWorkbook sourceWorkbook = getWorkbookFromStyle(source);
    InternalWorkbook targetWorkbook = getWorkbookFromStyle(target);
    if (targetWorkbook != sourceWorkbook) {
        // Finally we need to clone the font, and update the format record for this
        FontRecord fr = targetWorkbook.createNewFont();
        fr.cloneStyleFrom(sourceWorkbook.getFontRecordAt(source.getFontIndex()));
        HSSFFont font = newInstance(HSSFFont.class, new Class[]{int.class, FontRecord.class},
                (short)targetWorkbook.getFontIndex(fr), fr);
        target.setFont(font);
    }
}
 

public static void cloneStyleRelations(HSSFCellStyle source, HSSFCellStyle target) {
    //First we need to clone the extended format record
    getFormatFromStyle(target).cloneStyleFrom(getFormatFromStyle(source));
    //Handle matching things if we cross workbooks
    InternalWorkbook sourceWorkbook = getWorkbookFromStyle(source);
    InternalWorkbook targetWorkbook = getWorkbookFromStyle(target);
    if (targetWorkbook != sourceWorkbook) {
        //Then we need to clone the format string, and update the format record for this
        short fmt = targetWorkbook.getFormat(source.getDataFormatString(), true);
        target.setDataFormat(fmt);
    }
}
 

/**
 * Creates a stub Workbook from the supplied records,
 *  suitable for use with the {@link HSSFFormulaParser}
 * @param externs The ExternSheetRecords in your file
 * @param bounds The BoundSheetRecords in your file
 * @param sst The SSTRecord in your file.
 * @return A stub Workbook suitable for use with {@link HSSFFormulaParser}
 */
public static InternalWorkbook createStubWorkbook(ExternSheetRecord[] externs,
		BoundSheetRecord[] bounds, SSTRecord sst) {
	List<Record> wbRecords = new ArrayList<Record>();

	// Core Workbook records go first
	if(bounds != null) {
		for (BoundSheetRecord bound : bounds) {
			wbRecords.add(bound);
		}
	}
	if(sst != null) {
		wbRecords.add(sst);
	}

	// Now we can have the ExternSheetRecords,
	//  preceded by a SupBookRecord
	if(externs != null) {
		wbRecords.add(SupBookRecord.createInternalReferences(
				(short)externs.length));
		for (ExternSheetRecord extern : externs) {
			wbRecords.add(extern);
		}
	}

	// Finally we need an EoF record
	wbRecords.add(EOFRecord.instance);

	return InternalWorkbook.createWorkbook(wbRecords);
}
 

/**
 * Applying a user-defined style (UDS) is special. Excel does not directly reference user-defined styles, but
 * instead create a 'proxy' ExtendedFormatRecord referencing the UDS as parent.
 *
 * The proceudre to apply a UDS is as follows:
 *
 * 1. search for a ExtendedFormatRecord with parentIndex == style.getIndex()
 *    and xfType ==  ExtendedFormatRecord.XF_CELL.
 * 2. if not found then create a new ExtendedFormatRecord and copy all attributes from the user-defined style
 *    and set the parentIndex to be style.getIndex()
 * 3. return the index of the ExtendedFormatRecord, this will be assigned to the parent cell record
 *
 * @param style  the user style to apply
 *
 * @return  the index of a ExtendedFormatRecord record that will be referenced by the cell
 */
private short applyUserCellStyle(HSSFCellStyle style){
    if(style.getUserStyleName() == null) {
        throw new IllegalArgumentException("Expected user-defined style");
    }

    InternalWorkbook iwb = _book.getWorkbook();
    short userXf = -1;
    int numfmt = iwb.getNumExFormats();
    for(short i = 0; i < numfmt; i++){
        ExtendedFormatRecord xf = iwb.getExFormatAt(i);
        if(xf.getXFType() == ExtendedFormatRecord.XF_CELL && xf.getParentIndex() == style.getIndex() ){
            userXf = i;
            break;
        }
    }
    short styleIndex;
    if (userXf == -1){
        ExtendedFormatRecord xfr = iwb.createCellXF();
        xfr.cloneStyleFrom(iwb.getExFormatAt(style.getIndex()));
        xfr.setIndentionOptions((short)0);
        xfr.setXFType(ExtendedFormatRecord.XF_CELL);
        xfr.setParentIndex(style.getIndex());
        styleIndex = (short)numfmt;
    } else {
        styleIndex = userXf;
    }

    return styleIndex;
}
 

/**
 * Return the dimension of the embedded image in pixel
 *
 * @return image dimension in pixels
 */
@Override
public Dimension getImageDimension(){
    InternalWorkbook iwb = getPatriarch().getSheet().getWorkbook().getWorkbook();
    EscherBSERecord bse = iwb.getBSERecord(getPictureIndex());
    byte[] data = bse.getBlipRecord().getPicturedata();
    int type = bse.getBlipTypeWin32();
    return ImageUtils.getImageDimension(new ByteArrayInputStream(data), type);
}
 

public static void writeToFile(OutputStream fos, InputStream xlsWorkbook, boolean excludeWorkbookRecords, String[] params) throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(xlsWorkbook);
    InternalWorkbook internalWorkbook = workbook.getInternalWorkbook();
    DrawingGroupRecord r = (DrawingGroupRecord) internalWorkbook.findFirstRecordBySid(DrawingGroupRecord.sid);

    StringBuilder builder = new StringBuilder();
    builder.append("<workbook>\n");
    String tab = "\t";
    if (!excludeWorkbookRecords && r != null) {
        r.decode();
        List<EscherRecord> escherRecords = r.getEscherRecords();
        for (EscherRecord record : escherRecords) {
            builder.append(record.toXml(tab));
        }
    }
    List<Integer> sheets = getSheetsIndexes(params, workbook);
    for (Integer i : sheets) {
        HSSFPatriarch p = workbook.getSheetAt(i).getDrawingPatriarch();
        if(p != null ) {
            builder.append(tab).append("<sheet").append(i).append(">\n");
            builder.append(p.getBoundAggregate().toXml(tab + "\t"));
            builder.append(tab).append("</sheet").append(i).append(">\n");
        }
    }
    builder.append("</workbook>\n");
    fos.write(builder.toString().getBytes(StringUtil.UTF8));
    fos.close();
    workbook.close();
}
 

/**
 * Whether Excel will be asked to recalculate all formulas when the  workbook is opened.
 *
 * @since 3.8
 */
@Override
public boolean getForceFormulaRecalculation(){
    InternalWorkbook iwb = getWorkbook();
    RecalcIdRecord recalc = (RecalcIdRecord)iwb.findFirstRecordBySid(RecalcIdRecord.sid);
    return recalc != null && recalc.getEngineId() != 0;
}
 

/**
 * used internally to set the workbook properties.
 */

private void setPropertiesFromWorkbook(InternalWorkbook book)
{
    this.workbook = book;

    // none currently
}
 

InternalWorkbook getWorkbook() {
    return workbook;
}
 

HSSFRichTextString(InternalWorkbook book, LabelSSTRecord record) {
  setWorkbookReferences(book, record);

  _string = book.getSSTString(record.getSSTIndex());
}
 

public static InternalWorkbook getWorkbookFromStyle(HSSFCellStyle style) {
    return XslStyleHelper.getFieldValue(style, "_workbook");
}
 

private List<String> getLinkedWorkbooksHSSF() {
	List<String> result = new ArrayList<>();
	try {
		// this is a hack to fetch linked workbooks in the Old Excel format
		// we use reflection to access private fields
		// might not work if internal structure of the class changes
		InternalWorkbook intWb = ((HSSFWorkbook)this.currentWorkbook).getInternalWorkbook();
		// method to fetch link table
		Method linkTableMethod = InternalWorkbook.class.getDeclaredMethod("getOrCreateLinkTable");
        	linkTableMethod.setAccessible(true);
   			Object linkTable = linkTableMethod.invoke(intWb);
		// method to fetch external book and sheet name
   			Method externalBooksMethod = linkTable.getClass().getDeclaredMethod("getExternalBookAndSheetName", int.class);
   			externalBooksMethod.setAccessible(true);
		// now we need to browse through the table until we hit an array out of bounds
		int i = 0;
		try {
			while(i<MSExcelParser.MAX_LINKEDWB_OLDEXCEL) {
				String[] externalBooks = (String[])externalBooksMethod.invoke(linkTable, i++);
				if ((externalBooks!=null) && (externalBooks.length>0)){
					result.add(externalBooks[0]);
				}
	        	}
		} catch  ( java.lang.reflect.InvocationTargetException e) {
      				 if ( !(e.getCause() instanceof java.lang.IndexOutOfBoundsException) ) {
           			throw e;
       				}
		}
   			
	} catch (NoSuchMethodException nsme) {
		LOG.error(COULD_NOT_RETRIEVE_LINKED_WORKBOOKS_FOR_OLD_EXCEL_FORMAT);
		LOG.error(nsme);
	}
	 catch (IllegalAccessException iae) {
		LOG.error(COULD_NOT_RETRIEVE_LINKED_WORKBOOKS_FOR_OLD_EXCEL_FORMAT);
		LOG.error(iae);
	}
	catch (InvocationTargetException ite) {
		LOG.error(COULD_NOT_RETRIEVE_LINKED_WORKBOOKS_FOR_OLD_EXCEL_FORMAT);
		LOG.error(ite);
	}
	return result;
}
 

/** This must be called to setup the internal work book references whenever
 * a RichTextString is added to a cell
 */
void setWorkbookReferences(InternalWorkbook book, LabelSSTRecord record) {
  _book = book;
  _record = record;
}
 

public InternalWorkbook getStubWorkbook() {
	return createStubWorkbook(
			getExternSheetRecords(), getBoundSheetRecords(),
			getSSTRecord()
	);
}
 

public static HSSFWorkbook create(InternalWorkbook book) {
	return new HSSFWorkbook(book);
}
 

/**
 * Returns the Workbook that this Cell is bound to
 */
protected InternalWorkbook getBoundWorkbook() {
    return _book.getWorkbook();
}
 

private HSSFWorkbook(InternalWorkbook book) {
    super((DirectoryNode)null);
    workbook = book;
    _sheets = new ArrayList<HSSFSheet>(INITIAL_CAPACITY);
    names = new ArrayList<HSSFName>(INITIAL_CAPACITY);
}
 

/**
 * given a POI POIFSFileSystem object, and a specific directory
 *  within it, read in its Workbook and populate the high and
 *  low level models.  If you're reading in a workbook...start here.
 *
 * @param directory the POI filesystem directory to process from
 * @param preserveNodes whether to preserve other nodes, such as
 *        macros.  This takes more memory, so only say yes if you
 *        need to. If set, will store all of the POIFSFileSystem
 *        in memory
 * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
 * @exception IOException if the stream cannot be read
 */
public HSSFWorkbook(DirectoryNode directory, boolean preserveNodes)
        throws IOException
{
    super(directory);
    String workbookName = getWorkbookDirEntryName(directory);

    this.preserveNodes = preserveNodes;

    // If we're not preserving nodes, don't track the
    //  POIFS any more
    if(! preserveNodes) {
        clearDirectory();
    }

    _sheets = new ArrayList<HSSFSheet>(INITIAL_CAPACITY);
    names  = new ArrayList<HSSFName>(INITIAL_CAPACITY);

    // Grab the data from the workbook stream, however
    //  it happens to be spelled.
    InputStream stream = directory.createDocumentInputStream(workbookName);

    List<Record> records = RecordFactory.createRecords(stream);

    workbook = InternalWorkbook.createWorkbook(records);
    setPropertiesFromWorkbook(workbook);
    int recOffset = workbook.getNumRecords();

    // convert all LabelRecord records to LabelSSTRecord
    convertLabelRecords(records, recOffset);
    RecordStream rs = new RecordStream(records, recOffset);
    while (rs.hasNext()) {
        try {
            InternalSheet sheet = InternalSheet.createSheet(rs);
            _sheets.add(new HSSFSheet(this, sheet));
        } catch (UnsupportedBOFType eb) {
            // Hopefully there's a supported one after this!
            log.log(POILogger.WARN, "Unsupported BOF found of type " + eb.getType());
        }
    }

    for (int i = 0 ; i < workbook.getNumNames() ; ++i){
        NameRecord nameRecord = workbook.getNameRecord(i);
        HSSFName name = new HSSFName(this, nameRecord, workbook.getNameCommentRecord(nameRecord));
        names.add(name);
    }
}
 

/**
 * Get the contents of the format string, by looking up
 *  the DataFormat against the supplied low level workbook
 * @see org.apache.poi.hssf.usermodel.HSSFDataFormat
 */
public String getDataFormatString(org.apache.poi.hssf.model.InternalWorkbook workbook) {
    HSSFDataFormat format = new HSSFDataFormat( workbook );

    return format.getFormat(getDataFormat());
}
 

protected HSSFCellStyle(short index, ExtendedFormatRecord rec, InternalWorkbook workbook)
{
    _workbook = workbook;
    _index = index;
    _format     = rec;
}
 

@Override
public HSSFAutoFilter setAutoFilter(CellRangeAddress range) {
    InternalWorkbook workbook = _workbook.getWorkbook();
    int sheetIndex = _workbook.getSheetIndex(this);

    NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);

    if (name == null) {
        name = workbook.createBuiltInName(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);
    }

    int firstRow = range.getFirstRow();
    
    // if row was not given when constructing the range...
    if(firstRow == -1) {
        firstRow = 0;
    }

    // The built-in name must consist of a single Area3d Ptg.
    Area3DPtg ptg = new Area3DPtg(firstRow, range.getLastRow(),
            range.getFirstColumn(), range.getLastColumn(),
            false, false, false, false, sheetIndex);
    name.setNameDefinition(new Ptg[]{ptg});

    AutoFilterInfoRecord r = new AutoFilterInfoRecord();
    // the number of columns that have AutoFilter enabled.
    int numcols = 1 + range.getLastColumn() - range.getFirstColumn();
    r.setNumEntries((short) numcols);
    int idx = _sheet.findFirstRecordLocBySid(DimensionsRecord.sid);
    _sheet.getRecords().add(idx, r);

    //create a combobox control for each column
    HSSFPatriarch p = createDrawingPatriarch();
    final int firstColumn = range.getFirstColumn();
    final int lastColumn = range.getLastColumn();
    for (int col = firstColumn; col <= lastColumn; col++) {
        p.createComboBox(new HSSFClientAnchor(0, 0, 0, 0,
                (short) col, firstRow, (short) (col + 1), firstRow + 1));
    }

    return new HSSFAutoFilter(this);
}
 

@Internal
public InternalWorkbook getInternalWorkbook() {
    return workbook;
}
 

/**
 * Whether the application shall perform a full recalculation when the workbook is opened.
 * <p>
 * Typically you want to force formula recalculation when you modify cell formulas or values
 * of a workbook previously created by Excel. When set to true, this flag will tell Excel
 * that it needs to recalculate all formulas in the workbook the next time the file is opened.
 * </p>
 * <p>
 * Note, that recalculation updates cached formula results and, thus, modifies the workbook.
 * Depending on the version, Excel may prompt you with "Do you want to save the changes in <em>filename</em>?"
 * on close.
 * </p>
 *
 * @param value true if the application will perform a full recalculation of
 * workbook values when the workbook is opened
 * @since 3.8
 */
@Override
public void setForceFormulaRecalculation(boolean value){
    InternalWorkbook iwb = getWorkbook();
    RecalcIdRecord recalc = iwb.getRecalcId();
    recalc.setEngineId(0);
}
 

/**
 * Sets the name of the named range
 *
 * <p>The following is a list of syntax rules that you need to be aware of when you create and edit names.</p>
 * <ul>
 *   <li><strong>Valid characters</strong>
 *   The first character of a name must be a letter, an underscore character (_), or a backslash (\).
 *   Remaining characters in the name can be letters, numbers, periods, and underscore characters.
 *   </li>
 *   <li><strong>Cell references disallowed</strong>
 *   Names cannot be the same as a cell reference, such as Z$100 or R1C1.</li>
 *   <li><strong>Spaces are not valid</strong>
 *   Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
 *   </li>
 *   <li><strong>Name length</strong>
 *    A name can contain up to 255 characters.
 *   </li>
 *   <li><strong>Case sensitivity</strong>
 *   Names can contain uppercase and lowercase letters.
 *   </li>
 * </ul>
 *
 * <p>
 * A name must always be unique within its scope. POI prevents you from defining a name that is not unique
 * within its scope. However you can use the same name in different scopes. Example:
 * <pre><blockquote>
 * //by default names are workbook-global
 * HSSFName name;
 * name = workbook.createName();
 * name.setNameName("sales_08");
 *
 * name = workbook.createName();
 * name.setNameName("sales_08"); //will throw an exception: "The workbook already contains this name (case-insensitive)"
 *
 * //create sheet-level name
 * name = workbook.createName();
 * name.setSheetIndex(0); //the scope of the name is the first sheet
 * name.setNameName("sales_08");  //ok
 *
 * name = workbook.createName();
 * name.setSheetIndex(0);
 * name.setNameName("sales_08");  //will throw an exception: "The sheet already contains this name (case-insensitive)"
 *
 * </blockquote></pre>
* </p>
 *
 * @param nameName named range name to set
 * @throws IllegalArgumentException if the name is invalid or the name already exists (case-insensitive)
 */
public void setNameName(String nameName){
    validateName(nameName);

    InternalWorkbook wb = _book.getWorkbook();
    _definedNameRec.setNameText(nameName);

    int sheetNumber = _definedNameRec.getSheetNumber();

    //Check to ensure no other names have the same case-insensitive name
    final int lastNameIndex = wb.getNumNames()-1;
    for ( int i = lastNameIndex; i >=0; i-- )
    {
        NameRecord rec = wb.getNameRecord(i);
        if (rec != _definedNameRec) {
            if (rec.getNameText().equalsIgnoreCase(nameName) && sheetNumber == rec.getSheetNumber()){
                String msg = "The "+(sheetNumber == 0 ? "workbook" : "sheet")+" already contains this name: " + nameName;
                _definedNameRec.setNameText(nameName + "(2)");
                throw new IllegalArgumentException(msg);
            }
        }
    }
    
    // Update our comment, if there is one
    if(_commentRec != null) {
       _commentRec.setNameText(nameName);
       _book.getWorkbook().updateNameCommentRecordCache(_commentRec);
    }
}
 

/**
 * Creates new HSSFWorkbook from scratch (start here!)
 *
 */
public HSSFWorkbook() {
    this(InternalWorkbook.createWorkbook());
}
 

/**
 * Creates a stub workbook from the supplied records,
 *  suitable for use with the {@link HSSFFormulaParser}
 * @param externs The ExternSheetRecords in your file
 * @param bounds The BoundSheetRecords in your file
 * @return A stub Workbook suitable for use with {@link HSSFFormulaParser}
 */
public static InternalWorkbook createStubWorkbook(ExternSheetRecord[] externs,
		BoundSheetRecord[] bounds) {
	return createStubWorkbook(externs, bounds, null);
}