Advanced API (C# / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\10.API\12.Advanced API and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/VS2022/netframework/Modules/10.API/12.Advanced API
Overview
This demo shows a lot of unrelated things you can do with the FlexCel API.
It is a complement of what was shown in GettingStarted: this means things that were shown there (like how to insert an image) will not be repeated here. You should look at both demos when studying the API.
The things shown here do not make much sense in the real world; they are explicitly designed to show what can be done.
Also remember that most of this stuff can be found easier with APIMate
The created file will be password protected, and all the passwords are "flexcel" (without quotes)
Concepts
FlexCel shines at modifying existing Excel files. It has been designed from the start with that in mind, and to not losing anything in the original file. So we will start from an existing file with the skeleton of what we want to do, and modify it with code. This starting file (template.xls/x) could be embedded in the exe file, but this is not shown here. To see an example on how to embed the template inside the exe, look at Templates In The Exe
The starting template has a macro. At this time we can not create macros with FlexCel, but we can modify a file with existing macros and those macros will be in the final file.
How to copy a chart from other template and fill the values.
How to add Outlines.
How to freeze panes.
How to add data validation.
How to search and replace in a range of cells.
How to sort a range of cells.
How to Autofit the rows in a sheet.
How to protect the file and cells with a password.
As FlexCel doesn't convert charts between xlsx and xls, if you want to create an xls file you need to start with an xls template, and the same goes for xlsx. So we need two templates.
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 - 2025 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.24.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.Windows.Forms;
using System.Data;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using System.Text;
namespace AdvancedAPI
{
/// <summary>
/// A demo on creating a file using more advanced features.
/// </summary>
public partial class mainForm: System.Windows.Forms.Form
{
public mainForm()
{
InitializeComponent();
}
private void button1_Click(object sender, System.EventArgs e)
{
ExcelFile Xls = new XlsFile(true);
AddData(Xls);
NormalOpen(Xls);
}
/// <summary>
/// We will use this path to find the template.xls. Code is a little complex because it has to run in mono.
/// </summary>
private string PathToExe
{
get
{
return Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
}
}
//some silly data to fill in the cells. A real app would read this from somewhere else.
readonly string[] Country = { "USA", "Canada", "Spain", "France", "United Kingdom", "Australia", "Brazil", "Unknown" };
readonly int DataRows = 100;
/// <summary>
/// Will return a list of countries separated by Character(0) so it can be used as input for a built in list.
/// </summary>
/// <returns></returns>
private string GetCountryList()
{
StringBuilder sb = new StringBuilder();
string sep = "";
foreach (string c in Country)
{
sb.Append(sep);
sb.Append(c);
sep = "\0"; //not very efficient method to concat, but good enough for this demo.
}
return sb.ToString();
}
private void AddChart(TXlsNamedRange DataCell, ExcelFile Xls)
{
//Find the cell where the cart will go.
TXlsNamedRange ChartRange = Xls.GetNamedRange("ChartData", -1);
//Insert cells to expand the range for the chart. It already has 2 rows, so we need to insert Country.Length - 2
//Note also that we insert after ChartRange.Top, so the chart is updates with the new range.
Xls.InsertAndCopyRange(new TXlsCellRange(ChartRange.Top, ChartRange.Left, ChartRange.Top, ChartRange.Left + 1),
ChartRange.Top + 1, ChartRange.Left, Country.Length - 2, TFlxInsertMode.ShiftRangeDown); //we use shiftrangedown so not all the row goes down and the chart stays in place.
//Get the cell addresses of the data range.
TCellAddress FirstCell = new TCellAddress(DataCell.Top, DataCell.Left);
TCellAddress SecondCell = new TCellAddress(DataCell.Top + DataRows, DataCell.Left + 1);
TCellAddress FirstSumCell = new TCellAddress(DataCell.Top, DataCell.Left + 1);
//Fill a table with the data to be used in the chart.
for (int r = ChartRange.Top; r < ChartRange.Top + Country.Length; r++)
{
Xls.SetCellValue(r, ChartRange.Left, Country[r - ChartRange.Top]);
Xls.SetCellValue(r, ChartRange.Left + 1, new TFormula("=SUMIF(" + FirstCell.CellRef + ":" + SecondCell.CellRef +
",\"" + Country[r - ChartRange.Top] + "\", " + FirstSumCell.CellRef + ":" + SecondCell.CellRef + ")"));
}
}
private void AddData(ExcelFile Xls)
{
string TemplateFile = "template.xls";
if (cbXlsxTemplate.Checked)
{
if (!XlsFile.SupportsXlsx)
{
throw new Exception("Xlsx files are not supported in this version of the .NET framework");
}
TemplateFile = "template.xlsm";
}
// Open an existing file to be used as template. In this example this file has
// little data, in a real situation it should have as much as possible. (Or even better, be a report)
Xls.Open(Path.Combine(PathToExe, TemplateFile));
//Find the cell where we want to fill the data. In this case, we have created a named range "data" so the address
//is not hardcoded here.
TXlsNamedRange DataCell = Xls.GetNamedRange("Data", -1);
//Add a chart with totals
AddChart(DataCell, Xls);
//Note that "DataCell" will change because we inserted rows above it when creating the chart. But we will keep using the old one.
//Add the captions. This should probably go into the template, but in a dynamic environment it might go here.
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left, "Country");
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left + 1, "Quantity");
//Add a rectangle around the cells
TFlxApplyFormat ApplyFormat = new TFlxApplyFormat();
ApplyFormat.SetAllMembers(false);
ApplyFormat.Borders.SetAllMembers(true); //We will only apply the borders to the existing cell formats
TFlxFormat fmt = Xls.GetDefaultFormat;
fmt.Borders.Left.Style = TFlxBorderStyle.Double;
fmt.Borders.Left.Color = Colors.Black;
fmt.Borders.Right.Style = TFlxBorderStyle.Double;
fmt.Borders.Right.Color = Colors.Black;
fmt.Borders.Top.Style = TFlxBorderStyle.Double;
fmt.Borders.Top.Color = Colors.Black;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Double;
fmt.Borders.Bottom.Color = Colors.Black;
Xls.SetCellFormat(DataCell.Top - 1, DataCell.Left, DataCell.Top, DataCell.Left + 1, fmt, ApplyFormat, true); //Set last parameter to true so it draws a box.
//Freeze panes
Xls.FreezePanes(new TCellAddress(DataCell.Top, 1));
Random Rnd = new Random();
//Fill the data
int z = 0;
int OutlineLevel = 0;
for (int r = 0; r <= DataRows; r++)
{
//Fill the values.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left, Country[z % Country.Length]); //For non C# users, "%" means "mod" or modulus in other languages. It is the rest of the integer division.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left + 1, Rnd.Next(1000));
//Add the country to the outline
Xls.SetRowOutlineLevel(DataCell.Top + r, OutlineLevel);
//increment the country randomly
if (Rnd.Next(3) == 0)
{
z++;
OutlineLevel = 0; //Break the group and create a new one.
}
else
{
OutlineLevel = 1;
}
}
//Make the "+" signs of the outline appear at the top.
Xls.OutlineSummaryRowsBelowDetail = false;
//Collapse the outline to the first level.
Xls.CollapseOutlineRows(1, TCollapseChildrenMode.Collapsed);
//Add Data Validation for the first column, it must be a country.
TDataValidationInfo dv = new TDataValidationInfo(
TDataValidationDataType.List, //We will use a built in list.
TDataValidationConditionType.Between, //This parameter does not matter since it is a list. It will not be used.
"=\"" + GetCountryList() + "\"", //We could have used a range of cells here with the values (like "=C1..C4") Instead, we directly entered the list in the formula.
null, //no need for a second formula, not used in List
false,
true,
true, //Note that as we entered the data directly in FirstFormula, we need to set this to true
true,
"Unknown country",
"Please make sure that the country is in the list",
false, //We will not use an input box, so this is false and the 2 next entries are null
null,
null,
TDataValidationIcon.Stop); //We will use the stop icon so no invalid input is permitted.
Xls.AddDataValidation(new TXlsCellRange(DataCell.Top, DataCell.Left, DataCell.Top + DataRows, DataCell.Left), dv);
//Add Data Validation for the second column, it must be an integer between 0 and 1000.
dv = new TDataValidationInfo(
TDataValidationDataType.WholeNumber, //We will request a number.
TDataValidationConditionType.Between,
"=0", //First formula marks the first part of the "between" condition.
"=1000", //Second formula is the second part.
false,
false,
false,
true,
"Invalid Quantity",
null, //We will leave the default error message.
true,
"Quantity:",
"Please enter a quantity between 0 and 1000",
TDataValidationIcon.Stop); //We will use the stop icon so no invalid input is permitted.
Xls.AddDataValidation(new TXlsCellRange(DataCell.Top, DataCell.Left + 1, DataCell.Top + DataRows, DataCell.Left + 1), dv);
//Search country "Unknown" and replace it by "no".
//This does not make any sense here (we could just have entered "no" to begin)
//but it shows how to do it when modifying an existing file
Xls.Replace("Unknown", "no", TXlsCellRange.FullRange(), true, false, true);
//Autofit the rows. As we keep the row height automatic this will not show when opening in Excel, but will work when directly printing from FlexCel.
Xls.AutofitRowsOnWorkbook(false, true, 1);
Xls.Recalc(); //Calculate the SUMIF formulas so we can sort by them. Note that FlexCel automatically recalculates before saving,
//but in this case we haven't saved yet, so the sheet is not recalculated. You do not normally need to call Recalc directly.
//Sort the data. As in the case with replace, this does not make much sense. We could have entered the data sorted to begin
//But it shows how you can use the feature.
//Find the cell where the chart goes.
TXlsNamedRange ChartRange = Xls.GetNamedRange("ChartData", -1);
Xls.Sort(new TXlsCellRange(ChartRange.Top, ChartRange.Left, ChartRange.Top + Country.Length, ChartRange.Left + 1),
true, new int[] { 2 }, new TSortOrder[] { TSortOrder.Descending }, null);
//Protect the Sheet
TSheetProtectionOptions Sp = new TSheetProtectionOptions(false); //Create default protection options that allows everything.
Sp.InsertColumns = false; //Restrict inserting columns.
Xls.Protection.SetSheetProtection("flexcel", Sp);
//Set a modify password. Note that this does *not* encrypt the file.
Xls.Protection.SetModifyPassword("flexcel", true, "flexcel");
Xls.Protection.OpenPassword = "flexcel"; //OpenPasword is the only password that will actually encrypt the file, so you will not be able to open it with flexcel if you do not know the password.
//Select cell A1
Xls.SelectCell(1, 1, true);
}
//This is part of an advanced feature (showing the user using a file) , you do not need to use
//this method on normal places.
private string GetLockingUser(string FileName)
{
try
{
XlsFile xerr = new XlsFile();
xerr.Open(FileName);
return " - File might be in use by: " + xerr.Protection.WriteAccess;
}
catch
{
return String.Empty;
}
}
private void NormalOpen(ExcelFile Xls)
{
if (cbXlsxTemplate.Checked) saveFileDialog1.FilterIndex = 1; else saveFileDialog1.FilterIndex = 0;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
if (!XlsFile.SupportsXlsx && Path.GetExtension(saveFileDialog1.FileName) == ".xlsm")
{
throw new Exception("Xlsx files are not supported in this version of the .NET framework");
}
try
{
Xls.Save(saveFileDialog1.FileName);
}
catch (IOException ex) //This is not really needed, just to show the username of the user locking the file.
{
throw new IOException(ex.Message + GetLockingUser(saveFileDialog1.FileName), ex);
}
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();
}
}
}
}
}
}
Form1.Designer.cs
using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using System.Text;
namespace AdvancedAPI
{
public partial class mainForm : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.SaveFileDialog saveFileDialog1;
private System.Windows.Forms.Label label1;
/// <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()
{
System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(mainForm));
this.button1 = new System.Windows.Forms.Button();
this.saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
this.label1 = new System.Windows.Forms.Label();
this.cbXlsxTemplate = new System.Windows.Forms.CheckBox();
this.SuspendLayout();
//
// button1
//
this.button1.Anchor = System.Windows.Forms.AnchorStyles.Bottom;
this.button1.Location = new System.Drawing.Point(136, 160);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75, 23);
this.button1.TabIndex = 0;
this.button1.Text = "GO!";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// saveFileDialog1
//
this.saveFileDialog1.Filter = "Excel Files (97-2003)|*.xls|Excel Files (2007 or newer)|*.xlsm";
this.saveFileDialog1.RestoreDirectory = true;
//
// label1
//
this.label1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.label1.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(192)))));
this.label1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
this.label1.Location = new System.Drawing.Point(16, 16);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(320, 90);
this.label1.TabIndex = 1;
this.label1.Text = resources.GetString("label1.Text");
//
// cbXlsxTemplate
//
this.cbXlsxTemplate.AutoSize = true;
this.cbXlsxTemplate.Location = new System.Drawing.Point(16, 120);
this.cbXlsxTemplate.Name = "cbXlsxTemplate";
this.cbXlsxTemplate.Size = new System.Drawing.Size(108, 17);
this.cbXlsxTemplate.TabIndex = 2;
this.cbXlsxTemplate.Text = "Use xlsx template";
this.cbXlsxTemplate.UseVisualStyleBackColor = true;
//
// mainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(344, 197);
this.Controls.Add(this.cbXlsxTemplate);
this.Controls.Add(this.label1);
this.Controls.Add(this.button1);
this.Name = "mainForm";
this.Text = "Advanced API";
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private CheckBox cbXlsxTemplate;
}
}
Program.cs
using System;
using System.Windows.Forms;
namespace AdvancedAPI
{
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());
}
}
}