Difference between revisions of "FPSpreadsheet/ru"
(New page: 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...) |
|||
Line 1: | Line 1: | ||
+ | {{ FPSpreadsheet }} | ||
+ | |||
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. | 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. | ||
Revision as of 21:35, 12 April 2010
│
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
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; 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>
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>
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
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.
- 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