Blog

All Blog Posts | Next Post | Previous Post

Save and load Excel .XLSX files from grids in Delphi or C++Builder apps

Tuesday, April 12, 2022

The Microsoft Excel file as industry standard

The Excel .XLSX file format is these days a de facto standard file format to exchange all kinds of tabular data. As the information in an Excel sheet is 2 dimensional, a grid control is the most logical choice for viewing such data or to be the source of such data.

For a long time, the TMS VCL TAdvStringGrid component offered built-in support to open and save .XLS files natively without the need to have Microsoft Excel installed. Given how complex the .XLSX file format is, TAdvStringGrid offered only built-in support to save and load .XLSX files via OLE automation, i.e. with the requirement to have Microsoft Excel installed.

On the other side, also for a long time, we have the TMS FlexCel product that specializes in native Excel file manipuluation, i.e. reading, writing, modifying Excel files (.XLS and .XLSX) as well as reporting based on Excel template files or exporting Excel files to PDF, HTML, ... TMS FlexCel has grown into an extremely feature rich and sophisticated product to work with Excel files.

Hence, the idea to make it extremely easy to take advantage of the FlexCel technology to also allow to natively save and load .XLSX files in our grid components, in particular our VCL grid for Windows application development with Delphi or C++Builder as well as our FNC grid for cross-platform development for Windows, macOS, iOS, Android, Linux.

Bridge components

To make this possible, we introduced two free bridge components, the TMS VCL Grid Excel bridge and the TMS FNC Grid Excel bridge. These are non-visual components TAdvGridExcelExport, TAdvGridExcelImport or TTMSFNCGridExcelExport, TTMSFNCGridExcelImport, that simply connect to either TAdvStringGrid (and descending components) as well as the TTMSFNCGrid cross-platform grid component.

After hooking the grid to this component via a property Grid, import or export is as low-code as a single line of code:

To load .XLSX sheet data into a grid:

  TMSFNCGridExcelImport1.Import('test.xlsx', 'my sheet');

In case you wish to work with multiple sheets at once, the bridge components can be connected to a TAdvGridWorkbook and multiple sheets will be imported or exported via the same functions.

To save the data from the grid to an .XLSX file:

  TMSFNCGridExcelExport1.Export('test.xlsx');

and in addition to export to Excel files, there are methods:

  TMSFNCGridExcelExport1.ExportPDF('test.pdf');

  TMSFNCGridExcelExport1.ExportHTML('test.html');

to export the grid to PDF or HTML files.

By default, the TAdvGridExcelExport or TTMSFNCGridExcelExport take in account a lot of cell characteristics for the import or export. This includes:

background color
font color
font name & size
cell alignment
cell pictures
cell merging
cell checkboxes
cell sizes
hyperlinks

There is further fine control over what grid and/or Excel features will be imported & exported via properties under:

TTMSFNCGridExcelImport.ImportOptions, TAdvGridExcelImport.ImportOptions and TTMSFNCGridExcelImport.ExportOptions, TAdvGridExcelExport.ExportOptions

Also, the bridge components also allow you to specify what range of cells to import or export. By default this is the range of used cells in the grid or Excel file, but via TTMSFNCGridExcelImport.LocationOptions, TAdvGridExcelImport.LocationOptions and TTMSFNCGridExcelImport.LocationOptions, TAdvGridExcelExport.LocationOptions you have fine-grained control over what range of cells will be involved in the export/import.

Finally, if you want to dynamically control in code additional formatting of cells, there is the TAdvGridExcelExport.OnCellExport() event that is triggered for every cell and allows to override in code the cell format to apply for example:

procedure TExampleForm.AdvGridExcelExport1ExportCell(Sender: TObject;
  var Args: TExportCellEventArgs);
var
  Fm: TFlxFormat;
begin
  //Format cells in column 3 with a specific format.
  if Args.GridCol = 3 then
  begin
   //We can't modify Args.CellFormat.Property directly, so we assign it to a variable.
   Fm := Args.CellFormat;
   Fm.Format := '00.00';
   Args.CellFormat := Fm;
  end;
end;

Detailed information about the bridge components is available in the online documentation for the VCL bridge component and for the FNC bridge component.

Bruno Fierens

This blog post has received 2 comments.

1. Wednesday, April 13, 2022 at 2:48:19 PM

Do you assume that you will also be able to achieve this for the web components in the foreseeable future?

Eduard Appelhans

2. Wednesday, April 13, 2022 at 2:55:00 PM

We hope so, but porting FlexCel to the web is quite a lot of work. We''ve been working for some years on it, but still there is some way to go

Adrian Gallero