Validating FlexCel recalculation (C# / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\10.API\75.Validate Recalc and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/VS2022/netframework/Modules/10.API/75.Validate Recalc
Overview
By default, FlexCel recalculates the files it generates before saving. This is not completely necessary for normal xls/x files, because Excel will recalculate the file again when opening. But when natively printing or exporting to PDF, you need the values from the recalculation, because no Excel is involved in the process.
While most files will recalculate fine, if you have complex formulas and need to verify they will recalculate ok, this is the application to use.
Concepts
Button "Validate Recalc": This will verify that flexcel can understand all the formulas on your sheet.
Button "Compare with Excel": This will open a file you saved with Excel, force a recalculation in FlexCel and then compare all formula results with the original ones. You can use this to verify the results are actually what you expect them to be.
Files
AssemblyInfo.cs
using System.Reflection;
using System.Runtime.CompilerServices;
//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("")]
[assembly: AssemblyCopyright("(c) 2002 - 2024 TMS Software")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("7.21.0.0")]
//
// In order to sign your assembly you must specify a key to use. Refer to the
// Microsoft .NET Framework documentation for more information on assembly signing.
//
// Use the attributes below to control which key is used for signing.
//
// Notes:
// (*) If no key is specified, the assembly is not signed.
// (*) KeyName refers to a key that has been installed in the Crypto Service
// Provider (CSP) on your machine. KeyFile refers to a file which contains
// a key.
// (*) If the KeyFile and the KeyName values are both specified, the
// following processing occurs:
// (1) If the KeyName can be found in the CSP, that key is used.
// (2) If the KeyName does not exist and the KeyFile does exist, the key
// in the KeyFile is installed into the CSP and used.
// (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
// When specifying the KeyFile, the location of the KeyFile should be
// relative to the project output directory which is
// %Project Directory%\obj\<configuration>. For example, if your KeyFile is
// located in the project directory, you would specify the AssemblyKeyFile
// attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
// (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
// documentation for more information on this.
//
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("")]
[assembly: AssemblyKeyName("")]
Form1.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using System.IO;
using System.Diagnostics;
using System.Reflection;
namespace ValidateRecalc
{
/// <summary>
/// Use this demo to validate the recalculation made by FlexCel.
/// </summary>
public partial class mainForm: System.Windows.Forms.Form
{
private FlexCel.Report.FlexCelReport XlsReport;
public mainForm()
{
InitializeComponent();
ResizeToolbar(mainToolbar);
}
private void ResizeToolbar(ToolStrip toolbar)
{
using (Graphics gr = CreateGraphics())
{
double xFactor = gr.DpiX / 96.0;
double yFactor = gr.DpiY / 96.0;
toolbar.ImageScalingSize = new Size((int)(24 * xFactor), (int)(24 * yFactor));
toolbar.Width = 0; //force a recalc of the buttons.
}
}
private void button2_Click(object sender, System.EventArgs e)
{
Close();
}
private void btnInfo_Click(object sender, EventArgs e)
{
MessageBox.Show("This example will validate the calculations performed by the FlexCel engine.\n" +
"It can do it in 2 different ways:\n" +
" 1) The button 'Validate Recalc' will analyze a file, and report if there is anything that FlexCel doesn't support on it.\n" +
" 2) The button 'Compare with Excel' will open a file saved by Excel, recalculate it with FlexCel, compare the values reported by both FlexCel and Excel and report if there are any differences.");
}
private void validateRecalc_Click(object sender, System.EventArgs e)
{
if (openFileDialog1.ShowDialog() != DialogResult.OK) return;
XlsFile Xls = new XlsFile();
Xls.Open(openFileDialog1.FileName);
// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// ////////Code here is only needed if you have linked files. In this example we don't know, so we will use it /////////
TWorkspace Work = new TWorkspace(); //Create a workspace
Work.Add(Path.GetFileName(openFileDialog1.FileName), Xls); //Add the original file to it
Work.LoadLinkedFile += new LoadLinkedFileEventHandler(Work_LoadLinkedFile); //Set up an event to load the linked files.
// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
report.Text = "Results on file: " + openFileDialog1.FileName;
TUnsupportedFormulaList Usl = Xls.RecalcAndVerify();
if (Usl.Count == 0)
{
report.Text += "\n**********All formulas supported!**********";
return;
}
report.Text += "\nIssues Found:";
for (int i = 0; i < Usl.Count; i++)
{
string FileName = String.Empty;
if (Usl[i].FileName != null) FileName = "File: " + Usl[i].FileName + " => ";
report.Text += "\n " + FileName + Usl[i].Cell.CellRef + ": " + Usl[i].ErrorType.ToString();
if (Usl[i].StackTrace != null)
{
for (int k = 0; k < Usl[i].StackTrace.Length; k++)
{
if (Usl[i].StackTrace[k].Address != null)
{
String TraceFileName = Usl[i].StackTrace[k].FileName == null ? "" : "[" + Usl[i].StackTrace[k].FileName + "]";
report.Text += "\n -> References cell: " + TraceFileName + Usl[i].StackTrace[k].Address.CellRef;
}
}
}
if (Usl[i].FunctionName != null)
{
string FunctionStr = "Function";
if (Usl[i].ErrorType == TUnsupportedFormulaErrorType.ExternalReference) FunctionStr = "Linked file not found";
report.Text += " ->" + FunctionStr + ": " + Usl[i].FunctionName;
}
}
}
private void compareWithExcel_Click(object sender, System.EventArgs e)
{
if (openFileDialog1.ShowDialog() != DialogResult.OK) return;
compareWithExcel.Enabled = false;
validateRecalc.Enabled = false;
try
{
XlsFile xls1 = new XlsFile();
XlsFile xls2 = new XlsFile();
xls1.Open(openFileDialog1.FileName);
xls2.Open(openFileDialog1.FileName);
report.Text = "Compare with Excel: " + openFileDialog1.FileName;
// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// ////////Code here is only needed if you have linked files. In this example we don't know, so we will use it /////////
TWorkspace Work = new TWorkspace(); //Create a workspace
Work.Add(Path.GetFileName(openFileDialog1.FileName), xls1); //Add the original file to it
Work.LoadLinkedFile += new LoadLinkedFileEventHandler(Work_LoadLinkedFile); //Set up an event to load the linked files.
// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CompareXls(xls1, xls2, null);
}
finally
{
compareWithExcel.Enabled = true;
validateRecalc.Enabled = true;
}
}
private void CompareXls(XlsFile xls1, XlsFile xls2, DataTable table)
{
int DiffCount = 0;
xls1.Recalc();
for (int sheet = 1; sheet <= xls1.SheetCount; sheet++)
{
xls1.ActiveSheet = sheet;
xls2.ActiveSheet = sheet;
int aColCount = xls1.ColCount;
for (int r = 1; r <= xls1.RowCount; r++)
for (int c = 1; c <= aColCount; c++)
{
TFormula f = xls1.GetCellValue(r, c) as TFormula;
if (f != null)
{
TCellAddress ad = new TCellAddress(r, c);
TFormula f2 = (TFormula)xls2.GetCellValue(r, c);
if (f.Result == null) f.Result = "";
if (f2.Result == null) f2.Result = "";
double eps = 0;
if (f.Result is Double && f2.Result is Double)
{
if ((Double)f2.Result == 0)
{
if (Math.Abs((double)f.Result) < Double.Epsilon)
eps = 0;
else
eps = Double.NaN;
}
else
eps = (double)f.Result / (Double)f2.Result;
if (Math.Abs(eps - 1) < 0.001)
f.Result = f2.Result;
}
if (!f.Result.Equals(f2.Result))
{
if (table == null)
{
report.Text += "\nSheet:" + xls1.SheetName + " --- Cell:" + ad.CellRef + " --- Calculated: " + f.Result.ToString() + " Excel: " + f2.Result.ToString() + " dif: " + eps.ToString() + " formula: " + f.Text;
Application.DoEvents();
}
else
{
table.Rows.Add(new object[] { xls1.SheetName, ad.CellRef, f.Result.ToString(), f2.Result.ToString(), eps.ToString(), f.Text });
}
DiffCount++;
}
}
}
}
if (table == null)
{
report.Text += "\nFinished Comparing.";
if (DiffCount == 0) report.Text += "\n**********No differences found!**********";
else
report.Text += String.Format("\n --->Found {0} differences", DiffCount);
}
}
private void ValidateXls(XlsFile xls, DataTable table)
{
TUnsupportedFormulaList Usl = xls.RecalcAndVerify();
for (int i = 0; i < Usl.Count; i++)
{
table.Rows.Add(new object[]
{
Usl[i].FileName,
Usl[i].Cell.CellRef,
Usl[i].ErrorType.ToString(),
Usl[i].FunctionName
});
}
}
/// <summary>
/// This is the method that will be called by the ASP.NET front end. It returns an array of bytes
/// with the report data, so the ASP.NET application can stream it to the client.
/// </summary>
/// <returns>The generated file as a byte array.</returns>
public byte[] WebRun(Stream DataStream, string FileName)
{
XlsReport.SetValue("Date", DateTime.Now);
XlsReport.SetValue("FileName", FileName);
DataSet Data = new DataSet();
DataTable ValidateResult = Data.Tables.Add("ValidateResult");
ValidateResult.Columns.Add("FileName", typeof(string));
ValidateResult.Columns.Add("CellRef", typeof(string));
ValidateResult.Columns.Add("ErrorType", typeof(string));
ValidateResult.Columns.Add("FunctionName", typeof(string));
DataTable CompareResult = Data.Tables.Add("CompareResult");
CompareResult.Columns.Add("SheetName", typeof(string));
CompareResult.Columns.Add("CellRef", typeof(string));
CompareResult.Columns.Add("CalcResult", typeof(string));
CompareResult.Columns.Add("XlsResult", typeof(string));
CompareResult.Columns.Add("Diff", typeof(string));
CompareResult.Columns.Add("FormulaText", typeof(string));
XlsReport.AddTable(Data);
XlsFile xls1 = new XlsFile();
XlsFile xls2 = new XlsFile();
xls1.Open(DataStream);
DataStream.Position = 0;
xls2.Open(DataStream);
CompareXls(xls1, xls2, CompareResult);
ValidateXls(xls1, ValidateResult);
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
using (MemoryStream OutStream = new MemoryStream())
{
using (FileStream InStream = new FileStream(DataPath + "ValidateReport.xls", FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XlsReport.Run(InStream, OutStream);
return OutStream.ToArray();
}
}
}
/// <summary>
/// This event is used when there are linked files, to load them on demand.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Work_LoadLinkedFile(object sender, LoadLinkedFileEventArgs e)
{
//IMPORTANT: DO NOT USE THIS METHOD IN PRODUCTION IF SECURITY IS IMPORTANT.
//This method will access any file in your harddisk, as long as it is linked in the spreaadhseet, and
//that could mean an IMPORTANT SECURITY RISK. You should limit the places where the app can search for
//linked files. Look at the "Recalculating Linked Files" in the PDF API Guide for more information.
string FilePath = Path.Combine(Path.GetDirectoryName(openFileDialog1.FileName), e.FileName);
if (File.Exists(FilePath)) //If we find the path, just load the file.
{
e.Xls = new XlsFile();
e.Xls.Open(FilePath);
return;
}
//If we couldn't find the file, ask the user for its location.
linkedFileDialog.FileName = FilePath;
if (linkedFileDialog.ShowDialog() != DialogResult.OK) return; //if user cancels, e.Xls will be null, so no file will be used and an #errna error will show in the formulas.
e.Xls = new XlsFile();
e.Xls.Open(linkedFileDialog.FileName);
}
}
}
Form1.Designer.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using System.IO;
using System.Diagnostics;
using System.Reflection;
namespace ValidateRecalc
{
public partial class mainForm: System.Windows.Forms.Form
{
private System.Windows.Forms.OpenFileDialog openFileDialog1;
private System.Windows.Forms.RichTextBox report;
private System.Windows.Forms.OpenFileDialog linkedFileDialog;
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(mainForm));
this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
this.report = new System.Windows.Forms.RichTextBox();
this.XlsReport = new FlexCel.Report.FlexCelReport();
this.linkedFileDialog = new System.Windows.Forms.OpenFileDialog();
this.mainToolbar = new System.Windows.Forms.ToolStrip();
this.validateRecalc = new System.Windows.Forms.ToolStripButton();
this.compareWithExcel = new System.Windows.Forms.ToolStripButton();
this.toolStripSeparator1 = new System.Windows.Forms.ToolStripSeparator();
this.btnInfo = new System.Windows.Forms.ToolStripButton();
this.btnExit = new System.Windows.Forms.ToolStripButton();
this.mainToolbar.SuspendLayout();
this.SuspendLayout();
//
// openFileDialog1
//
this.openFileDialog1.DefaultExt = "xls";
this.openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All " +
"files|*.*";
this.openFileDialog1.Title = "Open an Excel File";
//
// report
//
this.report.Dock = System.Windows.Forms.DockStyle.Fill;
this.report.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.report.Location = new System.Drawing.Point(0, 38);
this.report.Name = "report";
this.report.ReadOnly = true;
this.report.Size = new System.Drawing.Size(768, 327);
this.report.TabIndex = 3;
this.report.Text = "";
//
// linkedFileDialog
//
this.linkedFileDialog.DefaultExt = "xls";
this.linkedFileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All " +
"files|*.*";
this.linkedFileDialog.Title = "Please supply the location for the following linked file.";
//
// mainToolbar
//
this.mainToolbar.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {
this.validateRecalc,
this.compareWithExcel,
this.toolStripSeparator1,
this.btnInfo,
this.btnExit});
this.mainToolbar.Location = new System.Drawing.Point(0, 0);
this.mainToolbar.Name = "mainToolbar";
this.mainToolbar.Size = new System.Drawing.Size(768, 38);
this.mainToolbar.TabIndex = 11;
this.mainToolbar.Text = "toolStrip1";
//
// validateRecalc
//
this.validateRecalc.Image = ((System.Drawing.Image)(resources.GetObject("validateRecalc.Image")));
this.validateRecalc.ImageTransparentColor = System.Drawing.Color.Magenta;
this.validateRecalc.Name = "validateRecalc";
this.validateRecalc.Size = new System.Drawing.Size(90, 35);
this.validateRecalc.Text = "&Validate Recalc";
this.validateRecalc.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
this.validateRecalc.Click += new System.EventHandler(this.validateRecalc_Click);
//
// compareWithExcel
//
this.compareWithExcel.Image = ((System.Drawing.Image)(resources.GetObject("compareWithExcel.Image")));
this.compareWithExcel.ImageTransparentColor = System.Drawing.Color.Magenta;
this.compareWithExcel.Name = "compareWithExcel";
this.compareWithExcel.Size = new System.Drawing.Size(115, 43);
this.compareWithExcel.Text = "Compare with Excel";
this.compareWithExcel.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
this.compareWithExcel.Click += new System.EventHandler(this.compareWithExcel_Click);
//
// toolStripSeparator1
//
this.toolStripSeparator1.Name = "toolStripSeparator1";
this.toolStripSeparator1.Size = new System.Drawing.Size(6, 46);
//
// btnInfo
//
this.btnInfo.Image = ((System.Drawing.Image)(resources.GetObject("btnInfo.Image")));
this.btnInfo.ImageTransparentColor = System.Drawing.Color.Magenta;
this.btnInfo.Name = "btnInfo";
this.btnInfo.Size = new System.Drawing.Size(74, 43);
this.btnInfo.Text = "Information";
this.btnInfo.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
this.btnInfo.Click += new System.EventHandler(this.btnInfo_Click);
//
// btnExit
//
this.btnExit.Alignment = System.Windows.Forms.ToolStripItemAlignment.Right;
this.btnExit.Image = ((System.Drawing.Image)(resources.GetObject("btnExit.Image")));
this.btnExit.ImageTransparentColor = System.Drawing.Color.Magenta;
this.btnExit.Name = "btnExit";
this.btnExit.Size = new System.Drawing.Size(59, 35);
this.btnExit.Text = " E&xit ";
this.btnExit.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
this.btnExit.Click += new System.EventHandler(this.button2_Click);
//
// mainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(768, 365);
this.Controls.Add(this.report);
this.Controls.Add(this.mainToolbar);
this.Name = "mainForm";
this.Text = "Validate FlexCel recalculation";
this.mainToolbar.ResumeLayout(false);
this.mainToolbar.PerformLayout();
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private ToolStrip mainToolbar;
private ToolStripButton validateRecalc;
private ToolStripButton compareWithExcel;
private ToolStripSeparator toolStripSeparator1;
private ToolStripButton btnInfo;
private ToolStripButton btnExit;
}
}
Program.cs
using System;
using System.Windows.Forms;
namespace ValidateRecalc
{
static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new mainForm());
}
}
}