Sign in Join
Wiki copytemporary

Revision 1 of 3, by WikiArchive on 11-4-2026. The page as it is now.

FPSpreadsheet/es

El contenido que sigue a continuación es una traducción de la versión en inglés como fuente primaria, por lo que ante la duda aconsejo visitar esa página. Procuraré manter el contenido actualizado/sincronizado. Ultima fecha edición 21/septiembre/2016.

La librería fpSpreadsheet ofrece un modo práctico de generar y leer documentos spreadsheet (hosas de cálculo) en varios formatos. Está escrita en una manera muy flexible, capaz de ser extendida para soportar cualquier número de formatos facilmente.

Aquí se muestra un pantallazo del programa de demo "spready" que viene con fpspreadsheet, que muestra un fichero XLS.

fpsgrid.png

Contents

Documentación

Esta página wiki cubre las últimas versión de desarrollo/trunk de FPSpreadsheet disponibles vía subversin. Por favor mira la sección Stable releases para la documentación de la versión estable que puedes descargar.

Documentación API

Referencia de la API

Hay disponible un fichero de ayuda en formato CHM dentro de la carpeta docs al descomprimir el paquete descargado (e.g. \fpspreadsheet-1.6.2\docs). Si todavía no has instalado el paquete sigue: http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/docs/fpspreadsheet-api.chm para fpspreadsheet-api.chm.

Hay también disponible un segundo fichero CHM de ayuda en la misma carpeta docs , fpspreadsheet-wiki.chm , que es una instantanea de las páginas wiki relativas a FPSpreadsheet.

Conceptos básicos

Las entidades más pequeñas en un spreadsheet son las cells (celdas), las cuales contienen datos. Las celdas (Cells) pueden contener varios tipos de datos, tales como cadenas, números, fechas, horas, valores booleanos o fórmulas. Adicionalmente, las celdas pueden contener información de formato, i.e. estilo de fuente, color de fondo, alineación de texto, etc.

Las celdas están dispuestas en una estructura a modo de cuadrícula o rejilla llamada worksheet , or spreadsheet consistente en rows (filas) y columns (columnas). Cada celda tiene una dirección única dada por el índice de fila y columna.

Las Worksheets (hojas del libro de trabajo) se ponen juntas para formar un workbook (libro de trabajo), el cual representa el documento de la aplicación spreadsheet (hoja de cálculo). El workbook almacena además la información de todos los worksheets, i.e. listado de fuentes, listados de formato de número, etc.

FPSpreadsheet sigue la misma estructura - hay un TCell, un TsWorksheet, y un TsWorkbook.

Workbook

La clase TsWorkbook es la clase principal visible para el usuario. Provee métodos de lectura y escritura a ficheros. La estructura versátil de la librería provee acceso a una variedad de formatos de fichero populares tales como Excel .xls o .xlsx , así como de OpenOffice/LibreOffice .ods.

El formato de fichero se especifica mediante el tipo TsSpreadsheetFormat definido en la unit fpstypes

pascal
type
      TsSpreadsheetFormat = (sfExcel2, sfExcel5, sfExcel8, sfExcelXML, sfOOXML, 
        sfOpenDocument, sfCSV, sfHTML, sfWikiTable_Pipes, sfWikiTable_WikiMedia, sfUser);

Donde:

Cuando se aplica un fpspreadsheet la primera tarea es crear una instancia de workbook :

pascal
var
      MyWorkbook: TsWorkbook;
    begin
      MyWorkbook := TsWorkbook.Create;
      ...

La lectura de ficheros spreadsheets se realiza (entre otros) por los siguientes métodos de workbook :

Lee el fichero especificado y determina automáticamente su formato correcto.

Lee el fichero, pero asume que su formato viene especificado en AFormat.

Se pueden utilizar los siguientes métodos de workbook para salvar a fichero:

Escribe un workbook al fichero dado utilizando el formato dado a spreadsheet. En caso de que el fichero ya exista entonces se sobreescribe automáticamente si AOverwriteExisting vale true:

mismo, pero el formato de fichero se determina a partir de la extensión dada (en el caso de Excel con extensión xls y para la versión más reciente se utiliza sfExcel8).

Después de llamar a estos métodos es conveniente revisar la propiedad ErrorMsg de workbook para verificar si existen mensajes de error o advertencias generadas durante el proceso de lectura o escritura. Esta propiedad retorna cadenas multi-línea por lo que se mostrarán mejor en un componente tipo memo; en caso de que el proceso de lectura o escritura se realice correctamente entonces nos retornará una cadena vacía.

Note: FPSpreadsheets provee units especializadas para la lectura / escritura de cada formato de fichero. Estas unidades no se aplican automáticamente, deben ser añadidas explícitamente a la claúsula uses. FPSpreadsheet will complain about "unsupported file format" si no encuentra la lectura / escritura solicitada. Aquí se muestra un listado de los nombres de las units.

Worksheet

El workbook contiene un listado de instancias TsWorksheet. Estas corresponde con las solapas (tabs) que puedes ver en Excel o en OpenOffice/LibreOffice. Cuando se realiza la lectura de un fichero spreadsheet (hoja de cálculo) entonces se crean automáticamente las hojas del libro (worksheets) de acuerdo al contenido del fichero. Cuando se crea una hoja de cálculo manualmente para guardarlo en un fichero, entonces hay que crear un worksheet añadiendolo al libro:

pascal
var
      MyWorkbook: TsWorkbook;
      MyWorksheet: TsWorksheet;
    begin
      MyWorkbook := TsWorkbook.Create;
      MyWorksheet := MyWorkbook.AddWorksheet('My_Table');  
        // 'My_Table' is the "name" of the worksheet
      ...

Already existing worksheets can be accessed by using the TsWorkbook methods

La cuenta de los worksheets que contiene se puede consultar llamando a GetWorksheetCount.

Celda (cell)

El worksheet, finalmente, permite acceder a las celdas. Un worksheet creado de cero, como en el ejemplo mostrado, se encuentra vacío y no contiene celdas. Las celdas se añaden mediante la asignación de datos o atributos utilizando métodos WriteXXXX del worksheet. Como ya se ha mencionado, la celda "A1" pertenece a la fila 0, columna 0 (row 0, col 0). Debe tenerse en cuenta que el índice de fila y columna siempre se especifican en este orden, lo cual es "diferente de la convención de" TStringGrid. El siguiente ejemplo crea una celda en la dirección A1 y le asigna el número 1.0.

pascal
var
      MyWorkbook: TsWorkbook;
      MyWorksheet: TsWorksheet;
    begin
      MyWorkbook := TsWorkbook.Create;
      MyWorksheet := MyWorkbook.AddWorksheet('Mi_Tabla');
      MyWorksheet.WriteNumber(0, 0, 1.0);  // "A1" tiene row=0 y col=0
      ...

Además es posible acceder a las celdas directamente por medio de los métodos FindCell(ARow, ACol) o GetCell(ARow, ACol) del workbook. Ambas funciones existen además en la versión overloaded en la cual la dirección de la celda se puede pasar en notación Excel: FindCell(ACellStr: String) o GetCell(ACellStr: String). Por favor, estar al tanto de que estas funciones retornan un puntero al dato de la celda (tipo PCell). ¡ No olvidarse de dereferenciar los punteros !. La diferencia entre FindCell y GetCell es que la modalidad uno retorna nil, si la celda no existe todavía, mientras que la última crea en este caso una celda vacía.

pascal
if MyWorksheet.FindCell('A1') = nil then
      WriteLn('Cell A1 does not exist.');

This is the declaration of the cell's data type:

pascal
type
      TCell = record
        { Location of the cell }
        Worksheet: TsWorksheet;
        Col: Cardinal; // zero-based
        Row: Cardinal; // zero-based
    
        { Index of format record }
        FormatIndex: Integer;
    
        { Status flags }
        Flags: TsCellFlags;        // (cfHasComment, cfMerged, cfHyperlink, ...)
    
        { Cell content }
        UTF8StringValue: String;   // strings cannot be part of a variant record
        FormulaValue: String;
        case ContentType: TCellContentType of  // must be at the end of the declaration
          cctEmpty      : ();      // has no data at all
          cctFormula    : ();      // UTF8StringValue is outside the variant record
          cctNumber     : (Numbervalue: Double);
          cctUTF8String : ();      // FormulaValue is outside the variant record
          cctDateTime   : (DateTimevalue: TDateTime);
          cctBool       : (BoolValue: boolean);
          cctError      : (ErrorValue: TsErrorValue);
      end;
      PCell = ^TCell;

The field ContentType indicates which data type is stored in the cell:

pascal
type
      TCellContentType = (cctEmpty, cctFormula, cctNumber, cctUTF8String, cctDateTime, cctBool, cctError);

According to this field the corresponding data can be found in the fields

Due to usage of a variant record most of these values are overlapping, i.e. modification of NumberValue affects also the other values. Therefore, always respect the ContentType when accessing the TCell record directly (the worksheet methods discussed below consider this automatically).

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Nota: After calculation of a formula or after reading of a file, the ContentType of the formula cell is converted to that of the result. Then the presence of a formula can only be detetected from the FormulaValue string (or call HasFormula(cell) of unit fpsUtils).

Adding values to a cell is most easily accompished by using one of the WriteXXXX methods of the worksheet. The most important ones are:

