FPSpreadsheet

https://wiki.freepascal.org/FPSpreadsheet
Admin
FPSpreadsheet is a Free Pascal and Lazarus library for reading, writing, and manipulating...
Tokens:71,239
Snippets:333
Trust Score:9.6
Update:1 month ago
Show doc for...
Tokens:
Raw
### CONCATENATE formula implementation example

Source: https://wiki.freepascal.org/FPSpreadsheet

An example showing how to register and implement the CONCATENATE formula, which joins multiple string arguments.

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

--------------------------------

### Create various types of hyperlinks

Source: https://wiki.freepascal.org/FPSpreadsheet

Examples of linking to websites, local files, email addresses, and internal cell references.

```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');
```

--------------------------------

### Sorting worksheet cells example

Source: https://wiki.freepascal.org/FPSpreadsheet

Demonstrates configuring sorting parameters with multiple keys and executing the sort on a worksheet range.

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

--------------------------------

### Number Format String Symbols

Source: https://wiki.freepascal.org/FPSpreadsheet

Examples of format strings using symbols like General and 0 to control numeric output.

```text
'General':
1.2345678 --> '1.2345678'
```

```text
'000': 1 --> '001'
'0.0': 1 --> '1.0'
'0.0': 1.2345678 --> '1.2'
```

--------------------------------

### CreateChart with workbook and worksheet setup

Source: https://wiki.freepascal.org/FPSpreadsheet%3A_Chart_Tutorial

Completes the CreateChart procedure by creating a TsWorkbook, adding a worksheet named 'Sales', and saving to xlsx and ods files. The workbook is freed in a finally block.

```pascal
procedure TForm1.CreateChart;
var
  wbook: TsWorkbook;
  wsheet: TsWorksheet;
begin
  wbook := TsWorkbook.Create;
  try
    wsheet := wbook.AddWorksheet('Sales');  // 'Sales' is the caption of the worksheet tab.

    // more code to be inserted here...

    wbook.SaveToFile('chartdemo.xlsx', true);
    wbook.SaveToFile('chartdemo.ods', true);
  finally
    wbook.Free;
  end;
end;

```

--------------------------------

### TsWorksheet background methods and usage examples

Source: https://wiki.freepascal.org/FPSpreadsheet

Shows the TsWorksheet methods for writing and reading cell background fills, plus two usage examples. WriteBackground assigns a fill pattern with a style and two colors; WriteBackgroundColor sets a uniform color. ReadBackground and ReadBackgroundColor retrieve the current fill. Example 1 sets thin horizontal yellow stripes on blue in cell A1; Example 2 sets a uniform silver background on cell B1 after writing the number 3.14.

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

```

--------------------------------

### Searching for cell content with TsSearchEngine

Source: https://wiki.freepascal.org/FPSpreadsheet

Example of using the fpsSearch unit to find text within a workbook and iterate through results.

```pascal
uses
  fpsTypes, fpSpreadsheet, fpsUtils, fpsSearch, fpsAllFormats;
var
  MyWorkbook: TsWorkbook;
  foundWorksheet: TsWorksheet;
  foundRow, foundCol: Cardinal;
  MySearchParams: TsSearchParams;
begin
  MyWorkbook := TsWorkbook.Create;
  try
    MyWorkbook.ReadFromFile(AFileName);

    // Specify search criteria
    MySearchParams.SearchText := 'Hallo';
    MySearchParams.Options := [soEntireDocument];
    MySearchParams.Within := swWorkbook;

    // or: MySearchParaams := InitSearchParams('Hallo', [soEntireDocument], swWorkbook);

    // Create search engine and execute search
    with TsSearchEngine.Create(MyWorkbook) do begin
      if FindFirst(MySearchParams, foundWorksheet, foundRow, foundCol) then begin
        WriteLn('First "', MySearchparams.SearchText, '" found in cell ', GetCellString(foundRow, foundCol), ' of worksheet ', foundWorksheet.Name);
        while FindNext(MySeachParams, foundWorksheet, foundRow, foundCol) do
          WriteLn('Next "', MySearchParams.SearchText, '" found in cell ', GetCellString(foundRow, foundCol), ' of worksheet ', foundWorksheet.Name);
      end;
      Free;
    end;
  finally
    MyWorkbook.Free;
  end;
