hssfworkbook.java

java 读写word excel ppt

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */


/*
 * HSSFWorkbook.java
 *
 * Created on September 30, 2001, 3:37 PM
 */
package org.apache.poi.hssf.usermodel;

import org.apache.poi.ddf.EscherBSERecord;
import org.apache.poi.ddf.EscherBitmapBlip;
import org.apache.poi.ddf.EscherRecord;
import org.apache.poi.ddf.EscherBlipRecord;
import org.apache.poi.hssf.eventmodel.EventRecordFactory;
import org.apache.poi.hssf.model.Sheet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.record.formula.Area3DPtg;
import org.apache.poi.hssf.record.formula.MemFuncPtg;
import org.apache.poi.hssf.record.formula.UnionPtg;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;

import java.io.ByteArrayInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Stack;

/**
 * High level representation of a workbook.  This is the first object most users
 * will construct whether they are reading or writing a workbook.  It is also the
 * top level object for creating new sheets/etc.
 *
 * @see org.apache.poi.hssf.model.Workbook
 * @see org.apache.poi.hssf.usermodel.HSSFSheet
 * @author  Andrew C. Oliver (acoliver at apache dot org)
 * @author  Glen Stampoultzis (glens at apache.org)
 * @author  Shawn Laubach (slaubach at apache dot org)
 * @version 2.0-pre
 */

public class HSSFWorkbook
{
    private static final int DEBUG = POILogger.DEBUG;

    /**
     * used for compile-time performance/memory optimization.  This determines the
     * initial capacity for the sheet collection.  Its currently set to 3.
     * Changing it in this release will decrease performance
     * since you're never allowed to have more or less than three sheets!
     */

    public final static int INITIAL_CAPACITY = 3;

    /**
     * this is the reference to the low level Workbook object
     */

    private Workbook workbook;

    /**
     * this holds the HSSFSheet objects attached to this workbook
     */

    protected ArrayList sheets;

    /**
     * this holds the HSSFName objects attached to this workbook
     */

    private ArrayList names;

    /**
     * holds whether or not to preserve other nodes in the POIFS.  Used
     * for macros and embedded objects.
     */
    private boolean   preserveNodes;

    /**
     * if you do preserve the nodes, you'll need to hold the whole POIFS in
     * memory.
     */
    private POIFSFileSystem poifs;

    /**
     * Used to keep track of the data formatter so that all
     * createDataFormatter calls return the same one for a given
     * book.  This ensures that updates from one places is visible
     * someplace else.
     */
    private HSSFDataFormat formatter;


    /** Extended windows meta file */
    public static final int PICTURE_TYPE_EMF = 2;
    /** Windows Meta File */
    public static final int PICTURE_TYPE_WMF = 3;
    /** Mac PICT format */
    public static final int PICTURE_TYPE_PICT = 4;
    /** JPEG format */
    public static final int PICTURE_TYPE_JPEG = 5;
    /** PNG format */
    public static final int PICTURE_TYPE_PNG = 6;
    /** Device independant bitmap */
    public static final int PICTURE_TYPE_DIB = 7;


