(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

The fpSpreadsheet library offers a convenient way to generate and read spreadsheet documents in various formats. The library is written in a very flexible manner, capable of being extended to support any number of formats easily.

## API Documentation

### API Reference

In CHM format here:

### Formulas

Two kinds of formulas are supported by FPSpreadsheet: string formulas and RPN formulas. String formulas are written in strings just like in the office application, for example "ROUND(A1+B1)", while RPN formulas are written in Reverse Polish Notation (RPN), for example: A1, B1, Add, 0, ROUND

### RPN Formulas

Example for using build-in routines

<delphi>

``` // Write the formula E1 = ABS(A1)
SetLength(MyRPNFormula, 2);
MyRPNFormula[0].ElementKind := fekCell;
MyRPNFormula[0].Col := 0;
MyRPNFormula[0].Row := 0;
MyRPNFormula[1].ElementKind := fekABS;
MyWorksheet.WriteRPNFormula(0, 4, MyRPNFormula);
```
``` // Write the formula F1 = ROUND(A1, 0)
SetLength(MyRPNFormula, 3);
MyRPNFormula[0].ElementKind := fekCell;
MyRPNFormula[0].Col := 0;
MyRPNFormula[0].Row := 0;
MyRPNFormula[1].ElementKind := fekNum;
MyRPNFormula[1].DoubleValue := 0.0;
MyRPNFormula[2].ElementKind := fekROUND;
MyWorksheet.WriteRPNFormula(0, 5, MyRPNFormula);
```

</delphi>

### Built-in routines

ABS - Returns the absolute value of a given number. Takes 1 argument. Example: ABS(-1) = 1

ROUND - Rounds a number to a given number of decimal places. Takes 2 arguments, first the number to be rounded and then the number of decimal places to be rounded to. This second argument is zero by default and may be negative.

## 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.

### Excel 5 example

<delphi> { 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, fpspreadsheet, fpsallformats, fpspreadsheet_pkg;
```

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;
```
``` // 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
// or, in RPN: A1, B1, +
SetLength(MyFormula, 3);
MyFormula[0].ElementKind:=fekCell; {A1}
MyFormula[0].Col := 0;
MyFormula[0].Row := 0;
MyFormula[1].ElementKind:=fekCell; {B1}
MyFormula[1].Col := 1;
MyFormula[1].Row := 0;
MyWorksheet.WriteRPNFormula(0, 4, MyFormula);
```
``` // Creates a new worksheet
```
``` // Write some string cells
MyWorksheet.WriteUTF8Text(0, 0, 'First');
MyWorksheet.WriteUTF8Text(0, 1, 'Second');
MyWorksheet.WriteUTF8Text(0, 2, 'Third');
MyWorksheet.WriteUTF8Text(0, 3, 'Fourth');
```
``` // Save the spreadsheet to a file
MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
MyWorkbook.Free;
```

end. </delphi>

### Iterating through all Worksheets

<delphi> 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;
```

</delphi>

### Converting a database to a spreadsheet

Note that this is only meta-code, not tested.

<delphi> program db5xls;

{\$mode delphi}{\$H+}

uses

``` Classes, SysUtils,

```

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';
MyDatabase.Active := True;
```
``` // Create the spreadsheet
MyWorkbook := TsWorkbook.Create;

// Write the field names
for i := 0 to MyDatabase.Fields.Count - 1 do
MyWorksheet.WriteUTF8Text(0, i, MyDatabase.Field[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.WriteUTF8Text(j + 1, i, MyDatabase.Field[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. </delphi>

### Subversion

```svn co https://lazarus-ccr.svn.sourceforge.net/svnroot/lazarus-ccr/components/fpspreadsheet fpspreadsheet
```

## Current Progress

Progress by supported format:

Format Supports multiple sheets? Supports Unicode? Reader Progress Writer Progress Text Number String Formula RPN Formula
Excel 2.x No No** Working Working Working Working Not implemented Working
Excel 3.0 No No** Not implemented Not implemented Not implemented Not implemented Not implemented Not implemented
Excel 4.0 No No** Not implemented Not implemented Not implemented Not implemented Not implemented Not implemented
Excel 5.0 (Excel 5.0 and 95) Yes No** Working* Working Working Working Not implemented Working
Excel 8.0 (Excel 97, 2000, XP and 2003) Yes Yes Working* Working Working Working Not implemented Not implemented
Microsoft OOXML Yes Yes Not implemented Not implemented Not implemented Not implemented Not implemented Not implemented
OpenDocument Yes Yes Working Working Working Working Not implemented Not implemented

(*) Some cell could be returned blank due missing or non ready implemented number and text formats. (**) In formats which don't support unicode the data is stored as ISO 8859-1. Note that FPSpreadsheet offers UTF-8 read and write routines, but the data will be converted to ISO when reading or writing to the disk. Be careful that characters which don't fit ISO Latin 1 will be lost in those operations.

## Changelog

Jan 2009

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