Sometimes you want to know the exact FlexCel version you are using from inside your app, and you might also want to make sure you aren''t using a trial. You can use the following code to find out:

TMS FlexCel v6.17 introduced full support for reading and saving strict xlsx files. Strict xlsx files are a different file format from normal xlsx files, and you can select them when doing a "File Save As..." in Excel:

Now the question is: Should I use normal or strict xlsx files? And the short answer is: Use normal xlsx files unless you have a non negotiable requirement from your customers to use strict xlsx.

The long answer can be found here.

One common thing you might want to do when entering formulas in FlexCel is to change the column or rows where they appear. So for example, let’s imagine you have this formula in A1:

=A2 * 2

And you want to expand the formula to Columns B to Z.
In B1, you will want the formula =B2 * 2, in C1 you will want =C2 * 2 and so on.

There are multiple ways to achieve this:

1)You can enter the formula in A1:
xls.SetCellValue(1, 1, TFormula.Create(''=A2 * 2''));

And then copy the cell to the range B:X:
xls.InsertAndCopyRange(TXlsCellRange.Create(1, 1, 1, 1), 1, 2, 25, TFlxInsertMode.NoneRight, TRangeCopyMode.All);

This will work the same as in Excel, and the formulas will be adapted when copied. Same as in Excel absolute formulas (like $B$1) won’t be changed, but relative formulas will change when copied.

2)You can manually create the formulas by using TCellAddress
TCellAddress is the record you use in FlexCel to convert cell references from/to numbers to letters. Here is a little example on how you can get the Row and column from the string "B5" and also how to get the string "B5" from the row and column:

So, for our original example, we could use some code like this:


3)Using R1C1 notation
R1C1 is an alternative notation to the classical “A1” notation, to describe formulas based in their rows and columns, instead of in a letter and a number. R1C1 is completely equivalent to A1, but has the advantages of always using row numbers, and that the cells are relative to their position, which is what you normally want. You can find a lot of information in R1C1 cell references internet just by a web search, so we will focus in how to use it from FlexCel.

There are a couple of properties that govern R1C1 in FlexCel.:
A)xls.FormulaReferenceStyle := TReferenceStyle.R1C1 :
https://download.tmssoftware.com/flexcel/hlp/vcl/index.htm?FlexCel.Core.TExcelFile.FormulaReferenceStyle.htm

This will affect how you can insert the formulas (so the formula you quoted would be valid), but Excel will still show the formulas in the file as R1C1.

B) xls.OptionsR1C1 := true
https://download.tmssoftware.com/flexcel/hlp/vcl/index.htm?FlexCel.Core.TExcelFile.OptionsR1C1.htm

OptionsR1C1 only affects how Excel shows the formulas, but not how FlexCel expects them. So TFormula.Create(''=SUM(R[-2]C:R[-1]C)'') would still fail: You need to use FormulaReferenceStyle for FlexCel as shown in A)

So for our original example, here is the code to do it with R1C1 notation. Note that due to the fact that R1C1 is relative, the formula is always exactly the same. There is no need to calculate a formula for each cell as we did in Solution 2):


Also note that while we used R1C1 internally to enter the formulas, in Excel they will show in A1 exactly the same as they do with the other 2 solutions. FlexCel comes with full R1C1 support built in.

Bonus track
R1C1 formulas are not only nice to enter formulas, but also to check for consistency in existing files. Imagine you have a file with formulas like in our example above, and you want to check that they are all as they are supposed to be. So for example in Column J, you have =J2 * 2 and not =A2 * 2. Checking this in A1 notation can be very complex, specially if the formulas are not simple. But retrieve the formulas in R1C1 instead, and all you need to do to check for consistence is that all formulas in A1:Z1 are the same!. That is, retrieve the formula in A1 (in this case "=R[1]C * 2") and then check that all other formulas in the range are the same as the text in A1. If a formula is different, then it is not consistent.

Excel uses many different units for measuring different things, and FlexCel uses the same.

For most rendering stuff, including the PDF API, FlexCel uses points, and those is simple: A point is 1/72 of an inch. As you can see in the google calculator here.

A point is also normally used to specify font sizes: For example a font with 10 points has a bounding box of 10/72 inches.

Now, the Excel units are a little more complex. The simplest ones are the y coordinates (rows): A row is measured in 1/20 of an inch. From: https://download.tmssoftware.com/flexcel/hlp/vcl/index.htm?FlexCel.Core.TExcelFile.GetRowHeight(Int32).htm Returns the current Row height, in Excel internal units. (1/20th of a point)