end;
```

--------------------------------

### Initialize TsWorkbook instance

Source: https://wiki.freepascal.org/FPSpreadsheet

Create an instance of the TsWorkbook class to begin working with spreadsheet data.

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

--------------------------------

### Export Dataset to Spreadsheet with TFPSExport

Source: https://wiki.freepascal.org/FPSpreadsheet

Demonstrates how to configure and execute a dataset export to an XLS file using TFPSExport and TFPSExportFormatSettings.

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

--------------------------------

### Modify Font Size Example

Source: https://wiki.freepascal.org/FPSpreadsheet

Iterates through the workbook font list to update 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;
```

--------------------------------

### Excel 5.x file creation

Source: https://wiki.freepascal.org/FPSpreadsheet%3A_Examples

Demonstrates writing an Excel 5.x file using the fpspreadsheet library. Requires specific Lazarus components from the lazutils directory when using the trunk version.

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

--------------------------------

### Retrieve and display formulas

Source: https://wiki.freepascal.org/FPSpreadsheet

Use ReadFormula to get the internal formula format and ReadFormulaAsString with the ALocalized parameter to retrieve the formula using workbook-specific separators.

```pascal
var
  MyWorksheet: TsWorksheet;
  cell: PCell;
//...
  cell := MyWorksheet.FindCell(0, 1);
  WriteLn('The formula in internal format is ', MyworkSheet.ReadFormula(cell));
  WriteLn('The localized formula is ', MyWorksheet.ReadFormulaAsString(cell, true));
//-------------------------------------------------------------------
// For the previous example, this will result in the following output
The formula in internal format is ROUND(A1+B2+1.215,0)
The localized formula is ROUND(A1+B2+1,215;0)
```

--------------------------------

### Open spreadsheet with explicit format using ReadFromFile

Source: https://wiki.freepascal.org/FPSpreadsheet%3A_Examples

Use ReadFromFile with two parameters to specify the file format (e.g., sfExcel5). This ensures the correct format is used without relying on extension detection.

```pascal
MyWorkbook.ReadFromFile(AFileName, sfExcel5);

```

--------------------------------

### CreateChart event handler and skeleton

Source: https://wiki.freepascal.org/FPSpreadsheet%3A_Chart_Tutorial

Initial skeleton for the chart creation workflow. Button1Click calls CreateChart, which is initially empty and will be completed in subsequent steps.

```pascal
procedure TForm1.Button1Click(Sender: TObject);
begin
  CreateChart;
end;

procedure TForm1.CreateChart;
begin
  // to be completed...
end;

```

--------------------------------

### Load encrypted workbook with password (TsWorkbook.OnQueryPassword / ReadFromFile)

Source: https://wiki.freepascal.org/FPSpreadsheet

Shows two ways to load an encrypted spreadsheet: either by handling the OnQueryPassword event to prompt for a password, or by passing the password directly to ReadFromFile. The first method uses a handler that returns the password from an input box; the second passes the password as a parameter. Both create a TsWorkbook, read the file, and free the workbook in a finally block.

```pascal
function TForm1.QueryPasswordHandler: String;
begin
  Result := InputBox('Password required', 'Password', '');
end;

procedure TForm1.LoadWorkbook(AFileName: String);
var 
  workbook: TsWorkbook;
begin
  workbook := TsWorkbook.Create;
  try
    workbook.OnQueryPassword := @QueryPasswordHandler;
    workbook.ReadFromFile(AFileName);
    ...
  finally
    workbook.Free;
  end;
end;

// or

procedure TForm1.LoadWorkbookWithPassword(AFileName, APassword: STring);
var
  workbook: TsWorkbook;
begin
  workbook := TsWorkbook.Create;
  try
    workbook.ReadFromFile(AFileName, APassword);
    ...

```

--------------------------------

### Convert large database table to spreadsheet using virtual mode

Source: https://wiki.freepascal.org/FPSpreadsheet%3A_Examples