pascal
type
      TsWorksheet = class
        ...
        { Writing of currency values }
        function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
          ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
          ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
          ANegCurrFormat: Integer = -1): PCell; overload;
        procedure WriteCurrency(ACell: PCell; AValue: Double;
          ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
          ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
          ANegCurrFormat: Integer = -1); overload;
        function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
          ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
        procedure WriteCurrency(ACell: PCell; AValue: Double;
          ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
    
        { Writing of date/time values }
        function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime): PCell; overload;
        procedure WriteDateTime(ACell: PCell; AValue: TDateTime); overload;
        function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
          ANumFormat: TsNumberFormat; ANumFormatStr: String = ''): PCell; overload;
        procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
          ANumFormat: TsNumberFormat; ANumFormatStr: String = ''); overload;
        function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
          ANumFormatStr: String): PCell; overload;
        procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
          ANumFormatStr: String); overload;
    
        { Writing of number values } 
        function WriteNumber(ARow, ACol: Cardinal; ANumber: double): PCell; overload;
        procedure WriteNumber(ACell: PCell; ANumber: Double); overload;
        function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
          ANumFormat: TsNumberFormat; ADecimals: Byte = 2): PCell; overload;
        procedure WriteNumber(ACell: PCell; ANumber: Double;
          ANumFormat: TsNumberFormat; ADecimals: Byte = 2); overload;
        function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
          ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
        procedure WriteNumber(ACell: PCell; ANumber: Double;
          ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
    
        { Writing of string values }
        function WriteText(ARow, ACol: Cardinal; AText: ansistring;
          ARichTextParams: TsRichTextParams = nil): PCell; overload;
        procedure WriteText(ACell: PCell; AText: String;
          ARichTextparams: TsRichTextParams = nil); overload;
    
        // the old string methods "WriteUTF8Text" are deprecated now
        ...

Some of these methods exist in overloaded versions in which cell formatting parameters can be added together with the cell value. Correspondingly to writing, there is also a number of worksheet methods for reading the cell values:

pascal
type
      TsWorksheet = class
        ...
        { Reading cell content as a string }
        function  ReadAsText(ARow, ACol: Cardinal): string; overload;
        function  ReadAsText(ACell: PCell): string; overload;
        function  ReadAsText(ACell: PCell; AFormatSettings: TFormatSettings): string; overload;
    
        { Reading cell content as a number }
        function  ReadAsNumber(ARow, ACol: Cardinal): Double; overload;
        function  ReadAsNumber(ACell: PCell): Double; overload;
        function  ReadNumericValue(ACell: PCell; out AValue: Double): Boolean;
    
        { Reading cell content as a date/time value }
        function  ReadAsDateTime(ARow, ACol: Cardinal; out AResult: TDateTime): Boolean; overload;
        function  ReadAsDateTime(ACell: PCell; out AResult: TDateTime): Boolean; overload;
        ...

FormatIndex is the index of the cell format record. It describes the formatting attributes of a cell. These records are collected by an internal list of the workbook and are defined like this:

pascal
type
      TsCellFormat = record
        FontIndex: Integer;
        TextRotation: TsTextRotation;
        HorAlignment: TsHorAlignment;
        VertAlignment: TsVertAlignment;
        Border: TsCellBorders;
        BorderStyles: TsCellBorderStyles;
        Background: TsFillPattern;
        NumberFormat: TsNumberFormat;
        NumberFormatStr: String;
        UsedFormattingFields: TsUsedFormattingFields;
          //uffTextRotation, uffFont, uffBold, uffBorder, uffBackground, uffNumberFormat, uffWordWrap, uffHorAlign, uffVertAlign
      end;

For specifying a format for a given cell call the corresponding the worksheet method WriteXXXX, for retrieving a format call ReadXXXX. These methods usually get a pointer to the cell as a parameter, but there are also overloaded versions which accept the row and column index. Moreover, formatting styles can also be applied directly to the cell by using a record helper implemented in unit fpsCell.

See cell formatting below for a more detailed description.

Columns and rows

Column and row records are added for each column and row having a non-default size:

pascal
type
      TCol = record
        Col: Cardinal;
        Width: Single;
      end;
      PCol = ^TCol;
    
      TRow = record
        Row: Cardinal;
        Height: Single;
      end;
      PRow = ^TRow;

Column widths and row heights can be specified in a variety of units defined by the type TsSizeUnits = (suChars, suLines, suMillimeters, suCentimeters, suPoints, suInches). suChars refers to the count of 0 characters fitting into the column width - this is the way how Excel defines column widths. suLines is the number of lines fitting into the row height. Both units are based on the character size of the workbook's default font. The other units are conventional physical length units (1 cm = 10 mm, 1 inch = 25.4 mm = 72 pt). Fractional values are accepted. The workbook and worksheets store lengths internally in millimeters (MyWorkbook.Units).

The Office applications usually adjust the row heights automatically according to the font or text rotation of the cell content, and empty rows have a predefined default row height. In order to overrun this and to set a predefined row height use the worksheet method WriteRowHeight. In the same way the width of columns can be set to a specific value by calling WriteColWidth. The height/width of a particular row/column can be retrieved by means of the methods GetRowHeight or GetColHeight. Note that these methods return the default row heights/column widths if there are no TRow/TCol records.

pascal
type TsWorksheet = class
      ...
      { Set row height }
      procedure WriteRowHeight(ARowIndex: Cardinal; AHeight: Single; AUnits: TsSizeUnits);
      { Set column width }
      procedure WriteColWidth(AColIndex: Cardinal; AWidth: Single; AUnits: TsSizeUnits);
      { Set default row height }
      procedure WriteDefaultRowHeight(AHeight: Single; AUnits: TsSizeUnits);
      {Set default cokumn width }
      procedure WriteDefaultColWidth(AWidth: Single; AUnits: TsSizeUnits);
    
      { Return row height }
      function GetRowHeight(ARowIndex: Cardinal; AUnits: TsSizeUnits): Single;
      { Return column width }
      function GetColWidth(AColIndex: Cardinal; AUnits: TsSizeUnits): Single;
      { Return default row height }
      function ReadDefaultRowHeight(AUnits: TsSizeUnits): Single;
      { Return default column width }
      function ReadDefaultColWidth(AUnits: TsSizeUnits): Single;
    
      property DefaultRowHeight: Single;
      property DefaultColWidht: Single;

There are also overloaded versions of these methods which do not require the AUnits parameter. In this case, row heights are defined in terms of line count, and column widthts are defined in terms of character count. Note that these variants are from previos versions and are deprecated now.

The default row height and default column width is used unless a specific value is assigned to individual rows or columns. The property DefaultRowHeight expects the value to be in line count, and DefaultColWidth expects it to be in "0" character count. Other size units can be applied if using the corresponding WriteXXXX and ReadXXXX methods.

Formulas

Two kinds of formulas are supported by FPSpreadsheet:

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 FormulaValue. 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 written by the Office application. To activate reading of formulas add the element boReadformulas to the workbook's Options before opening the file.

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 boCalcBeforeSaving to the workbook's Options.

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 boAutoCalc in the workbook's Options.

The most general setting regarding formulas, therefore, is

pascal
MyWorkbook.Options := MyWorkbook.Options + [boReadFormulas, boCalcBeforeSaving, boAutoCalc];

String formulas

String formulas are written in the same way as in the Office applications. The worksheet method for creating a string formula is WriteFormula:

pascal
var
      MyWorksheet: TsWorksheet;
    //...
      MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1.215,0)');
      // By default, use dot as decimal and comma as list separator!

A few notes on syntax :

pascal
var
      MyWorksheet: TsWorksheet;
    //...
      MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1,215;0)', true);
      // Because of the "true" the formula parser accepts the comma as decimal and the
      // semicolon as list separator if the workbook's FormatSettings are set up like this.

RPN formulas

At application level, string formulas are mainly used, and RPN formulas are of little practical importance. Therefore, documentation of RPN formulas has been removed from this main FPSpreadsheet wiki and can be found in the article "RPN Formulas in FPSpreadsheet".

Shared formulas and array 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 the 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.

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 RegisterFunction from the unit fpsExprParser :

pascal
procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
      const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprFunctionCallBack); overload;
    
    procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
      const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprEventCallBack); overload;
pascal
type
      TsExprFunctionCallBack = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray);
      TsExprFunctionEvent = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray) of object;

The TsExpressionResult is a variant record containing result or argument data of several types:

pascal
type
      TsResultType = (rtEmpty, rtBoolean, rtInteger, rtFloat, rtDateTime, rtString,
        rtCell, rtCellRange, rtError, rtAny);
    
      TsExpressionResult = record
        Worksheet       : TsWorksheet;
        ResString       : String;
        case ResultType : TsResultType of
          rtEmpty       : ();
          rtError       : (ResError       : TsErrorValue);
          rtBoolean     : (ResBoolean     : Boolean);
          rtInteger     : (ResInteger     : Int64);
          rtFloat       : (ResFloat       : TsExprFloat);
          rtDateTime    : (ResDateTime    : TDatetime);
          rtCell        : (ResRow, ResCol : Cardinal);
          rtCellRange   : (ResCellRange   : TsCellRange);
          rtString      : ();
      end;
    
      TsExprParameterArray = array of TsExpressionResult;

As an example we show here the code for the CONCATENATE() formula which joins two or more strings:

pascal
const
      INT_EXCEL_SHEET_FUNC_CONCATENATE = 336;  
    ...
      RegisterFunction('CONCATENATE', 'S', 'S+', INT_EXCEL_SHEET_FUNC_CONCATENATE, @fpsCONCATENATE);
    
    procedure fpsCONCATENATE(var Result: TsExpressionResult; const Args: TsExprParameterArray);
    // CONCATENATE( text1, text2, ... text_n )
    var
      s: String;
      i: Integer;
    begin
      s := '';
      for i:=0 to Length(Args)-1 do
      begin
        if Args[i].ResultType = rtError then
        begin
          Result := ErrorResult(Args[i].ResError);
          exit;
        end;
        s := s + ArgToString(Args[i]);
        // "ArgToString" simplifies getting the string from a TsExpressionResult as 
        // a string may be contained in the ResString and in the ResCell fields.
        // There is such a function for each basic data type.
      end;
      Result := StringResult(s);
      // "StringResult" stores the string s in the ResString field of the
      // TsExpressionResult and sets the ResultType to rtString.
      // There is such a function for each basic data type.
    end;

There is a worked-out example (demo_formula_func.pas) in the folder examples/other of the FPSpreadsheet installation. In this demo, four financial functions (FV(), PV(), PMT(), RATE()) are added to FPSpreadsheet.

Cell formatting

Number and date/time formats

Numbers and date/time values can be displayed in a variety of formats. In FPSpreadsheet this can be achieved in two ways:

Number formats can be specified by these worksheet methods:

pascal
type
      TsWorksheet = class
      public
        // Set number formats alone
        function WriteNumberFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
          const AFormatString: String = ''): PCell; overload;
        procedure WriteNumberFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
          const AFormatString: String = ''); overload;  
    
        function WriteNumberFormat(ARow, ACol: Cardinal; ANumFormat: TsNumberFormat;
          ADecimals: Integer; ACurrencySymbol: String = ''; APosCurrFormat: Integer = -1;
          ANegCurrFormat: Integer = -1): PCell; overload;
        procedure WriteNumberFormat(ACell: PCell; ANumFormat: TsNumberFormat;
          ADecimals: Integer; ACurrencySymbol: String = '';
          APosCurrFormat: Integer = -1; ANegCurrFormat: Integer = -1); overload;
    
        function WriteFractionFormat(ARow, ACol: Cardinal; AMixedFraction: Boolean;
          ANumeratorDigits, ADenominatorDigits: Integer): PCell; overload;
        procedure WriteFractionFormat(ACell: PCell; AMixedFraction: Boolean;
          ANumeratorDigits, ADenominatorDigits: Integer); overload;
    
        // Set date/time formats alone  
        function WriteDateTimeFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
          const AFormatString: String = ''): PCell; overload;
        procedure WriteDateTimeFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
          const AFormatString: String = ''); overload;
    
    
        // Set cell values and number formats in one call
    
        // number values
        function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
          AFormat: TsNumberFormat = nfGeneral; ADecimals: Byte = 2;
          ACurrencySymbol: String = ''): PCell; overload;
        procedure WriteNumber(ACell: PCell; ANumber: Double; AFormat: TsNumberFormat = nfGeneral;
          ADecimals: Byte = 2; ACurrencySymbol: String = ''); overload;
        function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
          AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
        procedure WriteNumber(ACell: PCell; ANumber: Double;
          AFormat: TsNumberFormat; AFormatString: String); overload;   
    
        // date/time values
        function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
          AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''): PCell; overload;
        procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
          AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''); overload;
        function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
          AFormatStr: String): PCell; overload;
        procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
          AFormatStr: String); overload;  
    
        // currency values
        function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
          AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
          ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
          ANegCurrFormat: Integer = -1): PCell; overload;
        procedure WriteCurrency(ACell: PCell; AValue: Double;
          AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
          ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
          ANegCurrFormat: Integer = -1); overload;
        function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
          AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
        procedure WriteCurrency(ACell: PCell; AValue: Double;
          AFormat: TsNumberFormat; AFormatString: String); overload;     
      ...
