hssfsheet.java

介绍java核心技术的pio编程方法以及基本概念

/* ====================================================================
   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.
==================================================================== */

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

import org.apache.poi.ddf.EscherRecord;
import org.apache.poi.hssf.model.FormulaParser;
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.Ptg;
import org.apache.poi.hssf.record.formula.ReferencePtg;
import org.apache.poi.hssf.util.HSSFCellRangeAddress;
import org.apache.poi.hssf.util.HSSFDataValidation;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.hssf.util.PaneInformation;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;

import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Stack;
import java.util.TreeMap;
import java.text.AttributedString;
import java.text.NumberFormat;
import java.text.DecimalFormat;
import java.awt.font.TextLayout;
import java.awt.font.FontRenderContext;
import java.awt.font.TextAttribute;

import java.awt.geom.AffineTransform;

/**
 * High level representation of a worksheet.
 * @author  Andrew C. Oliver (acoliver at apache dot org)
 * @author  Glen Stampoultzis (glens at apache.org)
 * @author  Libin Roman (romal at vistaportal.com)
 * @author  Shawn Laubach (slaubach at apache dot org) (Just a little)
 * @author  Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too)
 * @author  Yegor Kozlov (yegor at apache.org) (Autosizing columns)
 */

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

    /* Constants for margins */
    public static final short LeftMargin = Sheet.LeftMargin;
    public static final short RightMargin = Sheet.RightMargin;
    public static final short TopMargin = Sheet.TopMargin;
    public static final short BottomMargin = Sheet.BottomMargin;

    public static final byte PANE_LOWER_RIGHT = (byte)0;
    public static final byte PANE_UPPER_RIGHT = (byte)1;
    public static final byte PANE_LOWER_LEFT = (byte)2;
    public static final byte PANE_UPPER_LEFT = (byte)3;


    /**
     * Used for compile-time optimization.  This is the initial size for the collection of
     * rows.  It is currently set to 20.  If you generate larger sheets you may benefit
     * by setting this to a higher number and recompiling a custom edition of HSSFSheet.
     */

    public final static int INITIAL_CAPACITY = 20;

    /**
     * reference to the low level Sheet object
     */

    private Sheet sheet;
    private TreeMap rows;
    protected Workbook book;
    protected HSSFWorkbook workbook;
    private int firstrow;
    private int lastrow;
    private static POILogger log = POILogFactory.getLogger(HSSFSheet.class);

    /**
     * Creates new HSSFSheet   - called by HSSFWorkbook to create a sheet from
     * scratch.  You should not be calling this from application code (its protected anyhow).
     *
     * @param workbook - The HSSF Workbook object associated with the sheet.
     * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
     */

    protected HSSFSheet(HSSFWorkbook workbook)
    {
        sheet = Sheet.createSheet();
        rows = new TreeMap();   // new ArrayList(INITIAL_CAPACITY);
        this.workbook = workbook;
        this.book = workbook.getWorkbook();
    }

    /**
     * Creates an HSSFSheet representing the given Sheet object.  Should only be
     * called by HSSFWorkbook when reading in an exisiting file.
     *
     * @param workbook - The HSSF Workbook object associated with the sheet.
     * @param sheet - lowlevel Sheet object this sheet will represent
     * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
     */

    protected HSSFSheet(HSSFWorkbook workbook, Sheet sheet)
    {
        this.sheet = sheet;
        rows = new TreeMap();
        this.workbook = workbook;
        this.book = workbook.getWorkbook();
        setPropertiesFromSheet(sheet);
    }

    HSSFSheet cloneSheet(HSSFWorkbook workbook) {
      return new HSSFSheet(workbook, sheet.cloneSheet());
    }


    /**
     * used internally to set the properties given a Sheet object
     */

    private void setPropertiesFromSheet(Sheet sheet)
    {
        int sloc = sheet.getLoc();
        RowRecord row = sheet.getNextRow();

        while (row != null)
        {
            createRowFromRecord(row);

            row = sheet.getNextRow();
        }
        sheet.setLoc(sloc);
        CellValueRecordInterface cval = sheet.getNextValueRecord();
        long timestart = System.currentTimeMillis();

        if (log.check( POILogger.DEBUG ))
            log.log(DEBUG, "Time at start of cell creating in HSSF sheet = ",
                new Long(timestart));
        HSSFRow lastrow = null;

        while (cval != null)
        {
            long cellstart = System.currentTimeMillis();
            HSSFRow hrow = lastrow;

            if ( ( lastrow == null ) || ( lastrow.getRowNum() != cval.getRow() ) )
            {
                hrow = getRow( cval.getRow() );
            }
            if ( hrow != null )
            {
                lastrow = hrow;
                if (log.check( POILogger.DEBUG ))
                    log.log( DEBUG, "record id = " + Integer.toHexString( ( (Record) cval ).getSid() ) );
                hrow.createCellFromRecord( cval );
                cval = sheet.getNextValueRecord();
                if (log.check( POILogger.DEBUG ))
                    log.log( DEBUG, "record took ",
                        new Long( System.currentTimeMillis() - cellstart ) );
            }
            else
            {
                cval = null;
            }
        }
        if (log.check( POILogger.DEBUG ))
            log.log(DEBUG, "total sheet cell creation took ",
                new Long(System.currentTimeMillis() - timestart));
    }

    /**
     * Create a new row within the sheet and return the high level representation
     *
     * @param rownum  row number
     * @return High level HSSFRow object representing a row in the sheet
     * @see org.apache.poi.hssf.usermodel.HSSFRow
     * @see #removeRow(HSSFRow)
     */
    public HSSFRow createRow(int rownum)
    {
        HSSFRow row = new HSSFRow(book, sheet, rownum);

        addRow(row, true);
        return row;
    }

    /**
     * Used internally to create a high level Row object from a low level row object.
     * USed when reading an existing file
     * @param row  low level record to represent as a high level Row and add to sheet
     * @return HSSFRow high level representation
     */

    private HSSFRow createRowFromRecord(RowRecord row)
    {
        HSSFRow hrow = new HSSFRow(book, sheet, row);

        addRow(hrow, false);
        return hrow;
    }

    /**
     * Remove a row from this sheet.  All cells contained in the row are removed as well
     *
     * @param row   representing a row to remove.
     */

    public void removeRow(HSSFRow row)
    {
        sheet.setLoc(sheet.getDimsLoc());
        if (rows.size() > 0)
        {
            rows.remove(row);
            if (row.getRowNum() == getLastRowNum())
            {
                lastrow = findLastRow(lastrow);
            }
            if (row.getRowNum() == getFirstRowNum())
            {
                firstrow = findFirstRow(firstrow);
            }
            Iterator iter = row.cellIterator();

            while (iter.hasNext())
            {
                HSSFCell cell = (HSSFCell) iter.next();

                sheet.removeValueRecord(row.getRowNum(),
                        cell.getCellValueRecord());
            }
            sheet.removeRow(row.getRowRecord());
        }
    }

    /**
     * used internally to refresh the "last row" when the last row is removed.
     */

    private int findLastRow(int lastrow)
    {
        int rownum = lastrow - 1;
        HSSFRow r = getRow(rownum);

        while (r == null && rownum > 0)
        {
            r = getRow(--rownum);
        }
        if (r == null)
          return -1;
        return rownum;
    }

    /**
     * used internally to refresh the "first row" when the first row is removed.
     */

    private int findFirstRow(int firstrow)
    {
        int rownum = firstrow + 1;
        HSSFRow r = getRow(rownum);

        while (r == null && rownum <= getLastRowNum())
        {
            r = getRow(++rownum);
        }

        if (rownum > getLastRowNum())
            return -1;

        return rownum;
    }

    /**
     * add a row to the sheet
     *
     * @param addLow whether to add the row to the low level model - false if its already there
     */

    private void addRow(HSSFRow row, boolean addLow)
    {
        rows.put(row, row);
        if (addLow)
        {
            sheet.addRow(row.getRowRecord());
        }
        if (row.getRowNum() > getLastRowNum())
        {
            lastrow = row.getRowNum();
        }
        if (row.getRowNum() < getFirstRowNum())
        {
            firstrow = row.getRowNum();
        }
    }

    /**
     * Returns the logical row (not physical) 0-based.  If you ask for a row that is not
     * defined you get a null.  This is to say row 4 represents the fifth row on a sheet.
     * @param rownum  row to get
     * @return HSSFRow representing the rownumber or null if its not defined on the sheet
     */

    public HSSFRow getRow(int rownum)
    {
        HSSFRow row = new HSSFRow();

        //row.setRowNum((short) rownum);
        row.setRowNum( rownum);
        return (HSSFRow) rows.get(row);
    }

    /**
     * Returns the number of phsyically defined rows (NOT the number of rows in the sheet)
     */

    public int getPhysicalNumberOfRows()
    {
        return rows.size();
    }

    /**
     * gets the first row on the sheet
     * @return the number of the first logical row on the sheet
     */

    public int getFirstRowNum()
    {
        return firstrow;
    }

    /**
     * gets the last row on the sheet
     * @return last row contained n this sheet.
     */

    public int getLastRowNum()
    {
        return lastrow;
    }
    
    /**
     * Creates a data validation object
     * @param obj_validation The Data validation object settings
     */
    public void addValidationData(HSSFDataValidation obj_validation)
    {
       if ( obj_validation == null )
       {
         return;
       }
       DVALRecord dvalRec = (DVALRecord)sheet.findFirstRecordBySid( DVALRecord.sid );
       int eofLoc = sheet.findFirstRecordLocBySid( EOFRecord.sid );
       if ( dvalRec == null )
       {
          dvalRec = new DVALRecord();
          sheet.getRecords().add( eofLoc, dvalRec );
       }
       int curr_dvRecNo = dvalRec.getDVRecNo();
       dvalRec.setDVRecNo(curr_dvRecNo+1);

       //create dv record
       DVRecord dvRecord = new DVRecord();

       //dv record's option flags
       dvRecord.setDataType( obj_validation.getDataValidationType() );
       dvRecord.setErrorStyle(obj_validation.getErrorStyle());
       dvRecord.setEmptyCellAllowed(obj_validation.getEmptyCellAllowed());
       dvRecord.setSurppresDropdownArrow(obj_validation.getSurppressDropDownArrow());
       dvRecord.setShowPromptOnCellSelected(obj_validation.getShowPromptBox());
       dvRecord.setShowErrorOnInvalidValue(obj_validation.getShowErrorBox());
       dvRecord.setConditionOperator(obj_validation.getOperator());

       //string fields
       dvRecord.setStringField( DVRecord.STRING_PROMPT_TITLE,obj_validation.getPromptBoxTitle());
       dvRecord.setStringField( DVRecord.STRING_PROMPT_TEXT, obj_validation.getPromptBoxText());
       dvRecord.setStringField( DVRecord.STRING_ERROR_TITLE, obj_validation.getErrorBoxTitle());
       dvRecord.setStringField( DVRecord.STRING_ERROR_TEXT, obj_validation.getErrorBoxText());

       //formula fields ( size and data )
       String str_formula = obj_validation.getFirstFormula();
       FormulaParser fp = new FormulaParser(str_formula+";",book);
       fp.parse();
       Stack ptg_arr = new Stack();
       Ptg[] ptg  = fp.getRPNPtg();
       int size = 0;
       for (int k = 0; k < ptg.length; k++)
       {
           if ( ptg[k] instanceof org.apache.poi.hssf.record.formula.AreaPtg )
           {
              //we should set ptgClass to Ptg.CLASS_REF and explicit formula string to false
              ptg[k].setClass(Ptg.CLASS_REF);
              obj_validation.setExplicitListFormula(false);
           }
           size += ptg[k].getSize();
           ptg_arr.push(ptg[k]);
       }
       dvRecord.setFirstFormulaRPN(ptg_arr);
       dvRecord.setFirstFormulaSize((short)size);

       dvRecord.setListExplicitFormula(obj_validation.getExplicitListFormula());

       if ( obj_validation.getSecondFormula() != null )
       {
         str_formula = obj_validation.getSecondFormula();
         fp = new FormulaParser(str_formula+";",book);
         fp.parse();
         ptg_arr = new Stack();
         ptg  = fp.getRPNPtg();
         size = 0;
         for (int k = 0; k < ptg.length; k++)
         {
             size += ptg[k].getSize();
             ptg_arr.push(ptg[k]);
         }
         dvRecord.setSecFormulaRPN(ptg_arr);
         dvRecord.setSecFormulaSize((short)size);
       }

       //dv records cell range field
       HSSFCellRangeAddress cell_range = new HSSFCellRangeAddress();
       cell_range.addADDRStructure(obj_validation.getFirstRow(), obj_validation.getFirstColumn(), obj_validation.getLastRow(), obj_validation.getLastColumn());
       dvRecord.setCellRangeAddress(cell_range);

       //add dv record
       eofLoc = sheet.findFirstRecordLocBySid( EOFRecord.sid );
       sheet.getRecords().add( eofLoc, dvRecord );
    }

    /**
     * Get the visibility state for a given column.
     * @param column - the column to get (0-based)
     * @param hidden - the visiblity state of the column
     */