/// <summary> /// Creates a sheet reference from a rectangle and a sheet /// </summary> /// <param name="rect">The rectangle to create the sheet reference from</param> /// <param name="sheet">The sheet that the reference will be on</param> /// <returns>A sheet reference that matches the given rectangle and is on the given sheet</returns> /// <remarks> /// Use this method when you have a rectangle that you would like translated into a sheet reference. Note that the /// top-left corner of the sheet is (1,1). The method will try to create the appropriate type of reference based on /// the /// dimensions of the rectangle. For example: A rectangle 1 unit wide and 1 unit tall will be translated into a cell /// reference /// </remarks> /// <exception cref="T:System.ArgumentException"> /// <para>The resulting sheet reference is not within the bounds of its sheet</para> /// <para>The given sheet argument is not registered with the SheetManager</para> /// </exception> /// <example> /// The following code creates a reference to the range A1:B2 on the currently active sheet /// <code> /// Dim engine As New FormulaEngine /// Dim rect As New Rectangle(1, 1, 2, 2) /// Dim ref As ISheetReference = factory.FromRectangle(rect) /// </code> /// </example> public ISheetReference FromRectangle(ISheet sheet, Rectangle rect) { FormulaEngine.ValidateNonNull(sheet, "sheet"); SheetReference @ref; Rectangle sheetRect = SheetReference.GetSheetRectangle(sheet); if (rect.Width == 1 & rect.Height == 1) { @ref = new CellReference(rect.Top, rect.Left); } else if (rect.Height == sheetRect.Height) { @ref = new ColumnReference(rect.Left, rect.Right - 1); } else if (rect.Width == sheetRect.Width) { @ref = new RowReference(rect.Top, rect.Bottom - 1); } else { @ref = new CellRangeReference(rect); } InitializeGridReference(@ref, sheet); return(@ref); }
private void InitReferenceDiagram(String Cell) { tableFlow1.Clear(); SheetReference Reference = CellType.ReferenceInfo as SheetReference; if (Reference != null && !String.IsNullOrEmpty(Reference.ReferenceXml)) { TableName = Reference.TableName; ColumnName = Reference.ColumnName; DataFilter = Reference.DataFilter; LoadFromXml(Reference.ReferenceXml); } else { DataTableSchema DataTable = SheetConfiguration.DataTableSchema; bool HaveItem = DataTable.HaveDataItem(Cell); if (HaveItem) { FieldDefineInfo FieldInfo = DataTable.GetDataItem(Cell); if (FieldInfo != null && DataTable.Schema != null) { TableName = DataTable.Schema.Name; ColumnName = FieldInfo.FieldName; AppendTableByTableName(TableName, false); } else { MessageBox.Show("表单‘" + SheetConfiguration.Description + "’没有设置数据项!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } }
private void InitializeGridReference(SheetReference @ref, ISheet sheet) { @ref.SetEngine(_owner); ValidateSheet(sheet); @ref.Sheet = sheet; @ref.Validate(); @ref.ComputeHashCode(); }
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); }
private void InitializeParsedGridReference(SheetReference @ref, string s) { ReferenceParseProperties parseProps = SheetReference.CreateParseProperties(s); @ref.SetEngine(_owner); @ref.ProcessParseProperties(parseProps, _owner); @ref.Validate(); @ref.ComputeHashCode(); }
public void ColumnIndexToLabel() { Assert.AreEqual(SheetReference.ColumnIndex2Label(247), "IM"); Assert.AreEqual(SheetReference.ColumnIndex2Label(233), "HY"); Assert.AreEqual(SheetReference.ColumnIndex2Label(226), "HR"); Assert.AreEqual(SheetReference.ColumnIndex2Label(702), "ZZ"); Assert.AreEqual(SheetReference.ColumnIndex2Label(515), "SU"); Assert.AreEqual(SheetReference.ColumnIndex2Label(285), "JY"); }
public void CheckSheetReference() { _formulaEngine.ResetParser(); Formula f = _formulaEngine.CreateFormula("=IF(Outputs!Z50<>0;TRUE;FALSE)"); CellReference cell = (CellReference)f.References[0]; Assert.AreEqual(cell.Sheet.Name, "Outputs"); Assert.AreEqual(SheetReference.ColumnIndex2Label(cell.ColumnIndex), "Z"); Assert.AreEqual(cell.RowIndex, 50); }
/// <summary> /// Creates a sheet reference from a string /// </summary> /// <param name="s">A string that contains a sheet reference expression</param> /// <returns>A sheet reference parsed from the given string</returns> /// <remarks> /// This method creates a sheet reference by parsing a given string. /// The method accepts strings with the following syntax: /// <list type="table"> /// <listheader> /// <term>String format</term><description>Resultant reference</description> /// </listheader> /// <item> /// <term>Column letter followed by a row number: "C3"</term><description>Cell</description> /// </item> /// <item> /// <term>Two column letter and row number pairs separated by a colon: "C3:D4"</term> /// <description>Cell range</description> /// </item> /// <item> /// <term>Two column letters separated by a colon: "E:G"</term><description>Columns</description> /// </item> /// <item> /// <term>Two row numbers separated by a colon: "4:6"</term><description>Rows</description> /// </item> /// </list> /// All of the above formats can specify a specific sheet by prefixing the reference with a sheet name /// followed by an exclamation point (ie: "Sheet2!E:G") /// If no sheet name is specified, the currently active sheet is used. /// </remarks> /// <example> /// This example shows how you would create sheet references from various strings /// <code> /// ' Get a reference to cell A1 /// Dim cellRef As ISheetReference = factory.Parse("A1") /// ' Get a reference to cells B2 through E4 /// Dim rangeRef As ISheetReference = factory.Parse("b2:e4") /// ' Get a reference to columns D through F /// Dim colsRef As ISheetReference = factory.Parse("D:F") /// 'Get a reference to rows 4 through 6 /// Dim rowsRef As ISheetReference = factory.Parse("4:6") /// ' Get a reference to cell C4 on sheet 'Sheet4' /// Dim cellRef As ISheetReference = factory.Parse("Sheet4!C4") /// </code> /// </example> /// <exception cref="T:System.ArgumentException"> /// <para>The given string could not be parsed into a sheet reference</para> /// <para>The string references a sheet name that is not defined</para> /// <para>The resulting sheet reference is not within the bounds of its sheet</para> /// </exception> public ISheetReference Parse(string s) { FormulaEngine.ValidateNonNull(s, "s"); SheetReference @ref = TryParseGridReference(s); if (@ref == null) { OnInvalidReferenceString(); } InitializeParsedGridReference(@ref, s); return(@ref); }
private void Button_Ok_Click(object sender, EventArgs e) { CellType.ReferenceStyle = BizCommon.ReferenceStyle.Sheet; SheetReference Reference = CellType.ReferenceInfo as SheetReference; Reference.TableName = TableName; Reference.ColumnName = ColumnName; Reference.ReferenceItems = GetReferenceItems(); Reference.ReferenceXml = SaveAsXml(); Reference.DataFilter = DataFilter; this.DialogResult = DialogResult.OK; this.Close(); }
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); }
public override ReferenceOperationResultType OnRangeMoved(SheetReference source, SheetReference dest) { return(new ReferenceOperationResultType()); }
public RangeMovedOperator(FormulaEngine owner, SheetReference source, SheetReference dest) { _owner = owner; _source = source; _dest = dest; }
public abstract ReferenceOperationResultType OnRangeMoved(SheetReference source, SheetReference dest);
/// <summary> /// Handles a range move and tries to do it the same way as Excel. This function is way too complicated but Excel has /// some very /// weird rules with regards to range moves and this is the only way I can think of emulating them. /// </summary> protected ReferenceOperationResultType HandleRangeMoved(SheetReference current, SheetReference source, SheetReference dest, SetRangeCallback callback) { Rectangle destRect = dest.Range; Rectangle sourceRect = source.Range; int rowOffset = destRect.Top - sourceRect.Top; int colOffset = destRect.Left - sourceRect.Left; Rectangle myRect = current.Range; bool isOnSourceSheet = ReferenceEquals(source.Sheet, current.Sheet); bool isOnDestSheet = ReferenceEquals(dest.Sheet, current.Sheet); bool sameSheet = isOnSourceSheet & isOnDestSheet; if (isOnSourceSheet & isOnDestSheet == false && IsEdgeMove(myRect, sourceRect)) { // Move of one of our edges to another sheet callback(SubtractRectangle(myRect, sourceRect)); return(ReferenceOperationResultType.Affected); } if (isOnSourceSheet == false & isOnDestSheet && IsEdgeDestroyMove(myRect, sourceRect, destRect)) { callback(SubtractRectangle(myRect, destRect)); return(ReferenceOperationResultType.Affected); } if (sameSheet && IsEdgeExpandMove(myRect, sourceRect, rowOffset, colOffset)) { callback(GetEdgeExpandRectangle(myRect, sourceRect, destRect)); return(ReferenceOperationResultType.Affected); } if (sameSheet && IsEdgeShrinkMove(myRect, sourceRect, rowOffset, colOffset)) { callback(GetEdgeShrinkRectangle(myRect, sourceRect, rowOffset, colOffset)); return(ReferenceOperationResultType.Affected); } if (sameSheet && IsEdgeDestroyMoveSameSheet(myRect, sourceRect, destRect)) { callback(SubtractRectangle(myRect, destRect)); return(ReferenceOperationResultType.Affected); } if (isOnSourceSheet && sourceRect.Contains(myRect)) { // Move of the whole range myRect.Offset(colOffset, rowOffset); current.SetSheetForRangeMove(dest.Sheet); callback(myRect); return(ReferenceOperationResultType.Affected); } if (isOnDestSheet && destRect.Contains(myRect)) { // We are overwritten by the move return(ReferenceOperationResultType.Invalidated); } // We are affected only if the moved range intersects us if (current.Intersects(dest)) { return(ReferenceOperationResultType.Affected); } return(ReferenceOperationResultType.NotAffected); }
/// <summary> /// Determines whether a rectangle is inside the bounds of a given sheet /// </summary> /// <param name="rect">The rectangle to test</param> /// <param name="sheet">The sheet to use</param> /// <returns>True if the sheet contains the rectangle; False otherwise</returns> /// <remarks> /// Use this function when you have a rectangle and a sheet and need to know if the rectangle is inside the /// sheet's bounds. /// </remarks> public static bool IsRectangleInSheet(Rectangle rect, ISheet sheet) { FormulaEngine.ValidateNonNull(sheet, "sheet"); return(SheetReference.IsRectangleInSheet(rect, sheet)); }
/// <summary> /// Gets the label for a column index /// </summary> /// <param name="columnIndex">The index whose label you wish to get</param> /// <returns>A string with the colum label</returns> /// <remarks>This function is handy when you have a column index and you want to get its associated label.</remarks> /// <example> /// <list type="table"> /// <listheader> /// <term>Column index</term><description>Resultant label</description> /// </listheader> /// <item> /// <term>1</term><description>"A"</description> /// </item> /// <item> /// <term>14</term><description>"N"</description> /// </item> /// <item> /// <term>123</term><description>"DS"</description> /// </item> /// <item> /// <term>256</term><description>"IV"</description> /// </item> /// </list> /// </example> public static string ColumnIndex2Label(int columnIndex) { return(SheetReference.ColumnIndex2Label(columnIndex)); }