Getting Started with FlexCel Studio for VCL and FireMonkey

0. Finding out which files you need to use

Before starting, it is good to know what functionality FlexCel has, and which units you need to use to access that functionality. Here is a list of the units you might need to use in FlexCel explaining what each unit does:

1. Creating an Excel file with code

The simplest way to use FlexCel is to use the TXlsFile class to manipulate files. To get started, create an empty VCL application, add a button, and save it.

Now we need to add the FlexCel units to the “uses” clause. For this example we will add VCL.FlexCel.Core and FlexCel.XlsAdapter. We will also be using System.IOUtils for the TPath object:

uses
  ...,
  System.IOUtils,
  VCL.FlexCel.Core, FlexCel.XlsAdapter;

Then drop a button into the form, double click it, and add the following code:

procedure CreateExcelFile;
var
  xls: TXlsFile;
begin
  xls := TXlsFile.Create(1, TExcelFileFormat.v2016, true);
  try
    xls.SetCellValue(1, 1, 'Hello from FlexCel!');
    xls.SetCellValue(2, 1, 7);
    xls.SetCellValue(3, 1, 11.3);
    xls.SetCellValue(4, 1, TFormula.Create('=Sum(A2:A3)'));
    xls.Save(TPath.Combine(TPath.GetDocumentsPath, 'test.xlsx'));

  finally
    xls.Free;
  end;
end;

procedure TForm2.Button1Click(Sender: TObject);
begin
  CreateExcelFile;
end;

And that is it. You have just created an application that crates an Excel file.

Note that 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 correct 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.

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 Delphi or C++.

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.

3. Reading a file

There is a complete example on Reading files in the documentation. But for simple reading, you can do as follows: Add a Memo component to the form in the app you created in 1. Then add a new Button, and the following code:


procedure TForm2.ReadExcelFile;
var
  xls: TXlsFile;
  row, colIndex: integer;
  XF: integer;
  cell: TCellValue;
  addr: TCellAddress;
  s: string;
begin
  xls := TXlsFile.Create(TPath.Combine(TPath.GetDocumentsPath, 'test.xlsx'));
  try
    xls.ActiveSheetByName := 'Sheet1';  //we'll read sheet1. We could loop over the existing sheets by using xls.SheetCount and xls.ActiveSheet
    for row := 1 to xls.RowCount do
    begin
      for colIndex := 1 to xls.ColCountInRow(row) do //Don't use xls.ColCount as it is slow: See Performance.Pdf
      begin
        XF := -1;
        cell := xls.GetCellValueIndexed(row, colIndex, XF);

        addr := TCellAddress.Create(row, xls.ColFromIndex(row, colIndex));
        s := ('Cell ' + addr.CellRef + ' has ');
        if (cell.IsString) then  s := s + 'a string: ' + cell.ToString
        else if (cell.IsNumber) then s := s + 'a number: ' + FloatToStr(cell.AsNumber)
        else if (cell.IsBoolean) then s := s + 'a boolean: ' + BoolToStr(cell.AsBoolean)
        else if (cell.IsError) then s := s + 'an error: ' + cell.ToString
        else if (cell.IsFormula) then s := s + 'a formula: ' + cell.AsFormula.Text
        else s := s + ('Error: Unknown cell type');
        Memo1.Lines.Add(s);
      end;
    end;
  finally
    xls.Free;
  end;
end;


procedure TForm2.Button2Click(Sender: TObject);
begin
  ReadExcelFile;
end;

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 xls.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 cels 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 xls.DeleteRange to delete ranges of cells, full rows or full columns.

Use xls.MoveRange to move a range, full rows or full columns from one place to another.

Use xls.InsertAndCopySheets to insert a sheet, to copy a sheet, or to insert and copy a sheet in the same operation.

Use xls.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.

5.1. Create an empty file in Excel

5.2. In cell A1 of the first sheet, write <#Customer.Name>. In cell B1 write <#Customer.Address>

5.3. In the ribbon, go to “Formulas” tab, and press “Name manager” (In Excel for OSX or Excel 2003, go to Menu->Insert->Name->Define)

5.3.1. 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.

5.4. Save the file as “report.template.xlsx”

5.5. Create a new Delphi Console app (File->New->Other), save it as “CustomerReport”, and paste the code:

program CustomerReport;
{$APPTYPE CONSOLE}
{$R *.res}

uses
  System.SysUtils, System.IOUtils,
  Generics.Collections, Generics.Defaults,
  VCL.FlexCel.Core, FlexCel.XlsAdapter,
  FlexCel.Report;

type
 TCustomer = class
  private
    FAddress: string;
 public
   Name: string;
   property Address: string read FAddress write FAddress;

   constructor Create(const aName, aAddress: string);
 end;

constructor TCustomer.Create(const aName, aAddress: string);
begin
  Name := aName;
  FAddress := aAddress;
end;


procedure CreateReport;
var
  Customers: TObjectList<TCustomer>;
  fr: TFlexCelReport;
begin
  Customers := TObjectList<TCustomer>.Create;
  try
    Customers.Add(TCustomer.Create('Bill',  '555 demo line'));
    Customers.Add(TCustomer.Create('Joe', '556 demo line'));
    fr := TFlexCelReport.Create(true);
    try
      fr.AddTable<TCustomer>('Customer', Customers);
      fr.Run(
        TPath.Combine(TPath.GetDocumentsPath, 'report.template.xlsx'),
                      TPath.Combine(TPath.GetDocumentsPath, 'result.xlsx')
      );

    finally
      fr.Free;
    end;

  finally
    Customers.Free;
  end;

end;


begin
  try
    CreateReport;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.
5.5.1. Notes.

In this example, we used a TObjectList as a datasource for the report. You can also use arrays or TDataSets as sources, or even create your own datasources. Take a look at the bundled examples for more information.

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 export you can use the following code:

uses ..., VCL.FlexCel.Core, FlexCel.XlsAdapter, FlexCel.Render;
...
procedure ExportToPdf(const InFile, OutFile: string);
var
  xls: TXlsFile;
  pdf: TFlexCelPdfExport;
begin
  xls := TXlsFile.Create(InFile);
  try
    pdf := TFlexCelPdfExport.Create(xls, true);
    try
      pdf.Export(OutFile);
    finally
      pdf.Free;
    end;
  finally
    xls.Free;
  end;

end;

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, 4 or 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.

uses ..., VCL.FlexCel.Core, FlexCel.XlsAdapter, FlexCel.Render;
...
procedure ExportToHtml(const InFile, OutFile: string);
var
  xls: TXlsFile;
  html: TFlexCelHtmlExport;
begin
  xls := TXlsFile.Create(InFile);
  try
    html := TFlexCelHtmlExport.Create(xls, true);
    try
      html.Export(OutFile, '');
    finally
      html.Free;
    end;
  finally
    xls.Free;
  end;
end;

8.Browsing through the Examples

FlexCel comes with more than 50 examples on 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.dproj) 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.

To continue read the following documents (which are also available offline when you install FlexCel for Windows in the start menu->TMS FlexCel):