FLEXCEL STUDIO FOR VCL AND FIREMONKEY
TMS Software
FlexCel API
Developers Guide
Volume
1
Table of Contents
TABLE OF CONTENTS ............................................................................................................................................. I
INTRODUCTION .................................................................................................................................................... 1
BASIC CONCEPTS ................................................................................................................................................. 1
ARRAYS AND CELLS ...................................................................................................................................................... 1
CELL FORMATS ............................................................................................................................................................. 2
CELL AND STYLE FORMATS ............................................................................................................................................. 3
FONT INDEXES ............................................................................................................................................................... 3
COLORS ....................................................................................................................................................................... 4
DATE CELLS .................................................................................................................................................................. 5
COPYING AND PASTING NATIVE EXCEL DATA .................................................................................................................. 6
READING AND WRITING FILES ............................................................................................................................. 7
STARTING TO USE FLEXCEL ............................................................................................................................................. 7
OPENING AND SAVING FILES .......................................................................................................................................... 8
MODIFYING FILES .......................................................................................................................................................... 8
USING THE APIMATE TOOL ............................................................................................................................................. 9
INSERTING, COPYING AND MOVING CELLS / ROWS / COLUMNS AND SHEETS .................................................................. 9
CONSIDERATIONS ABOUT EXCEL 2007 SUPPORT ............................................................................................. 11
AUTOFITTING ROWS AND COLUMNS ................................................................................................................ 13
AUTOFITTING MERGED CELLS ....................................................................................................................................... 15
PREPARING FOR PRINTING ................................................................................................................................ 16
MAKING THE SHEET FIT IN ONE PAGE OF WIDTH .............................................................................................................. 16
REPEATING ROWS AND COLUMNS AT THE TOP .............................................................................................................. 16
USING PAGE HEADERS/FOOTERS ................................................................................................................................. 17
INTELLIGENT PAGE BREAKS ........................................................................................................................................... 17
The Widow / Orphan problem ................................................................................................................... 18
The different printers problem .................................................................................................................... 19
Using different levels of “Keep together” ................................................................................................. 20
Inserting and copying Rows with a “keep together” level .................................................................... 20
USING EXCEL'S USER DEFINED FUNCTIONS (UDF) ............................................................................................ 21
INTRODUCTION ........................................................................................................................................................... 21
Step 1: Defining the Function in Delphi ..................................................................................................... 22
Step 2: Registering the UDF in FlexCel ....................................................................................................... 23
MISCELLANEA ..................................................................................................................................................... 25
USING FLEXCEL INSIDE A DLL ........................................................................................................................................ 25
FINDING OUT WHAT FORMAT STRING TO USE IN TFLXFORMAT.FORMAT ............................................................................. 25
RECALCULATING LINKED FILES ...................................................................................................................................... 27
CLOSING WORDS ............................................................................................................................................... 31
F L E X C E L A P I D E V E L O P E R S G U I D E
1
Introduction
The FlexCel API (Application Programmer Interface) is what you use to read or write Excel
files on a low level way. By “low level” we mean that this API is designed to work really
“close to the metal” and there aren’t many layers between you and the xls/xlsx file being
created. For example, FlexCel API doesn’t know about datasets, because datasets are a
higher level concept. If you want to dump a dataset into an xls file using the API, you need
to loop in all records and enter the contents into the cells.
In addition to the FlexCel API we provide a higher level abstraction, FlexCelReport, that
does know about datasets and in general works at a more functional level; a declarative
approach instead of an imperative approach. What is best for you depends on your
needs.
Basic Concepts
Before starting writing code, there are some basic concepts you should be familiar with.
Mastering them will make things much easier in the future.
Arrays and Cells
To maintain our syntax compatible with Excel OLE automation, most FlexCel indexes/arrays
are 1-based.
That is, cell A1 is (1,1) and not (0,0). To set the first sheet as ActiveSheet, you would write
ActiveSheet := 1 and not ActiveSheet := 0.
So, in C++ loops should read: “for (int i=1; i<=Count; i++)”, and in Delphi they
should be like “for i:=1 to Count”
The two exceptions to this rule are XF and Font indexes, which are 0 based because they
are so on Excel.
2
Cell Formats
All formats (colors, fonts, borders, etc) on an Excel workbook are stored into a list, and
referred by number. This number is known as the XF (eXtended Format) index. A simple
example follows:
Here Cell B3 has XF=0 and the XF definition for the background color is green. Row 6 has
XF=2, so all the empty cells on row 6 are yellow. Column C has XF=1, so all the empty cells
on column C that do not have a Row format are Blue.
Most formatting methods at FlexCel return an XF index, and then you have to look at the XF
list (using the GetFormat method) to get a class encapsulating the real format. There are
two helper methods, GetCellFormatDef and GetCellVisibleFormatDef that obtain the XF
index and return the format class in one step.
To create new formats, you have to use the AddFormat method. Once you get the Id of
the new XF, you can use it as you wish.
Also, you don't have to worry also on inserting a format 2 times, if it already exists,
AddFormat will return the existing id and not add a new XF entry.
3
Cell and Style Formats
XF formats can be of two types, “Cell” or “Style”. Cell formats are applied directly to a cell
and can have a “ParentStyle” which must be a style format. Style formats cannot have
ParentStyle and cannot be applied to cells, but they can be used as base for defining
different Cell formats. You can know if a TFlxFormat contains a “Style” or “Cell” format by
looking at its “IsStyle” property. Also, a cell style can link only parts of its format to its parent
style, for example have the font linked so when you change the font in the style it changes
the font in the cell, but not the cell borders. In order to set which properties are linked to
the main style, you need to change the “LinkedStyle” property in TFlxFormat.
You can create new Style formats with ExcelFile.SetStyle, and new Cell formats with
ExcelFile.AddFormat. Once you create a new style and give it a name, you can get its
definition as a TFlxFormat, change its “IsStyle” property to true, define which propertyies
you want to link with the “LinkedStyle” property, and add that format using AddFormat to
get the cell format. Once you have the cell style, you can just apply it to a cell.
The LinkedStyle class has a member, “AutomaticChoose”, which if left
to true (the default) will compare your new format with the style and
only link those properties that are the same.
For example, let’s imagine you create a style “MyHyperlink”, with font
blue and underlined. Then you create a Cell format that uses
“MyHyperLink” as parent style, but also has red background, and apply
it to cell A1. If you leave AutomaticChoose true, FlexCel will detect that the cell format is
equal in everything to its parent style except in the background, so it will not link the
background of the cell to the style. If you later change the background of MyHyperlink,
cell A1 will continue to be red.
This allows for having “partial styles”, as in the example above, a “hyperlink” style defines
that text should be blue and underlined, but it doesn’t care about the cell background. If
you want to manually set which properties you want to have linked instead of FlexCel
calculating that for you, you need to set AutomaticChoose to false and set the “Linked….”
Properties in LinkedStyle to the values you want.
Font Indexes
The same way we have an XF list were we store the formats for global use, there is a Font
list were fonts are stored to be used by XFs. You normally don't need to worry about the
FONT list because inserting on this list is automatically handled for you when you define an
XF format. But, if you want to, you can for example change Font number 7 to be 15 points,
and all XFs referencing Font 7 will automatically change to 15 points.
