Formulas: Some corrections
← Older revision
Revision as of 20:14, 27 June 2015
(6 intermediate revisions by one user not shown)
Line 8:
Line 8:
== Documentation ==
== Documentation ==
−
This wiki
pages
covers the latest development/trunk version of FPSpreadsheet available via subversion. Please see section [[#Stable_releases|Stable releases]] for documentation on the stable
release
that you can download.
+
This wiki
page
covers the latest development/trunk version of FPSpreadsheet available via subversion. Please see section [[#Stable_releases|Stable releases]] for documentation on the stable
version
that you can download.
== API Documentation ==
== API Documentation ==
Line 14:
Line 14:
=== API Reference ===
=== API Reference ===
−
A help file in CHM format can be found in the
fpspreadsheet
installation
root
folder. If you did not yet install the package follow
+
A help file in CHM format can be found in the
FPSpreadsheet
installation folder
''docs''
. If you did not yet install the package follow
−
[http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/fpspreadsheet.chm http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/fpspreadsheet.chm] to
"
fpspreadsheet.chm
"
.
+
[http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet
/docs
/fpspreadsheet
-api
.chm http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet
/docs
/fpspreadsheet
-api
.chm] to
''
fpspreadsheet
-api
.chm
''.
+
+
The second CHM file available, ''fpspreadsheet-wiki.chm'' in the folder ''docs'' is a snapshot of the FPSpreadsheet-related wiki pages bundled into a single help file
.
=== Basics ===
=== Basics ===
−
The smallest entities in a spreadsheet are the '''cells''' which contain the data. Cells can hold various data types, like strings, numbers, dates, times, boolean values, or formulas.
+
The smallest entities in a spreadsheet are the '''cells''' which contain the data. Cells can hold various data types, like strings, numbers, dates, times, boolean values, or formulas
. In addition, cells can contain information on formatting, i.e. font style, background color, text alignment etc
.
The cells are arranged in a grid-like structure, called '''worksheet''', or '''spreadsheet''', consisting of '''rows''' and '''columns'''. Each cell has a unique address given by the row and column index.
The cells are arranged in a grid-like structure, called '''worksheet''', or '''spreadsheet''', consisting of '''rows''' and '''columns'''. Each cell has a unique address given by the row and column index.
−
Worksheets are bound together to form a '''workbook''' which represents the document of the spreadsheet application.
+
Worksheets are bound together to form a '''workbook''' which represents the document of the spreadsheet application
. The workbook also stores information that is needed from all worksheets, i.e. font list, cell and number format lists, etc
.
−
FPSpreadsheet follows this same structure - there is a TCell, a TsWorksheet, and a TsWorkbook.
+
FPSpreadsheet follows this same structure - there is a
<tt>
TCell
</tt>
, a
<tt>
TsWorksheet
</tt>
, and a
<tt>
TsWorkbook
</tt>
.
==== Workbook ====
==== Workbook ====
−
The class <tt>TsWorkbook</tt> is the main class visible to the user. It provides methods for reading data from and writing
them
to
file
. The versatile structure of the library provides access to various popular file formats, like Excel .xls or .xlsx, or OpenOffice .ods.
+
The class <tt>TsWorkbook</tt> is the main class visible to the user. It provides methods for reading data from and writing to
files
. The versatile structure of the library provides access to various popular file formats, like Excel
''
.xls
''
or
''
.xlsx
''
, or OpenOffice
/LibreOffice ''
.ods
''
.
The file format is specified by the type <tt>TsSpreadsheetFormat</tt>
The file format is specified by the type <tt>TsSpreadsheetFormat</tt>
Line 67:
Line 69:
After calling these methods it is advantageous to look at the workbook's property <tt>ErrorMsg</tt> in which messages due to errors or warnings are collected that might have occured during reading/writing. This property returns a multi-lined string which is displayed best in a memo component; if everything was fine it is an empty string.
After calling these methods it is advantageous to look at the workbook's property <tt>ErrorMsg</tt> in which messages due to errors or warnings are collected that might have occured during reading/writing. This property returns a multi-lined string which is displayed best in a memo component; if everything was fine it is an empty string.
−
<b>Note:</b> FPSpreadsheets provides specialized
classes
for reading from and writing to each file format. These units are not available automatically, you have to add them to the uses clause explicitly. FPSpreadsheet will complain about "unsupported file format" if the requested reader/writer is not found. Here is a list of the unit names:
+
<b>Note:</b> FPSpreadsheets provides specialized
unitss
for reading from and writing to each file format. These units are not available automatically, you have to add them to the uses clause explicitly. FPSpreadsheet will complain about "unsupported file format" if the requested reader/writer is not found. Here is a list of the unit names:
* ''xlsbiff2'', ''xlsbiff5'' and ''xlsbiff8'' for the binary xls file formats <tt>sfExcel2</tt>, <tt>sfExcel5</tt> and <tt>sfExcel8</tt>, respectively,
* ''xlsbiff2'', ''xlsbiff5'' and ''xlsbiff8'' for the binary xls file formats <tt>sfExcel2</tt>, <tt>sfExcel5</tt> and <tt>sfExcel8</tt>, respectively,
* ''xlsOOXML'' for the xlsx file format <tt>sfOOXML</tt> of Excel 2007 and later,
* ''xlsOOXML'' for the xlsx file format <tt>sfOOXML</tt> of Excel 2007 and later,
Line 184:
Line 186:
* <tt>TextRotation</tt>: specifies whether the cell text is written horizontally or vertically
* <tt>TextRotation</tt>: specifies whether the cell text is written horizontally or vertically
* <tt>HorAlignment</tt>: left-aligned, horizontally centered, or right-aligned text
* <tt>HorAlignment</tt>: left-aligned, horizontally centered, or right-aligned text
−
* <tt>VertAlignment</tt>: top,
buttom
or vertically centered text
+
* <tt>VertAlignment</tt>: top,
bottom
or vertically centered text
* <tt>Border</tt>: a set of flags indicating that - if set - a border line is drawn at the left, top, right, or bottom cell edge. The lines are drawn according to the <tt>BorderStyles</tt> which define the linestyle and color of the border.
* <tt>Border</tt>: a set of flags indicating that - if set - a border line is drawn at the left, top, right, or bottom cell edge. The lines are drawn according to the <tt>BorderStyles</tt> which define the linestyle and color of the border.
* <tt>Background</tt>: a record defining the background fill of a cell (pattern style, pattern color, and background color - see chapter on [[#Cell_background|cell background]] below).
* <tt>Background</tt>: a record defining the background fill of a cell (pattern style, pattern color, and background color - see chapter on [[#Cell_background|cell background]] below).
Line 197:
Line 199:
Two kinds of formulas are supported by FPSpreadsheet:
Two kinds of formulas are supported by FPSpreadsheet:
−
* '''String formulas''': These are written in strings just like in the office applications, for example <tt>"=ROUND(A1+B1,0)"</tt>. They used internally in the files of Open/LibreOffice and Excel xlsx.
+
* '''String formulas''': These are written in strings just like in the office applications, for example <tt>"=ROUND(A1+B1,0)"</tt>. They
are
used internally in the files of Open/LibreOffice and Excel
''.
xlsx
''
.
−
* '''RPN formulas''' are used internally
in
the binary xls Excel files. They are written in Reverse Polish Notation (RPN), for example: <tt>A1, B1, Add, 0, ROUND</tt>.
+
* '''RPN formulas''' are used internally
by
the binary
''.
xls
''
Excel files. They are written in Reverse Polish Notation (RPN), for example: <tt>A1, B1, Add, 0, ROUND</tt>.
The current trunk version of FPSpreadsheet can convert between string and rpn formulas. Formulas in both types can be calculated. The cell record stores the string formula in the field <tt>FormulaValue</tt>. If a spreadsheet containing formulas is to be saved in a binary Excel format, the RPN formulas required are generated automatically.
The current trunk version of FPSpreadsheet can convert between string and rpn formulas. Formulas in both types can be calculated. The cell record stores the string formula in the field <tt>FormulaValue</tt>. If a spreadsheet containing formulas is to be saved in a binary Excel format, the RPN formulas required are generated automatically.
−
FPSpreadsheet supports the majority of the formulas provided by the common spreadsheet applications. However, when reading a file created by these applications, there is always a chance that an unsupported formula is contained. To avoid crashing of fpspreadsheet, reading of formulas is disabled by default; the cell displays only the result of the formula. To activate reading of formulas add the element <tt>boReadformulas</tt> to the workbook's <tt>Options</tt> before
reading a
file.
+
FPSpreadsheet supports the majority of the formulas provided by the common spreadsheet applications. However, when reading a file created by these applications, there is always a chance that an unsupported formula is contained. To avoid crashing of fpspreadsheet, reading of formulas is disabled by default; the cell displays only the result of the formula
written by the Office application
. To activate reading of formulas add the element <tt>boReadformulas</tt> to the workbook's <tt>Options</tt> before
opening the
file.
−
Calculation of formulas is normally not needed when a file is written by FPSpreadsheet for opening
it
in an Office application because that automatically calculates the formula results. If the same file, however, is openend by an application based on FPSpreadsheet the calculated cells would be empty because the formulas are not automatically calculated by default. To activate calculation of formulas before writing a spreadsheet to file you have to add the option <tt>boCalcBeforeSaving</tt> to the workbook's <tt>Options</tt>.
+
Calculation of formulas is normally not needed when a file is written by FPSpreadsheet for opening in an Office application because that automatically calculates the formula results. If the same file, however, is openend by an application based on FPSpreadsheet the calculated cells would be empty because the formulas are not automatically calculated by default. To activate calculation of formulas before writing a spreadsheet to file you have to add the option <tt>boCalcBeforeSaving</tt> to the workbook's <tt>Options</tt>.
If FPSpreadshet is used in an interactive application (such as the ''spready'' demo found in the ''examples'' folder of the FPSpreadsheet installation) it is desirable to calculate formulas automatically whenever formula strings or cell values are changed by the user. This can be achieved by the option <tt>boAutoCalc</tt> in the workbook's <tt>Options</tt>.
If FPSpreadshet is used in an interactive application (such as the ''spready'' demo found in the ''examples'' folder of the FPSpreadsheet installation) it is desirable to calculate formulas automatically whenever formula strings or cell values are changed by the user. This can be achieved by the option <tt>boAutoCalc</tt> in the workbook's <tt>Options</tt>.
Line 231:
Line 233:
* Strings must be enclosed in double quotes.
* Strings must be enclosed in double quotes.
* Normally, floating point numbers must be entered with a dot as decimal separator, and a comma must be used to separate function arguments.
* Normally, floating point numbers must be entered with a dot as decimal separator, and a comma must be used to separate function arguments.
−
* Setting the optional parameter <tt>ALocalized</tt> of the worksheet methods <tt>WriteFormula</tt> to <tt>TRUE</tt>, however, allows to use localized decimal and list separators taken from the workbook's <tt>FormatSettings</tt> - see
<tt>
spready
</tt>
demo.
+
* Setting the optional parameter <tt>ALocalized</tt> of the worksheet methods <tt>WriteFormula</tt> to <tt>TRUE</tt>, however, allows to use localized decimal and list separators taken from the workbook's <tt>FormatSettings</tt> - see
''
spready
''
demo.
<syntaxhighlight>
<syntaxhighlight>
Line 250:
Line 252:
==== List of built-in formulas ====
==== List of built-in formulas ====
−
FPSpreadsheet supports more than 80 built-in formulas. In order not to blow up this wiki page too much documentation of these formulas has been moved to
a
[[FPSpreadsheet:_List_of_formulas|separate document]].
+
FPSpreadsheet supports more than 80 built-in formulas. In order not to blow up this wiki page too much documentation of these formulas has been moved to
the
[[FPSpreadsheet:_List_of_formulas|separate document
"List of formulas"
]].
To learn more about the functions available, look at file ''testcases_calcrpnformula.inc'' in the ''tests'' folder of the FPSpreadsheet installation where every function is included with at least one sample.
To learn more about the functions available, look at file ''testcases_calcrpnformula.inc'' in the ''tests'' folder of the FPSpreadsheet installation where every function is included with at least one sample.
==== Extending FPSpreadsheet by user-defined formulas ====
==== Extending FPSpreadsheet by user-defined formulas ====
−
Although the built-in formulas cover most of the applications there may be a need to access a formula which is available in the Office application, but not in FPSpreadsheet. For this reason, the library supports a registration mechanism which allows to add user-defined functions to the spreadsheets. This can be done by calling the procedure <tt>RegisterFunction</tt> from the unit
<tt>
fpsExprParser
</tt>
:
+
Although the built-in formulas cover most of the applications there may be a need to access a formula which is available in the Office application, but not in FPSpreadsheet. For this reason, the library supports a registration mechanism which allows to add user-defined functions to the spreadsheets. This can be done by calling the procedure <tt>RegisterFunction</tt> from the unit
''
fpsExprParser
''
:
<syntaxhighlight>
<syntaxhighlight>