FlexCalc Example (C# / uwp10)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\uwp10\FlexCalc and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/VS2022/uwp10/FlexCalc
Overview
This example shows how to use the FlexCel calculating engine as a calculator for the device.
Under the hood, every textbox in the app is mapped to a cell in a spreadsheet (A1, A2, A3...), and whenever a textbox changes, the sheet is recalculated and the results of the new calculations are shown at the column on the right. You can use the full range of Excel functions in this demo, and reference cells in the usual way (for example, you can write "=A2 + 1" in the textbox for A3)
As in Excel, any text that starts with "=" will be considered a formula.
The backing spreadsheet will be saved when you exit the app and loaded when open it, and this will allow to persist the formulas between sessions.
Files
App.xaml.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.WindowsRuntime;
using Windows.ApplicationModel;
using Windows.ApplicationModel.Activation;
using Windows.Foundation;
using Windows.Foundation.Collections;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Controls.Primitives;
using Windows.UI.Xaml.Data;
using Windows.UI.Xaml.Input;
using Windows.UI.Xaml.Media;
using Windows.UI.Xaml.Navigation;
namespace FlexCalc
{
/// <summary>
/// Provides application-specific behavior to supplement the default Application class.
/// </summary>
sealed partial class App : Application
{
/// <summary>
/// Initializes the singleton application object. This is the first line of authored code
/// executed, and as such is the logical equivalent of main() or WinMain().
/// </summary>
public App()
{
this.InitializeComponent();
this.Suspending += OnSuspending;
}
/// <summary>
/// Invoked when the application is launched normally by the end user. Other entry points
/// will be used such as when the application is launched to open a specific file.
/// </summary>
/// <param name="e">Details about the launch request and process.</param>
protected override void OnLaunched(LaunchActivatedEventArgs e)
{
Frame rootFrame = Window.Current.Content as Frame;
// Do not repeat app initialization when the Window already has content,
// just ensure that the window is active
if (rootFrame == null)
{
// Create a Frame to act as the navigation context and navigate to the first page
rootFrame = new Frame();
rootFrame.NavigationFailed += OnNavigationFailed;
if (e.PreviousExecutionState == ApplicationExecutionState.Terminated)
{
}
// Place the frame in the current Window
Window.Current.Content = rootFrame;
}
if (e.PrelaunchActivated == false)
{
if (rootFrame.Content == null)
{
// When the navigation stack isn't restored navigate to the first page,
// configuring the new page by passing required information as a navigation
// parameter
rootFrame.Navigate(typeof(MainPage), e.Arguments);
}
// Ensure the current window is active
Window.Current.Activate();
}
}
/// <summary>
/// Invoked when Navigation to a certain page fails
/// </summary>
/// <param name="sender">The Frame which failed navigation</param>
/// <param name="e">Details about the navigation failure</param>
void OnNavigationFailed(object sender, NavigationFailedEventArgs e)
{
throw new Exception("Failed to load Page " + e.SourcePageType.FullName);
}
/// <summary>
/// Invoked when application execution is being suspended. Application state is saved
/// without knowing whether the application will be terminated or resumed with the contents
/// of memory still intact.
/// </summary>
/// <param name="sender">The source of the suspend request.</param>
/// <param name="e">Details about the suspend request.</param>
private void OnSuspending(object sender, SuspendingEventArgs e)
{
var deferral = e.SuspendingOperation.GetDeferral();
deferral.Complete();
}
}
}
DataModel.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using FlexCel.Core;
using FlexCel.XlsAdapter;
namespace FlexCalc
{
class DataModel
{
ExcelFile xls;
bool Saving;
public void LoadSpreadsheet(string FileName)
{
if (xls == null) xls = new XlsFile(true);
try
{
xls.Open(FileName);
}
catch
{
xls.NewFile(1, TExcelFileFormat.v2019);
xls.SetCellValue(1, 1, "Example");
xls.SetCellValue(2, 1, 42);
xls.SetCellValue(3, 1, new TFormula("=Sqrt(A2) * A2^2"));
}
}
public bool Loaded
{
get { return xls != null; }
}
public string GetCellOrFormula(int row)
{
object cell = xls.GetCellValue(row, 1);
if (cell == null)
return "";
TFormula fmla = (cell as TFormula);
if (fmla != null)
return fmla.Text;
return Convert.ToString(cell);
}
public string GetStringFromCell(int row, int col)
{
return xls.GetStringFromCell(row, col);
}
public void SetCellFromString(int row, int col, string value)
{
xls.SetCellFromString(row, col, value);
}
internal void SaveState(string FileName)
{
if (Saving) return; //if 2 or more events try to save, only listen to one.
Saving = true;
try
{
xls.Save(FileName);
}
finally
{
Saving = false;
}
}
public void Recalc()
{
xls.Recalc();
}
}
}
MainPage.xaml.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.WindowsRuntime;
using Windows.Foundation;
using Windows.Foundation.Collections;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Controls.Primitives;
using Windows.UI.Xaml.Data;
using Windows.UI.Xaml.Input;
using Windows.UI.Xaml.Media;
using Windows.UI.Xaml.Navigation;
namespace FlexCalc
{
/// <summary>
/// An empty page that can be used on its own or navigated to within a Frame.
/// </summary>
public sealed partial class MainPage : Page
{
DataModel Model = new DataModel();
readonly string FileName = Path.Combine(Windows.Storage.ApplicationData.Current.TemporaryFolder.Path, "result.xlsx");
bool UpdatingInput;
public MainPage()
{
this.InitializeComponent();
}
private void Page_Loaded(object sender, RoutedEventArgs e)
{
UpdatingInput = true;
try
{
Model.LoadSpreadsheet(FileName);
CreateCells();
FillInput();
}
finally
{
UpdatingInput = false;
}
}
private void CreateCells()
{
for (int i = 0; i < 10; i++)
{
ContentPanel.RowDefinitions.Add(new RowDefinition());
}
for (int r = 0; r < ContentPanel.RowDefinitions.Count; r++)
{
var Head = new TextBlock() { Text = "A" + (r + 1).ToString(), VerticalAlignment = VerticalAlignment.Center, FontSize = 16 };
ContentPanel.Children.Add(Head);
Grid.SetRow(Head, r);
Grid.SetColumn(Head, 0);
var Formula = new TextBox() { TextWrapping = TextWrapping.Wrap };
ContentPanel.Children.Add(Formula);
Grid.SetRow(Formula, r);
Grid.SetColumn(Formula, 1);
Formula.InputScope = new InputScope() { Names = { new InputScopeName() { NameValue = InputScopeNameValue.Formula } } };
Formula.TextChanged += Formula_TextChanged;
var Result = new TextBlock() { VerticalAlignment = VerticalAlignment.Center, FontSize = 16 };
ContentPanel.Children.Add(Result);
Grid.SetRow(Result, r);
Grid.SetColumn(Result, 2);
}
}
private void FillInput()
{
var boxes = from b in ContentPanel.Children where (b is TextBox) && Grid.GetColumn(b as TextBox) == 1 select b;
foreach (TextBox b in boxes)
{
b.Text = Model.GetCellOrFormula(Grid.GetRow(b) + 1);
}
}
void Formula_TextChanged(object sender, TextChangedEventArgs e)
{
if (!Model.Loaded || UpdatingInput) return;
TextBox tb = sender as TextBox;
if (tb == null) return;
Model.SetCellFromString(Grid.GetRow(tb) + 1, 1, tb.Text);
Model.SaveState(FileName);
Model.Recalc();
UpdateResults();
}
private void UpdateResults()
{
var boxes = from b in ContentPanel.Children where (b is TextBlock) && Grid.GetColumn(b as TextBlock) == 2 select b;
foreach (TextBlock b in boxes)
{
b.Text = Model.GetStringFromCell(Grid.GetRow(b) + 1, 1);
}
}
}
}