Difference between revisions of "FPSpreadsheet"

Revision as of 11:17, 23 August 2011

│ Deutsch (de) │ English (en) │ español (es) │ français (fr) │ polski (pl) │ русский (ru) │

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:

http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/fpspreadsheet.chm

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

Using simple constant numbers

<delphi> var

 MyRPNFormula: TsRPNFormula;

begin

 // Write the formula =4+5
 MyWorksheet.WriteUTF8Text(3, 0, '=4+5'); // A4
 //
 SetLength(MyRPNFormula, 3);
 MyRPNFormula[0].ElementKind := fekNum;
 MyRPNFormula[0].DoubleValue := 4.0;
 MyRPNFormula[1].ElementKind := fekNum;
 MyRPNFormula[1].DoubleValue := 5.0;
 MyRPNFormula[2].ElementKind := fekAdd;
 MyWorksheet.WriteRPNFormula(3, 2, MyRPNFormula);

end; </delphi>

Using ranges of cells and the unary SUM operator

 // =Sum(E2:e5)
 MyWorksheet.WriteUTF8Text(1, 0, '=Sum(E2:e5)'); // A2
 //
 SetLength(MyRPNFormula, 2);
 MyRPNFormula[0].ElementKind := fekCellRange;
 MyRPNFormula[0].Row := 1;
 MyRPNFormula[0].Row2 := 4;
 MyRPNFormula[0].Col := 4;
 MyRPNFormula[0].Col2 := 4;
 MyRPNFormula[1].ElementKind := fekOpSUM;
 MyWorksheet.WriteRPNFormula(1, 2, MyRPNFormula);   // C2

</delphi>

Using built-in routines

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

Packages

FPSpreadsheet comes with the following packages to facilitate it's usage with:

laz_fpspreadsheet.lpk

Adds all necessary files to use fpspreadsheet in your Lazarus projects. After adding this package as a requirement to your project you can add "fpspreadsheet" to the uses clause of the units which use it.

laz_fpspreadsheet_visual.lpk

Adds a set of visual components which expands the capabilities of FPSpreadsheet to also include editing and displaying data on screen inside LCL applications.

The following LCL components are installed with this package:

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;
 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
 // 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;
 MyFormula[2].ElementKind:=fekAdd;;  {+}
 MyWorksheet.WriteRPNFormula(0, 4, MyFormula);

 // Creates a new worksheet
 MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet 2');

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

Opening an existing spreadsheet

This is some code I wrote (with some suggestions and enhancements from Felipe Monteiro de Carvalho) to patch the fpspreadsheet.pas unit. The first string constant should go in the Interface section while the rest is implementation.

It modifies the existing overloaded ReadFromFile procedure to try to detect the file type based on the file name extension. Unlike the current procedure, this one falls back to earlier format types if the latest type using a particular extension doesn't work on a file (currently only useful for Excel binary formats).

Note that reading the sfExcel5 format may require using Felipe's patched xlsBiff5.pas unit.

<delphi> const

 STR_COMMA_SEPARATED_EXTENSION = '.csv';

 SPREADSHEET_EXTS: array [ TsSpreadsheetFormat ] of String
   = ( STR_EXCEL_EXTENSION,
       STR_EXCEL_EXTENSION,
       STR_EXCEL_EXTENSION,
       STR_EXCEL_EXTENSION,
       STR_EXCEL_EXTENSION,
       STR_OOXML_EXCEL_EXTENSION,
       STR_OPENDOCUMENT_CALC_EXTENSION,
       STR_COMMA_SEPARATED_EXTENSION );

function getFormatFromFileName(const AFileName: TFileName; var SheetType: TsSpreadsheetFormat): Boolean; var suffix: String;

   t: TsSpreadsheetFormat;

begin

   suffix := ExtractFileExt(AFileName);
   t := High(TsSpreadsheetFormat);
   while (SPREADSHEET_EXTS[t] <> suffix) and (t > Low(TsSpreadsheetFormat)) do
      begin
         Dec(t);
      end;
   if SPREADSHEET_EXTS[t] = suffix then
      begin
         SheetType := t;
         Result := True;
      end
   else
      Result := False;

