/// <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));
}
