public void ColumnLabelToIndexText() { Assert.AreEqual(SheetReference.ColumnLabel2Index('A', 'A'), 27); Assert.AreEqual(SheetReference.ColumnLabel2Index('A', 'U'), 47); Assert.AreEqual(SheetReference.ColumnLabel2Index('A', 'I'), 35); Assert.AreEqual(SheetReference.ColumnLabel2Index('B', 'S'), 71); Assert.AreEqual(SheetReference.ColumnLabel2Index('C', 'M'), 91); Assert.AreEqual(SheetReference.ColumnLabel2Index('I', 'D'), 238); }
public void CheckLowerThanOneRefToCurrentYear() { object cellResult = 2011; _outputsSheet[12, 3] = cellResult; const string formula = "=Если(Outputs!C12 < ГОД(СЕГОДНЯ()); 1;0)"; Formula f = _formulaEngine.CreateFormula(formula); Assert.IsInstanceOfType(f.References[0], typeof(CellReference)); var cell = (CellReference)f.References[0]; Assert.AreEqual(cell.ColumnIndex, SheetReference.ColumnLabel2Index('C'), "Column number does not equal to 5 (E)"); Assert.AreEqual(cell.RowIndex, 12, "Row number does not equal to 3 ()"); Assert.AreEqual(cell.TargetCellValue, cellResult, "Cell value does not equal to " + cellResult); }
public void CheckLowerThanOneRef() { object cellResult = 50; _outputsSheet[3, 5] = cellResult; const string formula = "=ЕСЛИ(Outputs!E3 > 0;1;0)"; Formula f = _formulaEngine.CreateFormula(formula); Assert.IsInstanceOfType(f.References[0], typeof(CellReference)); var cell = (CellReference)f.References[0]; Assert.AreEqual(cell.ColumnIndex, SheetReference.ColumnLabel2Index('E'), "Column number does not equal to 5 (E)"); Assert.AreEqual(cell.RowIndex, 3, "Row number does not equal to 3 ()"); Assert.AreEqual(cell.TargetCellValue, cellResult, "Cell value does not equal to " + cellResult); }
/// <summary> /// Gets the column index from a column label /// </summary> /// <param name="label">The label whose column index you wish to get</param> /// <returns>An index representing the label</returns> /// <remarks>This function is handy when you have a column label and you want to get its associated index.</remarks> /// <example> /// <list type="table"> /// <listheader> /// <term>Column label</term><description>Resultant index</description> /// </listheader> /// <item> /// <term>"A"</term><description>1</description> /// </item> /// <item> /// <term>"N"</term><description>14</description> /// </item> /// <item> /// <term>"DS"</term><description>123</description> /// </item> /// <item> /// <term>"IV"</term><description>256</description> /// </item> /// </list> /// </example> public static int ColumnLabel2Index(string label) { FormulaEngine.ValidateNonNull(label, "label"); if (label.Length < 1 || label.Length > 2) { throw new ArgumentException("The given label must be one or two characters long"); } char c2 = Char.MinValue; if (label.Length == 2) { c2 = label[1]; } return(SheetReference.ColumnLabel2Index(label[0], c2)); }
public void CheckSummOfTwoRefsLowerOrEqTo() { object cellResult1 = 2016; _outputsSheet[12, 3] = cellResult1; object cellResult2 = 2017; _outputsSheet[20, 20] = cellResult2; const string formula = "=Если(Outputs!C12 = ГОД(СЕГОДНЯ());0;1) + Если(Outputs!T20 = ГОД(СЕГОДНЯ()) - 1;3;5) >= 6"; Formula f = _formulaEngine.CreateFormula(formula); Assert.IsInstanceOfType(f.References[0], typeof(CellReference)); Assert.IsInstanceOfType(f.References[1], typeof(CellReference)); var cell1 = (CellReference)f.References[0]; // var cell2 = (CellReference)f.References[1]; Assert.AreEqual(cell1.ColumnIndex, SheetReference.ColumnLabel2Index('C')); Assert.AreEqual(cell2.ColumnIndex, SheetReference.ColumnLabel2Index('T')); Assert.AreEqual(cell1.RowIndex, 12); Assert.AreEqual(cell2.RowIndex, 20); //Set Param ID cell1.SetRow(55); foreach (ReferenceProperties property in f.ReferenceProperties) { var gringProp = property as SheetReference.GridReferenceProperties; if (gringProp != null) { gringProp.ImplicitSheet = true; } } //Set Year cell1.SetColumn(2015 % 2000); //Set Param ID cell2.SetRow(75); //Set Year cell2.SetColumn(2025 % 2000); string resultFormula = f.ToString(); }
public void CheckLowerThanTwoRefsToCurrentYear() { object cellResult1 = 2011; _outputsSheet[12, 3] = cellResult1; object cellResult2 = 2017; _outputsSheet[20, 20] = cellResult2; const string formula = "=Если(И(Outputs!C12 < ГОД(СЕГОДНЯ());Outputs!T20 < ГОД(СЕГОДНЯ())); 1;0)"; Formula f = _formulaEngine.CreateFormula(formula); Assert.IsInstanceOfType(f.References[0], typeof(CellReference)); Assert.IsInstanceOfType(f.References[1], typeof(CellReference)); var cell1 = (CellReference)f.References[0]; var cell2 = (CellReference)f.References[1]; Assert.AreEqual(cell1.ColumnIndex, SheetReference.ColumnLabel2Index('C')); Assert.AreEqual(cell2.ColumnIndex, SheetReference.ColumnLabel2Index('T')); Assert.AreEqual(cell1.RowIndex, 12); Assert.AreEqual(cell2.RowIndex, 20); }