FLEXCEL STUDIO FOR VCL
TMS Software
FlexCel API
Developers Guide
Volume
1
Table of Contents
Table of Contents ...........................................................................i
Introduction................................................................................... 1
Basic Concepts ............................................................................ 1
Arrays ......................................................................................................... 1
Cell Formats: ............................................................................................. 2
Font Indexes: ............................................................................................ 2
Palette colors: ........................................................................................... 3
Automatic Colors: ..................................................................................... 3
Date Cells: ................................................................................................. 3
Copying and pasting in BIFF8 ................................................................ 4
Reading And Writing Files ........................................................... 4
Opening and saving files:........................................................................ 5
Modifying files: .......................................................................................... 6
Autofitting Rows and Columns .................................................... 7
Preparing for Printing ................................................................... 8
Making the sheet fit in one page of width. ............................................ 8
Repeating Rows and Columns at the top ............................................. 9
Using Page Headers/Footers ................................................................. 9
Miscellanea ................................................................................ 10
Finding out what format string to use in TFlxFormat.Format .......... 10
Closing Words ............................................................................ 13
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. To create Excel file with a template, use
FlexCelReport.
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
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, that are 0 based because they
are so on Excel.
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
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 methods at FlexCel import return a 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.
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.
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
3
Palette colors:
Colors in Excel are referred by a palette index. For example, you can have color 3 =
rgb(123,134,188)
To get the real RGB color from a palette index, you have to use ColorPalette property.
You can also modify the palette to fit your needs, by assigning this property.
There is a handy function for converting an RGB value to the nearest palette index,
this is MatchNearestColorIndex.
Automatic Colors:
Besides the normal colors on the palette, Excel lets you set colors to Automatic. This
will be returned as a <=0 index, or bigger than the max color palette entry, depending
on Excel. You can use GetColorPalette(colorIndex, automaticColor) to get the real rgb
color. Automatic colors depend on the case, but are normally black for the
foregrounds and white for the backgrounds.
Date Cells:
As you might already know, there is no DATE datatype in Excel.
Dates are saved as a double floating number where the integer part is the number of
days that have passed from 1/1/1900, and the fractional part is corresponding fraction
of the day. For example, the number 2.75 stands for "02/01/1900 06:00:00 p.m." You
can see this easily at Excel by entering a number on a cell and then changing the cell
format to a date, or changing the cell format of a date back to a number.
The good news is that you can normally convert directly from/to Delphi/Excel
automatically since TDateTime is actually a double. That is, if you enter
XlsFile.CellValue[1,1] := now, and the cell (1,1) has date format, you will write the
actual value of "now" to the sheet. Note that FlexCel will automatically take in account
if you are using 1904 date mode (see below) and enter the correct date into the cell.
The bad news is that you have no way to know if a cell has a number or a date just by
looking at its value. If you enter a date value into a cell and then read it back, you will
get a double. So you have to look at the format of the cell. There is a helper function,
XlsFormatValue1904 that can help you on this issue.
There is also a “1904” date mode, where dates begin at 1904 and
not 1900. This is used on Mac Excel, but you can change this
option in Excel for Windows too. FlexCel completely supports
1900 and 1904 dates, but you need to be careful when converting
dates to numbers and back.
When FlexCelImport.Option1904 is true, you can’t cast a TDateTime to a
double as they don’t have the same values. You need to use the helper functions
“ToOADate” and “FromOADate” in FlexCelImport to do the conversion.
- 1
- 2
- 3
- 4
前往页