    private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);



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

    protected HSSFWorkbook( Workbook book )
    {
        workbook = book;
        sheets = new ArrayList( INITIAL_CAPACITY );
        names = new ArrayList( INITIAL_CAPACITY );
    }

    public HSSFWorkbook(POIFSFileSystem fs) throws IOException {
      this(fs,true);
    }

    /**
     * given a POI POIFSFileSystem object, read in its Workbook and populate the high and
     * low level models.  If you're reading in a workbook...start here.
     *
     * @param fs the POI filesystem that contains the Workbook stream.
     * @param preserveNodes whether to preseve other nodes, such as
     *        macros.  This takes more memory, so only say yes if you
     *        need to.
     * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
     * @exception IOException if the stream cannot be read
     */

    public HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes)
            throws IOException
    {
        this.preserveNodes = preserveNodes;

        if (preserveNodes) {
           this.poifs = fs;
        }

        sheets = new ArrayList(INITIAL_CAPACITY);
        names  = new ArrayList(INITIAL_CAPACITY);
        
        // Normally, the Workbook will be in a POIFS Stream
        //  called "Workbook". However, some wierd XLS generators
        //  put theirs in one called "WORKBOOK"
        String workbookName = "Workbook";
        try {
        	fs.getRoot().getEntry(workbookName);
        	// Is the default name
        } catch(FileNotFoundException fe) {
        	// Try the upper case form
        	try {
        		workbookName = "WORKBOOK";
        		fs.getRoot().getEntry(workbookName);
        	} catch(FileNotFoundException wfe) {
        		// Doesn't contain it in either form
        		throw new IllegalArgumentException("The supplied POIFSFileSystem contained neither a 'Workbook' entry, nor a 'WORKBOOK' entry. Is it really an excel file?");
        	}
        }

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

        EventRecordFactory factory = new EventRecordFactory();

        List records = RecordFactory.createRecords(stream);

        workbook = Workbook.createWorkbook(records);
        setPropertiesFromWorkbook(workbook);
        int recOffset = workbook.getNumRecords();
        int sheetNum = 0;
        
        // convert all LabelRecord records to LabelSSTRecord
        convertLabelRecords(records, recOffset);        
        while (recOffset < records.size())
        {
            Sheet sheet = Sheet.createSheet(records, sheetNum++, recOffset );

            recOffset = sheet.getEofLoc()+1;
            if (recOffset == 1)
            {
                break;
            }

            HSSFSheet hsheet = new HSSFSheet(workbook, sheet);

            sheets.add(hsheet);

            // workbook.setSheetName(sheets.size() -1, "Sheet"+sheets.size());
        }

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

     public HSSFWorkbook(InputStream s) throws IOException {
         this(s,true);
     }

    /**
     * Companion to HSSFWorkbook(POIFSFileSystem), this constructs the POI filesystem around your
     * inputstream.
     *
     * @param s  the POI filesystem that contains the Workbook stream.
     * @param preserveNodes whether to preseve other nodes, such as
     *        macros.  This takes more memory, so only say yes if you
     *        need to.
     * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
     * @see #HSSFWorkbook(POIFSFileSystem)
     * @exception IOException if the stream cannot be read
     */

    public HSSFWorkbook(InputStream s, boolean preserveNodes)
            throws IOException
    {
        this(new POIFSFileSystem(s), preserveNodes);
    }

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

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

        // none currently
    }
    
    /**
      * This is basically a kludge to deal with the now obsolete Label records.  If
      * you have to read in a sheet that contains Label records, be aware that the rest
      * of the API doesn't deal with them, the low level structure only provides read-only
      * semi-immutable structures (the sets are there for interface conformance with NO
      * impelmentation).  In short, you need to call this function passing it a reference
      * to the Workbook object.  All labels will be converted to LabelSST records and their
      * contained strings will be written to the Shared String tabel (SSTRecord) within
      * the Workbook.
      *
      * @param wb sheet's matching low level Workbook structure containing the SSTRecord.
      * @see org.apache.poi.hssf.record.LabelRecord
      * @see org.apache.poi.hssf.record.LabelSSTRecord
      * @see org.apache.poi.hssf.record.SSTRecord
      */
 
     private void convertLabelRecords(List records, int offset)
     {
         if (log.check( POILogger.DEBUG ))
             log.log(POILogger.DEBUG, "convertLabelRecords called");
         for (int k = offset; k < records.size(); k++)
         {
             Record rec = ( Record ) records.get(k);

             if (rec.getSid() == LabelRecord.sid)
             {
                 LabelRecord oldrec = ( LabelRecord ) rec;

                 records.remove(k);
                 LabelSSTRecord newrec   = new LabelSSTRecord();
                 int            stringid =
                     workbook.addSSTString(new UnicodeString(oldrec.getValue()));

                 newrec.setRow(oldrec.getRow());
                 newrec.setColumn(oldrec.getColumn());
                 newrec.setXFIndex(oldrec.getXFIndex());
                 newrec.setSSTIndex(stringid);
                       records.add(k, newrec);
             }
         }
         if (log.check( POILogger.DEBUG ))
             log.log(POILogger.DEBUG, "convertLabelRecords exit");
     }
    

    /**
     * sets the order of appearance for a given sheet.
     *
     * @param sheetname the name of the sheet to reorder
     * @param pos the position that we want to insert the sheet into (0 based)
     */

    public void setSheetOrder(String sheetname, int pos ) {
        sheets.add(pos,sheets.remove(getSheetIndex(sheetname)));
        workbook.setSheetOrder(sheetname, pos);
    }
    
    /**
     * sets the tab whose data is actually seen when the sheet is opened.
     * This may be different from the "selected sheet" since excel seems to
     * allow you to show the data of one sheet when another is seen "selected"
     * in the tabs (at the bottom).
     * @see org.apache.poi.hssf.usermodel.HSSFSheet#setSelected(boolean)
     * @param index
     */
    public void setSelectedTab(short index) {
        workbook.getWindowOne().setSelectedTab(index);
    }
    
    /**
     * gets the tab whose data is actually seen when the sheet is opened.
     * This may be different from the "selected sheet" since excel seems to
     * allow you to show the data of one sheet when another is seen "selected"
     * in the tabs (at the bottom).
     * @see org.apache.poi.hssf.usermodel.HSSFSheet#setSelected(boolean)
     * @return