public void TestFindBuiltInNameRecord()
{
// TestRRaC has multiple (3) built-in name records
// The second print titles name record has SheetNumber==4
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRRaC.xls");
NameRecord nr;
Assert.AreEqual(3, wb.Workbook.NumNames);
nr = wb.Workbook.GetNameRecord(2);
// TODO - render full row and full column refs properly
Assert.AreEqual("Sheet2!$A$1:$IV$1", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // 1:1
try
{
wb.SetRepeatingRowsAndColumns(3, 4, 5, 8, 11);
}
catch (Exception e)
{
if (e.Message.Equals("Builtin (7) already exists for sheet (4)"))
{
// there was a problem in the code which locates the existing print titles name record
throw new Exception("Identified bug 45720b");
}
throw e;
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(3, wb.Workbook.NumNames);
nr = wb.Workbook.GetNameRecord(2);
Assert.AreEqual("Sheet2!E:F,Sheet2!$A$9:$IV$12", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // E:F,9:12
}
private static void ConfirmFunc(string formula, int expPtgArraySize, bool isVarArgFunc, int funcIx)
{
Ptg[] ptgs = Parse(formula);
Ptg ptgF = ptgs[ptgs.Length - 1]; // func is last RPN token in all these formulas
// Check critical things in the Ptg array encoding.
if (!(ptgF is AbstractFunctionPtg))
{
throw new Exception("function token missing");
}
AbstractFunctionPtg func = (AbstractFunctionPtg)ptgF;
if (func.FunctionIndex == 255)
{
throw new AssertionException("Failed to recognise built-in function in formula '"
+ formula + "'");
}
Assert.AreEqual(expPtgArraySize, ptgs.Length);
Assert.AreEqual(funcIx, func.FunctionIndex);
Type expCls = isVarArgFunc ? typeof(FuncVarPtg) : typeof(FuncPtg);
Assert.AreEqual(expCls, ptgF.GetType());
// check that Parsed Ptg array Converts back to formula text OK
HSSFWorkbook book = new HSSFWorkbook();
string reRenderedFormula = HSSFFormulaParser.ToFormulaString(book, ptgs);
Assert.AreEqual(formula, reRenderedFormula);
}
private String FormatValue(Object value)
{
if (value is Ptg[])
{
Ptg[] ptgs = (Ptg[])value;
return(HSSFFormulaParser.ToFormulaString(_book, ptgs));
}
if (value is NumberEval)
{
NumberEval ne = (NumberEval)value;
return(ne.StringValue);
}
if (value is StringEval)
{
StringEval se = (StringEval)value;
return("'" + se.StringValue + "'");
}
if (value is BoolEval)
{
BoolEval be = (BoolEval)value;
return(be.StringValue);
}
if (value == BlankEval.instance)
{
return("#BLANK#");
}
if (value is ErrorEval)
{
ErrorEval ee = (ErrorEval)value;
return(ErrorEval.GetText(ee.ErrorCode));
}
throw new ArgumentException("Unexpected value class ("
+ value.GetType().Name + ")");
}
public void TestSpaceAtStartOfFormula()
{
// Simulating cell formula of "= 4" (note space)
// The same Ptg array can be observed if an excel file is1 saved with that exact formula
AttrPtg spacePtg = AttrPtg.CreateSpace(AttrPtg.SpaceType.SPACE_BEFORE, 1);
Ptg[] ptgs = { spacePtg, new IntPtg(4), };
String formulaString;
try
{
formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
}
catch (InvalidOperationException e)
{
if (e.Message.Equals("too much stuff left on the stack", StringComparison.InvariantCultureIgnoreCase))
{
throw new AssertFailedException("Identified bug 44609");
}
// else some unexpected error
throw e;
}
// FormulaParser strips spaces anyway
Assert.AreEqual("4", formulaString);
ptgs = new Ptg[] { new IntPtg(3), spacePtg, new IntPtg(4), spacePtg, AddPtg.instance, };
formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
Assert.AreEqual("3+4", formulaString);
}
private String ToFormulaString(Ptg[] ParsedExpression)
{
if (ParsedExpression == null)
{
return(null);
}
return(HSSFFormulaParser.ToFormulaString(workbook, ParsedExpression));
}
protected internal static String ToFormulaString(Ptg[] parsedExpression, HSSFWorkbook workbook)
{
if (parsedExpression == null || parsedExpression.Length == 0)
{
return(null);
}
return(HSSFFormulaParser.ToFormulaString(workbook, parsedExpression));
}
protected internal String ToFormulaString(Ptg[] ParsedExpression)
{
if (ParsedExpression == null)
{
return(null);
}
return(HSSFFormulaParser.ToFormulaString(this.workbook, ParsedExpression));
}
public void TestToFormulaStringZeroArgFunction()
{
HSSFWorkbook book = new HSSFWorkbook();
Ptg[] ptgs =
{
new FuncPtg(10),
};
Assert.AreEqual("NA()", HSSFFormulaParser.ToFormulaString(book, ptgs));
}
public void TestParseSumIfSum()
{
String formulaString;
Ptg[] ptgs;
ptgs = ParseFormula("sum(5, 2, if(3>2, sum(A1:A2), 6))");
formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
Assert.AreEqual("SUM(5,2,IF(3>2,SUM(A1:A2),6))", formulaString);
ptgs = ParseFormula("if(1<2,sum(5, 2, if(3>2, sum(A1:A2), 6)),4)");
formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
Assert.AreEqual("IF(1<2,SUM(5,2,IF(3>2,SUM(A1:A2),6)),4)", formulaString);
}
private static String ShiftAllColumnsBy1(String formula)
{
int letUsShiftColumn1By1Column = 1;
HSSFWorkbook wb = null;
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, wb);
for (int i = 0; i < ptgs.Length; i++)
{
Ptg ptg = ptgs[i];
if (ptg is AreaPtg)
{
AreaPtg aptg = (AreaPtg)ptg;
aptg.FirstColumn = ((short)(aptg.FirstColumn + letUsShiftColumn1By1Column));
aptg.LastColumn = ((short)(aptg.LastColumn + letUsShiftColumn1By1Column));
}
}
String newFormula = HSSFFormulaParser.ToFormulaString(wb, ptgs);
return(newFormula);
}
public void TestTooFewOperandArgs()
{
// Simulating badly encoded cell formula of "=/1"
// Not sure if Excel could ever produce this
Ptg[] ptgs =
{
// Excel would probably have put tMissArg here
new IntPtg(1),
DividePtg.instance,
};
try
{
HSSFFormulaParser.ToFormulaString(null, ptgs);
Assert.Fail("Expected exception was not thrown");
}
catch (InvalidOperationException e)
{
// expected during successful test
Assert.IsTrue(e.Message.StartsWith("Too few arguments supplied to operation"));
}
}
protected static void CopyRow(int blockRowCount, int tagRowBlockIndex, HSSFRow srcRow, HSSFRow tagRow)
{
tagRow.Height = srcRow.Height;
//tagRow.RowStyle = srcRow.RowStyle;
for (int i = 0; i < srcRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
HSSFCell srcCell = (HSSFCell)srcRow.GetCell(i);
HSSFCell tagCell = (HSSFCell)tagRow.CreateCell(i);
// If the old cell is null jump to next cell
if (srcCell == null)
{
continue;
}
// Copy style from old cell and apply to new cell
tagCell.CellStyle = srcCell.CellStyle;
// If there is a cell comment, copy
if (tagCell.CellComment != null)
{
tagCell.CellComment = srcCell.CellComment;
}
// If there is a cell hyperlink, copy
if (srcCell.Hyperlink != null)
{
tagCell.Hyperlink = srcCell.Hyperlink;
}
// Set the cell data type
tagCell.SetCellType(srcCell.CellType);
// Set the cell data value
switch (srcCell.CellType)
{
case CellType.BLANK:
tagCell.SetCellValue(srcCell.StringCellValue);
break;
case CellType.BOOLEAN:
tagCell.SetCellValue(srcCell.BooleanCellValue);
break;
case CellType.ERROR:
tagCell.SetCellErrorValue(srcCell.ErrorCellValue);
break;
case CellType.FORMULA:
int sheetIndex = srcRow.Sheet.Workbook.GetSheetIndex(srcRow.Sheet);
Ptg[] ptgs = HSSFFormulaParser.Parse(srcCell.CellFormula, srcRow.Sheet.Workbook as HSSFWorkbook, FormulaType.CELL, sheetIndex);
foreach (Ptg ptg in ptgs)
{
if (ptg is RefPtgBase)
{
RefPtgBase refptg = ptg as RefPtgBase;
if (refptg.Row >= srcRow.RowNum - tagRowBlockIndex && refptg.Row <= srcRow.RowNum - tagRowBlockIndex + blockRowCount)
{
refptg.Row += tagRow.RowNum - srcRow.RowNum;
}
}
else if (ptg is AreaPtgBase)
{
AreaPtgBase aptg = ptg as AreaPtgBase;
if (aptg.FirstRow >= srcRow.RowNum - tagRowBlockIndex && aptg.FirstRow <= srcRow.RowNum - tagRowBlockIndex + blockRowCount)
{
aptg.FirstRow += tagRow.RowNum - srcRow.RowNum;
aptg.LastRow += tagRow.RowNum - srcRow.RowNum;
}
}
}
tagCell.CellFormula = HSSFFormulaParser.ToFormulaString(srcRow.Sheet.Workbook as HSSFWorkbook, ptgs);
break;
case CellType.NUMERIC:
tagCell.SetCellValue(srcCell.NumericCellValue);
break;
case CellType.STRING:
tagCell.SetCellValue(srcCell.RichStringCellValue);
break;
case CellType.Unknown:
tagCell.SetCellValue(srcCell.StringCellValue);
break;
}
}
}
public void TestFormulas()
{
FormulaRecord[] fRecs = mockListen.GetFormulaRecords();
// Check our formula records
Assert.AreEqual(6, fRecs.Length);
InternalWorkbook stubWB = listener.GetStubWorkbook();
Assert.IsNotNull(stubWB);
HSSFWorkbook stubHSSF = listener.GetStubHSSFWorkbook();
Assert.IsNotNull(stubHSSF);
// Check these stubs have the right stuff on them
Assert.AreEqual("Sheet1", stubWB.GetSheetName(0));
Assert.AreEqual("S2", stubWB.GetSheetName(1));
Assert.AreEqual("Sh3", stubWB.GetSheetName(2));
// Check we can Get the formula without breaking
for (int i = 0; i < fRecs.Length; i++)
{
HSSFFormulaParser.ToFormulaString(stubHSSF, fRecs[i].ParsedExpression);
}
// Peer into just one formula, and check that
// all the ptgs give back the right things
Ptg[] ptgs = fRecs[0].ParsedExpression;
Assert.AreEqual(1, ptgs.Length);
Assert.IsTrue(ptgs[0] is Ref3DPtg);
Ref3DPtg ptg = (Ref3DPtg)ptgs[0];
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(stubHSSF);
Assert.AreEqual("Sheet1!A1", ptg.ToFormulaString(book));
// Now check we Get the right formula back for
// a few sample ones
FormulaRecord fr;
// Sheet 1 A2 is on same sheet
fr = fRecs[0];
Assert.AreEqual(1, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("Sheet1!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Sheet 1 A5 is to another sheet
fr = fRecs[3];
Assert.AreEqual(4, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("'S2'!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Sheet 1 A7 is to another sheet, range
fr = fRecs[5];
Assert.AreEqual(6, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("SUM(Sh3!A1:A4)", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Now, load via Usermodel and re-check
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("3dFormulas.xls");
Assert.AreEqual("Sheet1!A1", wb.GetSheetAt(0).GetRow(1).GetCell(0).CellFormula);
Assert.AreEqual("SUM(Sh3!A1:A4)", wb.GetSheetAt(0).GetRow(6).GetCell(0).CellFormula);
}
/// <summary>
/// Process an HSSF Record. Called when a record occurs in an HSSF file.
/// </summary>
/// <param name="record"></param>
public void ProcessRecord(Record record)
{
String thisText = null;
int thisRow = -1;
switch (record.Sid)
{
case BoundSheetRecord.sid:
BoundSheetRecord sr = (BoundSheetRecord)record;
sheetNames.Add(sr.Sheetname);
break;
case BOFRecord.sid:
BOFRecord bof = (BOFRecord)record;
if (bof.Type == BOFRecordType.Worksheet)
{
sheetNum++;
rowNum = -1;
if (includeSheetNames)
{
if (text.Length > 0)
{
text.Append("\n");
}
text.Append(sheetNames[sheetNum]);
}
}
break;
case SSTRecord.sid:
sstRecord = (SSTRecord)record;
break;
case FormulaRecord.sid:
FormulaRecord frec = (FormulaRecord)record;
thisRow = frec.Row;
if (formulasNotResults)
{
thisText = HSSFFormulaParser.ToFormulaString((HSSFWorkbook)null, frec.ParsedExpression);
}
else
{
if (frec.HasCachedResultString)
{
// Formula result is a string
// This is stored in the next record
outputNextStringValue = true;
nextRow = frec.Row;
}
else
{
thisText = FormatNumberDateCell(frec, frec.Value);
}
}
break;
case StringRecord.sid:
if (outputNextStringValue)
{
// String for formula
StringRecord srec = (StringRecord)record;
thisText = srec.String;
thisRow = nextRow;
outputNextStringValue = false;
}
break;
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord)record;
thisRow = lrec.Row;
thisText = lrec.Value;
break;
case LabelSSTRecord.sid:
LabelSSTRecord lsrec = (LabelSSTRecord)record;
thisRow = lsrec.Row;
if (sstRecord == null)
{
throw new Exception("No SST record found");
}
thisText = sstRecord.GetString(lsrec.SSTIndex).ToString();
break;
case NoteRecord.sid:
NoteRecord nrec = (NoteRecord)record;
thisRow = nrec.Row;
// TODO: Find object to match nrec.GetShapeId()
break;
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord)record;
thisRow = numrec.Row;
thisText = FormatNumberDateCell(numrec, numrec.Value);
break;
default:
break;
}
if (thisText != null)
{
if (thisRow != rowNum)
{
rowNum = thisRow;
if (text.Length > 0)
{
text.Append("\n");
}
}
else
{
text.Append("\t");
}
text.Append(thisText);
}
}
/// <summary>
/// Main HSSFListener method, processes events, and outputs the
/// strings as the file is processed
/// </summary>
public void ProcessRecord(Record record)
{
var thisRow = -1;
string thisStr = null;
switch (record.Sid)
{
case BoundSheetRecord.sid:
_boundSheetRecords.Add(record);
break;
case BOFRecord.sid:
var br = (BOFRecord)record;
if (br.Type == BOFRecord.TYPE_WORKSHEET)
{
// Create sub workbook if required
if (_workbookBuildingListener != null && _stubWorkbook == null)
{
_stubWorkbook = _workbookBuildingListener.GetStubHSSFWorkbook();
}
// Output the worksheet name
// Works by ordering the BSRs by the location of
// their BOFRecords, and then knowing that we
// process BOFRecords in byte offset order
if (_orderedBsRs == null)
{
_orderedBsRs = BoundSheetRecord.OrderByBofPosition(_boundSheetRecords);
}
}
break;
case SSTRecord.sid:
_sstRecord = (SSTRecord)record;
break;
case BlankRecord.sid:
var brec = (BlankRecord)record;
thisRow = brec.Row;
thisStr = "";
break;
case BoolErrRecord.sid:
var berec = (BoolErrRecord)record;
thisRow = berec.Row;
thisStr = "";
break;
case FormulaRecord.sid:
var frec = (FormulaRecord)record;
thisRow = frec.Row;
if (OutputFormulaValues)
{
if (double.IsNaN(frec.Value))
{
// Formula result is a string
// This is stored in the next record
_outputNextStringRecord = true;
_nextRow = frec.Row;
}
else
{
thisStr = _formatListener.FormatNumberDateCell(frec);
}
}
else
{
thisStr = HSSFFormulaParser.ToFormulaString(_stubWorkbook, frec.ParsedExpression);
}
break;
case StringRecord.sid:
if (_outputNextStringRecord)
{
// String for formula
var srec = (StringRecord)record;
thisStr = srec.String;
thisRow = _nextRow;
_outputNextStringRecord = false;
}
break;
case LabelRecord.sid:
var lrec = (LabelRecord)record;
thisRow = lrec.Row;
thisStr = lrec.Value;
break;
case LabelSSTRecord.sid:
var lsrec = (LabelSSTRecord)record;
thisRow = lsrec.Row;
if (_sstRecord == null)
{
thisStr = "(No SST Record, can't identify string)";
}
else
{
thisStr = _sstRecord.GetString(lsrec.SSTIndex).ToString();
}
break;
case NoteRecord.sid:
var nrec = (NoteRecord)record;
thisRow = nrec.Row;
thisStr = "";
break;
case NumberRecord.sid:
var numrec = (NumberRecord)record;
thisRow = numrec.Row;
// Format
thisStr = _formatListener.FormatNumberDateCell(numrec);
break;
case RKRecord.sid:
var rkrec = (RKRecord)record;
thisRow = rkrec.Row;
thisStr = "";
break;
}
// Handle new row
if (thisRow != -1 && thisRow != _lastRowNumber)
{
_row = new List <string>();
}
// Handle missing column
if (record is MissingCellDummyRecord)
{
var mc = (MissingCellDummyRecord)record;
thisRow = mc.Row;
thisStr = "";
}
// If we got something to print out, do so
if (thisStr != null)
{
_row.Add(thisStr);
}
// Update column and row count
if (thisRow > -1)
{
_lastRowNumber = thisRow;
}
// Handle end of row
if (record is LastCellOfRowDummyRecord)
{
// We're onto a new row
Output.Add(_row);
}
}