User defined formats (C# / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\20.Reports\95b.User Defined Formats and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/VS2022/netframework/Modules/20.Reports/95b.User Defined Formats
Overview
User defined formats are a way to format cells in a report with code. Normally you will want to use them if the data to format the cells is in the database and it is not known at compile time, so you can't use conditional formatting or formats defined in the config sheet.
Concepts
How to define and use a user defined format.
You can pass parameters to the user defined format by writing them after the format name. In this example we show passing one parameter (in user format "ZipCode") and two parameters (in user format "ShipFormat").
If you don't want to modify the format then return a null TFlxFormat and a null TFlxApplyFormat. We do this in the ZipCode user format, when the zip code is not numeric. If you wanted to return a format but not apply, you could set only the TFlxApplyFormat to null and the full TFlxFormat would be applied.
As you can see, the colors in this demo don't make much sense. There is a reason for that: trying to find an example of stuff that can only be made with user defined formats is not easy. If you wanted for example to mark red the cells where a value is > 100, then you would just use an Excel conditional format instead. The recommended order of preference would be:
If you can do it with Excel conditional formats, then use that.
If you can't use conditional formats then use formats defined in the config sheet
For more complex formats where you can't use any of the above (like for example if the color of the cell is stored in the database, or the rules are too complex to code them in a conditional format or the config sheet), then use user defined formats.
You can use user defined formats to format rows, columns or cells. As format cell has more priority than format row, in this example there are cells which override the row format.
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 System.IO;
using System.Diagnostics;
using System.Reflection;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using FlexCel.Report;
using FlexCel.Demo.SharedData;
namespace UserDefinedFormats
{
public partial class mainForm: System.Windows.Forms.Form
{
public mainForm()
{
InitializeComponent();
}
private void button1_Click(object sender, System.EventArgs e)
{
AutoRun();
}
public void AutoRun()
{
using (FlexCelReport ordersReport = SharedData.CreateReport())
{
ordersReport.SetValue("Date", DateTime.Now);
ordersReport.SetUserFormat("ZipCode", new ZipCodeImp());
ordersReport.SetUserFormat("ShipFormat", new ShipFormatImp());
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
ordersReport.Run(DataPath + "User Defined Formats.template.xlsx", saveFileDialog1.FileName);
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
using (Process p = new Process())
{
p.StartInfo.FileName = saveFileDialog1.FileName;
p.StartInfo.UseShellExecute = true;
p.Start();
}
}
}
}
}
private void btnCancel_Click(object sender, System.EventArgs e)
{
Close();
}
}
#region ZipCode Implementation
class ZipCodeImp: TFlexCelUserFormat
{
public ZipCodeImp()
{
}
public override TFlxPartialFormat Evaluate(ExcelFile workbook, TXlsCellRange rangeToFormat, object[] parameters)
{
if (parameters == null || parameters.Length != 1)
throw new ArgumentException("Bad parameter count in call to ZipCode() user-defined format");
int color;
//If the zip code is not valid, don't modify the format.
if (parameters[0] == null || !int.TryParse(Convert.ToString(parameters[0]), out color)) return new TFlxPartialFormat(null, null, false);
//This code is not supposed to make sense. We will convert the zip code to a color based in the numeric value.
TFlxFormat fmt = workbook.GetDefaultFormat;
fmt.FillPattern.Pattern = TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor = TExcelColor.FromArgb(color);
fmt.FillPattern.BgColor = TExcelColor.Automatic;
fmt.Font.Color = TExcelColor.FromArgb(~color);
TFlxApplyFormat apply = new TFlxApplyFormat();
apply.FillPattern.SetAllMembers(true);
apply.Font.Color = true;
return new TFlxPartialFormat(fmt, apply, false);
}
}
#endregion
#region ShipFormat Implementation
class ShipFormatImp : TFlexCelUserFormat
{
public ShipFormatImp()
{
}
public override TFlxPartialFormat Evaluate(ExcelFile workbook, TXlsCellRange rangeToFormat, object[] parameters)
{
//Again, this example is not supposed to make sense, only to show how you can code a complex rule.
//This method will format the rows with a color that depends in the length of the first parameter,
//and if the second parameter starts with "B" it will make the text red.
if (parameters == null || parameters.Length != 2)
throw new ArgumentException("Bad parameter count in call to ShipFormat() user-defined format");
int len = Convert.ToString(parameters[0]).Length;
string country = Convert.ToString(parameters[1]);
Int32 color = 0xFFFFFF - len * 100;
TFlxFormat fmt = workbook.GetDefaultFormat;
fmt.FillPattern.Pattern = TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor = TExcelColor.FromArgb(color);
fmt.FillPattern.BgColor = TExcelColor.Automatic;
TFlxApplyFormat apply = new TFlxApplyFormat();
apply.FillPattern.SetAllMembers(true);
if (country.StartsWith("B"))
{
fmt.Font.Color = Colors.OrangeRed;
apply.Font.Color = true;
}
return new TFlxPartialFormat(fmt, apply, false);
}
}
#endregion
}
Form1.Designer.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using FlexCel.Report;
using FlexCel.Demo.SharedData;
namespace UserDefinedFormats
{
public partial class mainForm: System.Windows.Forms.Form
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container 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()
{
this.button1 = new System.Windows.Forms.Button();
this.saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
this.label1 = new System.Windows.Forms.Label();
this.btnCancel = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// button1
//
this.button1.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.button1.BackColor = System.Drawing.Color.Green;
this.button1.ForeColor = System.Drawing.Color.White;
this.button1.Location = new System.Drawing.Point(152, 88);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(112, 23);
this.button1.TabIndex = 0;
this.button1.Text = "GO!";
this.button1.UseVisualStyleBackColor = false;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// saveFileDialog1
//
this.saveFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All files|*.*";
this.saveFileDialog1.RestoreDirectory = true;
//
// label1
//
this.label1.Location = new System.Drawing.Point(24, 24);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(288, 24);
this.label1.TabIndex = 2;
this.label1.Text = "A report using an user defined format.";
//
// btnCancel
//
this.btnCancel.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.btnCancel.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(192)))), ((int)(((byte)(0)))), ((int)(((byte)(0)))));
this.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel;
this.btnCancel.ForeColor = System.Drawing.Color.White;
this.btnCancel.Location = new System.Drawing.Point(272, 88);
this.btnCancel.Name = "btnCancel";
this.btnCancel.Size = new System.Drawing.Size(112, 23);
this.btnCancel.TabIndex = 3;
this.btnCancel.Text = "Cancel";
this.btnCancel.UseVisualStyleBackColor = false;
this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
//
// mainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(416, 133);
this.Controls.Add(this.btnCancel);
this.Controls.Add(this.label1);
this.Controls.Add(this.button1);
this.Name = "mainForm";
this.Text = "User defined formats";
this.ResumeLayout(false);
}
#endregion
private System.Windows.Forms.Button button1;
private System.Windows.Forms.SaveFileDialog saveFileDialog1;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Button btnCancel;
}
}
Program.cs
using System;
using System.Windows.Forms;
namespace UserDefinedFormats
{
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());
}
}
}