How to use FlexCel TMS library via Delphi app

How to download and install TMS FlexCel component for VCL and FMX

In the process of obtaining and processing various data (such as the results of scientific experiments), you may face a need to save it as an array in a format that is convenient for analysis, such as Excel or PDF, for example.

It is much more convenient to analyze the obtained data when it is also presented in the form of charts (graphs).

 In our article, we will explore the capabilities of the TMS FlexCel library for saving experimental or other statistical data in Excel format with an Embarcadero Delphi FMX app. We will also discuss the possibility of saving data presented as charts (graphs) in an Excel document.

As an example, we will tabulate the standard mathematical sine function. The obtained data for the argument X (of type Double) and the sine function values Y (of type Double) will be displayed in a chart, with both the data and the chart saved in an Excel document.

To work with TMS FlexCel, you will need to install the library. To do this, follow the link https://www.tmssoftware.com/site/flexcel.asp. 

Next, select “FREE TRIAL” to download the free trial version of the library.

Now, you need to select the version of the development environment you are using (in our case, RAD Studio 12 Athens) and click “DOWNLOAD”. The process of downloading the installer for the Trial version of TMS FlexCel will begin. When the download is complete, launch the installer and simply follow the instructions provided by the installation wizard.

How to use TMS FlexCel features for Excel document generation via our Embarcadero Delphi FMX app

Let’s take a look at our Embarcadero Delphi FMX application. To display the graph of the sine function, we will use the TChart component from the TeeChart Lite component palette.

The main functionality for tabulating the function and generating the Excel report is implemented in the OnClick event handler of the button (class TButton).

We should declare a string variable FChartPicturePath to store the path to the image file containing our sine function graph. We will also declare a string variable FFlexCelPath, which will contain the path to the saved Excel document with the X and Y values of our sine function and the chart as an image.

In the main form’s method, we will perform the initial settings. For Chart1, we will hide the legend (Chart1.Legend.Visible := False), disable 3D display (Chart1.View3D := False), clear the Chart1 title (Chart1.Title.Text.Clear), and set the title “SIN”.

To do this, we will use the command Chart1.Title.Text.Add(‘SIN’). We should also set the paths for saving the image file of our sine function graph FChartPicturePath := TPath.Combine(TPath.GetDocumentsPath, ‘chart.bmp’) and the Excel report FFlexCelPath := TPath.Combine(TPath.GetDocumentsPath, ‘MyFlexCelSample.xls’). In our case, we will save both files in the “Documents” folder.

To use the features of the TMS FlexCel library, we will need to include the following units.

To use the mathematical sine function from the library, we will need to include the Math unit.

Let’s take a closer look at the OnClick event handler for the “OK” button, where the tabulation of the sine function is implemented and the Excel report is saved. We should declare the following local variables.

The Xls object (of type TXlsFile) allows us to save the results of the sine function tabulation and the chart image to an Excel file.

The ImProps object (of type IImageProperties) holds the information about the image settings for the chart of the sine function Y (of the Double type) in relation to argument X (of the Double type). This image is saved from Chart1 using the SaveToBitmapFile method. Subsequently, this image will be added to our Excel report.

We should also declare the variables XStart, XFinish, and Step. All of them are of the Double type. XStart contains the starting value of the argument X (in our case, 0). XFinish contains the ending value of the argument X (in our case, 2 * PI). Step contains the step size for tabulating the function (in our case, 0.01).

The integer variable I holds the row number for writing the values of the argument X and the sine function Y into the Excel report.

The Stream object (of the TFileStream class) holds the image of the sine function chart loaded from a file. The Img object (of the TUIImage class) contains the sine function chart image for further saving into the Excel report. The Sl object includes a series of type “line” for plotting the function Y(X)=sin(X).

Next, we will perform the initial setup for the Xls object. We will do this by calling the constructor TXlsFile.Create(1, TExcelFileFormat.v2019, True). The first parameter will include the number of sheets in the Excel report (in our case, 1). The second parameter will include the version of Excel (in our case, 2019). The third parameter True indicates that the existing Excel report can be overwritten if it already exists.

Next, we will configure the series and add it to the Chart1 field using the TLineSeries.Create(Chart1) method. We will also set the color of the series and line thickness for the sine function graph  (Sl.SeriesColor := TAlphaColors.Red, s: Sl.Pen.Width := 4). Let’s assign the value 1 to the variable I.

In a loop, we need to calculate the value of the function Y (Y := Sin(X)) based on X with a step size of Step. The calculations in the loop will continue until X is less than the final value XFinish. At each iteration, we will add X and Y to the series (Sl.AddXY(X, Y, ”, TAlphaColors.Blue)). The chart line color will be blue. We will also add the values of X and Y to the Xls object (Xls.SetCellValue(I, 1, X) and Xls.SetCellValue(I, 2, Y)). The SetCellValue method will take three parameters: the row number, the column number, and the value. In our case, it will add the values of the variables X and Y. Afterwards, we will increment the current value of X by adding the step size Step at each iteration of the while loop (X := X + Step), and also increment the value of I by one. Next, we will add the series to Chart1 to display the function graph to the user (Chart1.AddSeries(Sl)). We will save the chart as a Bitmap image file for further inclusion in the Excel document (Chart1.SaveToBitmapFile(FChartPicturePath)).

We have saved the tabulated values of the functions X and Y in the Excel report. Now, we also need to add and save the sine function chart image in the document. To do this, we should load the previously saved chart image into TFileStream.

Next, we should read our image from TFileStream and save it into the Img object using the FromStream method.

We will configure ImProps. With the help of the TClientAnchor.Create constructor, we will anchor the left edge of our image to row 4 and column 4 (D) of the Excel document.

We will also provide the height and width of our chart image with Trunc(Img.Height) Trunc(Img.Width) and assign them to the  Xls object.

The ShapeName field will contain the name of our image in the Excel document. TFlxAnchorType.MoveAndDontResize specifies that the image should not be resized; its dimensions remain fixed.

We will add the image to the Xls object using Xls.AddImage(Stream, ImProps). Then, we should save the tabulated function values and the chart image to the Excel file with Xls.Save(FFlexCelPath). After that, we will free up resources and delete the no longer needed bitmap image file of the function chart using DeleteFile(FChartPicturePath).

The full source code of our app is provided below.

Demonstration of our Embarcadero Delphi FMX app work

Let’s demonstrate how our Embarcadero Delphi FMX application works. Upon launch, the application features the following interface.

After we press the “OK” button, the application will look as follows.

We can also see the generated Excel file in the “Documents” folder.

Let’s open our file and have a look at the result.