Sets up a TsWorkbook, TsWorksheet, and TSdfDataset, then enables virtual mode and writes to an Excel8 file. The header row uses a template cell with bold font and gray background. Requires implementing TDataProvider and its WriteCellData method.

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

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

--------------------------------

### Write a number to a cell

Source: https://wiki.freepascal.org/FPSpreadsheet

Shows how to create a cell at address A1 (row 0, column 0) and assign the value 1.0.

```pascal
var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
begin
  MyWorkbook := TsWorkbook.Create;
  MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
  MyWorksheet.WriteNumber(0, 0, 1.0);  // "A1" has row=0 and col=0
  ...
```

--------------------------------

### Enable Word Wrap

Source: https://wiki.freepascal.org/FPSpreadsheet

Use WriteWordwrap to allow text that exceeds cell width to wrap into multiple lines.

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

--------------------------------

### Set header and footer texts with HEADER_FOOTER_INDEX constants

Source: https://wiki.freepascal.org/FPSpreadsheet

Sets the header and footer texts for all pages, odd pages, and even pages using the HEADER_FOOTER_INDEX_* constants. The header uses centered date and time, while footers show page numbers right-aligned on odd pages and left-aligned on even pages. Leave index 0 and 2 empty for a uniform header/footer.

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

--------------------------------

### InitSortParams utility function

Source: https://wiki.freepascal.org/FPSpreadsheet

A helper function to initialize the sorting parameters record.

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

--------------------------------

### Load spreadsheet file with TFileOpen OnAccept handler

Source: https://wiki.freepascal.org/FPSpreadsheet_tutorial%3A_Writing_a_mini_spreadsheet_application

Handles the OnAccept event of a TFileOpen standard action to load a spreadsheet file into a TsWorkbookSource. It disables auto-detection, then selects the file format based on the dialog's FilterIndex (1-8) and assigns the chosen filename to load the file.

```pascal
{ Loads the spreadsheet file selected by the FileOpen standard action }
procedure TForm1.FileOpen1Accept(Sender: TObject);
begin
  sWorkbookSource1.AutodetectFormat := false;
  case FileOpen1.Dialog.FilterIndex of
    1: sWorkbookSource1.AutoDetectFormat := true;         // All spreadsheet files
    2: sWorkbookSource1.AutoDetectFormat := true;         // All Excel files
    3: sWorkbookSource1.FileFormat := sfOOXML;            // Excel 2007+
    4: sWorkbookSource1.FileFormat := sfExcel8;           // Excel 97-2003
    5: sWorkbookSource1.FileFormat := sfExcel5;           // Excel 5.0
    6: sWorkbookSource1.FileFormat := sfExcel2;           // Excel 2.1
    7: sWorkbookSource1.FileFormat := sfOpenDocument;     // Open/LibreOffice
    8: sWorkbookSource1.FileFormat := sfCSV;              // Text files
  end;
  sWorkbookSource1.FileName :=FileOpen1.Dialog.FileName;  // This loads the file
end;
```

--------------------------------

### Define TWorksheet.WriteHyperlink syntax

Source: https://wiki.freepascal.org/FPSpreadsheet

The general procedure signature for creating hyperlinks in a worksheet.

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

--------------------------------

### Add a new worksheet to a workbook

Source: https://wiki.freepascal.org/FPSpreadsheet

Demonstrates how to instantiate a TsWorkbook and add a new worksheet named 'My_Table'.

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

--------------------------------

### Convert between two spreadsheet formats (ODS to XLS)

Source: https://wiki.freepascal.org/FPSpreadsheet%3A_Examples

A complete Pascal program that reads an OpenDocument spreadsheet (test.ods) and writes it as an Excel 8 file (test.xls). Uses fpspreadsheet_pkg and fpsallformats. Demonstrates basic conversion workflow.

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

```

--------------------------------

### Configure Chart Title

Source: https://wiki.freepascal.org/FPSpreadsheet%3A_Chart_Tutorial

Sets the chart title text and applies bold formatting with a specific font size.

```pascal
  wChart.Title.Caption := 'Sales Report';
  wChart.Title.Font.Size := 16;
  wChart.Title.Font.Style := [fssBold];
```
Tokens:
Raw