/**
* Makes sure that a formula referring to the named range parses properly
*/
private static void ConfirmParseFormula(HSSFWorkbook workbook)
{
Ptg[] ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
}
public void SetCellFormula(String formula)
{
if (IsPartOfArrayFormulaGroup)
{
NotifyArrayFormulaChanging();
}
int row = _record.Row;
int col = _record.Column;
short styleIndex = _record.XFIndex;
if (string.IsNullOrEmpty(formula))
{
NotifyFormulaChanging();
SetCellType(CellType.Blank, false, row, col, styleIndex);
return;
}
int sheetIndex = book.GetSheetIndex(_sheet);
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, book, FormulaType.Cell, sheetIndex);
SetCellType(CellType.Formula, false, row, col, styleIndex);
FormulaRecordAggregate agg = (FormulaRecordAggregate)_record;
FormulaRecord frec = agg.FormulaRecord;
frec.Options = ((short)2);
frec.Value = (0);
//only set to default if there is no extended format index already set
if (agg.XFIndex == (short)0)
{
agg.XFIndex = ((short)0x0f);
}
agg.SetParsedExpression(ptgs);
}
public void TestParse()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("externalFunctionExample.xls");
Ptg[] ptgs = HSSFFormulaParser.Parse("YEARFRAC(B1,C1)", wb);
Assert.AreEqual(4, ptgs.Length);
Assert.AreEqual(typeof(NameXPtg), ptgs[0].GetType());
wb.GetSheetAt(0).GetRow(0).CreateCell(6).CellFormula = ("YEARFRAC(C1,B1)");
#if !HIDE_UNREACHABLE_CODE
if (false)
{
// In case you fancy Checking in excel
try
{
FileStream tempFile = File.Create("testExtFunc.xls");
//FileOutputStream fout = new FileOutputStream(tempFile);
wb.Write(tempFile);
tempFile.Close();
//Console.WriteLine("check out " + tempFile.getAbsolutePath());
}
catch (IOException e)
{
throw e;
}
}
#endif
}
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);
}
public HSSFAutoFilter(string formula, HSSFWorkbook workbook)
{
//this.workbook = workbook;
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, workbook);
if (!(ptgs[0] is Area3DPtg))
{
throw new ArgumentException("incorrect formula");
}
Area3DPtg ptg = (Area3DPtg)ptgs[0];
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(ptg.ExternSheetIndex);
//look for the prior record
int loc = sheet.Sheet.FindFirstRecordLocBySid(DefaultColWidthRecord.sid);
CreateFilterModeRecord(sheet, loc + 1);
CreateAutoFilterInfoRecord(sheet, loc + 2, ptg);
//look for "_FilterDatabase" NameRecord of the sheet
NameRecord name = workbook.Workbook.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, ptg.ExternSheetIndex + 1);
if (name == null)
{
name = workbook.Workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, ptg.ExternSheetIndex + 1);
}
name.IsHiddenName = true;
name.NameDefinition = ptgs;
}
/**
* TODO - Parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
* this call will produce the wrong results if the formula Contains any cell references
* One approach might be to apply the inverse of SharedFormulaRecord.ConvertSharedFormulas(Stack, int, int)
* Note - two extra parameters (rowIx &colIx) will be required. They probably come from one of the Region objects.
*
* @return <c>null</c> if <c>formula</c> was null.
*/
private static Ptg[] ParseFormula(String formula, HSSFWorkbook workbook)
{
if (formula == null)
{
return(null);
}
return(HSSFFormulaParser.Parse(formula, workbook));
}
/**
* TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
* this call will produce the wrong results if the formula contains any cell references
* One approach might be to apply the inverse of SharedFormulaRecord.convertSharedFormulas(Stack, int, int)
* Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects.
*
* @return <code>null</code> if <c>formula</c> was null.
*/
private static Ptg[] ParseFormula(String formula, HSSFSheet sheet)
{
if (formula == null)
{
return(null);
}
int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);
return(HSSFFormulaParser.Parse(formula, (HSSFWorkbook)sheet.Workbook, FormulaType.CELL, sheetIndex));
}
/**
* TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
* this call will produce the wrong results if the formula Contains any cell references
* One approach might be to apply the inverse of SharedFormulaRecord.ConvertSharedFormulas(Stack, int, int)
* Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects.
*
* @return <code>null</code> if <tt>formula</tt> was null.
*/
public static Ptg[] ParseFormula(String formula, HSSFSheet sheet)
{
if (formula == null)
{
return(null);
}
int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);
return(HSSFFormulaParser.Parse(formula, sheet.Workbook as HSSFWorkbook, FormulaType.Cell, sheetIndex));
}
public void TestTypeOfRootPtg()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("CSCO");
Ptg[] ptgs = HSSFFormulaParser.Parse("CSCO!$E$71", wb, FormulaType.NamedRange, 0);
for (int i = 0; i < ptgs.Length; i++)
{
Assert.AreEqual('R', ptgs[i].RVAType);
}
}
public void TestTypeOfRootPtg()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("CSCO");
Ptg[] ptgs = HSSFFormulaParser.Parse("CSCO!$E$71", wb, FormulaType.NamedRange, 0);
foreach (Ptg ptg in ptgs)
{
Assert.AreEqual('R', ptg.RVAType);
}
}
private static void ConfirmArgCountMsg(String formula, String expectedMessage)
{
HSSFWorkbook book = new HSSFWorkbook();
try
{
HSSFFormulaParser.Parse(formula, book);
throw new AssertFailedException("Didn't Get Parse exception as expected");
}
catch (Exception e)
{
Assert.AreEqual(expectedMessage, e.Message);
}
}
public void TestSheetLevelFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
IRow row;
ISheet sh1 = wb.CreateSheet("Sheet1");
IName nm1 = wb.CreateName();
nm1.NameName = ("sales_1");
nm1.SheetIndex = (0);
nm1.RefersToFormula = ("Sheet1!$A$1");
row = sh1.CreateRow(0);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*2");
ISheet sh2 = wb.CreateSheet("Sheet2");
IName nm2 = wb.CreateName();
nm2.NameName = ("sales_1");
nm2.SheetIndex = (1);
nm2.RefersToFormula = ("Sheet2!$A$1");
row = sh2.CreateRow(0);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*3");
//check that NamePtg refers to the correct NameRecord
Ptg[] ptgs1 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 0);
NamePtg nPtg1 = (NamePtg)ptgs1[0];
Assert.AreSame(nm1, wb.GetNameAt(nPtg1.Index));
Ptg[] ptgs2 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 1);
NamePtg nPtg2 = (NamePtg)ptgs2[0];
Assert.AreSame(nm2, wb.GetNameAt(nPtg2.Index));
//check that the formula evaluator returns the correct result
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
Assert.AreEqual(3.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(6.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(2)).NumberValue, 0.0);
Assert.AreEqual(5.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(15.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(2)).NumberValue, 0.0);
}
/**
* @return The parsed token array representing the formula or value specified.
* Empty array if both formula and value are <c>null</c>
*/
private static Ptg[] ConvertDoubleFormula(String formula, Double value, HSSFWorkbook workbook)
{
if (formula == null)
{
if (double.IsNaN(value))
{
return(Ptg.EMPTY_PTG_ARRAY);
}
return(new Ptg[] { new NumberPtg(value), });
}
if (!double.IsNaN(value))
{
throw new InvalidOperationException("Both formula and value cannot be present");
}
return(HSSFFormulaParser.Parse(formula, workbook));
}
public void TestMacroFunction()
{
// testNames.xls contains a VB function called 'myFunc'
HSSFWorkbook w = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls");
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(w);
Ptg[] ptg = HSSFFormulaParser.Parse("myFunc()", w);
// the name Gets encoded as the first arg
NamePtg tname = (NamePtg)ptg[0];
Assert.AreEqual("myFunc", tname.ToFormulaString(book));
AbstractFunctionPtg tfunc = (AbstractFunctionPtg)ptg[1];
Assert.IsTrue(tfunc.IsExternalFunction);
}
/**
* @return The Parsed token array representing the formula or value specified.
* Empty array if both formula and value are <code>null</code>
*/
private static Ptg[] ConvertDoubleFormula(String formula, Double value, HSSFSheet sheet)
{
if (formula == null)
{
if (double.IsNaN(value))
{
return(Ptg.EMPTY_PTG_ARRAY);
}
return(new Ptg[] { new NumberPtg(value), });
}
if (!double.IsNaN(value))
{
throw new InvalidOperationException("Both formula and value cannot be present");
}
IWorkbook wb = sheet.Workbook;
return(HSSFFormulaParser.Parse(formula, (HSSFWorkbook)wb, FormulaType.CELL, wb.GetSheetIndex(sheet)));
}
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 TestOperandClass()
{
HSSFWorkbook book = new HSSFWorkbook();
Ptg[] ptgs = HSSFFormulaParser.Parse("sum(A1:A2)", book);
Assert.AreEqual(2, ptgs.Length);
Assert.AreEqual(typeof(AreaPtg), ptgs[0].GetType());
switch (ptgs[0].PtgClass)
{
case Ptg.CLASS_REF:
// correct behaviour
break;
case Ptg.CLASS_VALUE:
throw new AssertionException("Identified bug 44675b");
default:
throw new Exception("Unexpected operand class");
}
}
private Ptg[] CreateListFormula(HSSFWorkbook workbook)
{
if (_explicitListValues == null)
{
// formula is parsed with slightly different RVA rules: (root node type must be 'reference')
return(HSSFFormulaParser.Parse(_formula1, workbook, FormulaType.DATAVALIDATION_LIST));
// To do: Excel places restrictions on the available operations within a list formula.
// Some things like union and intersection are not allowed.
}
// explicit list was provided
StringBuilder sb = new StringBuilder(_explicitListValues.Length * 16);
for (int i = 0; i < _explicitListValues.Length; i++)
{
if (i > 0)
{
sb.Append('\0'); // list delimiter is the nul char
}
sb.Append(_explicitListValues[i]);
}
return(new Ptg[] { new StringPtg(sb.ToString()), });
}
public void TestWithNamedRange()
{
HSSFWorkbook workbook = new HSSFWorkbook();
FormulaParser fp;
Ptg[] ptgs;
NPOI.SS.UserModel.Sheet s = workbook.CreateSheet("Foo");
s.CreateRow(0).CreateCell((short)0).SetCellValue(1.1);
s.CreateRow(1).CreateCell((short)0).SetCellValue(2.3);
s.CreateRow(2).CreateCell((short)2).SetCellValue(3.1);
NPOI.SS.UserModel.Name name = workbook.CreateName();
name.NameName = ("testName");
name.RefersToFormula = ("A1:A2");
ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
// Now make it a single cell
name.RefersToFormula = ("C3");
ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
// And make it non-contiguous
name.RefersToFormula = ("A1:A2,C3");
ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
}
private void ConfirmCell(ICell formulaCell, String formula, HSSFWorkbook wb)
{
Ptg[] excelPtgs = FormulaExtractor.GetPtgs(formulaCell);
Ptg[] poiPtgs = HSSFFormulaParser.Parse(formula, wb);
int nExcelTokens = excelPtgs.Length;
int nPoiTokens = poiPtgs.Length;
if (nExcelTokens != nPoiTokens)
{
if (nExcelTokens == nPoiTokens + 1 && excelPtgs[0].GetType() == typeof(AttrPtg))
{
// compensate for missing tAttrVolatile, which belongs in any formula
// involving OFFSET() et al. POI currently does not insert where required
Ptg[] temp = new Ptg[nExcelTokens];
temp[0] = excelPtgs[0];
Array.Copy(poiPtgs, 0, temp, 1, nPoiTokens);
poiPtgs = temp;
}
else
{
throw new Exception("Expected " + nExcelTokens + " tokens but got "
+ nPoiTokens);
}
}
bool hasMismatch = false;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < nExcelTokens; i++)
{
Ptg poiPtg = poiPtgs[i];
Ptg excelPtg = excelPtgs[i];
if (excelPtg.GetType() != poiPtg.GetType())
{
hasMismatch = true;
sb.Append(" mismatch token type[" + i + "] " + GetShortClassName(excelPtg) + " "
+ excelPtg.RVAType + " - " + GetShortClassName(poiPtg) + " "
+ poiPtg.RVAType);
sb.Append(Environment.NewLine);
continue;
}
if (poiPtg.IsBaseToken)
{
continue;
}
sb.Append(" token[" + i + "] " + excelPtg.ToString() + " "
+ excelPtg.RVAType);
if (excelPtg.PtgClass != poiPtg.PtgClass)
{
hasMismatch = true;
sb.Append(" - was " + poiPtg.RVAType);
}
sb.Append(Environment.NewLine);
}
//if (false)
//{ // Set 'true' to see trace of RVA values
// Console.WriteLine(formula);
// Console.WriteLine(sb.ToString());
//}
if (hasMismatch)
{
throw new AssertionException(sb.ToString());
}
}
private static Ptg[] Parse(string formula)
{
HSSFWorkbook book = new HSSFWorkbook();
return HSSFFormulaParser.Parse(formula, book);
}
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;
}
}
}
/**
* @return Parsed token array alReady Confirmed not <c>null</c>
*/
/* package */
public static Ptg[] ParseFormula(String formula)
{
Ptg[] result = HSSFFormulaParser.Parse(formula, null);
Assert.IsNotNull(result, "Ptg array should not be null");
return(result);
}
private static Ptg[] ParseFormula(string formula)
{
Ptg[] result = HSSFFormulaParser.Parse(formula, (HSSFWorkbook)null);
Assert.IsNotNull(result, "Ptg array should not be null");
return(result);
}