Built-in number formats

Los formatos internos están definidos por la enumeración TsNumberFormat. A pesar de su nombre, los elementos cubren tanto valores de número como la fecha/hora:

pascal
type
      TsNumberFormat = (
        // De propósito general para todos los números.
        nfGeneral,
        // numbers
        nfFixed, nfFixedTh, nfExp, nfPercentage, nfFraction,
        // currency
        nfCurrency, nfCurrencyRed, 
        // dates and times
        nfShortDateTime, nfShortDate, nfLongDate, nfShortTime, nfLongTime,
        nfShortTimeAM, nfLongTimeAM, nfDayMonth, nfMonthYear, nfTimeInterval,
        // other (using format string)
        nfCustom);

As already noted the workbook has a property FormatSettings which provides additional information to control the resulting formatting. This is essentially a copy of the DefaultFormatSettings declared in the sysutils unit (the elements LongDateFormat and ShortDateFormat are slightly modified to better match the default settings in the main spreadsheet applications). The main purpose of the FormatSettings is to add a simple way of localization to the number formats.

Number format strings

In addition to these pre-defined formats, more specialized formatting can be achieved by using the format constant nfCustom along with a dedicated format string. The format string is constructed according to Excel syntax which is close to the syntax of fpc's FormatFloat and FormatDateTime commands (accepted as well, see the online-help for these functions).

Here is a basic list of the symbols used:

Symbol Meaning Format string: Number --> Output string
General Displays all decimal places of the number 'General':
1.2345678 --> '1.2345678'
0 Displays insignificant zeros if a number has less digits than there are zeros in the format. If used for decimal places then the number is rounded to as many decimal places as 0s are found. '000': 1 --> '001'
'0.0': 1 --> '1.0'
'0.0': 1.2345678 --> '1.2'
* Like "0" above, but does not display insignificant zeros. '0.*': 1 --> '1.'
'0.*': 1.2345678 --> '1.2'
? Like "0" above, but insignificant zeros are replaced by space characters. Good for aligning decimal points and fractions '??0': 1 --> ' 1'
'0.0??': 1 --> '1.0 '
. Decimal separator; will be replaced by the value used in the DecimalSeparator of the workbook's FormatSettings '0.00': 8.9 --> '8.90'
, Thousand separator; will be replaced by the value used in the ThousandSeparator of the workbook's FormatSettings. If at the end of a number formatting sequence the displayed value is divided by 1000. '#,##0.00': 1200 --> '1,200.00'
'0.00,': 1200 --> '1.20'
E+, e+ Displays a number in exponential format. The digits used for the exponent are defined by the number of zeros added the this symbol. The sign of the exponent is shown for positive and negative exponents. '0.00E+00': 1200 --> 1.20E+03
E-, e- Displays a number in exponential format. The digits used for the exponent are defined by the number of zeros added the this symbol. The sign of the exponent is shown only for negative exponents. '0.00e-000': 1200 --> 1.20e003
% Displays the number as a "percentage", i.e. the number is multiplied by 100 and a % sign is added. '0.0%': 0.75 --> 75.0%
/ This symbol has two meanings: if the cell represents a "number" then the slash indicates formatting as fraction, place holders for numerator and denominator must follow. If the cell represents a "date/time" then the slash indicates the date separator which will be replaced by the DateSeparator of the workbook's FormatSettings '#/#': 1.5 --> '3/2'
'# #/#': 1.5 --> '1 1/2'
'# #/16': 1.5 --> '1 8/16'
also: see date/time examples below
: Separator between hours, minutes and seconds of a date/time value. Will be replaced by the TimeSeparator of the workbook's FormatSettings. see examples below
yyyy Place holder for the year of a date/time value. The year is displayed as a four-digit number. 'yyyy/mm/dd':
Jan 3, 2012 --> '2012-01-03'
In this example, the DateSeparator is a dash character (-).
yy Place holder for the year of a date/time value. The year is displayed as a two-digit number. 'yy/mm/dd':
Jan 3, 2012 --> '12-01-03'
m Place holder for the month of a date/time value. The month is shown as a number without extra digits.
Please note that the m code can also be interpreted as the "minutes" of a time value (see below). 'yyyy/m/dd':
Jan 3, 2012 --> '2012-1-03'
mm Place holder for the month of a date/time value. The month is shown as a two-digit number, i.e. a leading zero is added for January to September.
Please note that the mm code can also be interpreted as the "minutes" of a time value (see below). 'yyyy/mm/dd':
Jan 3, 2012 --> '2012-01-03'
mmm Place holder for the month of a date/time value. The month is displayed by its abbreviated name. 'yyyy/mmm/dd':
Jan 3, 2012 --> '2012-Jan-03'
mmmm Place holder for the month of a date/time value. The month is displayed by its full name.
'yyyy/mmm/dd':
Jan 3, 2012 --> '2012-January-03'
d Place holder for the day of a date/time value to be displayed as a number. The day is displayed as a simple number, without adding a leading zero. 'yyyy/mm/d':
Jan 3, 2012 --> '2012-01-3'
dd Place holder for the day of a date/time value to be displayed as a number. dd adds a leading zero to single-digit day numbers. 'yyyy/mm/dd':
Jan 3, 2012 --> '2012-01-03'
ddd Place holder for the day of a date/time value. The day is displayed as its abbreviated name. 'dddd, yyyy/mm/ddd':
Jan 03, 2012 --> 'Tue 2012-01-03'
dddd Place holder for the day of a date/time value. The day is displayed as its full name. 'dddd, yyyy/mmmm/dd':
Jan 03, 2012 --> 'Tuesday 2012-01-03'
h Place holder of the hour part of a date/time value. The hour is displayed as a simple number, without adding a leading zero. 'h:mm':
0.25 --> '6:00'
hh Place holder of the hour part of a date/time value. The hour is displayed with a leading zero if the hour is less than 10. 'hh:mm':
0.25 --> '06:00'
[hh], or [h] Displays elapsed time such that the hour part can become greater than 23 '[h]:mm':
1.25 --> '30:00'
m Place holder of the minutes part of a date/time value. The minutes are shown as a simple number without adding a leading zero. Note that if the m codes are surrounded by date symbols (y, d) then they are interpreted as "month". 'h:m':
0.25 --> '6:0'
mm Place holder of the minutes part of a date/time value. Single-digit minutes are displayed with a leading zero. Note that if the mm code is surrounded by date symbols (y, d) then it is interpreted as "month". 'h:mm':
0.25 --> '6:00'
[mm], or [m] Displays elapsed time such that the minute part can become greater than 59 '[mm]:ss':
1.25 --> '1800:00'
s Place holder of the seconds part of a date/time value. The seconds are displayed as a simple number, without adding a leading zero. 'hh:mm:s':
0.25 --> '06:00:0'
ss Place holder of the seconds part of a date/time value. Single-digit seconds are displayed with a leading zero. 'hh:mm:ss':
0.25 --> '06:00:00'
[ss], or [s] Displays elapsed time such that the seconds part can become greater than 59 '[ss]':
1.25 --> '108000'
AM/PM, am/pm, A/P, or a/p Displays the time in the 12-hour format. 'hh:mm:ss AM/PM':
0.25 --> '6:00:00 AM'
" The text enclosed by quotation marks is inserted into the formatted strings literally. 'yyyy"/"mm"/"dd':
Jan 3, 2012 --> '2012/01/03' (i.e. the / is not replaced by the DateSeparator of the workbook).
\ The next character of the format string appears in the result string literally. The \ itself does not show up. 'yyyy\/mm\/dd':
Jan 3, 2012 --> '2012/01/03'
; A format string can contain up to three sections separated by the semicolon. The first section is used for positive numbers, the second section for negative numbers, and the third section for zero numbers. If the third section is missing then a zero value is formatted as specified in the first section. If the second section is missing as well then all values are formatted according to the first section. '"#,##0"$"';-#,##0"$";"-"':
1200 --> '1,200$'
-1200 --> '1,200$'
0 --> '-'
(, and ) Sometimes used for currency values to indicate negative numbers, instead of minus sign '#,##0"$";(#,##0)"$"':
-1200 --> '(1200)$'
[red] The formatted string is displayed in the specified color. Instead of [red], you can use accordingly [black], [white], [green], [blue], [magenta], [yellow], or [cyan]. Often used to highlight negative currency values. '"$" #,##0.00;[red]("$" #,##0.00)':
-1200 --> '($ 1200.00)'

Colores

FPSpreadsheet soporta colores de texto, fondo de celda y bordes de celda. En la unit fpstypes se declaran los colores EGA básicos como constantes.

Predefined colors

pascal
type
      TsColor = DWORD;  // ts = text style color
    
    const
      scBlack = $00000000;      // Negro
      scWhite = $00FFFFFF;      // Blanco
      scRed = $000000FF;        // Rojo
      scGreen = $0000FF00;      // Verde  
      scBlue = $00FF0000;       // Azul
      scYellow = $0000FFFF;     // Amarillo
      scMagenta = $00FF00FF;    // Magenta
      scCyan = $00FFFF00;       // Cian
      scDarkRed = $00000080;    // Rojo oscuro
      scDarkGreen = $00008000;  // Verde oscuro
      scDarkBlue = $00800000;   // Azul oscuro
      scOlive = $00008080;      // Oliva
      scPurple = $00800080;     // Púrpura
      scTeal = $00808000;       // Verde azulado
      scSilver = $00C0C0C0;     // Plateado
      scGray = $00808080;       // Gris
      scGrey = scGray;       // redefine to allow different spelling
    
      // Identificador para color no definido
      scNotDefined = $40000000;
    
      // Identifier for transparent color 
      scTransparent = $20000000;

El TsColor representa el valor rgb (red,green,blue) de un color, un solo byte utilizado para el rojo (red), otro para el verde (green), y otro para el azul(blue). El número resultante tiene notación little endian, i.e. el valor rojo va primero en memoria: $00BBGGRR. (Esto es directamente compatible con los valores de color tales como están definidos en la unit graphics).

The high order byte is usually zero but is used internally to identify special color values, such as for undefined or transparent colors.

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Nota: In older versions of the library colors were defined as indexes into a color palette. THIS IS NO LONGER WORKING.

Unit fpsutils contains some useful functions for modification of colors :

returns the name of the colors defined above, or a string showing the rgb components for other colors.

returns scBlack for a "bright", scWhite for a "dark" input color.

brightens or darkens a color by applying a factor tint = -1..+1, where -1 means "100% darken", +1 means "100% brighten", and 0 means "no change". The hue of the color is preserved.

Cell background