end;

procedure TsWorkbook.ReadFromFile(AFileName: string); overload; var

 SheetType: TsSpreadsheetFormat;
 lException: Exception = nil;
 suffix: String;

begin

 if getFormatFromFileName(AFileName, SheetType) then
 begin
   try
      ReadFromFile(AFileName, SheetType);
   except
      on E: Exception do
      begin 
         lException := E;
         suffix := ExtractFileExt(AFileName);
         while (SheetType > Low(TsSpreadsheetFormat)) and (lException <> nil) do
            begin
               Dec(SheetType);
               if (SPREADSHEET_EXTS[SheetType] <> suffix) then
                  Break
               else begin
                  try
                     ReadFromFile(AFileName, SheetType);
                     lException := nil;
                  except
                      on E: Exception do
                         { nothing };
                  end;
               end;
            end;
         if lException <> nil then raise lException;
      end;
   end;
 end;

end;

</delphi>

I've submitted this code to the Lazarus CCR so in the future you may be able to use the fpspreadsheet package directly. The actual call to open a spreadsheet is:

<delphi> MyWorkbook.ReadFromFile(AFileName); </delphi>

Writing a spreadsheet to file based on extension

Similar to the ReadFromFile routine, I've implemented a WriteToFile procedure (also with suggestions and enhancements from Felipe Monteiro de Carvalho) 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.

<delphi> 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; </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, 
 // add database units
 fpspreadsheet, fpsallformats, fpspreadsheet_pkg;

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

Converting between two spreadsheet formats

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

<delphi> program ods2xls;

{$mode delphi}{$H+}

uses

 Classes, SysUtils, 
 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. </delphi>

Using formatting options

The formatting is set independently from the text, as in the example bellow:

 // Write a bold text
 MyWorksheet.WriteUTF8Text(0, 0, 'First');
 MyWorksheet.WriteUsedFormatting(0, 0, [uffBold]);

</delphi>

Setting the text orientation

There are three possible text rotations: trHorizontal, rt90DegreeClockwiseRotation and rt90DegreeCounterClockwiseRotation

They are relative to the standard text position.

 // Write a bold text
 MyWorksheet.WriteUTF8Text(0, 0, 'First');
 MyCell := MyWorksheet.GetCell(0, 0);
 MyCell^.TextRotation := rt90DegreeClockwiseRotation;
 MyCell^.UsedFormattingFields := [uffTextRotation];

</delphi>

Setting a Border

 // Write a text with borders and background color
 MyWorksheet.WriteUTF8Text(5, 9, '[N,W,E,S]');// J6
 MyCell := MyWorksheet.GetCell(5, 9);
 MyCell^.Border := [cbNorth, cbWest, cbEast, cbSouth];
 MyCell^.UsedFormattingFields := [uffBorder, uffBold, uffBackgroundColor];

</delphi>

Setting a Background Color

One can set one of 16 colors from a pallete to set to the background. The possible values are:

 {@@ Colors in FPSpreadsheet as given by a pallete to be compatible with Excel }

 TsColor = (
   scBlack,    // 000000H
   scWhite,    // FFFFFFH
   scRed,      // FF0000H
   scGREEN,    // 00FF00H
   scBLUE,     // 0000FFH
   scYELLOW,   // FFFF00H
   scMAGENTA,  // FF00FFH
   scCYAN,     // 00FFFFH
   scDarkRed,  // 800000H
   scDarkGreen,// 008000H
   scDarkBlue, // 000080H
   scOLIVE,    // 808000H
   scPURPLE,   // 800080H
   scTEAL,     // 008080H
   scSilver,   // C0C0C0H
   scGrey      // 808080H
   //
   scGrey10pct,// E6E6E6H
   scGrey20pct // CCCCCCH
 );

</delphi>

