|
|
| Line 1: |
Line 1: |
| − | {{FPSpreadsheet}}
| + | Because of it's very simple format, it's easy to generate Excel 2.1 files from code. New Excel versions are still capable of reading those files, so this can be enough to automate the generation of simple Excel files. Unfortunately Excel 4 and older formats don't support multiple sheets in the same file, so this isn't supported by this method. |
| | | | |
| − | 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.
| + | Here is a unit which can write Excel 2.1 files: |
| | | | |
| − | == API Documentation ==
| + | <pre> |
| − | | + | { |
| − | === API Reference ===
| + | excel2utils.pas |
| − | | |
| − | In CHM format here:
| |
| | | | |
| − | http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/fpspreadsheet.chm
| + | Writes an Excel 2.1 file |
| | | | |
| − | === Formulas ===
| + | To write the file, always start calling StartExcel2File to create the Excel |
| | + | file header, then call WriteExcel2Cell once for each cell value and finally |
| | + | call EndExcel2File to write the end of the file. |
| | | | |
| − | 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
| + | License: The same modifyed LGPL as the Lazarus Component Library |
| − | | |
| − | === 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.
| |
| − | | |
| − | === 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:
| |
| − | | |
| − | * [[TsWorksheetGrid]]
| |
| − | * [[TsWorksheetChartSource]]
| |
| − | | |
| − | == 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 | | AUTHORS: Felipe Monteiro de Carvalho |
| | } | | } |
| − | program excel5demo;
| + | unit excel2utils; |
| | | | |
| | + | {$ifdef fpc} |
| | {$mode delphi}{$H+} | | {$mode delphi}{$H+} |
| | + | {$endif} |
| | + | |
| | + | interface |
| | | | |
| | uses | | uses |
| − | Classes, SysUtils, fpspreadsheet, fpsallformats, fpspreadsheet_pkg; | + | Classes, SysUtils; |
| | | | |
| − | const OUTPUT_FORMAT = sfExcel5; | + | const |
| | + | STR_EXCEL_EXTENSION = '.xls'; |
| | | | |
| | + | procedure WriteExcel2Cell(AStream: TStream; const ACol, ARow: Word; const AValue: string); |
| | + | procedure StartExcel2File(AStream: TStream); |
| | + | procedure EndExcel2File(AStream: TStream); |
| | + | |
| | + | implementation |
| | + | |
| | + | procedure WriteExcel2Cell(AStream: TStream; const ACol, ARow: Word; |
| | + | const AValue: string); |
| | var | | var |
| − | MyWorkbook: TsWorkbook; | + | L: Word; |
| − | MyWorksheet: TsWorksheet; | + | CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0); |
| − | MyFormula: TsRPNFormula;
| |
| − | MyDir: string;
| |
| | begin | | begin |
| − | // Initialization | + | L := Length(AValue); |
| − | MyDir := ExtractFilePath(ParamStr(0));
| + | CXlsLabel[1] := 8 + L; |
| − | | + | CXlsLabel[2] := ARow; |
| − | // Create the spreadsheet
| + | CXlsLabel[3] := ACol; |
| − | MyWorkbook := TsWorkbook.Create;
| + | CXlsLabel[5] := L; |
| − | MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
| + | AStream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel)); |
| − | | + | AStream.WriteBuffer(Pointer(AValue)^, L); |
| − | // Write some number cells
| + | end; |
| − | 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>
| |
| | | | |
| − | | + | procedure StartExcel2File(AStream: TStream); |
| − | === Iterating through all Worksheets ===
| |
| − | | |
| − | <delphi>
| |
| | var | | var |
| − | MyWorkbook: TsWorkbook; | + | CXlsBof: array[0..5] of Word = ($809, 8, 0, $10, 0, 0); |
| − | MyWorksheet: TsWorksheet;
| |
| − | i: Integer;
| |
| | begin | | begin |
| − | // Here load MyWorkbook from a file or build it | + | AStream.WriteBuffer(CXlsBof, SizeOf(CXlsBof)); |
| − |
| + | end; |
| − | 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>
| + | procedure EndExcel2File(AStream: TStream); |
| − | program db5xls;
| |
| − |
| |
| − | {$mode delphi}{$H+}
| |
| − |
| |
| − | uses
| |
| − | Classes, SysUtils,
| |
| − | // add database units
| |
| − | fpspreadsheet, fpsallformats, fpspreadsheet_pkg;
| |
| − |
| |
| − | const OUTPUT_FORMAT = sfExcel5;
| |
| − |
| |
| | var | | var |
| − | MyWorkbook: TsWorkbook; | + | CXlsEof: array[0..1] of Word = ($0A, 00); |
| − | MyWorksheet: TsWorksheet;
| |
| − | MyDatabase: TSdfDataset;
| |
| − | MyDir: string;
| |
| − | i, j: Integer;
| |
| | begin | | begin |
| − | // Initialization | + | AStream.WriteBuffer(CXlsEof, SizeOf(CXlsEof)); |
| − | MyDir := ExtractFilePath(ParamStr(0));
| + | end; |
| − |
| |
| − | // Open the database
| |
| − | MyDatabase := TSdfDataset.Create;
| |
| − | MyDatabase.Filename := 'test.dat';
| |
| − | // Add table description here
| |
| − | MyDatabase.Active := True;
| |
| | | | |
| − | // Create the spreadsheet
| + | end. |
| − | MyWorkbook := TsWorkbook.Create;
| + | </pre> |
| − | 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
| + | And here is an example of how to use it: |
| − | 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;
| + | <pre> |
| − | Inc(j);
| + | { |
| − | end;
| + | excel2demo.dpr |
| | | | |
| − | // Close the database
| + | Demonstrates how to write an Excel 2.1 file using the excel2utils unit |
| − | MyDatabase.Active := False;
| |
| − | MyDatabase.Free;
| |
| | | | |
| − | // Save the spreadsheet to a file
| + | Public Domain |
| − | MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
| |
| − | MyWorkbook.Free;
| |
| − | end.
| |
| − | </delphi>
| |
| | | | |
| − | ===Converting between two spreadsheet formats===
| + | AUTHORS: Felipe Monteiro de Carvalho |
| | + | } |
| | + | program excel2demo; |
| | | | |
| − | Note that this is only meta-code, not tested.
| + | {$mode delphi}{$H+} |
| | | | |
| − | <delphi>
| |
| − | program ods2xls;
| |
| − |
| |
| − | {$mode delphi}{$H+}
| |
| − |
| |
| | uses | | uses |
| − | Classes, SysUtils, | + | Classes, SysUtils; |
| − | fpspreadsheet, fpsallformats, fpspreadsheet_pkg;
| + | |
| − |
| |
| − | const
| |
| − | INPUT_FORMAT = sfOpenDocument;
| |
| − | OUTPUT_FORMAT = sfExcel8;
| |
| − |
| |
| | var | | var |
| − | MyWorkbook: TsWorkbook; | + | OutputFile: TFileStream; |
| | MyDir: string; | | MyDir: string; |
| | begin | | begin |
| − | // Initialization | + | // Open the output file |
| | MyDir := ExtractFilePath(ParamStr(0)); | | MyDir := ExtractFilePath(ParamStr(0)); |
| − |
| + | OutputFile := TFileStream.Create(MyDir + 'test' + STR_EXCEL_EXTENSION, fmCreate or fmOpenWrite); |
| − | // Convert the spreadsheet
| + | StartExcel2File(OutputFile); |
| − | 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 ===
| + | // Write some cells |
| | + | WriteExcel2Cell(OutputFile, 0, 0, '1'); |
| | + | WriteExcel2Cell(OutputFile, 1, 0, '2'); |
| | + | WriteExcel2Cell(OutputFile, 2, 0, '3'); |
| | + | WriteExcel2Cell(OutputFile, 3, 0, '4'); |
| | | | |
| − | The formatting is set independently from the text, as in the example bellow:
| + | // Close the files |
| − | | + | EndExcel2File(OutputFile); |
| − | <delphi>
| + | OutputFile.Free; |
| − | // Write a bold text | + | end. |
| − | MyWorksheet.WriteUTF8Text(0, 0, 'First'); | |
| − | MyWorksheet.WriteUsedFormatting(0, 0, [uffBold]); | |
| − | </delphi>
| |
| − | | |
| − | == Download ==
| |
| − | | |
| − | === Subversion ===
| |
| − | | |
| − | You can download FPSpreadsheet using the subversion software and the following command line:
| |
| − | | |
| − | <pre>
| |
| − | svn co https://lazarus-ccr.svn.sourceforge.net/svnroot/lazarus-ccr/components/fpspreadsheet fpspreadsheet
| |
| | </pre> | | </pre> |
| − |
| |
| − | == Current Progress ==
| |
| − |
| |
| − | Progress by supported format:
| |
| − |
| |
| − | {| BORDER="1" CELLSPACING="0"
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Format'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Supports multiple sheets?'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Supports Unicode?'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Reader Progress'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Writer Progress'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Text'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Number'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''String Formula'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''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.<br>
| |
| − | (**) 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:
| |
| − |
| |
| − | {| BORDER="1" CELLSPACING="0"
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Format'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Text rotation'''
| |
| − | !COLSPAN="1" STYLE="background:#ffdead;"|'''Bold'''
| |
| − | |----
| |
| − | |Excel 2.x||Not implemented||Not implemented
| |
| − | |----
| |
| − | |Excel 3.0||Not implemented||Not implemented
| |
| − | |----
| |
| − | |Excel 4.0||Not implemented||Not implemented
| |
| − | |----
| |
| − | |Excel 5.0 (Excel 5.0 and 95)||-||-
| |
| − | |----
| |
| − | |Excel 8.0 (Excel 97, 2000, XP and 2003)||Working||Working
| |
| − | |----
| |
| − | |Microsoft OOXML||Not implemented||Not implemented
| |
| − | |----
| |
| − | |OpenDocument||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.
| |
| − |
| |
| − | == See also ==
| |
| − |
| |
| − | * [[Office Automation]]
| |
| − |
| |
| − | == External Links ==
| |
| − |
| |
| − | * Microsoft OLE Document Format - http://sc.openoffice.org/compdocfileformat.pdf
| |
| − | * Excel file format description - http://sc.openoffice.org/excelfileformat.pdf
| |
| − |
| |
| − | [[Category:Components]]
| |
Because of it's very simple format, it's easy to generate Excel 2.1 files from code. New Excel versions are still capable of reading those files, so this can be enough to automate the generation of simple Excel files. Unfortunately Excel 4 and older formats don't support multiple sheets in the same file, so this isn't supported by this method.
Here is a unit which can write Excel 2.1 files:
{
excel2utils.pas
Writes an Excel 2.1 file
To write the file, always start calling StartExcel2File to create the Excel
file header, then call WriteExcel2Cell once for each cell value and finally
call EndExcel2File to write the end of the file.
License: The same modifyed LGPL as the Lazarus Component Library
AUTHORS: Felipe Monteiro de Carvalho
}
unit excel2utils;
{$ifdef fpc}
{$mode delphi}{$H+}
{$endif}
interface
uses
Classes, SysUtils;
const
STR_EXCEL_EXTENSION = '.xls';
procedure WriteExcel2Cell(AStream: TStream; const ACol, ARow: Word; const AValue: string);
procedure StartExcel2File(AStream: TStream);
procedure EndExcel2File(AStream: TStream);
implementation
procedure WriteExcel2Cell(AStream: TStream; const ACol, ARow: Word;
const AValue: string);
var
L: Word;
CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
begin
L := Length(AValue);
CXlsLabel[1] := 8 + L;
CXlsLabel[2] := ARow;
CXlsLabel[3] := ACol;
CXlsLabel[5] := L;
AStream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel));
AStream.WriteBuffer(Pointer(AValue)^, L);
end;
procedure StartExcel2File(AStream: TStream);
var
CXlsBof: array[0..5] of Word = ($809, 8, 0, $10, 0, 0);
begin
AStream.WriteBuffer(CXlsBof, SizeOf(CXlsBof));
end;
procedure EndExcel2File(AStream: TStream);
var
CXlsEof: array[0..1] of Word = ($0A, 00);
begin
AStream.WriteBuffer(CXlsEof, SizeOf(CXlsEof));
end;
end.
And here is an example of how to use it:
{
excel2demo.dpr
Demonstrates how to write an Excel 2.1 file using the excel2utils unit
Public Domain
AUTHORS: Felipe Monteiro de Carvalho
}
program excel2demo;
{$mode delphi}{$H+}
uses
Classes, SysUtils;
var
OutputFile: TFileStream;
MyDir: string;
begin
// Open the output file
MyDir := ExtractFilePath(ParamStr(0));
OutputFile := TFileStream.Create(MyDir + 'test' + STR_EXCEL_EXTENSION, fmCreate or fmOpenWrite);
StartExcel2File(OutputFile);
// Write some cells
WriteExcel2Cell(OutputFile, 0, 0, '1');
WriteExcel2Cell(OutputFile, 1, 0, '2');
WriteExcel2Cell(OutputFile, 2, 0, '3');
WriteExcel2Cell(OutputFile, 3, 0, '4');
// Close the files
EndExcel2File(OutputFile);
OutputFile.Free;
end.
