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