/// <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);
}
/// <summary>
/// Creates a sheet reference to a range of cells on a given sheet
/// </summary>
/// <param name="sheet">The sheet the reference will be on</param>
/// <param name="startRow">The top row of the range</param>
/// <param name="startColumn">The left column of the range</param>
/// <param name="endRow">The bottom row of the range</param>
/// <param name="endColumn">The right column of the range</param>
/// <returns>A sheet reference to the specified range on the specified sheet</returns>
/// <remarks>
/// Use this method when you need a sheet reference to a range of cells on a specific sheet and have the four
/// indices handy.
/// </remarks>
/// <exception cref="T:System.ArgumentException">
/// <para>The resultant range is not within the bounds of the given sheet</para>
/// <para>The given sheet argument is not registered with the SheetManager</para>
/// </exception>
/// <example>
/// The following example creates a reference to the range C3:E4 on the currently active sheet
/// <code>
/// Dim engine As New FormulaEngine
/// Dim ref As ISheetReference = engine.ReferenceFactory.Cells(3, 3, 4, 5)
/// </code>
/// </example>
public ISheetReference Cells(ISheet sheet, int startRow, int startColumn, int endRow, int endColumn)
{
FormulaEngine.ValidateNonNull(sheet, "sheet");
var @ref = new CellRangeReference(startRow, startColumn, endRow, endColumn);
InitializeGridReference(@ref, sheet);
return(@ref);
}
private SheetReference TryParseGridReference(string s)
{
if (CellReference.IsValidString(s))
{
return(CellReference.FromString(s));
}
if (CellRangeReference.IsValidString(s))
{
return(CellRangeReference.FromString(s));
}
if (ColumnReference.IsValidString(s))
{
return(ColumnReference.FromString(s));
}
if (RowReference.IsValidString(s))
{
return(RowReference.FromString(s));
}
return(null);
}
//-----------------------------------------------------------------------------
#region ** ctor
public FlexSheetDemo()
{
InitializeComponent();
// set language to match the system settings
this.Language = System.Windows.Markup.XmlLanguage.GetLanguage(CultureInfo.CurrentCulture.Name);
// connect function menu to _tbFx TextBlock
var fnMenu = new ExcelCalcFunctionMenu();
#if false
fnMenu.ItemClick += (s, e) =>
{
var item = e.Source as C1MenuItem;
var text = (string)item.Header;
var selStart = _txtFormula.SelectionStart;
_txtFormula.SelectedText = text + "()";
_txtFormula.SelectionStart = selStart + text.Length + 1;
_txtFormula.Focus();
};
#endif
// add some sheets
_flex.AddSheet("Sheet1", 50, 10);
_flex.AddSheet("Sheet2", 50, 10);
_flex.AddSheet("Sheet3", 50, 10);
_flex.AddSheet("New formulas", 50, 10);
_flex.Sheets.SelectedIndex = 0;
var tabContorl = _flex.Sheets.SelectedSheet.Parent as C1TabControl;
if (tabContorl != null)
{
tabContorl.SelectionChanged += (s, e) =>
{
_ribbon.DataContext = _flex.Sheets.SelectedSheet;
};
}
#region Populate Sheet1
// populate the grid with some formulas (multiplication table)
for (int r = 0; r < _flex.Rows.Count - 2; r++)
{
List <double> datas = new List <double>();
for (int c = 0; c < _flex.Columns.Count; c++)
{
_flex[r, c] = string.Format("={0}*{1}", r + 1, c + 1);
double value = (double)_flex[r, c];
datas.Add(value);
}
}
// add a totals row to illustrate
AddTotalRow();
#endregion
#region Populate Sheet2
var protectedSheet = _flex.Sheets["Sheet2"];
if (protectedSheet != null)
{
for (int r = 0; r < 2; r++)
{
for (int c = 0; c < 2; c++)
{
protectedSheet.Grid[r, c] = "Locked Cell";
}
}
protectedSheet.IsProtected = true;
protectedSheet.AddLockedCell(0, 0, 1, 1);
}
#endregion
#region Pupulate Sheet3
var sparklineSheet = _flex.Sheets["Sheet3"];
if (sparklineSheet != null)
{
for (int r = 0; r < 5; r++)
{
List <double> datas = new List <double>();
for (int c = 0; c < 6; c++)
{
if (c < 5)
{
Random rnd = new Random(new object().GetHashCode());
double num = rnd.Next(10);
sparklineSheet.Grid[r, c] = num;
datas.Add(num);
}
else
{
_flex.InsertSparkLine(SparkLineType.Line, datas, sparklineSheet, new CellRange(r, 5));
}
}
}
}
#endregion
#region Populate "New formulas" sheet
var formulaSheet = _flex.Sheets["New formulas"];
formulaSheet.Grid[0, 0] = "John";
formulaSheet.Grid[1, 0] = "Kelvin";
formulaSheet.Grid[2, 0] = "Micheal";
formulaSheet.Grid[3, 0] = "Peter";
formulaSheet.Grid[0, 2] = "Peter"; // VLookup value
formulaSheet.Grid[0, 1] = 1d;
formulaSheet.Grid[1, 1] = 2d;
formulaSheet.Grid[2, 1] = 3d;
formulaSheet.Grid[3, 1] = 4d;
formulaSheet.Grid[6, 1] = "Kelvin";
formulaSheet.Grid[7, 0] = "LOOKUP => ";
formulaSheet.Grid[7, 1] = "=LOOKUP(B7, $A$1:$A$4, $B$1:$B$4)";
formulaSheet.Grid[10, 0] = "VLOOKUP => ";
formulaSheet.Grid[10, 1] = "=VLOOKUP(C1, $A$1:$B$4, 2)";
formulaSheet.Grid[0, 5] = "John";
formulaSheet.Grid[0, 6] = "Kelvin";
formulaSheet.Grid[0, 7] = "Micheal";
formulaSheet.Grid[0, 8] = "Peter";
formulaSheet.Grid[1, 5] = 3d;
formulaSheet.Grid[1, 6] = 4d;
formulaSheet.Grid[1, 7] = 5d;
formulaSheet.Grid[1, 8] = 6d;
formulaSheet.Grid[10, 5] = "HLOOKUP => ";
formulaSheet.Grid[10, 6] = "=HLOOKUP(\"Kelvin\", F1:I2, 2)";
formulaSheet.Grid[12, 0] = "NOW => ";
formulaSheet.Grid[12, 1] = "=NOW()";
((ExcelRow)formulaSheet.Grid.Rows[12]).SetCellStyle(formulaSheet.Grid.Columns[1], new ExcelCellStyle()
{
Format = "f"
});
formulaSheet.Grid[13, 0] = "TODAY => ";
formulaSheet.Grid[13, 1] = "=TODAY()";
((ExcelRow)formulaSheet.Grid.Rows[13]).SetCellStyle(formulaSheet.Grid.Columns[1], new ExcelCellStyle()
{
Format = "d"
});
// A custom function to find the greatest number in a cell range.
_flex.CalcEngine.RegisterFunction("GREATEST", 1, (expressions) =>
{
CellRangeReference rangeReference = (expressions[0] as XObjectExpression).Value as CellRangeReference;
if (rangeReference != null)
{
var enumerator = rangeReference.GetEnumerator();
double greatest = double.MinValue;
while (enumerator.MoveNext())
{
double?v = (enumerator.Current as double?);
if (v.HasValue)
{
greatest = Math.Max(greatest, v.Value);
}
}
return(greatest);
}
return(null);
});
formulaSheet.Grid[15, 0] = "Custom function";
formulaSheet.Grid[15, 1] = "GREATEST =>";
formulaSheet.Grid[15, 2] = "=GREATEST(B1:B4)";
formulaSheet.Grid.Columns[0].Width = new GridLength(100);
#endregion
// set up event handler to update the selection status
_flex.SelectionChanged += (s, e) =>
{
_selectionBeingChanged = true;
var sel = _flex.Selection;
if (sel.Row >= _flex.Rows.Count || sel.Column >= _flex.Columns.Count)
{
if (sel.Row >= _flex.Rows.Count)
{
_flex.Selection = new CellRange(-1, sel.Column, -1, sel.Column2);
}
else
{
_flex.Selection = new CellRange(sel.Row, -1, sel.Row2, -1);
}
sel = _flex.Selection;
}
// update address label
var text = string.Empty;
if (sel.IsValid)
{
text = _flex.GetAddress(sel, false);
//update angle combobox
var row = _flex.Rows[sel.Row] as ExcelRow;
if (row != null)
{
double angle = row.GetCellAngle(_flex.Columns[sel.Column]);
int selectedIndex = 0;
if (angle == 90)
{
selectedIndex = 0;
}
else if (angle == 45)
{
selectedIndex = 1;
}
else if (angle == 0)
{
selectedIndex = 2;
}
else if (angle == -45)
{
selectedIndex = 3;
}
else if (angle == -90)
{
selectedIndex = 4;
}
else
{
selectedIndex = 2;
}
_cmbRotate.SelectedIndex = selectedIndex;
}
}
// update status bar
text = "Ready";
if (sel.IsValid && !sel.IsSingleCell &&
sel.BottomRow < _flex.Rows.Count && sel.RightColumn < _flex.Columns.Count)
{
try
{
var address = _flex.GetAddress(sel, true);
var avg = _flex.Evaluate(string.Format("Average({0})", address));
var cnt = _flex.Evaluate(string.Format("Count({0})", address));
var sum = _flex.Evaluate(string.Format("Sum({0})", address));
if ((double)cnt > 0)
{
text = string.Format("Average: {0:#,##0.##} Count: {1:n0} Sum: {2:#,##0.##}",
avg, cnt, sum);
}
}
catch
{
// circular reference/bad expression?
}
}
_tbStatus.Text = text;
_selectionBeingChanged = false;
};
// give grid the focus when the app loads
Loaded += (s, e) =>
{
_flex.Focus();
};
// close the find and replace window when app is closed.
Unloaded += (s, e) =>
{
if (_findReplaceWindow != null)
{
_findReplaceWindow.Close();
}
};
// create custom menu items
CustomizeContextMenu();
_flex.Rows.CollectionChanged += (s, e) => AddTotalRow(false);
_flex.Columns.CollectionChanged += (s, e) => AddTotalRow(false);
}
public override ReferenceProperties CreateProperties(bool implicitSheet)
{
return(CellRangeReference.CreateProperties(implicitSheet, _image));
}
public override Reference CreateReference()
{
return(CellRangeReference.FromString(_image));
}