The cell background can be filled by predefined patterns which are identified by the record TsFillPattern:

pascal
type
      TsFillPattern = record
        Style: TsFillStyle;   // fill style pattern as defined below
        FgColor: TsColor;     // forground color of the fill pattern
        BgColor: TsColor;     // background color of the fill pattern
      end;
    
      TsFillStyle = (fsNoFill, fsSolidFill, fsGray75, fsGray50, fsGray25, fsGray12, fsGray6,
        fsStripeHor, fsStripeVert, fsStripeDiagUp, fsStripeDiagDown,
        fsThinStripeHor, fsThinStripeVert, fsThinStripeDiagUp, fsThinStripeDiagDown,
        fsHatchDiag, fsThinHatchDiag, fsThickHatchDiag, fsThinHatchHor);
pascal
type
      TsWorksheet = class
      public
        function WriteBackground(ARow, ACol: Cardinal; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor): PCell; overload;
        procedure WriteBackground(ACell: PCell; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor); overload;
    
        function WriteBackgroundColor(ARow, ACol: Cardinal; AColor: TsColor): PCell; overload;
        procedure WriteBackgroundColor(ACell: PCell; AColor: TsColor); overload;
    
        function ReadBackground(ACell: PCell): TsFillPattern;
        function ReadBackgroundColor(ACell: PCell): TsColor; overload;
        // ...
      end;
    
    var
      cell: PCell;
    ...
      // Example 1: Assign a pattern of thin, horizontal, yellow stripes on a blue background to empty cell A1 (row 0, column 0)
      MyWorksheet.WriteBackground(0, 0, fsThinStripeHor, scYellow, scBlue);
    
      // Example 2: Uniform gray background color of cell B1 (row 0, column 1) containing the number 3.14
      cell := MyWorksheet.WriteNumber(0, 1, 3.14);
      MyWorksheet.WriteBackgroundColor(cell, clSilver);

Cell borders

Cells can be emphasized by drawing border lines along their edges or diagonal lines. There are four borders plus two diagonals enumerated in the data type TsCellBorder:

pascal
type
      TsCellBorder = (cbNorth, cbWest, cbEast, cbSouth, dbDiagUp, dbDiagDown);
      TsCellBorders = set of TsCellBorder;

In order to show a border line add the corresponding border to the cell's set Borders (type TsCellBorders, see above). In this way, each cell edge can be handled separately. Use the worksheet method WriteBorders for this purpose. This example adds top and bottom borders to the edges A1 and B1:

pascal
MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]);  // A1: row 0, column 0
      MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSourth]); // B1: row 0, column 1

Lines usually are drawn as thin, solid, black lines. But it is possible to modify line style and color of each line. For this purpose, the cell provides an array of TsCellBorderStyle records:

pascal
type
      TsLineStyle = (lsThin, lsMedium, lsDashed, lsDotted, lsThick, lsDouble, lsHair,
        lsMediumDash, lsDashDot, lsMediumDashDot, lsDashDotDot, lsMediumDashDotDot, lsSlantDashDot); 
    
      TsCellBorderStyle = record
        LineStyle: TsLineStyle;   
        Color: TsColor;
      end;
    
      TsCellBorderStyles = array[TsCellBorder] of TsCellBorderStyle;   
    
      TsWorksheet = class
      public
        function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; AStyle: TsCellBorderStyle): PCell; overload;
        procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; AStyle: TsCellBorderStyle); overload;
    
        function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor): PCell; overload;
        procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor); overload;
    
        function WriteBorderColor(ARow, ACol: Cardinal; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;
        proceure WriteBorderColor(ACell: PCell; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;
    
        function WriteBorderLineStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;
        procedure WriteBorderLineStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;
    
        function WriteBorderStyles(ARow, ACol: Cardinal; const AStyles: TsCellBorderStyles): PCell; overload;      
        procedure WriteBorderStyles(ACell: PCell; const AStyles: TsCellBorderStyles); overload;
    
        function WriteBorders(ARow, ACol: Cardinal; ABorders: TsCellBorders): PCell; overload
        procedure WriteBorders(ACell: PCell; ABorders: TsCellBorders); overload
    
        function ReadCellBorders(ACell: PCell): TsCellBorders;
        function ReadCellBorderStyle(ACell: PCell; ABorder: TsCellBorder): TsCellBorderStyle;
        function ReadCellBorderStyles(ACell: PCell): TsCellBorderStyles;
        ...
      end;

The style of a given cell border can be specified by the following methods provided by the worksheet:

This example adds a thin black border to the top, and a thick blue border to the bottom of cells A1 and B1:

pascal
var
      cellA1, cellB1: PCell;
    ...
      cellA1 := MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]);    // cell A1: row 0, column 0
      MyWorksheet.WriteBorderStyle(cellA1, cbNorth, lsThin, scBlack);  
      MyWorksheet.WriteBorderStyle(cellA1, cbSouth, lsThick, scBlue);
    
      cellB1 := MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSouth]);    // cell B1: row 0, column 1
      MyWorksheet.WriteBorderStyles(cellB1, cellA1^.BorderStyles);     // copy all border styles from cell A1 to B1

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Nota: Diagonal lines are not supported by sfExcel2 and sfExcel5 - they are just omitted. sfExcel8 and sfOOXML use the same linestyle and color for both diagonals; when writing the border style of the diagonal-up line is assumed to be valid also for the diagonal-down line. Some writers do not support all the line styles of the TsLineStyle enumeration. In this case, appropriate replacement line styles are used.

Fonts

The cell text can displayed in various fonts. For this purpose, the workbook provides a list of TsFont items:

pascal
type
      TsFont = class
        FontName: String;
        Size: Single;   
        Style: TsFontStyles;
        Color: TsColor;
        Position: TsFontPosition;
      end;

Every cell is provided with an index into the font list.

In order to assign a particular font to a cell, use one of the following methods of TsSpreadsheet:

pascal
type
      TsSpreadsheet = class
      public
        function  WriteFont(ARow, ACol: Cardinal; const AFontName: String;
          AFontSize: Single; AFontStyle: TsFontStyles; AFontColor: TsColor): Integer; overload;
        procedure WriteFont(ARow, ACol: Cardinal; AFontIndex: Integer); overload;
        function WriteFontColor(ARow, ACol: Cardinal; AFontColor: TsColor): Integer;
        function WriteFontSize(ARow, ACol: Cardinal; ASize: Integer): Integer;
        function WriteFontStyle(ARow, ACol: Cardinal; AStyle: TsFontStyles): Integer; 
        // plus: overloaded versions accepting a pointer to a cell record instead of the row and column index as parameter
        // ...
      end;

The workbook's font list contains at least one item which is the default font for cells with unmodified fonts. By default, this is 10-point "Arial". Use the workbook method SetDefaultFont to assign a different font to the first list item.

The font at a given index of the font list can be looked up by calling the workbook function GetFont. The count of available fonts is returned by GetFontCount.

Here is an example which decreases the size of all 10-point "Arial" fonts to 9-point:

pascal
var
      i: Integer;
      font: TsFont;
    begin
      for i := 0 to MyWorkbook.GetFontCount-1 do
      begin
        font := MyWorkbook.GetFont(i);
        if (font.FontName = 'Arial') and (font.Size = 10.0) then
          font.Size := 9.0;
      end;
    end;

Rich-text formatting

In addition to using a specific font for each cell it is also possible to specify particular font attributes for individual characters or groups of characters in each cell text. Following the Excel notation, we call this feature Rich-text formatting (although is has nothing in common with the "rich-text" file format).

For this purpose, unit fpstypes declares the type TsRichTextParams which is an array of TsRichTextParam records:

pascal
type
      TsRichTextParam = record
        FontIndex: Integer;
        FirstIndex: Integer;
      end;
    
      TsRichTextParams = array of TsRichTextParam;

FontIndex refers to the index of the font in the workbook's FontList to be used for formatting of the characters beginning at the index FirstIndex. Being a string character index the FirstIndex is 1-based.

There are two ways to add "rich-text" formatting to a cell text:

pascal
MyWorksheet.WriteTextAsHTML(0, 0, 'Area (m<sup>2</sup>');
pascal
var
      richTextParams: TsRichTextParams;
      fnt: TsFont;
    begin
      SetLength(rtp, 2);
      fnt := MyWorksheet.ReadCellFont(0, 0);
      richTextParams[0].FirstIndex := 8; // The superscript groups begins with "2" which is the (1-based) character #8 of the cell text.
      richTextParams[0].FontIndex := MyWorkbook.AddFont(fnt.FontName, fnt.Size, fnt.Style, fnt.Color, fpSuperscript);
      richTextParams[1].FirstIndex := 9; // Normal font again beginning with character #9.
      richTextParams[1].FontIndex := MyWorksheet.ReadCellFontIndex(0, 0);
      MyWorksheet.WriteUTF8Text(0, 0, 'Area (m2)', richTextParams);
    end;

Use the worksheet method DeleteRichTextParams to remove rich-text formatting from a previously formatted cell.

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Nota: If the cell is supposed to have a font different from the worksheet's default font then this font must be written to the cell before writing the rich-text formatted text. Otherwise the font in the unformatted regions will not be up-to-date.

Text rotation

Usually text is displayed in the cells horizontally. However, it is also possible to rotate it by 90 degrees in clockwise or counterclockwise directions. In addition, there is also an option to stack horizontal characters vertically above each other.

If you need this feature use the worksheet method WriteTextRotation and specify the text direction by an element of the enumeration type TsTextRotation:

pascal
type
      TsTextRotation = (trHorizontal, rt90DegreeClockwiseRotation,
        rt90DegreeCounterClockwiseRotation, rtStacked);
    
      TsWorksheet = class
      public
        function WriteTextRotation(ARow, ACol: Cardinal; ARotation: TsTextRotation): PCell; overload;
        procedure WriteTextRotation(ACell: PCell; ARotation: TsTextRotation); overload;
    
        function ReadTextRotation(ACell: PCell): TsTextRotation;
        // ...
      end;
    
      // example for counter-clockwise rotated text in cell A1:
      WriteTextRotation(0, 0, rt90DegreeCounterClockwizeRotation);

Warning-icon.png

Warning: Finer degrees of rotation which may be supported by some spreadsheet file formats are ignored.

Text alignment

By default, cell texts are aligned to the left and bottom edges of the cell, except for numbers which are right-aligned. This behavior can be changed by using the worksheet methods WriteHorAlignment and WriteVertAlignment:

pascal
type
      TsHorAlignment = (haDefault, haLeft, haCenter, haRight);
      TsVertAlignment = (vaDefault, vaTop, vaCenter, vaBottom);    
    
      TsWorkbook = class
      public
        function WriteHorAlignment(ARow, ACol: Cardinal; AValue: TsHorAlignment): PCell; overload;
        procedure WriteHorAlignment(ACell: PCell; AValue: TsHorAlignment); overload;
        function ReadHorAlignment(ACell: PCell): TsHorAlignment;
    
        function WriteVertAlignment(ARow, ACol: Cardinal; AValue: TsVertAlignment): PCell; overload; 
        procedure WriteVertAlignment(ACell: PCell; AValue: TsVertAlignment); overload;
        function ReadVertAlignment(ACell: PCell): TsVertAlignment;
        // ...
      end;
    
      // Example: Center the text in cell A1 both horizontally and vertically
      MyWorkbook.WriteHorAlignment(0, 0, haCenter);
      MyWorkbook.WriteVertAlignment(0, 0, vaCenter);