But the x coordinates (columns) are way more complex. They measure how many “0” you can put in a cell in the font used in the “Normal” style, plus some margins. So, for example, the normal style for an Excel 2007 or newer file is “Calibri 11”, so if you can fit 5 “0”s in a column with that font, (“00000”) then the column width is 5. But not exactly, there are some margins too, which aren’t correctly documented in Excel docs, and which actually change depending in the screen resolution. So the width is not going to be 5, but 5.something. As explained in the help on GetColWidth ( https://download.tmssoftware.com/flexcel/hlp/vcl/index.htm?FlexCel.Core.TExcelFile.GetColWidth(Int32).htm ) you can convert between columns and rows and pixels using ExcelMetrics.ColMult and ExcelMetrics.RowMult. I wish I could tell you this is an exact science, but sadly Excel isn’t very exact about this. You can see it simply by pressing a screen preview and measuring the real width of a cell, and compare it with what you see on the screen, you’ll see it is not exactly the same.

The actual box width for a cell can also change if you use a different screen scaling: They will look different if you use say 100% or 175% screen scaling. FlexCel can offset this with the property TExcelFile.ScreenScaling ( https://download.tmssoftware.com/flexcel/hlp/vcl/index.htm?FlexCel.Core.TFlxConsts.RowMult.htm ). While by default this is 100, if you are working always in a different setting, changing this property will make FlexCel work as if it was Excel showing the file at that particular screen scaling. Changes are not much, but they exist so in general it is a bad idea to try to do “pixel perfect” designs in Excel. You always need ot leave some room because in different resolutions the cells might be slightly smaller or bigger.

For more info about how Excel units can change with the resolution, you can also read page 14 of https://download.tmssoftware.com/flexcel/docs/vcl/UsingFlexCelAPI.pdf (Autofitting Rows and Columns)

Excel files can be very complex, and guessing how to for example add a gradient to a cell, or protect a sheet, or add an autofilter can get difficult.

To make it easier, FlexCel provides a very useful tool named APIMate that can "convert" an Excel file into FlexCel code, for either Delphi or C++ Builder.

It works like this: You create a file in Excel, and open it in APIMate (without closing Excel). It will tell you the Delphi or C++ code needed to create the file. Then you can make changes in Excel, save the file, and press “Refresh” in APIMate (all without closing Excel). It will tell you the code you need to modify the file from the old state to the new one.

We are now in position to answer the question at the top of this section: How to create a file with a gradient in a cell, a protected sheet and an autofilter? I created a simple file with those things in Excel, and APIMate gives back this code:


APIMate is not the panacea for all the questions, but it helps a lot answering many of those "how do I?" doubts. Remember to use it.

It can be confusing that the code to change the background of a cell is something like:


Why are we changing the FgColor instead of the BgColor in order to change the background color? And why does changing BgColor has no effect?

It can be a little confusing because of the names (which are the same the Excel documentation uses), but you need to understand that both FgColor and BgColor refer to the background color of a cell, and that’s why both are properties of the FillPattern. The foreground color is changed by changing the font color.

The thing is, in Excel, cells don’t need to have a solid fill, they can have a pattern fill. For example, here you have a “grid” pattern, where the foreground of the pattern is red, and the background of the pattern is yellow:

So, in FlexCel “FgColor” and “BgColor” both refer to the fill of the cell (never the foreground which as said is Font.Color). For the special case of Pattern = Solid (what we use in the 99.999% of the cases), then you set the FillPattern.Pattern to Solid, and BgColor doesn’t really matters. You can think of “Solid” as a pattern where everything is covered by the foreground color. The BgColor is still there, but not visible because FgColor covers all. If you set any other pattern than solid, you’ll see it more clearly.

This message happens when you have any formula in the Excel file, and open it with different Excel versions.

The thing is: If you create a file with formulas in say Excel 2007 and then open the file in Excel 2010, Excel will recalculate all cells and show that message. Sometimes (not always) it also happens in the reverse: If you save with Excel 2010 it might ask you to save when you open in Excel 2007.

This isn’t a FlexCel issue: But in Excel you will normally not see this because in your test machine you will use the same Excel version to save and open the file. But as soon as you start delivering the file to customers, they will start seeing the message.

Now, FlexCel is only one “version": It is not Excel 2003, or 2007, or 2010. So when it creates a file, it needs to say “this file was created with Excel version …” in the file, and this is the value Excel will use to decide if it shows the message or not. If you know that say all your users are in Excel 2013, then you can tell FlexCel to say “this file was saved with Excel 2013” and Excel 2013 users won’t see the dialog. But users of a different version of Excel might.

If you are opening the file in Excel 2013 for example, you can get rid of the dialog by doing:
xls.RecalcVersion = TRecalcVersion.Excel2013;
or just do
xls.RecalcVersion = TRecalcVersion.SameAsInputFile;
to make FlexCel save the “version” used in recalculation as the same version the input file had.

Due to its complexity FlexCel takes a while to compile. We are speaking about 1700 units with 700,000 lines of code, and a “mini-excel” which even includes a full PDF engine.

1) A full compilation for all platforms including debug and release configurations can take about 2 or 3 minutes. Most of the time is spent in mobile compilers like iOS and Android which are much slower than the Win32 compiler. If a full compilation takes up to 8 hours, then there is probably something wrong in the setup. (Antivirus, some unexisting library paths pointing to network locations could slow down compiling)

2) You should be compiling FlexCel just once: When you install it. Setup is similar to the VCL itself, it puts all dcus in the library path, and not the .pas files. And the result is similar, once you install it, it runs fast (recompiling the VCL would take many minutes too, again due to the complexity of it)

One way to speed it up, if you aren’t using the FireMonkey part, is to deselect it on setup:

FlexCel 5 replaced the non visual components by just classes, so they won't install in the toolbox.
For more information, please read:

https://www.tmssoftware.com/site/blog.asp?post=228

If you get an error similar to this while running the setup: Then you need to update Delphi XE to SP1. Check in the Delphi XE about box that it is: Embarcadero® Delphi® XE Version 15.0.3953.35171 If you have any lower number, you will need to update it. Note that "check for updates" might report that no delphi updates are available even when there are. So you need to check the version number in the about box, and if not the one above and "check for updates" doesn't update, you will need ot do a manual update.

You can call FlexCelImport.InsertEmptySheets to add new sheets.

To insert a new sheet as a last sheet, just call:

The Microsoft XLS file format has a limitation of 65535 rows. This is a limitation inherent to the .XLS file format. TMS Flexcel cannot workaround a limitation that is in a file format specification and can as such not export more than 65535 rows to an .XLS file. If you need more rows, you will have to save as xlsx.