Getting Started with FlexCel Studio for the .NET Framework
0. Before starting: Choosing how to install and reference FlexCel
When installing FlexCel, there are 2 options:
Download the exe setup. This is the preferred way to install FlexCel in Windows, since it will install the NuGet package, the libraries, the examples and docs.
Download the NuGet packages. This includes only the NuGet packages (which are also included in the exe setup), but doesn't include example code or docs, and won't register the NuGet source in your machine. This is the preferred way to install FlexCel in platforms different from Windows, and you can find more information on how to install it on the installation guide.
Once you have FlexCel installed, you need to decide how to reference it. There are 2 ways you can reference FlexCel:
Install via NuGet packages. This is a standard installation same as any other NuGet installation, but with the difference that FlexCel is not stored in nuget.org How to install via NuGet is detailed step by step in the installation guide
Install by manually referencing the assemblies. This is possible in all platforms except in .NET Core, where the only option is via NuGet.
How you decide to reference the assemblies is up to you: .NET is moving from a monolithic framework to a framework "on demand" via NuGet, and so we would recommend you to use NuGet too. But if you prefer to reference the assemblies directly, you can do that too.
Tip
If you are unsure, just install the exe setup and use the FlexCel NuGet packages.
1. Creating an Excel file with code
The simplest way to use FlexCel is to use the XlsFile class to manipulate files.
To get started, create an empty Console application and save it. Add the TMS.FlexCel NuGet package to your application or add a manual reference to FlexCel.dll
Then replace all the text in the file by the following:
using System;
using FlexCel.Core;
using FlexCel.XlsAdapter;
namespace Samples
{
class MainClass
{
public static void Main(string[] args)
{
//Create a new empty Excel file, with default formatting as if it was created by Excel 2019.
//Different Excel versions can have different formatting when they create
//an empty file, so for example
//Excel 2003 will have a default font of Arial, and 2019 will use Calibri.
//This format is anyway the starting format, you can change it all later.
XlsFile xls = new XlsFile(1, TExcelFileFormat.v2019, true);
//Enters a string into A1.
xls.SetCellValue(1, 1, "Hello from FlexCel!");
//Enters a number into A2.
//Note that xls.SetCellValue(2, 1, "7") would enter a string.
xls.SetCellValue(2, 1, 7);
//Enter another floating point number.
//All numbers in Excel are floating point,
//so even if you enter an integer, it will be stored as double.
xls.SetCellValue(3, 1, 11.3);
//Enters a formula into A4.
xls.SetCellValue(4, 1, new TFormula("=Sum(A2:A3)"));
//Saves the file to the "Documents" folder.
xls.Save(System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "test.xlsx"));
}
}
}
And that is it. You have just made an application that creates an Excel file. Of course we are just scratching the surface: we will see more advanced uses later in this guide.
Note
This sample deduces the file format from the file name. If you saved as "test.xls", the file format would be xls, not xlsx. You can specify the file format in a parameter to the "Save" method if needed; for example when saving to streams.
2. Creating a more complex file with code
While creating a simple file is simple (as it should), the functionality in Excel is quite big, and it can be hard to find out the exact method to do something. FlexCel comes with a tool that makes this simpler:
2.1 Open APIMate
When you install FlexCel, it will install a tool named APIMate. You can access it by going to the Start Menu and searching for APIMate.
You can also download APIMate for your operating system from the following locations:
Or you can compile it from source (sources are included when you install FlexCel).
2.2. Create a file in Excel with the functionality you want.
To get the best results, keep the file simple. Say you want to find out how to add an autofilter, create an empty file in FlexCel and add an autofilter. If you want to find out how to format a cell with a gradient, create a different file and format one cell with a gradient.
Using simple files will make it much easier to find the relevant code in APIMate
2.3. Open the file with APIMate
APIMate will tell you the code you need to recreate the file you created in Excel with FlexCel. You can see the code as C# or VB.NET.
You can keep the xls/x file open in both Excel and APIMate, modify the file in Excel, save, press "Refresh" in APIMate to see the changes.
Imagine you have this file, with a cell formatted in blue:
When you open it in apimate, you should see this code which is the code you need to write in FlexCel to generate the same file:
Note that there is a language button in the toolbar where you can choose which language you want the code to be.
3. Reading a file
There is a complete example on Reading files in the documentation. But for simple reading, you can do as follows:
Create a new Console application, and write the following code:
using System;
using FlexCel.Core;
using FlexCel.XlsAdapter;
namespace FileReader
{
class MainClass
{
public static void Main(string[] args)
{
XlsFile xls = new XlsFile(System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "test.xlsx"));
xls.ActiveSheetByName = "Sheet1"; //we'll read sheet1. We could loop over the existing sheets by using xls.SheetCount and xls.ActiveSheet
for (int row = 1; row <= xls.RowCount; row++)
{
for (int colIndex = 1; colIndex <= xls.ColCountInRow(row); colIndex++) //Don't use xls.ColCount as it is slow: https://doc.tmssoftware.com/flexcel/net/guides/performance-guide.html#avoid-calling-colcount
{
int XF = -1;
object cell = xls.GetCellValueIndexed(row, colIndex, ref XF);
TCellAddress addr = new TCellAddress(row, xls.ColFromIndex(row, colIndex));
Console.Write("Cell " + addr.CellRef + " ");
if (cell == null) Console.WriteLine("is empty.");
else if (cell is TRichString) Console.WriteLine("has a rich string.");
else if (cell is string) Console.WriteLine("has a string.");
else if (cell is Double) Console.WriteLine("has a number.");
else if (cell is bool) Console.WriteLine("has a bool.");
else if (cell is TFlxFormulaErrorValue) Console.WriteLine("has an error.");
else if (cell is TFormula) Console.WriteLine("has a formula.");
else Console.WriteLine("Error: Unknown cell type");
}
}
}
}
}
4. Manipulating files
APIMate will tell you about a huge number of things, like how to paint a cell in red, or how to insert an autofilter. But there are some methods that APIMate can't tell you about, and from those the most important are the manipulating methods:
Use ExcelFile.InsertAndCopyRange for inserting rows or column or ranges of cells. Also for copying ranges or cells or full rows or full columns. Or for inserting and copying cells/columns/rows in one operation (like pressing "Copy/Insert copied cells" in Excel). It can also copy the cells from one sheet to the same sheet, to another sheet, or to another sheet in another file. InsertAndCopyRange is a heavily overloaded method, and it can do many things depending on the parameters you pass to it.
Use ExcelFile.DeleteRange to delete ranges of cells, full rows or full columns.
Use ExcelFile.MoveRange to move a range, full rows or full columns from one place to another.
Use ExcelFile.InsertAndCopySheets to insert a sheet, to copy a sheet, or to insert and copy a sheet in the same operation.
Use ExcelFile.DeleteSheet to delete a sheet.
5. Creating Reports
You can create Excel files with code as shown above, but FlexCel also includes a reporting engine which uses Excel as the report designer. When using reports you create a template in Excel, write some tags on it, and run the report. FlexCel will replace those tags by the values from a database or memory.
Create an empty file in Excel
In cell A1 of the first sheet, write <#Customer.Name>. In cell B1 write <#Customer.Address>
In the ribbon, go to "Formulas" tab, and press "Name manager" (In Excel for macOS or Excel 2003, go to Menu->Insert->Name->Define)
Create a name "Customer" that refers to "=Sheet1!$A$1". The name is case insensitive, you can write it in any mix of upper and lower case letters. It needs to start with two underscores ("_") and end with two underscores too. We could use a single underscore for bands that don't take the full row or "I_" or "I__" for column reports instead, but this is for more advanced uses.
Save the file as "report.template.xlsx" in the "Documents" folder
Create a new Console app, save it as "CustomerReport", and paste the following code:
using System;
using System.Collections.Generic;
using FlexCel.Core;
using FlexCel.Report;
namespace Samples
{
class MainClass
{
public static void Main(string[] args)
{
var Customers = new List<Customer>();
Customers.Add(new Customer { Name = "Bill", Address = "555 demo line" });
Customers.Add(new Customer { Name = "Joe", Address = "556 demo line" });
using (FlexCelReport fr = new FlexCelReport(true))
{
fr.AddTable("Customer", Customers);
fr.Run(
System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "report.template.xlsx"),
System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "result.xlsx")
);
}
}
}
class Customer
{
public string Name { get; set; }
public string Address { get; set; }
}
}
Note
If creating a macOS Console application, you will need to add a reference to System.Data, System.Xml and XamMac or MonoMac to the app. You might also need to copy XamMac.dll or MonoMac.dll to your output folder. And in a console application, you will need to initialize the Cocoa framework by calling MonoMac.AppKit.NSApplication.Init() For normal macOS applications you will not need to do anything: This applies only to Console macOS apps.
6. Exporting a file to pdf
FlexCel offers a lot of options to export to pdf, like PDF/A, exporting font subsets, signing the generated pdf documents, etc. This is all shown in the examples and documentation. But for a simple conversion between xls/x and pdf you can use the following code:
public static void ExportToPdf(string inFile, string outFile)
{
XlsFile xls = new XlsFile(inFile);
using (var pdf = new FlexCel.Render.FlexCelPdfExport(xls, true))
{
pdf.Export(outFile);
}
}
7. Exporting a file to html
As usual, there are too many options when exporting to html to show here: Exporting as HTML 3.2, HTML 4 or HTML 5, embedding images or css, exporting each sheet as a tab and a big long list of etc. And as usual, you can find all the options in the documentation and examples.
For this getting started guide, we will show how to do an export with the default options of the active sheet.
public static void ExportToHtml(string inFile, string outFile)
{
XlsFile xls = new XlsFile(inFile);
using (var html = new FlexCel.Render.FlexCelHtmlExport(xls, true))
{
html.Export(outFile, null);
}
}
8. Browsing through the Examples
FlexCel comes with more than 50 examples of how to do specific things. You can open each demo as a standalone project, but you can also use the included "Demo Browser" (this is MainDemo.csproj) to look at them all in a single place.
You can search for specific keywords at the top right of the main screen, to locate the demos that deal with specific features. So for example if you are looking for demos which show encryption, you will write "encrypt" in the search box:
9. This ends this small guide, but there is much more.
Make sure to take a look at all the other documents available here. You can use the tabs at the top of this site to read the different sections.