public void TestExtraStringRecord_bug46213()
{
FormulaRecord fr = new FormulaRecord();
fr.Value = (2.0);
StringRecord sr = new StringRecord();
sr.String = ("NA");
SharedValueManager svm = SharedValueManager.CreateEmpty();
FormulaRecordAggregate fra;
try
{
fra = new FormulaRecordAggregate(fr, sr, svm);
}
catch (RecordFormatException e)
{
if ("String record was supplied but formula record flag is not set".Equals(e.Message))
{
throw new AssertionException("Identified bug 46213");
}
throw e;
}
TestCases.HSSF.UserModel.RecordInspector.RecordCollector rc = new TestCases.HSSF.UserModel.RecordInspector.RecordCollector();
fra.VisitContainedRecords(rc);
Record[] vraRecs = rc.Records;
Assert.AreEqual(1, vraRecs.Length);
Assert.AreEqual(fr, vraRecs[0]);
}
public void TestCheckNanPreserve()
{
byte[] formulaByte = new byte[29];
formulaByte[4] = (byte)0x0F;
formulaByte[6] = (byte)0x02;
formulaByte[8] = (byte)0x07;
formulaByte[12] = (byte)0xFF;
formulaByte[13] = (byte)0xFF;
formulaByte[18] = (byte)0xE0;
formulaByte[19] = (byte)0xFC;
formulaByte[20] = (byte)0x07;
formulaByte[22] = (byte)0x1E;
formulaByte[23] = (byte)0x01;
formulaByte[25] = (byte)0x1E;
formulaByte[28] = (byte)0x06;
FormulaRecord record = new FormulaRecord(TestcaseRecordInputStream.Create(FormulaRecord.sid, formulaByte));
Assert.AreEqual(0, record.Row, "Row");
Assert.AreEqual(0, record.Column, "Column");
Assert.AreEqual(record.CachedResultType,NPOI.SS.UserModel.CellType.ERROR);
byte[] output = record.Serialize();
Assert.AreEqual(33, output.Length, "Output size"); //includes sid+recordlength
for (int i = 5; i < 13; i++)
{
Assert.AreEqual(formulaByte[i], output[i + 4], "FormulaByte NaN doesn't match");
}
}
public void TestBasic()
{
FormulaRecord f = new FormulaRecord();
f.SetCachedResultTypeString();
StringRecord s = new StringRecord();
s.String = ("abc");
FormulaRecordAggregate fagg = new FormulaRecordAggregate(f, s, SharedValueManager.CreateEmpty());
Assert.AreEqual("abc", fagg.StringValue);
}
public override Object Clone()
{
FormulaRecord rec = new FormulaRecord();
CopyBaseFields(rec);
rec.field_4_value = field_4_value;
rec.field_5_options = field_5_options;
rec.field_6_zero = field_6_zero;
rec.field_8_parsed_expr = field_8_parsed_expr.Copy();
rec.specialCachedValue = specialCachedValue;
return(rec);
}
/**
* @return the equivalent {@link Ptg} array that the formula would have, were it not shared.
*/
public Ptg[] GetFormulaTokens(FormulaRecord formula)
{
int formulaRow = formula.Row;
int formulaColumn = formula.Column;
//Sanity checks
if (!IsInRange(formulaRow, formulaColumn))
{
throw new Exception("Shared Formula Conversion: Coding Error");
}
return(ConvertSharedFormulas(field_7_parsed_expr.Tokens, formulaRow, formulaColumn));
}
public void TestCreateFormulaRecord()
{
FormulaRecord record = new FormulaRecord();
record.Column=((short)0);
//record.SetRow((short)1);
record.Row=(1);
record.XFIndex=((short)4);
Assert.AreEqual(record.Column, (short)0);
//Assert.AreEqual(record.Row,(short)1);
Assert.AreEqual((short)record.Row, (short)1);
Assert.AreEqual(record.XFIndex, (short)4);
}
/**
* @return the equivalent {@link Ptg} array that the formula would have, were it not shared.
*/
public Ptg[] GetFormulaTokens(FormulaRecord formula)
{
int formulaRow = formula.Row;
int formulaColumn = formula.Column;
//Sanity checks
if (!IsInRange(formulaRow, formulaColumn))
{
throw new Exception("Shared Formula Conversion: Coding Error");
}
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97);
return(sf.ConvertSharedFormulas(field_7_parsed_expr.Tokens, formulaRow, formulaColumn));
//return ConvertSharedFormulas(field_7_parsed_expr.Tokens, formulaRow, formulaColumn);
}
private IList TestData()
{
IList records = new ArrayList();
FormulaRecord formulaRecord = new FormulaRecord();
//UnknownRecord unknownRecord = new UnknownRecord();
BlankRecord blankRecord = new BlankRecord();
WindowOneRecord windowOneRecord = new WindowOneRecord();
formulaRecord.Row = 1;
formulaRecord.Column = 1;
blankRecord.Row = 2;
blankRecord.Column = 2;
records.Add(formulaRecord);
records.Add(blankRecord);
records.Add(windowOneRecord);
return records;
}
public void ConvertSharedFormulaRecord(FormulaRecord formula)
{
int row = formula.Row;
int column = formula.Column;
// Traverse the list of shared formulas in
// reverse order, and try to find the correct one
// for us
for (int i = 0; i < _sfrs.Length; i++)
{
SharedFormulaRecord shrd = _sfrs[i];
if (shrd.IsInRange(row, column))
{
shrd.ConvertSharedFormulaRecord(formula);
return;
}
}
// not found
handleMissingSharedFormulaRecord(formula);
}
/// <summary>
/// Initializes a new instance of the <see cref="FormulaRecordAggregate"/> class.
/// </summary>
/// <param name="formulaRec">The formula rec.</param>
/// <param name="stringRec">The string rec.</param>
/// <param name="svm">The SVM.</param>
public FormulaRecordAggregate(FormulaRecord formulaRec, StringRecord stringRec, SharedValueManager svm)
{
if (svm == null)
{
throw new ArgumentException("sfm must not be null");
}
bool hasStringRec = stringRec != null;
bool hasCachedStringFlag = formulaRec.HasCachedResultString;
if (hasStringRec != hasCachedStringFlag)
{
throw new RecordFormatException("String record was "
+ (hasStringRec ? "" : "not ") + " supplied but formula record flag is "
+ (hasCachedStringFlag ? "" : "not ") + " set");
}
if (formulaRec.IsSharedFormula)
{
svm.ConvertSharedFormulaRecord(formulaRec);
}
_formulaRecord = formulaRec;
_sharedValueManager = svm;
_stringRecord = stringRec;
}
/// <summary>
/// Initializes a new instance of the <see cref="FormulaRecordAggregate"/> class.
/// </summary>
/// <param name="formulaRec">The formula rec.</param>
/// <param name="stringRec">The string rec.</param>
/// <param name="svm">The SVM.</param>
public FormulaRecordAggregate(FormulaRecord formulaRec, StringRecord stringRec, SharedValueManager svm)
{
if (svm == null)
{
throw new ArgumentException("sfm must not be null");
}
if (formulaRec.HasCachedResultString)
{
if (stringRec == null)
{
throw new RecordFormatException("Formula record flag is set but String record was not found");
}
_stringRecord = stringRec;
}
else
{
// Usually stringRec is null here (in agreement with what the formula rec says).
// In the case where an extra StringRecord is erroneously present, Excel (2007)
// ignores it (see bug 46213).
_stringRecord = null;
}
_formulaRecord = formulaRec;
_sharedValueManager = svm;
if (formulaRec.IsSharedFormula)
{
CellReference firstCell = formulaRec.Formula.ExpReference;
if (firstCell == null)
{
HandleMissingSharedFormulaRecord(formulaRec);
}
else
{
_sharedFormulaRecord = svm.LinkSharedFormulaRecord(firstCell, this);
}
}
}
public void TestRowAggregation()
{
ArrayList records = new ArrayList();
records.Add(InternalSheet.CreateBOF());
records.Add(new DimensionsRecord());
records.Add(new RowRecord(0));
records.Add(new RowRecord(1));
FormulaRecord formulaRecord = new FormulaRecord();
formulaRecord.SetCachedResultTypeString();
records.Add(formulaRecord);
records.Add(new StringRecord());
records.Add(new RowRecord(2));
records.Add(CreateWindow2Record());
records.Add(EOFRecord.instance);
InternalSheet sheet = CreateSheet(records);
Assert.IsNotNull(sheet.GetRow(2), "Row [2] was skipped");
}
/**
* @return the equivalent {@link Ptg} array that the formula would have, were it not shared.
*/
public Ptg[] GetFormulaTokens(FormulaRecord formula)
{
int formulaRow = formula.Row;
int formulaColumn = formula.Column;
//Sanity checks
if (!IsInRange(formulaRow, formulaColumn))
{
throw new Exception("Shared Formula Conversion: Coding Error");
}
return ConvertSharedFormulas(field_7_parsed_expr.Tokens, formulaRow, formulaColumn);
}
public void TestArrayFormulas()
{
int rownum = 4;
int colnum = 4;
FormulaRecord fr = new FormulaRecord();
fr.Row=(rownum);
fr.Column=((short)colnum);
FormulaRecordAggregate agg = new FormulaRecordAggregate(fr, null, SharedValueManager.CreateEmpty());
Ptg[] ptgsForCell = { new ExpPtg(rownum, colnum) };
agg.SetParsedExpression(ptgsForCell);
String formula = "SUM(A1:A3*B1:B3)";
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, null, FormulaType.ARRAY, 0);
agg.SetArrayFormula(new CellRangeAddress(rownum, rownum, colnum, colnum), ptgs);
Assert.IsTrue(agg.IsPartOfArrayFormula);
Assert.AreEqual("E5", agg.GetArrayFormulaRange().FormatAsString());
Ptg[] ptg = agg.FormulaTokens;
String fmlaSer = FormulaRenderer.ToFormulaString(null, ptg);
Assert.AreEqual(formula, fmlaSer);
agg.RemoveArrayFormula(rownum, colnum);
Assert.IsFalse(agg.IsPartOfArrayFormula);
}
/// <summary>
/// Checks the type of the formula cached value.
/// </summary>
/// <param name="expectedTypeCode">The expected type code.</param>
/// <param name="fr">The fr.</param>
private void CheckFormulaCachedValueType(CellType expectedTypeCode, FormulaRecord fr)
{
CellType cachedValueType = fr.CachedResultType;
if (cachedValueType != expectedTypeCode)
{
throw TypeMismatch(expectedTypeCode, cachedValueType, true);
}
}
/**
* Sometimes the shared formula flag "seems" to be erroneously set, in which case there is no
* call to <tt>SharedFormulaRecord.convertSharedFormulaRecord</tt> and hence the
* <tt>parsedExpression</tt> field of this <tt>FormulaRecord</tt> will not get updated.<br/>
* As it turns out, this is not a problem, because in these circumstances, the existing value
* for <tt>parsedExpression</tt> is perfectly OK.<p/>
*
* This method may also be used for setting breakpoints to help diagnose issues regarding the
* abnormally-set 'shared formula' flags.
* (see TestValueRecordsAggregate.testSpuriousSharedFormulaFlag()).<p/>
*
* The method currently does nothing but do not delete it without finding a nice home for this
* comment.
*/
private static void handleMissingSharedFormulaRecord(FormulaRecord formula)
{
// could log an info message here since this is a fairly unusual occurrence.
formula.IsSharedFormula=(false); // no point leaving the flag erroneously set
}
public FormulaRecordAggregate CreateFormula(int row, int col)
{
FormulaRecord fr = new FormulaRecord();
fr.Row=(row);
fr.Column=((short)col);
return new FormulaRecordAggregate(fr, null, _sharedValueManager);
}
public override Object Clone()
{
FormulaRecord rec = new FormulaRecord();
CopyBaseFields(rec);
rec.field_4_value = field_4_value;
rec.field_5_options = field_5_options;
rec.field_6_zero = field_6_zero;
rec.field_8_parsed_expr = field_8_parsed_expr.Copy();
rec.specialCachedValue = specialCachedValue;
return rec;
}
public void TestWithConcat()
{
// =CHOOSE(2,A2,A3,A4)
byte[] data = {
6, 0, 68, 0,
1, 0, 1, 0, 15, 0, 0, 0, 0, 0, 0, 0, 57,
64, 0, 0, 12, 0, 12, unchecked((byte)-4), 46, 0,
30, 2, 0, // Int - 2
25, 4, 3, 0, // Attr
8, 0, 17, 0, 26, 0, // jumpTable
35, 0, // chooseOffSet
36, 1, 0, 0, unchecked((byte)-64), // Ref - A2
25, 8, 21, 0, // Attr
36, 2, 0, 0, unchecked((byte)-64), // Ref - A3
25, 8, 12, 0, // Attr
36, 3, 0, 0, unchecked((byte)-64), // Ref - A4
25, 8, 3, 0, // Attr
66, 4, 100, 0 // CHOOSE
};
RecordInputStream inp = new RecordInputStream(new MemoryStream(data));
inp.NextRecord();
FormulaRecord fr = new FormulaRecord(inp);
Ptg[] ptgs = fr.ParsedExpression;
Assert.AreEqual(9, ptgs.Length);
Assert.AreEqual(typeof(IntPtg), ptgs[0].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
Assert.AreEqual(typeof(RefPtg), ptgs[2].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[3].GetType());
Assert.AreEqual(typeof(RefPtg), ptgs[4].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[5].GetType());
Assert.AreEqual(typeof(RefPtg), ptgs[6].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[7].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ptgs[8].GetType());
FuncVarPtg choose = (FuncVarPtg)ptgs[8];
Assert.AreEqual("CHOOSE", choose.Name);
}
/**
* Note - does not return SharedFormulaRecords currently, because the corresponding formula
* records have been converted to 'unshared'. POI does not attempt to re-share formulas. On
* the other hand, there is no such conversion for array or table formulas, so this method
* returns the TABLE or ARRAY record (if it should be written after the specified
* formulaRecord.
*
* @return the TABLE or ARRAY record for this formula cell, if it is the first cell of a
* table or array region.
*/
public SharedValueRecordBase GetRecordForFirstCell(FormulaRecord formulaRecord)
{
int row = formulaRecord.Row;
int column = formulaRecord.Column;
for (int i = 0; i < _tableRecords.Length; i++)
{
TableRecord tr = _tableRecords[i];
if (tr.IsFirstCell(row, column))
{
return tr;
}
}
for (int i = 0; i < _arrayRecords.Length; i++)
{
ArrayRecord ar = _arrayRecords[i];
if (ar.IsFirstCell(row, column))
{
return ar;
}
}
return null;
}
public void TestReSerialize()
{
FormulaRecord formulaRecord = new FormulaRecord();
formulaRecord.Row = (/*setter*/1);
formulaRecord.Column = (/*setter*/(short)1);
formulaRecord.ParsedExpression = (/*setter*/new Ptg[] { new RefPtg("B$5"), });
formulaRecord.Value = (/*setter*/3.3);
byte[] ser = formulaRecord.Serialize();
Assert.AreEqual(31, ser.Length);
RecordInputStream in1 = TestcaseRecordInputStream.Create(ser);
FormulaRecord fr2 = new FormulaRecord(in1);
Assert.AreEqual(3.3, fr2.Value, 0.0);
Ptg[] ptgs = fr2.ParsedExpression;
Assert.AreEqual(1, ptgs.Length);
RefPtg rp = (RefPtg)ptgs[0];
Assert.AreEqual("B$5", rp.ToFormulaString());
}
public override Object Clone()
{
FormulaRecord rec = new FormulaRecord();
rec.field_1_row = field_1_row;
rec.field_2_column = field_2_column;
rec.field_3_xf = field_3_xf;
rec.field_4_value = field_4_value;
rec.field_5_options = field_5_options;
rec.field_6_zero = field_6_zero;
int nTokens = field_8_parsed_expr.Length;
Ptg[] ptgs = new Ptg[nTokens];
for (int i = 0; i < nTokens; i++)
{
ptgs[i] = field_8_parsed_expr[i].Copy();
}
rec.field_8_parsed_expr = ptgs;
rec.specialCachedValue = specialCachedValue;
return rec;
}
public override void Dispose()
{
_formulaRecord = null;
_sharedValueManager.Dispose();
_stringRecord = null;
}
/// <summary>
/// Sometimes the shared formula flag "seems" to be erroneously set (because the corresponding
/// SharedFormulaRecord does not exist). Normally this would leave no way of determining
/// the Ptg tokens for the formula. However as it turns out in these
/// cases, Excel encodes the unshared Ptg tokens in the right place (inside the FormulaRecord).
/// So the the only thing that needs to be done is to ignore the erroneous
/// shared formula flag.
///
/// This method may also be used for setting breakpoints to help diagnose issues regarding the
/// abnormally-set 'shared formula' flags.
/// </summary>
/// <param name="formula">The formula.</param>
private static void HandleMissingSharedFormulaRecord(FormulaRecord formula)
{
// make sure 'unshared' formula is actually available
Ptg firstToken = formula.ParsedExpression[0];
if (firstToken is ExpPtg)
{
throw new RecordFormatException(
"SharedFormulaRecord not found for FormulaRecord with (isSharedFormula=true)");
}
// could log an info message here since this is a fairly unusual occurrence.
formula.IsSharedFormula = false; // no point leaving the flag erroneously set
}
public void TestCachedValue_bug46479()
{
FormulaRecord fr0 = new FormulaRecord();
FormulaRecord fr1 = new FormulaRecord();
// Test some other cached value types
fr0.Value = (/*setter*/3.5);
Assert.AreEqual(3.5, fr0.Value, 0.0);
fr0.SetCachedResultErrorCode (HSSFErrorConstants.ERROR_REF);
Assert.AreEqual(HSSFErrorConstants.ERROR_REF, fr0.CachedErrorValue);
fr0.SetCachedResultBoolean(false);
fr1.SetCachedResultBoolean(true);
if (fr0.CachedBooleanValue == true && fr1.CachedBooleanValue == false)
{
throw new AssertionException("Identified bug 46479c");
}
Assert.AreEqual(false, fr0.CachedBooleanValue);
Assert.AreEqual(true, fr1.CachedBooleanValue);
}
public void TestExpFormula()
{
byte[] formulaByte = new byte[27];
formulaByte[4] = (byte)0x0F;
formulaByte[14] = (byte)0x08;
formulaByte[18] = (byte)0xE0;
formulaByte[19] = (byte)0xFD;
formulaByte[20] = (byte)0x05;
formulaByte[22] = (byte)0x01;
FormulaRecord record = new FormulaRecord(TestcaseRecordInputStream.Create(FormulaRecord.sid, formulaByte));
Assert.AreEqual(0, record.Row, "Row");
Assert.AreEqual(0, record.Column, "Column");
byte[] output = record.Serialize();
Assert.AreEqual(31, output.Length, "Output size"); //includes sid+recordlength
Assert.AreEqual(1, output[26], "OffSet 22");
}
private static void verifySharedFormula(FormulaRecord firstFormula, Record rec)
{
CellRangeAddress8Bit range = ((SharedValueRecordBase)rec).Range;
Assert.AreEqual(range.FirstRow, firstFormula.Row);
Assert.AreEqual(range.FirstColumn, firstFormula.Column);
}
/**
* Sometimes the shared formula flag "seems" to be erroneously Set, in which case there is no
* call to <c>SharedFormulaRecord.ConvertSharedFormulaRecord</c> and hence the
* <c>ParsedExpression</c> field of this <c>FormulaRecord</c> will not Get updated.<br/>
* As it turns out, this is not a problem, because in these circumstances, the existing value
* for <c>ParsedExpression</c> is perfectly OK.<p/>
*
* This method may also be used for Setting breakpoints to help diagnose Issues regarding the
* abnormally-Set 'shared formula' flags.
* (see TestValueRecordsAggregate.testSpuriousSharedFormulaFlag()).<p/>
*
* The method currently does nothing but do not delete it without Finding a nice home for this
* comment.
*/
static void HandleMissingSharedFormulaRecord(FormulaRecord formula)
{
// could log an info message here since this is a fairly Unusual occurrence.
}
/**
* Creates a non shared formula from the shared formula
* counter part
*/
public void ConvertSharedFormulaRecord(FormulaRecord formula)
{
//Sanity Checks
int formulaRow = formula.Row;
int formulaColumn = formula.Column;
if (!IsInRange(formulaRow, formulaColumn))
{
throw new InvalidOperationException("Shared Formula Conversion: Coding Error");
}
Ptg[] ptgs = ConvertSharedFormulas(field_7_parsed_expr, formulaRow, formulaColumn);
formula.ParsedExpression = ptgs;
//Now its not shared!
formula.IsSharedFormula=false;
}