Word wrap

Text which is longer than the width of a cell can wrap into several lines by calling the method WriteWordwrap of the spreadsheet:

pascal
type
      TsWorksheet = class
      public
        function WriteWordwrap(ARow, ACol: Cardinal; AValue: Boolean): PCell; overload;
        procedure WriteWordwrap(ACell: PCell; AValue: Boolean); overload;
        function ReadWordwrap(ACell: PCell): Boolean;
        //...
      end;
    
      // Example: activate wordwrap in cell A1
      MyWorksheet.WriteWordwrap(0, 0, true);

Merged cells

Like the Office applications, FPSpreadsheet supports also to feature of merging cells to a single large cell which is often used as a common header above simlar columns. Simply call MergeCells and pass a parameter to specify the cell range to be merged, either an Excel range string (such as A1:D5), or the first and last rows and columns:

pascal
MyWorksheet.MergeCells('A1:D5');
      // or: MyWorksheet.MergeCells(0, 0, 4, 3);  // first row, first column, last row, last column

The content and format displayed for a merged range is taken from the upper left corner of the range, cell A1 in above example. This cell is called the MergeBase in the library. Except for this corner cell, there must not be any other cells in the range. If there are their contents and format will be hidden.

In order to break up a merged range back up into individual cells, use the command Unmerge and pass any cell that is within the merged range:

pascal
MyWorksheet.UnmergeCells('B1');
      // or: MyWorksheet.UnmergeCells(0, 1);   // row, column of any cell within the range

Merged cells can be read from/written to all file formats except for sfCSV, sfExcel2 and sfExcel5 which do not support this feature natively.

The information which cells are merged is stored in an internal list. Unlike in earlier versions it is no longer possible to access the MergeBase from the cell directly. Use the following functions to extract information on merged cells:

pascal
var
      cell, base: PCell;
      r1,c1,r2,c2: Cardinal;
    ...
      cell := MyWorksheet.FindCell('B1');
      if MyWorksheet.IsMerged(cell) then
      begin
        WriteLn('Cell B1 is merged.');
    
        MyWorksheet.FindMergedRange(cell, r1, c1, r2, c2);
        WriteLn('The merged range is ' + GetCellRangeString(r1, c1, r2, c2));
    
        base := MyWorksheet.FindMergeBase(cell);
        WriteLn('The merge base is cell ' + GetCellString(base^.Row, base^.Col));
      end;

Additional data

Cell comments

Comments can be attached to any cell by calling

pascal
MyWorksheet.WriteComment(0, 0, 'This is a comment for cell A1');

They are stored in an internal list of the worksheet. Use the corresponding worksheet methods to access comments:

Hyperlinks

Hyperlinks can be attached to cells in order to link cells to other documents or other cells in the same workbook. The general syntax for creating hyperlinks is

pascal
procedure TWorksheet.WriteHyperlink(ARow, ACol: Cardinal; ATarget: String; ATooltip: String = '');

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Nota: Hyperlinks can be added to empty cells or to cells with content. In the latter case, the displayed content is not changed by the hyperlink; in the former case the cell is converted to a label cell showing the hyperlink target. Be aware that OpenOffice/LibreOffice only does accept hyperlinks with non-text cells.

Examples:

pascal
// Open the web site www.google.com
      MyWorksheet.WriteText(0, 0, 'Open google');
      MyWorksheet.WriteHyperlink(0, 0, 'http://www.google.com');
    
      // Open the local file with the absolute path "C:\readme.txt" (assuming Windows)
      MyWorksheet.WriteHyperlink(1, 0, 'file:///c:\readme.txt');
    
      // Open the mail client to send a mail
      MyWorksheet.WriteText('Send mail');
      MyWorksheet.WriteHyperlink(3, 0, 'mailto:somebody@gmail.com?subject=Test');
    
      // Jump to a particular cell 
      MyWorksheet.WriteText(5, 0, 'Jump to cell A10 on sheet2');
      MyWorksheet.WriteHyperlink(5, 0, '#Sheet2!A10');
    
      // Jump to cell A10 on the current sheet and display a popup hint
      MyWorksheet.WriteHyperlink(5, 0, '#A10', 'Go to cell A10');

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Nota: FPSpreadsheet does not "follow" the links, it only provides a mechanism to get access to the link data. TsWorksheetGrid from the laz_fpspreadsheet_visual package, however, fires the event OnClickHyperlink if a cell with an external hyperlink is clicked for fractions of a second. In the corresponding event handler, you can, for example, load a target spreadsheet, or open a web browser to display the linked web site. If the link is an internal link to another cell within the same workbook then the grid jumps to the related cell.

Images

FPSpreadsheet supports embedding of images in worksheets. Use one of the worksheet methods WriteImage() to add an image to the worksheet:

pascal
MyWorksheet.WriteImage(row, col, filename, offsetX, offsetY, scaleX, scaleY);
      MyWorksheet.WriteImage(row, col, stream, offsetX, offsetY, scaleX, scaleY);
      MyWorksheet.WriteImage(row, col, imageindex, offsetX, offsetY, scaleX, scaleY);

The upper/left corner of the image is placed at the upper/left corner of the cell in the specified row and column. The floating point parameters offsetX, offsetY, scaleX and scaleY are optional: they define an offset of this image anchor point from the cell corner and a magnification factor. The path to the image file is given as parameter filename. Alternatively, overloaded versions can be used which accept a stream in place of the file name or an image index in the workbook's EmbeddedObj list - use MyWorkbook.FindEmbeddedObj(filename) to get this index for a previously loaded image file.

Note that FPSpreadsheet needs to know the image type for successfull picture import. Currently, the types png , jpg , tiff , bmp , gif , svg , wmf , emf , and pcx are supported (Excel2007 cannot read imported svg and pcx images). Other formats can be registered by writing a function which determines the image size and pixel density, and by registering the new format using the procedure RegisterImageType - see unit fpsImages for examples:

pascal
type
      TsImageType = integer;
      TGetImageSizeFunc = function (AStream: TStream; out AWidth, AHeight: DWord; out dpiX, dpiY: Double): Boolean;
    
    function RegisterImageType(AMimeType, AExtension: String; AGetImageSize: TGetImageSizeFunc): TsImageType;

Due to differences in row height and column width calculation between FPSpreadsheet and Office applications it is not possible to position images correctly. If exact image positions are important you should follow these rules:

Sorting

Cells in a worksheet can be sorted for a variety of criteria by calling the Sort method of the worksheet. This method takes a TsSortParams record and the edges of the cell rectangle to be sorted as parameters; in an overloaded version, the cell rectangle can also be specified by means of an Excel-type range string (e.g. 'A1:G10'):

pascal
type
      TsWorksheet = class
        // ...
        procedure Sort(const ASortParams: TsSortParams;
          ARowFrom, AColFrom, ARowTo, AColTo: Cardinal); overload;
        procedure Sort(ASortParams: TsSortParams; ARange: String); overload;
        // ...
      end;

The sorting criteria are defined by a record of type TsSortParams:

pascal
type
      TsSortParams = record
        SortByCols: Boolean;
        Priority: TsSortPriority;  // spNumAlpha ("Numbers first"), or spAlphaNum ("Text first")
        Keys: TsSortKeys;
      end;    
    
      TsSortKey = record
        ColRowIndex: Integer;
        Options: TsSortOptions;    // set of [spDescending, spCaseInsensitive]
      end;

InitSortParams is a handy utility to initialize the sorting parameters:

pascal
function InitSortParams(ASortByCols: Boolean = true; ANumSortKeys: Integer = 1;
      ASortPriority: TsSortPriority = spNumAlpha): TsSortParams;

The next code fragment shows a typical sorting call:

pascal
var
      sortParams: TsSortParams;
    begin
      sortParams := InitSortParams(true, 2);  // sorting along columns, 2 sorting keys
    
      // primary sorting key: column 3, ascending, case-insensitive
      sortParams.Keys[0].ColRowIndex := 3;
      sortParams.Keys[0].Options := [ssoCaseInsensitive];
    
      // secondary sorting key: colum 1, descending
      sortParams.Keys[1].ColRowIndex := 1;
      sortParams.Keys[1].Options := [ssoDescending];
    
      // The sorted block extends between cells A1 (row=0, col=0) and F10 (row=9, col=5)
      MyWorksheet.Sort(sortParams, 0, 0, 9, 5);
      // or: MyWorksheet.Sort(sortParams, 'A1:F10');
    end;

Searching and replacing

Unit fpsSearch implements a search engine which can be used to look for specific cell content within a workbook, or to replace the found cell content by some other string.

Example:

pascal
var
      MyWorkbook: TsWorkbook;
      MyWorksheet: TsWorksheet;
      MyRow, MyCol: Cardinal;
      MySearchParams: TsSearchParams;
    begin
      MyWorkbook := TsWorkbook.Create;
      try
        MyWorkbook.ReadFromFile(AFileName);
    
        // Specify search criteria
        MySearchParams.SearchText := 'Hallo';
        MySearchParams.Options := [soEntireDocument];
    
        // Create search engine and execute search
        with TSearchEngine.Create(MyWorkbook) do begin
          if FindFirst(MySearchParams, MyWorksheet, MyRow, MyCol) then begin
            WriteLn('First "', MySearchparams.SearchText, '" found in cell ', GetCellString(MyRow, MyCol));
            while FindNext(MySeachParams, MyWorksheet, myRow, MyCol) do
              WriteLn('Next "', MySearchParams.SearchText, '" found in cell ', GetCellString(MyRow, MyCol));
          end;
          Free;
        end;
      finally
        MyWorkbook.Free;
      end;
    end;

The search engine provides two methods for searching : FindFirst and FindNext. They are very similar, they only differ in where the search begins. In case of FindFirst, the starting cell is determined from the Options described below. In case of FindNext the search begins at the cell adjacent to the previously found cell. Both methods return the worksheet and row and column indexes of the cell in which the search text is found. If the search is not successful then the function result is FALSE.

The record TsSearchParams specifies the criteria used for searching:

pascal
type
      TsSearchParams = record
        SearchText: String;
        Options: TsSearchOptions;
        Within: TsSearchWithin;
      end;

Besides the text to be searched (SearchText) it provides a set of options to narrow the search:

The record field Within identifies the part of the spreadsheet to be searched:

In addition to searching the search engine can also be used for replacing the found text by another string. Call the functions ReplaceFirst or ReplaceNext for this purpose. They act like their FindXXXX counterparts, therefore, they require a TsSearchParams record to specify the search criteria. But in addition to searching, these functions also perform the text replacement according to the specification in a TsReplaceParams record:

pascal
type
      TsReplaceParams = record
        ReplaceText: String;
        Options: TsReplaceOptions;
      end;

The ReplaceText identifies the string which will replace the found text pattern. The Options define a set of criteria how the replacement is done:

Column and row operations

The worksheet provides these methods for inserting and deleting columns a row:

pascal
type
      TsWorksheet = class
      ...
        procedure DeleteCol(ACol: Cardinal);
        procedure DeleteRow(ARow: Cardinal);
    
        procedure InsertCol(ACol: Cardinal);
        procedure InsertRow(ARow: Cardinal);
    
        procedure RemoveCol(ACol: Cardinal);
        procedure RemoveRow(ARow: Cardinal);
    
        procedure RemoveAllCols;
        procedure RemoveAllRows

`

``

``

``

Page layout

``

General

``

So far, FPSpreadsheet does not support printing of worksheets, but the Office applications do, and they provide a section of information in their files for this purpose. In FPSpreadsheets this information is available in the TsPageLayout class which belongs to the TsWorksheet data structure. Its properties and methods combine the most important features from the Excel and OpenDocument worlds.

``

pascal
type 
      TsPageOrientation = (spoPortrait, spoLandscape);
    
      TsPrintOption = (poPrintGridLines, poPrintHeaders, poPrintPagesByRows,
        poMonochrome, poDraftQuality, poPrintCellComments, poDefaultOrientation,
        poUseStartPageNumber, poCommentsAtEnd, poHorCentered, poVertCentered,
        poDifferentOddEven, poDifferentFirst, poFitPages);
    
      TsPrintOptions = set of TsPrintOption;  
    
      TsHeaderFooterSectionIndex = (hfsLeft, hfsCenter, hfsRight);
    
      TsCellRange = record
        Row1, Col1, Row2, Col2: Cardinal;
      end;
    
      TsPageLayout = class
      ...
      public
        ...
        { Methods }
        // embedded header/footer images
        procedure AddHeaderImage(AHeaderIndex: Integer;
          ASection: TsHeaderFooterSectionIndex; const AFilename: String);
        procedure AddFooterImage(AFooterIndex: Integer;
          ASection: TsHeaderFooterSectionIndex; const AFilename: String);
        procedure GetImageSections(out AHeaderTags, AFooterTags: String);
        function HasHeaderFooterImages: Boolean;
    
        // Repeated rows and columns 
        function HasRepeatedCols: Boolean;
        function HasRepeatedRows: Boolean;
        procedure SetRepeatedCols(AFirstCol, ALastCol: Cardinal);
        procedure SetRepeatedRows(AFirstRow, ALastRow: Cardinal);
    
        // print ranges 
        function AddPrintRange(ARow1, ACol1, ARow2, ACol2: Cardinal): Integer; overload;
        function AddPrintRange(const ARange: TsCellRange): Integer; overload;
        function GetPrintRange(AIndex: Integer): TsCellRange;
        function NumPrintRanges: Integer;
        procedure RemovePrintRange(AIndex: Integer);
    
        { Properties }
        property Orientation: TsPageOrientation read FOrientation write FOrientation;
        property PageWidth: Double read FPageWidth write FPageWidth;
        property PageHeight: Double read FPageHeight write FPageHeight;
        property LeftMargin: Double read FLeftMargin write FLeftMargin;
        property RightMargin: Double read FRightMargin write FRightMargin;
        property TopMargin: Double read FTopMargin write FTopMargin;
        property BottomMargin: Double read FBottomMargin write FBottomMargin;
        property HeaderMargin: Double read FHeaderMargin write FHeaderMargin;
        property FooterMargin: Double read FFooterMargin write FFooterMargin;
        property StartPageNumber: Integer read FStartPageNumber write SetStartPageNumber;
        property ScalingFactor: Integer read FScalingFactor write SetScalingFactor;
        property FitHeightToPages: Integer read FFitHeightToPages write SetFitHeightToPages;
        property FitWidthToPages: Integer read FFitWidthToPages write SetFitWidthToPages;
        property Copies: Integer read FCopies write FCopies;
        property Options: TsPrintOptions read FOptions write FOptions;
        property Headers[AIndex: Integer]: String read GetHeaders write SetHeaders;
        property Footers[AIndex: Integer]: String read GetFooters write SetFooters;
        property RepeatedCols: TsRowColRange read FRepeatedCols;
        property RepeatedRows: TsRowColRange read FRepeatedRows;
        property PrintRange[AIndex: Integer]: TsCellRange read GetPrintRange;
        property FooterImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetFooterImages;
        property HeaderImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetHeaderImages;
      end;
    
      TsWorksheet = class
      ...
      public
        property PageLayout: TsPageLayout;
        ...
      end;

``

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Nota: The fact that the PageLayout belongs to the worksheet indicates that there can be several page layouts in the same workbook, one for each worksheet.

``

``

``

``

``

``

``

``

pascal
MyWorksheet.PageLayout.Options := MyWorksheet.PageLayout.Options + [poFitPages];
      MyWorksheet.PageLayout.FitWidthToPages := 1;     // all columns on one page width
      MyWorksheet.PageLayout.FitHeightToPages := 0;    // use as many pages as needed

``

``

``

``

``

Headers and footers

``

Header and footer texts can be composed of left-aligned, centered and right-aligned strings. Add the symbol &L to indicate that the following string is to be printed as the left-aligned part; use &C accordingly for the centered and &R for the right-aligned parts. There are other symbols which will be replaced by their counterparts during printing:

``

``

The arrays Headers[]/Footers[] provide space for usage of three different headers or footers:

``

``

Leave the strings at index 0 and 2 empty if the print-out should always have the same header/footer. You can use the constant HEADER_FOOTER_INDEX_ALL for better clarity. Example:

``

pascal
MyWorksheet.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL] := '&C&D &T';           // centered "date time" on all pages as header
      MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_ODD] := '&RPage &P of &N';   // right-aligned "Page .. of ..." on odd pages as footer
      MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_EVEN] := '&LPage &P of &N';  // dto, but left-aligned on even pages

``

Loading and saving

``

Adding new file formats

``

FPSpreadsheet is open to any spreadsheet file format. In addition to the built-in file formats which are specified by one of the sfXXXX declarations, it is possible to provide dedicated reader and writer units to get access to special file formats.

``

``

pascal
function RegisterSpreadFormat(AFormat: TsSpreadsheetFormat; AReaderClass: TsSpreadReaderClass; AWriterClass: TsSpreadWriterClass;
      AFormatName, ATechnicalName: String; const AFileExtensions: array of String): TsSpreadFormatID;

``

``

Stream selection

``

Workbooks are loaded and saved by means of the ReadFromFile and WriteToFile methods, respectively (or by their stream counterparts, ReadFromStream and WriteToStream).

By default, the data files are accessed by means of memory streams which yields the fastest access to the files. In case of very large files (e.g. tens of thousands of rows), however, the system may run out of memory. There are two methods to defer the memory overflow by some extent.

``

``

Virtual mode

``

Beyond the transient memory usage during reading/writing the main memory consumptions originates in the internal structure of FPSpreadsheet which holds all data in memory. To overcome this limitation, a "virtual mode" has been introduced. In this mode, data are received from a data source (such as a database table) and are passed through to the writer without being collected in the worksheet. It is clear that data loaded in virtual mode cannot be displayed in the visual controls. Virtual mode is good for conversion between different data formats.

These are the steps required to use this mode:

``

``

Virtual mode also works for reading spreadsheet files.

The folder example/other contains a worked out sample project demonstrating virtual mode using random data. More realistic database examples are in example/db_import_export and in the chapter on converting a large database table using virtual mode.

``

Dataset export

``

FPC contains a set of units that allow you to export datasets to various formats (XML, SQL statements, DBF files,...). There is a master package that allows you to choose an export format at design time or run time (Lazarus package lazdbexport).

FPSpreadsheet has TFPSExport which plugs into this system. It allows you to export the contents of a dataset to a new spreadsheet (.xls , .xlsx , .ods , wikitable format) file into a table on the first sheet by default. In addition, if MultipleSheets is set to TRUE it is possible to combine several sheets into individual worksheets in the same file. You can optionally include the field names as header cells on the first row using the HeaderRow properties in the export settings. The export component tries to find the number format of the cells according to the dataset field types.

For more complicated exports, you need to manually code a solution (see examples below) but for simple data transfer/dynamic exports at user request, this unit will probably be sufficient.

A simple example of how this works:

``

pascal
uses
    ...
    fpsexport
    ...
    var
      Exp: TFPSExport;
      ExpSettings: TFPSExportFormatSettings;
      TheDate: TDateTime;
    begin
      FDataset.First; //assume we have a dataset called FDataset
      Exp := TFPSExport.Create(nil);
      ExpSettings := TFPSExportFormatSettings.Create(true);
      try
        ExpSettings.ExportFormat := efXLS; // choose file format
        ExpSettings.HeaderRow := true; // include header row with field names
        Exp.FormatSettings := ExpSettings; // apply settings to export object
        Exp.Dataset:=FDataset; // specify source
        Exp.FileName := 'c:\temp\datadump.xls';
        Exp.Execute; // run the export
      finally
        Exp.Free;
        ExpSettings.Free;
      end;

``

Visual controls for FPSpreadsheet

``

The package laz_fpspreadsheet_visual implements a series of controls which simiplify creation of visual GUI applications:

``

``

See the FPSpreadsheet tutorial: Writing a mini spreadsheet application for more information and a tutorial, and see demo projects for examples of the application of these components.

``

Examples

``

To create a project which uses the fpspreadsheet library, add the fpspreadsheet_pkg package to it's Lazarus project, or add the base directory of fpspreadsheet to you compiler options if using another IDE.

``

Units

``

The entire FPSpreadsheet package consists of several units. A spreadsheet application typically "uses" the following units:

``

``

The next units are required only occasionally:

``

``

All other units are probably not needed at the application level. In case of the visual spreadsheet controls , the needed units usually are inserted at design-time automatically.

``

Excel 5 example

``

Note: at least with fpspreadsheet from trunk (development version), this example requires (at least) Lazarus avglvltree.pas, lazutf8.pas, asiancodepagefunctions.inc, asiancodepages.inc and lconvencoding.pas (in the _$(LazarusDir)\components\lazutils_ directory)

``

pascal
{
    excel5demo.dpr
    
    Demonstrates how to write an Excel 5.x file using the fpspreadsheet library
    
    You can change the output format by changing the OUTPUT_FORMAT constant
    
    AUTHORS: Felipe Monteiro de Carvalho
    }
    program excel5demo;
    
    {$mode delphi}{$H+}
    
    uses
      Classes, SysUtils, fpstypes, fpspreadsheet, fpsallformats, laz_fpspreadsheet;
    
    const 
      OUTPUT_FORMAT = sfExcel5;
    
    var
      MyWorkbook: TsWorkbook;
      MyWorksheet: TsWorksheet;
      MyFormula: TsRPNFormula;
      MyDir: string;
    begin
      // Initialization
      MyDir := ExtractFilePath(ParamStr(0));
    
      // Create the spreadsheet
      MyWorkbook := TsWorkbook.Create;
      try
        MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
    
        // Write some number cells
        MyWorksheet.WriteNumber(0, 0, 1.0);
        MyWorksheet.WriteNumber(0, 1, 2.0);
        MyWorksheet.WriteNumber(0, 2, 3.0);
        MyWorksheet.WriteNumber(0, 3, 4.0);
    
        // Write the formula E1 = A1 + B1
        MyWorksheet.WriteFormula(0, 4, 'A1+B1');
    
        // Creates a new worksheet
        MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet 2');
    
        // Write some string cells
        MyWorksheet.WriteText(0, 0, 'First');
        MyWorksheet.WriteText(0, 1, 'Second');
        MyWorksheet.WriteText(0, 2, 'Third');
        MyWorksheet.WriteText(0, 3, 'Fourth');
    
        // Save the spreadsheet to a file
        MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
      finally
        MyWorkbook.Free;
      end;
    end.

``

Opening an existing spreadsheet

``

To open a spreadsheet while specifying a particular format to use use ReadFromFile with two parameters:

``

pascal
MyWorkbook.ReadFromFile(AFileName, sfExcel5);

``

It is also possible to call ReadFromFile with only one parameter, the filename. Then the workbook will use the extension to auto-detect the file format. In case of the ambiguous extension .xls (Excel 2-8) it will simply try various possibilities until one works. Although typical fingerprint byte patterns are checked now it is still possible that an exception will be raised for each incorrect format if run from the IDE at designtime; this does not occur at runtime.

``

pascal
MyWorkbook.ReadFromFile(AFileName);

``

Writing a spreadsheet to file based on extension

``

Similar to the ReadFromFile routine, there is also a WriteToFile procedure to determine the spreadsheet's type based on the filename suffix. It uses the GetFormatFromFileName routine in the previous section's code, so the actual code is simple. However, it will always write files with a given extension using the latest format that uses that extension (e.g. Excel .xls files will be written as sfExcel8), so if you want to write them in an earlier format, you have to use the base routine.

As above, this code patches the fpspreadsheet.pas unit.

``

pascal
procedure TsWorkbook.WriteToFile(const AFileName: string; const AOverwriteExisting: Boolean = False); overload;
    var SheetType: TsSpreadsheetFormat;
    begin
      if getFormatFromFileName(AFileName, SheetType) then
        WriteToFile(AFileName, SheetType, AOverwriteExisting)
      else raise Exception.Create(Format(
        '[TsWorkbook.WriteToFile] Attempted to save a spreadsheet by extension, but the extension %s is invalid.', [ExtractFileExt(AFileName)]));
    end;

``

Iterating through all Worksheets

``

pascal
var
      MyWorkbook: TsWorkbook;
      MyWorksheet: TsWorksheet;
      i: Integer;
    begin
      // Here load MyWorkbook from a file or build it
     
      for i := 0 to MyWorkbook.GetWorksheetCount() - 1 do
      begin
        MyWorksheet := MyWorkbook.GetWorksheetByIndex(i);
        // Do something with MyWorksheet
      end;

``

Iteration through cells

``

The first idea is to use a simple for-to loop:

``

pascal
var
      MyWorksheet: TsWorksheet;
      col, row: Cardinal;
      cell: PCell;
    begin
      for row:=0 to MyWorksheet.GetLastRowIndex do
        for col := 0 to MyWorksheet.GetLastColIndex do
        begin
          cell := MyWorksheet.FindCell(row, col);
          WriteLn(MyWorksheet.ReadAsUTF8Text(cell));
        end;
    end;

``

FindCell initiates a search for a cell independently of the previously found cell. Cells, however, are are organized interally in a sorted tree structure, and each cell "knows" its previous and next neighbors. Moreover, FindCell wastes time on searching for non-existing cells in case of sparsely-occupied worksheets. In general, it is more efficient to use the for-in syntax which takes advantage of the internal tree structure by means of special enumerators. Note that there are also dedicated enumerators for searching along rows, columns or in cell ranges:

``

pascal
var
      MyWorksheet: TsWorksheet;
      cell: PCell;
    begin
      // Search in all cells
      for cell in Myworksheet.Cells do
        WriteLn(MyWorksheet.ReadAsText(cell));
    
      // Search in column 0 only
      for cell in MyWorksheet.Cells.GetColEnumerator(0) do
        WriteLn(MyWorksheet.ReadAsText(cell));
    
      // Search in row 2 only
      for cell in MyWorksheet.Cells.GetRowEnumerator(2) do
        WriteLn(MyWorksheet.ReadAsText(cell));
    
      // Search in range A1:C2 only (rows 0..1, columns 0..2)
      for cell in MyWorksheet.Cells.GetRangeEnumerator(0, 0, 1, 2) do
        WriteLn(MyWorksheet.ReadAsText(cell));
    end;

``

Converting a database to a spreadsheet

``

The easiest solution is to use the DatasetExport component.

If you need to have more control over the process, use something like:

``

pascal
program db5xls;
     
    {$mode delphi}{$H+}
     
    uses
      Classes, SysUtils, 
      // add database units
      fpstypes, fpspreadsheet, fpsallformats;
     
    const OUTPUT_FORMAT = sfExcel5;
     
    var
      MyWorkbook: TsWorkbook;
      MyWorksheet: TsWorksheet;
      MyDatabase: TSdfDataset;
      MyDir: string;
      i, j: Integer;
    begin
      // Initialization
      MyDir := ExtractFilePath(ParamStr(0));
     
      // Open the database
      MyDatabase := TSdfDataset.Create;
      MyDatabase.Filename := 'test.dat';
      // Add table description here
      MyDatabase.Active := True;
    
      // Create the spreadsheet
      MyWorkbook := TsWorkbook.Create;
      MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
     
      // Write the field names
      for i := 0 to MyDatabase.Fields.Count - 1 do
        MyWorksheet.WriteText(0, i, MyDatabase.Fields[i].FieldName);
    
      // Write all cells to the worksheet
      MyDatabase.First;
      j := 0;
      while not MyDatabase.EOF do
      begin
        for i := 0 to MyDatabase.Fields.Count - 1 do
          MyWorksheet.WriteText(j + 1, i, MyDatabase.Fields[i].AsString);
    
        MyDatabase.Next;
        Inc(j);
      end;
    
      // Close the database
      MyDatabase.Active := False;
      MyDatabase.Free;
    
      // Save the spreadsheet to a file
      MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
      MyWorkbook.Free;
    end.

``

Converting a large database table to a spreadsheet using virtual mode

``

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Nota: The example program in examples\db_import_export shows a demonstration of using virtual mode to export datasets to spreadsheet files.

``

We want to write a large database table to a spreadsheet file. The first row of the spreadsheet is to show the field names in bold type face and with a gray background.

Normally, FPSpreadsheet would load the entire representation of the spreadsheet into memory, so we'll use virtual mode to minimize memory usage.

``

pascal
type
      TDataProvider = class;
    
    var
      MyWorkbook: TsWorkbook;
      MyWorksheet: TsWorksheet;
      MyDatabase: TSdfDataset;
      MyDir: string;
      MyHeaderTemplateCell: PCell;
      DataProvider: TDataProvider;
    
    // Implement TDataProvider here - see below...
    
    begin
      // Initialization
      MyDir := ExtractFilePath(ParamStr(0));
     
      // Open the database
      MyDatabase := TSdfDataset.Create;
      try
        MyDatabase.Filename := 'test.dat';
        // Add table description here
        MyDatabase.Active := True;
    
        // Create the spreadsheet
        MyWorkbook := TsWorkbook.Create;
        try
          MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
    
          // Create the template cell for the header line, we want the 
          // header in bold type-face and gray background color
          // The template cell can be anywhere in the workbook, let's just select cell A1.
          MyWorksheet.WriteFontStyle(0, 0, [fssBold]);
          MyWorksheet.WriteBackgroundColor(0, 0, scGray);
          // We'll need this cell again and again, so let's save the pointer to it in a variable
          MyHeaderTemplateCell := MyWorksheet.Find(0, 0);
     
          // Enter virtual mode
          MyWorkbook.Options := MyWorkbook.Options + [boVirtualMode];
    
          // Define number of columns - we want a column for each field
          MyWorksheet.VirtualColCount := MyDatabase.FieldCount;
    
          // Define number of rows - we want every record, plus 1 row for the title row
          MyWorksheet.VirtualRowCount := MyDatabase.RecordCount + 1;
    
          // Link the event handler which passes data from database to spreadsheet writer
          MyWorksheet.OnWriteCellData := @DataProvider.WriteCellData;
    
          // Write all cells to an Excel8 file
          // The data to be written are specified in the OnWriteCellData event handler.
          MyWorkbook.WriteToFile(MyDir + 'test.xls', sfExcel8);
    
        finally
          // Clean-up
          MyWorkbook.Free;
        end;
    
      finally
        // Close the database & clean-up
        MyDatabase.Active := False;
        MyDatabase.Free;
      end;
    
    end.

``

What is left is to write the event handler for OnWriteCellData. For the command-line program above we setup a particular data provider class (in a gui program the event handler can also be a method of any form):

``

pascal
type
      TDataProvider = class
        procedure WriteCellData(Sender: TsWorksheet; ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell);
      end;
    
    procedure TDataProvider.WriteCellData(Sender: TsWorksheet; ARow, ACol: Cardinal; AValue: variant; var: AStyleCell: PCell);
    begin
      // Let's handle the header row first:
      if ARow = 0 then begin
        // The value to be written to the spreadsheet is the field name.
        AValue := MyDatabase.Fields[ACol].FieldName;
        // Formatting is defined in the HeaderTemplateCell.
        AStyleCell := MyHeaderTemplateCell;
        // Move to first record
        MyDatabase.First;
      end else begin
        // The value to be written to the spreadsheet is the record value in the field corresponding to the column.
        // No special requirements on formatting --> leave AStyleCell at its default (nil).
        AValue := MyDatabase.Fields[ACol].AsVariant;
        // Advance database cursor if last field of record has been written
        if ACol = MyDatabase.FieldCount-1 then MyDatabase.Next;
      end;
    end;

``

Converting between two spreadsheet formats

``

pascal
program ods2xls;
     
    {$mode delphi}{$H+}
     
    uses
      Classes, SysUtils, 
      fpstypes, fpspreadsheet, fpsallformats, fpspreadsheet_pkg;
     
    const 
      INPUT_FORMAT = sfOpenDocument;
      OUTPUT_FORMAT = sfExcel8;
     
    var
      MyWorkbook: TsWorkbook;
      MyDir: string;
    begin
      // Initialization
      MyDir := ExtractFilePath(ParamStr(0));
     
      // Convert the spreadsheet
      MyWorkbook := TsWorkbook.Create;
      try
        MyWorkbook.ReadFromFile(MyDir + 'test.ods', INPUT_FORMAT);
        MyWorkbook.WriteToFile(MyDir + 'test.xls', OUTPUT_FORMAT);
      finally
        MyWorkbook.Free;
      end;
    end.

``

Reading and writing of CSV files

``

CSV files (CSV = comma-separated values) are plain text files without metadata. Therefore, additional information for correct reading of writing from/to a worksheet is required. The global record CSVParams makes available fundamental settings for this purpose:

``

pascal
type
      CSVParams: TsCSVParams = record    // W = writing, R = reading, RW = reading/writing
        SheetIndex: Integer;             // W: Index of the sheet to be written
        LineEnding: TsCSVLineEnding;     // W: Specification for line ending to be written
        Delimiter: Char;                 // RW: Column delimiter
        QuoteChar: Char;                 // RW: Character for quoting texts
        Encoding: String;                // RW: Encoding of file
        DetectContentType: Boolean;      // R: try to convert strings to content types
        NumberFormat: String;            // W: if empty write numbers like in sheet, otherwise use this format
        AutoDetectNumberFormat: Boolean; // R: automatically detects decimal/thousand separator used in numbers
        TrueText: String;                // RW: String for boolean TRUE
        FalseText: String;               // RW: String for boolean FALSE
        FormatSettings: TFormatSettings; // RW: add'l parameters for conversion
      end;

``

This record contains fields which are evaluated for reading only, writing only, or for both - see the attached comments.

A common situation is to read a file using a number decimal separator which is different from the system's decimal separator: Suppose you are on a European system in which the decimal separator is a comma, but the csv file to be read originates from a machine which uses a decimal point. And suppose also, that the file contains tab characters as column separator instead of the default comma. In this case, simply set the CSVParams.FormatSettings.DecimalSeparator to '.', and the CSVParams.Delimiter to #9 (TAB character) before reading the file:

``

pascal
uses
      fpstypes, fpspreadsheet, fpscsv;
    var
      MyWorkbook: TsWorkbook;
    begin
      CSVParams.FormatSettings.DecimalSeparator := '.';
      CSVParams.Delimiter := #9;
      MyWorkbook := TsWorkbook.Create;
      try
        MyWorkbook.ReadFromFile('machine-data.csv', sfCSV);
      finally
        MyWorkbook.Free;
      end;
    end;

``

https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png Nota: If you want to give the user the possibility to interactively modify the CSVParams record have a look at the unit scsvparamsform.pas of the spready demo which provides a ready-to-use dialog.

``

Sample projects in the fpspreadsheet installation folder

``

A bunch of sample projects accompanies the FPSpreadsheet installation. They can be found in the folder "examples". Here is a brief description of these sample projects

``

``

Download

``

Subversion

``

You can download FPSpreadsheet using the subversion software and the following command line:

``

pascal
svn checkout svn://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet fpspreadsheet

``

SVN change log

``

``

Incompatible changes

``

``

To do list

``

Note: this list is provisional, maintained by developers and can change without notice. If you are interested in contributing, please feel free to get in touch and submit a patch - thanks!

``

``

Long-term:

``

``

Stable releases

``

You can find all releases of FPSpreadsheet on sourceforge.

``

Version 1.6

``

The latest stable release is version 1.6.2.

``

Change log (with respect to v1.4.x)

``

``

Incompatible changes

``

``

Version 1.4

``

Change log (with respect to v1.2.x)

``

``

Incompatible changes

``

``

Wiki documentation of old releases

``

This wiki page is work in progress and updated whenever a new feature is added; therefore, its state corresponds to the svn trunk version of the package. If you work with an older stable version please use these "historic" wiki versions:

``

``

Instalación

``

``

Compiler options

``

Here is a list of conditional defines which can be activated in order to tweak some operating modes of the packages and/or make it compilable with older Lazarus/FPC versions:

``

``

All these defines are collected in the include file fps.inc.

``

Soporte y reporte de errores

``

El lugar reconmendado para discusión y soporte sobre FPSpreadsheet es consultado en el foro de Lazarus.

Los reportes de error deben enviarse a Seguimiento de errores de Lazarus/Free Pascal; por favor, especificar el proyecto "Lazarus-CCR".

``

Current Progress

``

Progress by supported cell content

Format | Multiple sheets | Unicode | Reader support | Writer support | Text | Number | String Formula | RPN Formula | Date/Time | Comments | Hyperlinks | Images +++ ---|---|---|---|---|---|---|---|---|---|---|---|--- CSV files | No | Yes + | Working ++ | Working ++ | Working ++ | Working ++ | N/A | N/A | Working ++ | N/A | N/A | N/A Excel 2.x | No | No * | Working ** | Working | Working | Working | Working | Working *** | Working **** | Working | N/A | N/A Excel 5.0 (Excel 5.0 and 95) | Yes | No * | Working ** | Working | Working | Working | Working | Working *** | Working **** | Working | N/A | N/A Excel 8.0 (Excel 97- 2003) | Yes | Yes | Working ** | Working | Working | Working | Working | Working *** | Working **** | Reading only | Working | Not working Microsoft OOXML | Yes | Yes | Working ** | Working | Working | Working | Working *** | Working | Working **** | Working | Working | Writing only OpenDocument | Yes | Yes | Working ** | Working | Working | Working | Working *** | Working | Working **** | Working | Working | Working HTML | No | Yes | Working ++ | Working ++ | Working ++ | Working ++ | N/A | N/A | Working ++ | N/A | Working | Not working Wikitable files (Mediawiki) | No | Yes | planned | Working ++ | Working ++ | Working ++ | N/A | N/A | Working ++ | N/A | N/A | Not working

(+) Depends on file.
(++) No "true" number format support because the file does not containg number formatting information. But the number format currently used in the spreadsheet understood.
(+++) Only very basic image support: no transformations, no cropping, no image manipulation.
() In formats which don't support Unicode the data is stored by default as ISO 8859-1 (Latin 1). You can change the encoding in TsWorkbook.Encoding. Note that FPSpreadsheet offers UTF-8 read and write routines, but the data might be converted to ISO when reading or writing to the disk. Be careful that characters which don't fit selected encoding will be lost in those operations. The remarks here are only valid for formats which don't support Unicode.
() Some cell could be returned blank due to missing or non ready implemented number and text formats.
() This is the format in which the formulas are written to file (determined by design of the file format).
(****) Writing of all formats is supported. Some rare custom formats, however, may not be recognized correctly. BIFF2 supports only built-in formats by design.

``

Progreso de las opciones de formateado

``

Quite some formatting options are available:

Formato | Alineación de texto | Rotación de texto | Font | Texto enriquecido | Borde | Soporte de color | Fondo | Word wrap | Tamaño Col&Row | Formato numeros | Unir celdas | Distribución página | Imprimirt rangos | Encabezado/pie página images ---|---|---|---|---|---|---|---|---|---|---|---|---|---|--- CSV files | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A Excel 2.x | Funciona * | N/A | Funciona | N/A | Funciona | Funciona | Funciona** | N/A | Funciona | Funciona | N/A | Funciona | N/A | N/A Excel 5.0 (Excel 5.0 and 95) | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | N/A | Funciona | Funciona | N/A Excel 8.0 (Excel 97 - XP) | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | No funciona Microsoft OOXML (xlsx) | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Writing only OpenDocument | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona*** | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona | Funciona HTML | Funciona | Errores | Funciona | Funciona | Errores | Funciona | Funciona **** | Writing only | Writing only | N/A | Funciona | N/A | N/A | No funciona Wikitable (Mediawiki) | Funciona | N/A | Funciona | No funciona | Funciona | Funciona | | Funciona | Funciona | N/A | Funciona | N/A | N/A | No funciona

(N/A) Característica no disponible intrínsecamente para este formato.
() BIFF2 soporta solamente alineación horizontal de texto, la alineación vertical se ignora.
() BIFF2 no soporta color de fondo; en su lugar se utiliza un fondo punteado en blanco y negro.
() OpenDocument soporta únicamente fondos uniformes; en su lugar se escribe un color de relleno interpolado entre foreground y background.
(****) Únicamente color de fondo uniforme, sin estilos de relleno.

``

Progreso del las opciones del interface de usuario para workbook/worksheet

``

Se han añadido algunas opciones al interface de contenidos de fichero de TsWorksheetGrid:

`` Formato Ocultar líneas de la grid Ocultar cabeceras Inmovilizar paneles sheet/cell activa Modo BiDi
Excel 2.x Funciona Funciona No Funciona N/A N/A
Excel 5.0 (Excel 5.0 and 95) Funciona Funciona Funciona Funciona N/A
Excel 8.0 (Excel 97 - XP) Funciona Funciona Funciona Funciona Funciona
Microsoft OOXML Funciona Funciona Funciona Funciona Funciona
OpenDocument Funciona Funciona Funciona Funciona Funciona
HTML Solo escritura Solo escritura N/A N/A No Funciona
``

Log de cambios

``

Changes in the development version that have not yet been released are already documented on this page.

``

Version 1.6.2 released, basado en svn revision 4619. Corrige el desbordamiento de enteros en las fórmulas (Issue #29999).

Version 1.6 released, based on svn release 4106. Consumo de memoria mejorado, comentarios, hiperenlaces, patrones de relleno de fondo, enumerador de celda, fraction number format, page layout, modified color management, versión fuera de línea de la wiki.

Version 1.4.3 released. Corrige el cálculo de fórmula en una grid del worksheet.

Version 1.4.2 released. Corrige la lectura incompleta de ficheros ods con celdas repetidas (Issue #0027389)

Version 1.4.1 released. A bugfix release para 1.4 que corrige el streaming incorrecto de TsWorksheetGrid y los errores de lectura para lectores biff5 y biff8.

Version 1.4 released (based on subversion revision 3856 with full formula support, lector de xlsx, lectura y escritura de csv, wikitables writer, speed enhancement, virtual mode, db export component, row and column insertion/deletion, ordenación, fusión de celdas, controles visuales en adición a la grid de (mejorado) fpspreadsheet.

Version 1.2.1 released. This is a bugfix release for 1.2 that fixes a bug in spreadsheetGrid (Issue #26521)

Version 1.2 released (based on subversion revision 3241) with fpsgrid improvements, soporte en la celda para fecha/hora para .xls, .ods, vasta mejora en el soporte de formateado de celda, soporte mejorado de fórmulas, tests de la unit.

Version 1.1 released with all improvements up to revision 2621

Released version 1.0 , which includes OO XML generation support with bold formatting supported and also a lot of improvements from the last years

Implemented a cross-platform support for OLE file. Now Excel 5.0 files can be created in any operating system. Adds read support for Excel 2.1

Initial commit to lazarus-ccr con soporte de escritura para Excel 2.1, Excel 5.0 (Windows solamente) y experimental para OOXML y OpenDocument.

``

Licencia

``

LGPL with static linking exception. Es el mismo tipo de licecia que el utilizado por la librería de componentes de Lazarus.

``

Referencias

``

Enlaces Wiki

``

``

Enlaces externos

``

``

``

``

``

`

From the Free Pascal wiki, page "FPSpreadsheet/es", as the Internet Archive kept it on 2025-04-18. Copyright stays with the author who wrote it, according to the old wiki's Licensing page. If you have concerns about this material, message the admin.