And an example:

 // Write a text with background color
 MyWorksheet.WriteUTF8Text(0, 0, 'Test');
 MyCell := MyWorksheet.GetCell(0, 0);
 MyCell^.BackgroundColor := scSilver;
 MyCell^.UsedFormattingFields := [uffBackgroundColor];

</delphi>

Combining Multiple Formattings

 // Write a text with borders and background color
 MyWorksheet.WriteUTF8Text(5, 9, '[N,W,E,S]');
 MyCell := MyWorksheet.GetCell(5, 9);
 MyCell^.Border := [cbNorth, cbWest, cbEast, cbSouth];
 MyCell^.UsedFormattingFields := [uffBorder, uffBold, uffBackgroundColor];

</delphi>

Grid and Charting Controls with FPSpreadsheet

See TsWorksheetChartSource

Download

Subversion

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

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

Support and Bug Reporting

The recommended place to discuss FPSpreadsheet and obtain support is asking in the Lazarus Forum: http://www.lazarus.freepascal.org/index.php/board,42.0.html

Bug reports should be sent to the Free Pascal Bug Tracker in the "Lazarus-CCR" section: http://bugs.freepascal.org/

Current Progress

Progress by supported format:

Format	Supports multiple sheets?	Supports Unicode?	Reader Progress	Writer Progress	Text	Number	String Formula	RPN Formula
CSV	No	Yes	Not implemented	Not implemented	Not implemented	Not implemented	Not implemented	Not implemented
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	Working
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 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.

Progress of the formatting options

Some formatting options were added, but they aren't yet implemented for all formats:

Format	Text rotation	Bold	Border	Background Color
CSV	N/A	N/A	N/A	N/A
Excel 2.x	Not implemented	Not implemented	Not implemented	Not implemented
Excel 3.0	Not implemented	Not implemented	Not implemented	Not implemented
Excel 4.0	Not implemented	Not implemented	Not implemented	Not implemented
Excel 5.0 (Excel 5.0 and 95)	-	-	Not implemented	Not implemented
Excel 8.0 (Excel 97, 2000, XP and 2003)	Working	Working	Working	Working
Microsoft OOXML	Not implemented	Not implemented	Not implemented	Not implemented
OpenDocument	Working	Working	Working	Working

Changelog

Jan 2009

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

Feb 2008

Initial commit to lazarus-ccr with write support for Excel 2.1, Excel 5.0 (Windows only) and experimental OOXML and OpenDocument

License

LGPL with static linking exception. This is the same license as is used in the Lazarus Component Library.

External Links

Microsoft OLE Document Format - http://sc.openoffice.org/compdocfileformat.pdf
Excel file format description - http://sc.openoffice.org/excelfileformat.pdf

@@ Line 255: / Line 255: @@
 === Writing a spreadsheet to file based on extension ===
-Similar to the ReadFromFile routine, I've implemented a WriteToFile procedure (also with suggestions and enhancements from Felipe Monteiro de Carvalho) 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, so if you want to write them in an earlier format, you have to use the base routine.
+Similar to the ReadFromFile routine, I've implemented a WriteToFile procedure (also with suggestions and enhancements from Felipe Monteiro de Carvalho) 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.

Difference between revisions of "FPSpreadsheet"

Revision as of 11:17, 23 August 2011

API Documentation

API Reference

Formulas

RPN Formulas

Using simple constant numbers

Using ranges of cells and the unary SUM operator

Using built-in routines

Built-in routines

Packages

laz_fpspreadsheet.lpk

laz_fpspreadsheet_visual.lpk

Examples

Excel 5 example

Opening an existing spreadsheet

Writing a spreadsheet to file based on extension

Iterating through all Worksheets

Converting a database to a spreadsheet

Converting between two spreadsheet formats

Using formatting options

Setting the text orientation

Setting a Border

Setting a Background Color

Combining Multiple Formattings

Grid and Charting Controls with FPSpreadsheet

Download

Subversion

Support and Bug Reporting

Current Progress

Progress of the formatting options

Changelog

License

See also

External Links

Navigation menu

Search