/**
* If this formula was on an imported sheet, check that
* cell references to another sheet are warned appropriately
*
* @return TRUE if this formula was able to be imported, FALSE otherwise
*/
public bool handleImportedCellReferences(ExternalSheet es,
WorkbookMethods mt,
WorkbookSettings ws)
{
try
{
if (parser == null)
{
byte[] formulaData = formula.getFormulaData();
byte[] formulaBytes = new byte[formulaData.Length - 16];
System.Array.Copy(formulaData, 16,
formulaBytes, 0, formulaBytes.Length);
parser = new FormulaParser(formulaBytes,
this,
es, mt, ws);
parser.parse();
}
return(parser.handleImportedCellReferences());
}
catch (FormulaException e)
{
//logger.warn("cannot import formula: " + e.Message);
return(false);
}
}
/**
* Constructs this object from a string
*
* @param s the string
* @param w the external sheet
* @exception FormulaException
*/
public CellReference3d(string s, ExternalSheet w)
{
workbook = w;
columnRelative = true;
rowRelative = true;
// Get the cell details
int sep = s.IndexOf('!');
string cellString = s.Substring(sep + 1);
column = CellReferenceHelper.getColumn(cellString);
row = CellReferenceHelper.getRow(cellString);
// Get the sheet index
string sheetName = s.Substring(0, sep);
// Remove single quotes, if they exist
if (sheetName[0] == '\'' && sheetName[sheetName.Length - 1] == '\'')
{
sheetName = sheetName.Substring(1, sheetName.Length - 1);
}
sheet = w.getExternalSheetIndex(sheetName);
if (sheet < 0)
{
throw new FormulaException(FormulaException.SHEET_REF_NOT_FOUND, sheetName);
}
}
/// <summary> Gets the fully qualified cell reference given the column, row
/// external sheet reference etc
///
/// </summary>
/// <param name="">sheet
/// </param>
/// <param name="">column
/// </param>
/// <param name="">row
/// </param>
/// <param name="">workbook
/// </param>
/// <param name="">buf
/// </param>
public static void getCellReference(int sheet, int column, int row, ExternalSheet workbook, System.Text.StringBuilder buf)
{
buf.Append('\'');
buf.Append(workbook.getExternalSheetName(sheet));
buf.Append('\'');
buf.Append('!');
getCellReference(column, row, buf);
}
/**
* Constructor
* @param f
* @param ws
*/
public StringFormulaParser(string f, ExternalSheet es, WorkbookMethods nt, WorkbookSettings ws, ParseContext pc)
{
formula = f;
settings = ws;
externalSheet = es;
nameTable = nt;
parseContext = pc;
}
/**
* Constructor which creates the parse tree out of the string
*
* @param form the formula string
* @param es the external sheet handle
* @param nt the name table
* @param ws the workbook settings
* @param pc the context of the parse
*/
public FormulaParser(string form,
ExternalSheet es,
WorkbookMethods nt,
WorkbookSettings ws,
ParseContext pc)
{
parser = new StringFormulaParser(form, es, nt, ws, pc);
}
/**
* Constructs this object from the raw data
*
* @param t the basic number formula record
* @param fr the formatting records
* @param es the external sheet
* @param nt the name table
* @param nf flag indicating whether the 1904 date system is in use
* @param si the sheet
*/
public DateFormulaRecord(NumberFormulaRecord t, FormattingRecords fr,
ExternalSheet es, WorkbookMethods nt,
bool nf, SheetImpl si)
: base(t, t.getXFIndex(), fr, nf, si)
{
externalSheet = es;
nameTable = nt;
data = t.getFormulaData();
}
/**
* Gets the fully qualified cell reference given the column, row
* external sheet reference etc
*
* @param sheet
* @param column
* @param row
* @param workbook
* @return the cell reference in the form 'Sheet 1'!A1
*/
public static string getCellReference
(int sheet, int column, int row,
ExternalSheet workbook)
{
StringBuilder sb = new StringBuilder();
getCellReference(sheet, column, row, workbook, sb);
return(sb.ToString());
}
/// <summary> Constructs this object from the raw data
///
/// </summary>
/// <param name="t">the raw data
/// </param>
/// <param name="fr">the formatting records
/// </param>
/// <param name="es">the external sheet
/// </param>
/// <param name="nt">the name table
/// </param>
/// <param name="si">the sheet
/// </param>
public ErrorFormulaRecord(Record t, FormattingRecords fr, ExternalSheet es, WorkbookMethods nt, SheetImpl si) : base(t, fr, si)
{
externalSheet = es;
nameTable = nt;
data = getRecord().Data;
Assert.verify(data[6] == 2);
errorCode = data[8];
}
/// <summary> Constructor which creates the parse tree out of tokens
///
/// </summary>
/// <param name="tokens">the list of parsed tokens
/// </param>
/// <param name="rt">the cell containing the formula
/// </param>
/// <param name="es">a handle to the external sheet
/// </param>
/// <param name="nt">a handle to the name table
/// </param>
/// <param name="ws">the workbook settings
/// </param>
/// <exception cref=""> FormulaException
/// </exception>
public FormulaParser(sbyte[] tokens, Cell rt, ExternalSheet es, WorkbookMethods nt, WorkbookSettings ws)
{
// A null workbook bof means that it is a writable workbook and therefore
// must be biff8
if (es.WorkbookBof != null && !es.WorkbookBof.isBiff8())
{
throw new FormulaException(FormulaException.biff8Supported);
}
parser = new TokenFormulaParser(tokens, rt, es, nt, ws);
}
/// <summary> Constructor</summary>
public TokenFormulaParser(sbyte[] data, Cell c, ExternalSheet es, WorkbookMethods nt, WorkbookSettings ws)
{
tokenData = data;
pos = 0;
relativeTo = c;
workbook = es;
nameTable = nt;
tokenStack = new Stack();
settings = ws;
}
/**
* Constructs this number
*
* @param t the data
* @param excelFile the excel biff data
* @param v the errorCode
* @param fr the formatting records
* @param es the external sheet
* @param nt the name table
* @param si the sheet
*/
public SharedErrorFormulaRecord(Record t,
File excelFile,
int ec,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si)
: base(t, fr, es, nt, si, excelFile.getPos())
{
errorCode = ec;
}
/**
* Gets the fully qualified cell reference given the column, row
* external sheet reference etc
*
* @param sheet
* @param column
* @param row
* @param workbook
* @param buf
*/
public static void getCellReference
(int sheet, int column, int row,
ExternalSheet workbook, StringBuilder buf)
{
// Quotes are added by the WorkbookParser
string name = workbook.getExternalSheetName(sheet);
buf.Append(StringHelper.replace(name, "\'", "\'\'"));
buf.Append(sheetInd);
getCellReference(column, row, buf);
}
/**
* Constructs this number
*
* @param t the data
* @param excelFile the excel biff data
* @param v the errorCode
* @param fr the formatting records
* @param es the external sheet
* @param nt the name table
* @param si the sheet
*/
public SharedErrorFormulaRecord(Record t,
File excelFile,
int ec,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si)
: base(t,fr,es,nt,si,excelFile.getPos())
{
errorCode = ec;
}
/**
* Constructor
*/
public DataValiditySettingsRecord(Record t,
ExternalSheet es,
WorkbookMethods wm,
WorkbookSettings ws)
: base(t)
{
data = t.getData();
externalSheet = es;
workbook = wm;
workbookSettings = ws;
}
/**
* Constructs this number
*
* @param t the data
* @param excelFile the excel biff data
* @param v the value
* @param fr the formatting records
* @param es the external sheet
* @param nt the name table
* @param si the sheet
*/
public SharedBooleanFormulaRecord(Record t,
File excelFile,
bool v,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si)
: base(t,fr,es,nt,si,excelFile.getPos())
{
value = v;
}
/**
* Constructs this string formula
*
* @param t the record
* @param excelFile the excel file
* @param fr the formatting record
* @param es the external sheet
* @param nt the workbook
* @param si the sheet
* @param dummy the overload indicator
*/
public SharedStringFormulaRecord(Record t,
File excelFile,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si,
EmptyString dummy)
: base(t, fr, es, nt, si, excelFile.getPos())
{
value = string.Empty;
}
/**
* Constructs this number
*
* @param t the data
* @param excelFile the excel biff data
* @param v the value
* @param fr the formatting records
* @param es the external sheet
* @param nt the name table
* @param si the sheet
*/
public SharedBooleanFormulaRecord(Record t,
File excelFile,
bool v,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si)
: base(t, fr, es, nt, si, excelFile.getPos())
{
value = v;
}
/**
* Constructor
*/
public DataValiditySettingsRecord(Record t,
ExternalSheet es,
WorkbookMethods wm,
WorkbookSettings ws)
: base(t)
{
data = t.getData();
externalSheet = es;
workbook = wm;
workbookSettings = ws;
}
/**
* Constructor used to create writable data validations
*/
public DataValidation(uint objId,
ExternalSheet es,
WorkbookMethods wm,
WorkbookSettings ws)
{
workbook = wm;
externalSheet = es;
workbookSettings = ws;
validitySettings = new ArrayList();
comboBoxObjectId = objId;
copied = false;
}
/**
* Constructs this number
*
* @param t the record
* @param fr the formatting records
* @param es the external sheet
* @param nt the name table
* @param si the sheet
* @param pos the position of the next record in the file
*/
public BaseSharedFormulaRecord(Record t,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si,
int pos)
: base(t, fr, si)
{
externalSheet = es;
nameTable = nt;
filePos = pos;
}
/**
* Constructs this object from the raw data
*
* @param t the raw data
* @param fr the formatting records
* @param es the external sheet
* @param nt the name table
* @param si the sheet
*/
public ErrorFormulaRecord(Record t,FormattingRecords fr,ExternalSheet es,
WorkbookMethods nt,SheetImpl si)
: base(t,fr,si)
{
externalSheet = es;
nameTable = nt;
data = getRecord().getData();
Assert.verify(data[6] == 2);
errorCode = data[8];
}
/// <summary> Constructs this object from the raw data
///
/// </summary>
/// <param name="t">the raw data
/// </param>
/// <param name="fr">the formatting records
/// </param>
/// <param name="si">the sheet
/// </param>
/// <param name="es">the sheet
/// </param>
/// <param name="nt">the name table
/// </param>
public BooleanFormulaRecord(Record t, FormattingRecords fr, ExternalSheet es, WorkbookMethods nt, SheetImpl si) : base(t, fr, si)
{
externalSheet = es;
nameTable = nt;
_Value = false;
data = getRecord().Data;
Assert.verify(data[6] != 2);
_Value = data[8] == 1?true:false;
}
/**
* Gets the fully qualified cell reference given the column, row
* external sheet reference etc
*
* @param sheet
* @param column
* @param colabs TRUE if the column is an absolute reference
* @param row
* @param rowabs TRUE if the row is an absolute reference
* @param workbook
* @param buf
*/
public static void getCellReference
(int sheet, int column, bool colabs,
int row, bool rowabs,
ExternalSheet workbook, StringBuilder buf)
{
// WorkbookParser now appends quotes and escapes apostrophes
string name = workbook.getExternalSheetName(sheet);
buf.Append(name);
buf.Append(sheetInd);
getCellReference(column, colabs, row, rowabs, buf);
}
public static String PrependSheetName(IFormulaRenderingWorkbook book, int field_1_index_extern_sheet, String cellRefText)
{
ExternalSheet externalSheet = book.GetExternalSheet(field_1_index_extern_sheet);
StringBuilder sb;
if (externalSheet != null)
{
String wbName = externalSheet.WorkbookName;
String sheetName = externalSheet.SheetName;
if (wbName != null)
{
sb = new StringBuilder(wbName.Length + sheetName.Length + cellRefText.Length + 4);
SheetNameFormatter.AppendFormat(sb, wbName, sheetName);
}
else
{
sb = new StringBuilder(sheetName.Length + cellRefText.Length + 4);
SheetNameFormatter.AppendFormat(sb, sheetName);
}
if (externalSheet is ExternalSheetRange)
{
ExternalSheetRange r = (ExternalSheetRange)externalSheet;
if (!r.FirstSheetName.Equals(r.LastSheetName))
{
sb.Append(':');
SheetNameFormatter.AppendFormat(sb, r.LastSheetName);
}
}
}
else
{
String firstSheetName = book.GetSheetFirstNameByExternSheet(field_1_index_extern_sheet);
String lastSheetName = book.GetSheetLastNameByExternSheet(field_1_index_extern_sheet);
sb = new StringBuilder(firstSheetName.Length + cellRefText.Length + 4);
if (firstSheetName.Length < 1)
{
// What excel does if sheet has been deleted
sb.Append("#REF"); // note - '!' Added just once below
}
else
{
SheetNameFormatter.AppendFormat(sb, firstSheetName);
if (!firstSheetName.Equals(lastSheetName))
{
sb.Append(':');
sb.Append(lastSheetName);
}
}
}
sb.Append('!');
sb.Append(cellRefText);
return(sb.ToString());
}
/// <summary> Constructs this object from the raw data. Creates either a
/// NumberFormulaRecord or a StringFormulaRecord depending on whether
/// this formula represents a numerical calculation or not
///
/// </summary>
/// <param name="t">the raw data
/// </param>
/// <param name="excelFile">the excel file
/// </param>
/// <param name="fr">the formatting records
/// </param>
/// <param name="es">the workbook, which contains the external sheet references
/// </param>
/// <param name="nt">the name table
/// </param>
/// <param name="si">the sheet
/// </param>
/// <param name="ws">the workbook settings
/// </param>
public FormulaRecord(Record t, File excelFile, FormattingRecords fr, ExternalSheet es, WorkbookMethods nt, SheetImpl si, WorkbookSettings ws) : base(t, fr, si)
{
sbyte[] data = getRecord().Data;
shared = false;
// Check to see if this forms part of a shared formula
int grbit = IntegerHelper.getInt(data[14], data[15]);
if ((grbit & 0x08) != 0)
{
shared = true;
if (data[6] == 0 && data[12] == -1 && data[13] == -1)
{
// It is a shared string formula
formula = new SharedStringFormulaRecord(t, excelFile, fr, es, nt, si, ws);
}
else
{
// It is a numerical formula
double Value = DoubleHelper.getIEEEDouble(data, 6);
formula = new SharedNumberFormulaRecord(t, excelFile, Value, fr, es, nt, si);
}
return;
}
// microsoft and their goddam magic values determine whether this
// is a string or a number value
if (data[6] == 0 && data[12] == -1 && data[13] == -1)
{
// we have a string
formula = new StringFormulaRecord(t, excelFile, fr, es, nt, si, ws);
}
else if (data[6] == 1 && data[12] == -1 && data[13] == -1)
{
// We have a boolean formula
// multiple values. Thanks to Frank for spotting this
formula = new BooleanFormulaRecord(t, fr, es, nt, si);
}
else if (data[6] == 2 && data[12] == -1 && data[13] == -1)
{
// The cell is in error
formula = new ErrorFormulaRecord(t, fr, es, nt, si);
}
else
{
// it is most assuredly a number
formula = new NumberFormulaRecord(t, fr, es, nt, si);
}
}
/**
* Constructs this number
*
* @param t the data
* @param excelFile the excel biff data
* @param v the value
* @param fr the formatting records
* @param es the external sheet
* @param nt the name table
* @param si the sheet
*/
public SharedNumberFormulaRecord(Record t,
File excelFile,
double v,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si)
: base(t, fr, es, nt, si, excelFile.getPos())
{
value = v;
format = defaultFormat; // format is set up later from the
// SharedFormulaRecord
}
/**
* Constructs this number
*
* @param t the data
* @param excelFile the excel biff data
* @param v the value
* @param fr the formatting records
* @param es the external sheet
* @param nt the name table
* @param si the sheet
*/
public SharedNumberFormulaRecord(Record t,
File excelFile,
double v,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si)
: base(t,fr,es,nt,si,excelFile.getPos())
{
value = v;
format = defaultFormat; // format is set up later from the
// SharedFormulaRecord
}
/**
* Constructs this object from the raw data. Used when reading in formula
* strings which evaluate to null (in the case of some IF statements)
*
* @param t the raw data
* @param fr the formatting records
* @param es the external sheet records
* @param nt the workbook
* @param si the sheet impl
* @param ws the workbook settings
*/
public StringFormulaRecord(Record t,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si)
: base(t, fr, si)
{
externalSheet = es;
nameTable = nt;
data = getRecord().getData();
value = string.Empty;
}
/**
* Constructs this object from the raw data
*
* @param t the raw data
* @param fr the formatting records
* @param si the sheet
* @param es the sheet
* @param nt the name table
*/
public BooleanFormulaRecord(Record t,FormattingRecords fr,
ExternalSheet es,WorkbookMethods nt,
SheetImpl si)
: base(t,fr,si)
{
externalSheet = es;
nameTable = nt;
value = false;
data = getRecord().getData();
Assert.verify(data[6] != 2);
value = data[8] == 1 ? true : false;
}
/**
* Constructor used when copying sheets
*
* @param dvsr the record copied from a writable sheet
*/
public DataValiditySettingsRecord(DataValiditySettingsRecord dvsr,
ExternalSheet es,
WorkbookMethods w,
WorkbookSettings ws)
: base(Type.DV)
{
workbook = w;
externalSheet = es;
workbookSettings = ws;
Assert.verify(w != null);
Assert.verify(es != null);
data = new byte[dvsr.data.Length];
System.Array.Copy(dvsr.data, 0, data, 0, data.Length);
}
/**
* Constructor invoked when parsing a string formula
*
* @param s the string to parse
* @param es the external sheet
* @exception FormulaException
*/
public ColumnRange3d(string s, ExternalSheet es)
: base(es)
{
workbook = es;
int seppos = s.LastIndexOf(":");
Assert.verify(seppos != -1);
string startcell = s.Substring(0, seppos);
string endcell = s.Substring(seppos + 1);
// Get the the start cell details
int sep = s.IndexOf('!');
string cellString = s.Substring(sep + 1, seppos);
int columnFirst = CellReferenceHelper.getColumn(cellString);
int rowFirst = 0;
// Get the sheet index
string sheetName = s.Substring(0, sep);
int sheetNamePos = sheetName.LastIndexOf(']');
// Remove single quotes, if they exist
if (sheetName[0] == '\'' &&
sheetName[sheetName.Length - 1] == '\'')
{
sheetName = sheetName.Substring(1, sheetName.Length - 1);
}
sheet = es.getExternalSheetIndex(sheetName);
if (sheet < 0)
{
throw new FormulaException(FormulaException.SHEET_REF_NOT_FOUND, sheetName);
}
// Get the last cell index
int columnLast = CellReferenceHelper.getColumn(endcell);
int rowLast = 0xffff;
bool columnFirstRelative = true;
bool rowFirstRelative = true;
bool columnLastRelative = true;
bool rowLastRelative = true;
setRangeData(sheet, columnFirst, columnLast, rowFirst, rowLast,
columnFirstRelative, rowFirstRelative,
columnLastRelative, rowLastRelative);
}
/**
* Constructor which creates the parse tree out of tokens
*
* @param tokens the list of parsed tokens
* @param rt the cell containing the formula
* @param es a handle to the external sheet
* @param nt a handle to the name table
* @param ws the workbook settings
* @param pc the parse context
* @exception FormulaException
*/
public FormulaParser(byte[] tokens,
Cell rt,
ExternalSheet es,
WorkbookMethods nt,
WorkbookSettings ws)
{
// A null workbook bof means that it is a writable workbook and therefore
// must be biff8
if (es.getWorkbookBof() != null &&
!es.getWorkbookBof().isBiff8())
{
throw new FormulaException(FormulaException.BIFF8_SUPPORTED);
}
Assert.verify(nt != null);
parser = new TokenFormulaParser(tokens,rt,es,nt,ws,
ParseContext.DEFAULT);
}
/**
* Constructor which creates the parse tree out of tokens
*
* @param tokens the list of parsed tokens
* @param rt the cell containing the formula
* @param es a handle to the external sheet
* @param nt a handle to the name table
* @param ws the workbook settings
* @param pc the parse context
* @exception FormulaException
*/
public FormulaParser(byte[] tokens,
Cell rt,
ExternalSheet es,
WorkbookMethods nt,
WorkbookSettings ws,
ParseContext pc)
{
// A null workbook bof means that it is a writable workbook and therefore
// must be biff8
if (es.getWorkbookBof() != null &&
!es.getWorkbookBof().isBiff8())
{
throw new FormulaException(FormulaException.BIFF8_SUPPORTED);
}
Assert.verify(nt != null);
parser = new TokenFormulaParser(tokens, rt, es, nt, ws, pc);
}
/**
* Constructor invoked when parsing a string formula
*
* @param s the string to parse
* @param es the external sheet
* @exception FormulaException
*/
public ColumnRange3d(string s,ExternalSheet es)
: base(es)
{
workbook = es;
int seppos = s.LastIndexOf(":");
Assert.verify(seppos != -1);
string startcell = s.Substring(0,seppos);
string endcell = s.Substring(seppos + 1);
// Get the the start cell details
int sep = s.IndexOf('!');
string cellString = s.Substring(sep + 1,seppos);
int columnFirst = CellReferenceHelper.getColumn(cellString);
int rowFirst = 0;
// Get the sheet index
string sheetName = s.Substring(0,sep);
int sheetNamePos = sheetName.LastIndexOf(']');
// Remove single quotes, if they exist
if (sheetName[0] == '\'' &&
sheetName[sheetName.Length - 1] == '\'')
{
sheetName = sheetName.Substring(1,sheetName.Length - 1);
}
sheet = es.getExternalSheetIndex(sheetName);
if (sheet < 0)
{
throw new FormulaException(FormulaException.SHEET_REF_NOT_FOUND,sheetName);
}
// Get the last cell index
int columnLast = CellReferenceHelper.getColumn(endcell);
int rowLast = 0xffff;
bool columnFirstRelative = true;
bool rowFirstRelative = true;
bool columnLastRelative = true;
bool rowLastRelative = true;
setRangeData(sheet,columnFirst,columnLast,rowFirst,rowLast,
columnFirstRelative,rowFirstRelative,
columnLastRelative,rowLastRelative);
}
/**
* Constructor
*/
public TokenFormulaParser(byte[] data,
Cell c,
ExternalSheet es,
WorkbookMethods nt,
WorkbookSettings ws,
ParseContext pc)
{
tokenData = data;
pos = 0;
relativeTo = c;
workbook = es;
nameTable = nt;
tokenStack = new Stack <ParseItem>();
settings = ws;
parseContext = pc;
Assert.verify(nameTable != null);
}
/**
* Constructs this object from the raw data
*
* @param t the raw data
* @param fr the formatting record
* @param es the external sheet
* @param nt the name table
* @param si the sheet
*/
public NumberFormulaRecord(Record t, FormattingRecords fr,
ExternalSheet es, WorkbookMethods nt,
SheetImpl si)
: base(t, fr, si)
{
externalSheet = es;
nameTable = nt;
data = getRecord().getData();
format = fr.getNumberFormat(getXFIndex());
if (format == null)
{
format = defaultFormat;
}
value = DoubleHelper.getIEEEDouble(data, 6);
}
/**
* Constructs this object from the raw data. Creates either a
* NumberFormulaRecord or a StringFormulaRecord depending on whether
* this formula represents a numerical calculation or not
*
* @param t the raw data
* @param fr the base shared formula
* @param es the workbook, which contains the external sheet references
* @param nt the workbook
* @param si the sheet
*/
public SharedFormulaRecord(Record t, BaseSharedFormulaRecord fr,
ExternalSheet es, WorkbookMethods nt,
SheetImpl si)
{
sheet = si;
byte[] data = t.getData();
firstRow = IntegerHelper.getInt(data[0], data[1]);
lastRow = IntegerHelper.getInt(data[2], data[3]);
firstCol = data[4] & 0xff;
lastCol = data[5] & 0xff;
formulas = new ArrayList();
templateFormula = fr;
tokens = new byte[data.Length - 10];
System.Array.Copy(data, 10, tokens, 0, tokens.Length);
}
/// <summary> Constructs this object from the raw data. Creates either a
/// NumberFormulaRecord or a StringFormulaRecord depending on whether
/// this formula represents a numerical calculation or not
///
/// </summary>
/// <param name="t">the raw data
/// </param>
/// <param name="fr">the base shared formula
/// </param>
/// <param name="es">the workbook, which contains the external sheet references
/// </param>
/// <param name="nt">the workbook
/// </param>
/// <param name="si">the sheet
/// </param>
public SharedFormulaRecord(Record t, BaseSharedFormulaRecord fr, ExternalSheet es, WorkbookMethods nt, SheetImpl si)
{
externalSheet = es;
nameTable = nt;
sheet = si;
sbyte[] data = t.Data;
firstRow = IntegerHelper.getInt(data[0], data[1]);
lastRow = IntegerHelper.getInt(data[2], data[3]);
firstCol = (int)(data[4] & 0xff);
lastCol = (int)(data[5] & 0xff);
formulas = new ArrayList();
templateFormula = fr;
tokens = new sbyte[data.Length - 10];
Array.Copy(data, 10, tokens, 0, tokens.Length);
}
/**
* Constructor
* @param f
* @param ws
*/
public StringFormulaParser(string f,ExternalSheet es,WorkbookMethods nt,WorkbookSettings ws,ParseContext pc)
{
formula = f;
settings = ws;
externalSheet = es;
nameTable = nt;
parseContext = pc;
}
/**
* Constructor
*
* @param es the external sheet
*/
public Area3d(ExternalSheet es)
{
workbook = es;
}
/**
* Gets the fully qualified cell reference given the column, row
* external sheet reference etc
*
* @param sheet
* @param column
* @param row
* @param workbook
* @return the cell reference in the form 'Sheet 1'!A1
*/
public static string getCellReference(int sheet,int column,int row,
ExternalSheet workbook)
{
StringBuilder sb = new StringBuilder();
getCellReference(sheet,column,row,workbook,sb);
return sb.ToString();
}
/**
* Gets the fully qualified cell reference given the column, row
* external sheet reference etc
*
* @param sheet
* @param column
* @param row
* @param workbook
* @param buf
*/
public static void getCellReference(int sheet,int column,int row,
ExternalSheet workbook,StringBuilder buf)
{
// Quotes are added by the WorkbookParser
string name = workbook.getExternalSheetName(sheet);
buf.Append(StringHelper.replace(name, "\'", "\'\'"));
buf.Append(sheetInd);
getCellReference(column,row,buf);
}
/**
* Constructor used when copying sheets
*
* @param dvsr the record copied from a writable sheet
*/
public DataValiditySettingsRecord(DataValiditySettingsRecord dvsr,
ExternalSheet es,
WorkbookMethods w,
WorkbookSettings ws)
: base(Type.DV)
{
workbook = w;
externalSheet = es;
workbookSettings = ws;
Assert.verify(w != null);
Assert.verify(es != null);
data = new byte[dvsr.data.Length];
System.Array.Copy(dvsr.data,0,data,0,data.Length);
}
/**
* Copy constructor used to copy from read to write
*/
public DataValidation(DataValidation dv,
ExternalSheet es,
WorkbookMethods wm,
WorkbookSettings ws)
{
workbook = wm;
externalSheet = es;
workbookSettings = ws;
copied = true;
validityList = new DataValidityListRecord(dv.getDataValidityList());
validitySettings = new ArrayList();
DataValiditySettingsRecord[] settings = dv.getDataValiditySettings();
for (int i = 0; i < settings.Length; i++)
validitySettings.Add(new DataValiditySettingsRecord(settings[i],externalSheet,workbook,workbookSettings));
}
public void setExternalSheet(ExternalSheet es)
{
externalSheet = es;
}
/**
* Constructor
*/
public DVParser(byte[] data,
ExternalSheet es,
WorkbookMethods nt,
WorkbookSettings ws)
{
Assert.verify(nt != null);
wasCopied = false;
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 += 3;
}
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 += 3;
}
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 += 3;
}
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 += 3;
}
int formula1Length = IntegerHelper.getInt(data[pos],data[pos + 1]);
pos += 4;
int formula1Pos = pos;
pos += formula1Length;
int formula2Length = IntegerHelper.getInt(data[pos],data[pos + 1]);
pos += 4;
int formula2Pos = pos;
pos += formula2Length;
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;
hasExtendedCellsValidation = (row1 == row2 && column1 == column2) ? false : true;
// Do the formulas
try
{
// First, create a temporary blank cell for any formula relative
// references
EmptyCell tmprt = new EmptyCell(column1,row1);
if (formula1Length != 0)
{
byte[] tokens = new byte[formula1Length];
System.Array.Copy(data,formula1Pos,tokens,0,formula1Length);
formula1 = new FormulaParser(tokens,tmprt,es,nt,ws,
ParseContext.DATA_VALIDATION);
formula1.parse();
}
if (formula2Length != 0)
{
byte[] tokens = new byte[formula2Length];
System.Array.Copy(data,formula2Pos,tokens,0,formula2Length);
formula2 = new FormulaParser(tokens,tmprt,es,nt,ws,
ParseContext.DATA_VALIDATION);
formula2.parse();
}
}
catch (FormulaException e)
{
//logger.warn(e.Message + " for cells " +
// CellReferenceHelper.getCellReference(column1,row1) + "-" +
// CellReferenceHelper.getCellReference(column2,row2));
}
}
/**
* Constructs this object from the raw data. We need to use the excelFile
* to retrieve the string record which follows this formula record
*
* @param t the raw data
* @param excelFile the excel file
* @param fr the formatting records
* @param es the external sheet records
* @param nt the workbook
* @param si the sheet impl
* @param ws the workbook settings
*/
public StringFormulaRecord(Record t,File excelFile,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si,
WorkbookSettings ws)
: base(t,fr,si)
{
externalSheet = es;
nameTable = nt;
data = getRecord().getData();
int pos = excelFile.getPos();
// Look for the string record in one of the records after the
// formula. Put a cap on it to prevent looping
Record nextRecord = excelFile.next();
int count = 0;
while (nextRecord.getType() != Type.STRING && count < 4)
{
nextRecord = excelFile.next();
count++;
}
Assert.verify(count < 4," @ " + pos);
byte[] stringData = nextRecord.getData();
// Read in any continuation records
nextRecord = excelFile.peek();
while (nextRecord.getType() == Type.CONTINUE)
{
nextRecord = excelFile.next(); // move the pointer within the data
byte[] d = new byte[stringData.Length + nextRecord.getLength() - 1];
System.Array.Copy(stringData,0,d,0,stringData.Length);
System.Array.Copy(nextRecord.getData(),1,d,
stringData.Length,nextRecord.getLength() - 1);
stringData = d;
nextRecord = excelFile.peek();
}
readString(stringData,ws);
}
/**
* Constructs this object from the raw data. Used when reading in formula
* strings which evaluate to null (in the case of some IF statements)
*
* @param t the raw data
* @param fr the formatting records
* @param es the external sheet records
* @param nt the workbook
* @param si the sheet impl
* @param ws the workbook settings
*/
public StringFormulaRecord(Record t,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si)
: base(t,fr,si)
{
externalSheet = es;
nameTable = nt;
data = getRecord().getData();
value = string.Empty;
}
/**
* Constructs this object from the raw data. Creates either a
* NumberFormulaRecord or a StringFormulaRecord depending on whether
* this formula represents a numerical calculation or not
*
* @param t the raw data
* @param fr the base shared formula
* @param es the workbook, which contains the external sheet references
* @param nt the workbook
* @param si the sheet
*/
public SharedFormulaRecord(Record t,BaseSharedFormulaRecord fr,
ExternalSheet es,WorkbookMethods nt,
SheetImpl si)
{
sheet = si;
byte[] data = t.getData();
firstRow = IntegerHelper.getInt(data[0],data[1]);
lastRow = IntegerHelper.getInt(data[2],data[3]);
firstCol = data[4] & 0xff;
lastCol = data[5] & 0xff;
formulas = new ArrayList();
templateFormula = fr;
tokens = new byte[data.Length - 10];
System.Array.Copy(data,10,tokens,0,tokens.Length);
}
/**
* Constructs this string formula
*
* @param t the record
* @param excelFile the excel file
* @param fr the formatting record
* @param es the external sheet
* @param nt the workbook
* @param si the sheet
* @param dummy the overload indicator
*/
public SharedStringFormulaRecord(Record t,
File excelFile,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si,
EmptyString dummy)
: base(t,fr,es,nt,si,excelFile.getPos())
{
value = string.Empty;
}
/**
* Constructs this string formula
*
* @param t the record
* @param excelFile the excel file
* @param fr the formatting record
* @param es the external sheet
* @param nt the workbook
* @param si the sheet
* @param ws the workbook settings
*/
public SharedStringFormulaRecord(Record t,
File excelFile,
FormattingRecords fr,
ExternalSheet es,
WorkbookMethods nt,
SheetImpl si,
WorkbookSettings ws)
: base(t,fr,es,nt,si,excelFile.getPos())
{
int pos = excelFile.getPos();
// Save the position in the excel file
int filepos = excelFile.getPos();
// Look for the string record in one of the records after the
// formula. Put a cap on it to prevent ednas
Record nextRecord = excelFile.next();
int count = 0;
while (nextRecord.getType() != Type.STRING && count < 4)
{
nextRecord = excelFile.next();
count++;
}
Assert.verify(count < 4," @ " + pos);
byte[] stringData = nextRecord.getData();
// Read in any continuation records
nextRecord = excelFile.peek();
while (nextRecord.getType() == Type.CONTINUE)
{
nextRecord = excelFile.next(); // move the pointer within the data
byte[] d = new byte[stringData.Length + nextRecord.getLength() - 1];
System.Array.Copy(stringData,0,d,0,stringData.Length);
System.Array.Copy(nextRecord.getData(),1,d,
stringData.Length,nextRecord.getLength() - 1);
stringData = d;
nextRecord = excelFile.peek();
}
int chars = IntegerHelper.getInt(stringData[0],stringData[1]);
bool unicode = false;
int startpos = 3;
if (stringData.Length == chars + 2)
{
// string might only consist of a one byte length indicator, instead
// of the more normal 2
startpos = 2;
unicode = false;
}
else if (stringData[2] == 0x1)
{
// unicode string, two byte length indicator
startpos = 3;
unicode = true;
}
else
{
// ascii string, two byte length indicator
startpos = 3;
unicode = false;
}
if (!unicode)
{
value = StringHelper.getString(stringData,chars,startpos,ws);
}
else
{
value = StringHelper.getUnicodeString(stringData,chars,startpos);
}
// Restore the position in the excel file, to enable the SHRFMLA
// record to be picked up
excelFile.setPos(filepos);
}
/**
* Constructor used to create writable data validations
*/
public DataValidation(uint objId,
ExternalSheet es,
WorkbookMethods wm,
WorkbookSettings ws)
{
workbook = wm;
externalSheet = es;
workbookSettings = ws;
validitySettings = new ArrayList();
comboBoxObjectId = objId;
copied = false;
}
/**
* Constructs this object from a string
*
* @param s the string
* @param w the external sheet
* @exception FormulaException
*/
public CellReference3d(string s,ExternalSheet w)
{
workbook = w;
columnRelative = true;
rowRelative = true;
// Get the cell details
int sep = s.IndexOf('!');
string cellString = s.Substring(sep + 1);
column = CellReferenceHelper.getColumn(cellString);
row = CellReferenceHelper.getRow(cellString);
// Get the sheet index
string sheetName = s.Substring(0,sep);
// Remove single quotes, if they exist
if (sheetName[0] == '\'' && sheetName[sheetName.Length - 1] == '\'')
sheetName = sheetName.Substring(1,sheetName.Length - 1);
sheet = w.getExternalSheetIndex(sheetName);
if (sheet < 0)
throw new FormulaException(FormulaException.SHEET_REF_NOT_FOUND,sheetName);
}
/**
* Constructs this object from the raw data
*
* @param t the raw data
* @param fr the formatting record
* @param es the external sheet
* @param nt the name table
* @param si the sheet
*/
public NumberFormulaRecord(Record t,FormattingRecords fr,
ExternalSheet es,WorkbookMethods nt,
SheetImpl si)
: base(t,fr,si)
{
externalSheet = es;
nameTable = nt;
data = getRecord().getData();
format = fr.getNumberFormat(getXFIndex());
if (format == null)
{
format = defaultFormat;
}
value = DoubleHelper.getIEEEDouble(data,6);
}
/**
* Gets the fully qualified cell reference given the column, row
* external sheet reference etc
*
* @param sheet
* @param column
* @param colabs TRUE if the column is an absolute reference
* @param row
* @param rowabs TRUE if the row is an absolute reference
* @param workbook
* @param buf
*/
public static void getCellReference(int sheet,int column,bool colabs,
int row,bool rowabs,
ExternalSheet workbook,StringBuilder buf)
{
// WorkbookParser now appends quotes and escapes apostrophes
string name = workbook.getExternalSheetName(sheet);
buf.Append(name);
buf.Append(sheetInd);
getCellReference(column,colabs,row,rowabs,buf);
}
/**
* Called by the cell value when the cell features are added to the sheet
*/
public void setCell(int col,
int row,
ExternalSheet es,
WorkbookMethods nt,
WorkbookSettings ws)
{
// If this is part of an extended cells validation, then do nothing
// as this will already have been called and parsed when the top left
// cell was added
if (hasExtendedCellsValidation)
{
return;
}
row1 = row;
row2 = row;
column1 = col;
column2 = col;
formula1 = new FormulaParser(formula1String,
es,nt,ws,
ParseContext.DATA_VALIDATION);
formula1.parse();
if (formula2String != null)
{
formula2 = new FormulaParser(formula2String,
es,nt,ws,
ParseContext.DATA_VALIDATION);
formula2.parse();
}
}
/**
* If this formula was on an imported sheet, check that
* cell references to another sheet are warned appropriately
*
* @return TRUE if this formula was able to be imported, FALSE otherwise
*/
public bool handleImportedCellReferences(ExternalSheet es,
WorkbookMethods mt,
WorkbookSettings ws)
{
try
{
if (parser == null)
{
byte[] formulaData = formula.getFormulaData();
byte[] formulaBytes = new byte[formulaData.Length - 16];
System.Array.Copy(formulaData, 16,
formulaBytes, 0, formulaBytes.Length);
parser = new FormulaParser(formulaBytes,
this,
es, mt, ws);
parser.parse();
}
return parser.handleImportedCellReferences();
}
catch (FormulaException e)
{
//logger.warn("cannot import formula: " + e.Message);
return false;
}
}
/**
* Constructor
*/
public TokenFormulaParser(byte[] data,
Cell c,
ExternalSheet es,
WorkbookMethods nt,
WorkbookSettings ws,
ParseContext pc)
{
tokenData = data;
pos = 0;
relativeTo = c;
workbook = es;
nameTable = nt;
tokenStack = new Stack<ParseItem>();
settings = ws;
parseContext = pc;
Assert.verify(nameTable != null);
}
/**
* Constructor
*
* @param rt the cell containing the formula
* @param w the list of external sheets
*/
public CellReference3d(Cell rt,ExternalSheet w)
{
relativeTo = rt;
workbook = w;
}
/**
* Constructor which creates the parse tree out of the string
*
* @param form the formula string
* @param es the external sheet handle
* @param nt the name table
* @param ws the workbook settings
* @param pc the context of the parse
*/
public FormulaParser(string form,
ExternalSheet es,
WorkbookMethods nt,
WorkbookSettings ws,
ParseContext pc)
{
parser = new StringFormulaParser(form,es,nt,ws,pc);
}