Difference between revisions of "FPSpreadsheet"
Sekelsenmat (talk | contribs) |
m (→Using formatting options: typo) |
||
Line 315: | Line 315: | ||
=== Using formatting options === | === Using formatting options === | ||
− | The formatting is set independently from the text, as in the example | + | The formatting is set independently from the text, as in the example below: |
<syntaxhighlight> | <syntaxhighlight> |
Revision as of 13:23, 12 September 2012
│
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
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;
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
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);
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
{
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.
Opening an existing spreadsheet
To open a spreadsheet while specifying a particular format to use use ReadFromFile with two parameters:
MyWorkbook.ReadFromFile(AFileName, sfExcel5);
If ReadFromFile is called with only one parameter then it will use the extension to auto-detect the file format. In case of ambiguous formats like Excel 2-8 it will simply try various possibilities until one works. In the future it might make a more efficient check for the Excel format.
MyWorkbook.ReadFromFile(AFileName);
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.
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
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;
Converting a database to a spreadsheet
Use something like:
program db5xls;
{$mode delphi}{$H+}
uses
Classes, SysUtils,
// add database units
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.WriteUTF8Text(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.WriteUTF8Text(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 between two spreadsheet formats
Note that this is only meta-code, not tested.
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.
Using formatting options
The formatting is set independently from the text, as in the example below:
// Write a bold text
MyWorksheet.WriteUTF8Text(0, 0, 'First');
MyWorksheet.WriteUsedFormatting(0, 0, [uffBold]);
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];
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];
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
);
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];
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];
Grid and Charting Controls with FPSpreadsheet
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
Latest release
One can find the latest release in source forge:
https://sourceforge.net/projects/lazarus-ccr/files/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 | Date/Time |
---|---|---|---|---|---|---|---|---|---|
CSV | No | Yes | Not implemented | Not implemented | Not implemented | Not implemented | Not implemented | Not implemented | Not implemented |
Excel 2.x | No | No** | Working | Working | Working | Working | Not implemented | Working | Not implemented |
Excel 3.0 | No | No** | Not implemented | 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 | Not implemented |
Excel 5.0 (Excel 5.0 and 95) | Yes | No** | Working* | Working | Working | Working | Not implemented | Working | Not implemented |
Excel 8.0 (Excel 97, 2000, XP and 2003) | Yes | Yes | Working* | Working | Working | Working | Not implemented | Working | Read only and dates only |
Microsoft OOXML | Yes | Yes | Not implemented | Working | Working | Working | Not implemented | Not implemented | Not implemented |
OpenDocument | Yes | Yes | Working | Working | Working | Working | Not implemented | 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 | Word-wrap | Col&Row size |
---|---|---|---|---|---|---|
CSV | N/A | N/A | N/A | N/A | N/A | N/A |
Excel 2.x | Not implemented | Not implemented | Not implemented | Not implemented | Not implemented | Not implemented |
Excel 3.0 | Not implemented | Not implemented | Not implemented | Not implemented | Not implemented | Not implemented |
Excel 4.0 | Not implemented | Not implemented | Not implemented | Not implemented | Not implemented | Not implemented |
Excel 5.0 (Excel 5.0 and 95) | - | - | Not implemented | Not implemented | Not implemented | Not implemented |
Excel 8.0 (Excel 97, 2000, XP and 2003) | Working | Working | Working | Working | Not implemented | Not implemented |
Microsoft OOXML | Not implemented | Working | Not implemented | Not implemented | Not implemented | Not implemented |
OpenDocument | Working | Working | Working | Working | Working | Not implemented |
Changelog
Nov 2011
- Released version 1.0, which includes OO XML generation support with bold formating supported and also a lot of improvements from the last years
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.
See also
External Links
- Microsoft OLE Document Format - http://sc.openoffice.org/compdocfileformat.pdf
- Excel file format description - http://sc.openoffice.org/excelfileformat.pdf