dvparser.java

JAVA读取excel表格里的数据,与jdbc功能相当,是一个包

/*********************************************************************
*
*      Copyright (C) 2004 Andrew Khan
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
***************************************************************************/

package jxl.biff;

import common.Assert;
import common.Logger;

import java.text.MessageFormat;
import java.util.Collection;
import java.util.Iterator;

import jxl.WorkbookSettings;
import jxl.biff.formula.ExternalSheet;
import jxl.biff.formula.FormulaException;
import jxl.biff.formula.FormulaParser;

/**
 * Class which parses the binary data associated with Data Validity (DV)
 * setting
 */
public class DVParser
{
  /**
   * The logger
   */
  private static Logger logger = Logger.getLogger(DVParser.class);

  // DV Type
  public static class DVType 
  {
    private int value;
    private String desc;
    
    private static DVType[] types = new DVType[0];
   
    DVType(int v, String d) 
    {
      value = v;
      desc = d;
      DVType[] oldtypes = types;
      types = new DVType[oldtypes.length+1];
      System.arraycopy(oldtypes, 0, types, 0, oldtypes.length);
      types[oldtypes.length] = this;
    }

    static DVType getType(int v)
    {
      DVType found = null;
      for (int i = 0 ; i < types.length && found == null ; i++)
      {
        if (types[i].value == v)
        {
          found = types[i];
        }
      }
      return found;
    }

    public int getValue() 
    {
      return value;
    }

    public String getDescription()
    {
      return desc;
    }
  }

  // Error Style
  public static class ErrorStyle
  {
    private int value;
    
    private static ErrorStyle[] types = new ErrorStyle[0];
   
    ErrorStyle(int v) 
    {
      value = v;
      ErrorStyle[] oldtypes = types;
      types = new ErrorStyle[oldtypes.length+1];
      System.arraycopy(oldtypes, 0, types, 0, oldtypes.length);
      types[oldtypes.length] = this;
    }

    static ErrorStyle getErrorStyle(int v)
    {
      ErrorStyle found = null;
      for (int i = 0 ; i < types.length && found == null ; i++)
      {
        if (types[i].value == v)
        {
          found = types[i];
        }
      }
      return found;
    }

    public int getValue() 
    {
      return value;
    }
  }

  // Conditions
  public static class Condition
  {
    private int value;
    private MessageFormat format;
    
    private static Condition[] types = new Condition[0];
   
    Condition(int v, String pattern) 
    {
      value = v;
      format = new MessageFormat(pattern);
      Condition[] oldtypes = types;
      types = new Condition[oldtypes.length+1];
      System.arraycopy(oldtypes, 0, types, 0, oldtypes.length);
      types[oldtypes.length] = this;
    }

    static Condition getCondition(int v)
    {
      Condition found = null;
      for (int i = 0 ; i < types.length && found == null ; i++)
      {
        if (types[i].value == v)
        {
          found = types[i];
        }
      }
      return found;
    }

    public int getValue() 
    {
      return value;
    }

    public String getConditionString(String s1, String s2)
    {
      return format.format(new String[] {s1, s2});
    }
  }


  // The values
  public static final DVType ANY = new DVType(0, "any");
  public static final DVType INTEGER = new DVType(1, "int");
  public static final DVType DECIMAL = new DVType(2, "dec");
  public static final DVType LIST = new DVType(3, "list");
  public static final DVType DATE = new DVType(4, "date");
  public static final DVType TIME = new DVType(5, "time");
  public static final DVType TEXT_LENGTH = new DVType(6, "strlen");
  public static final DVType FORMULA = new DVType(7, "form");

  // The error styles
  public static final ErrorStyle STOP = new ErrorStyle(0);
  public static final ErrorStyle WARNING = new ErrorStyle(1);
  public static final ErrorStyle INFO = new ErrorStyle(2);

  // The conditions
  public static final Condition BETWEEN = new Condition(0, "{0} <= x <= {1}");
  public static final Condition NOT_BETWEEN = 
    new Condition(1, "!({0} <= x <= {1}");
  public static final Condition EQUAL = new Condition(2, "x == {0}");
  public static final Condition NOT_EQUAL = new Condition(3, "x != {0}");
  public static final Condition GREATER_THAN = new Condition(4, "x > {0}");
  public static final Condition LESS_THAN = new Condition(5, "x < {0}");
  public static final Condition GREATER_EQUAL = new Condition(6, "x >= {0}");
  public static final Condition LESS_EQUAL = new Condition(7, "x <= {0}");

  // The masks
  private static int STRING_LIST_GIVEN_MASK = 0x80;
  private static int EMPTY_CELLS_ALLOWED_MASK = 0x100;
  private static int SUPPRESS_ARROW_MASK = 0x200;
  private static int SHOW_PROMPT_MASK = 0x40000;
  private static int SHOW_ERROR_MASK = 0x80000;

  /**
   * The type
   */
  private DVType type;

  /**
   * The error style
   */
  private ErrorStyle errorStyle;

  /**
   * The condition
   */
  private Condition condition;

  /**
   * String list option
   */
  private boolean stringListGiven;

  /**
   * Empty cells allowed
   */
  private boolean emptyCellsAllowed;

  /**
   * Suppress arrow
   */
  private boolean suppressArrow;

  /**
   * Show prompt
   */
  private boolean showPrompt;

  /**
   * Show error
   */
  private boolean showError;

  /**
   * The title of the prompt box
   */
  private String promptTitle;

  /**
   * The title of the error box
   */
  private String errorTitle;

  /**
   * The text of the prompt box
   */
  private String promptText;

  /**
   * The text of the error box
   */
  private String errorText;

  /**
   * The first formula
   */
  private FormulaParser formula1;

  /**
   * The first formula string
   */
  private String formula1String;

  /**
   * The second formula
   */
  private FormulaParser formula2;

  /**
   * The second formula string
   */
  private String formula2String;

  /**
   * The column number of the cell at the top left of the range
   */
  private int column1;

  /**
   * The row number of the cell at the top left of the range
   */
  private int row1;

  /**
   * The column index of the cell at the bottom right
   */
  private int column2;

  /**
   * The row index of the cell at the bottom right
   */
  private int row2;

  /**
   * Constructor
   */
  public DVParser(byte[] data, 
                  ExternalSheet es, 
                  WorkbookMethods nt,
                  WorkbookSettings ws) throws FormulaException
  {
    Assert.verify(nt != null);

    int options = IntegerHelper.getInt(data[0], data[1], data[2], data[3]);

    int typeVal = options & 0xf;
    type = DVType.getType(typeVal);

    int errorStyleVal = (options & 0x70) >> 4;
    errorStyle = ErrorStyle.getErrorStyle(errorStyleVal);

    int conditionVal = (options & 0xf00000) >> 20;
    condition = Condition.getCondition(conditionVal);

    stringListGiven = (options & STRING_LIST_GIVEN_MASK) != 0;
    emptyCellsAllowed = (options & EMPTY_CELLS_ALLOWED_MASK) != 0;
    suppressArrow = (options & SUPPRESS_ARROW_MASK) != 0;
    showPrompt = (options & SHOW_PROMPT_MASK) != 0;
    showError = (options & SHOW_ERROR_MASK) != 0;

    int pos = 4;
    int length = IntegerHelper.getInt(data[pos], data[pos+1]);
    if (length > 0 && data[pos + 2] == 0)
    {
      promptTitle = StringHelper.getString(data, length, pos + 3, ws);
      pos += length + 3;
    }
    else if (length > 0)
    {
      promptTitle = StringHelper.getUnicodeString(data, length, pos + 3);
      pos += length * 2 + 3;
    }
    else
    {
      pos += 2;
    }

    length = IntegerHelper.getInt(data[pos], data[pos+1]);
    if (length > 0 && data[pos + 2] == 0)
    {
      errorTitle = StringHelper.getString(data, length, pos + 3, ws);
      pos += length + 3;
    }
    else if (length > 0)
    {
      errorTitle = StringHelper.getUnicodeString(data, length, pos + 3);
      pos += length * 2 + 3;
    }
    else
    {
      pos += 2;
    }

    length = IntegerHelper.getInt(data[pos], data[pos+1]);
    if (length > 0 && data[pos + 2] == 0)
    {
      promptText = StringHelper.getString(data, length, pos + 3, ws);
      pos += length + 3;
    }
    else if (length > 0)
    {
      promptText = StringHelper.getUnicodeString(data, length, pos + 3);
      pos += length * 2 + 3;
    }
    else
    {
      pos += 2;
    }

    length = IntegerHelper.getInt(data[pos], data[pos+1]);
    if (length > 0 && data[pos + 2] == 0)
    {
      errorText = StringHelper.getString(data, length, pos + 3, ws);
      pos += length + 3;
    }
    else if (length > 0)
    {
      errorText = StringHelper.getUnicodeString(data, length, pos + 3);
      pos += length * 2 + 3;
    }
    else
    {
      pos += 2;
    }

    int formulaLength = IntegerHelper.getInt(data[pos], data[pos+1]);
    pos += 4;
    if (formulaLength != 0)
    {
      byte[] tokens = new byte[formulaLength];
      System.arraycopy(data, pos, tokens, 0, formulaLength);
      formula1 = new FormulaParser(tokens, null, es, nt,ws);
      formula1.parse();
      pos += formulaLength;
    }

    formulaLength = IntegerHelper.getInt(data[pos], data[pos+1]);
    pos += 4;
    if (formulaLength != 0)
    {
      byte[] tokens = new byte[formulaLength];
      System.arraycopy(data, pos, tokens, 0, formulaLength);
      formula2 = new FormulaParser(tokens, null, es, nt, ws);
      formula2.parse();
      pos += formulaLength;
    }

    pos += 2;

    row1 = IntegerHelper.getInt(data[pos], data[pos+1]);
    pos += 2;

    row2 = IntegerHelper.getInt(data[pos], data[pos+1]);
    pos += 2;

    column1 = IntegerHelper.getInt(data[pos], data[pos+1]);
    pos += 2;

    column2 = IntegerHelper.getInt(data[pos], data[pos+1]);
    pos += 2;
  }

  /**
   * Constructor called when creating a data validation from the API
   */
  public DVParser(Collection strings)
  {
    type = LIST;
    errorStyle = STOP;