FPSpreadsheet: Examples

MyWorkbook.ReadFromFile(AFileName, sfExcel5);

MyWorkbook.ReadFromFile(AFileName);

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;

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.

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;

var
  MyWorksheet: TsWorksheet;
  col, row: Cardinal;
  cell: PCell;
begin
  for row:=0 to MyWorksheet.GetLastRowIndex do
    for col := 0 to MyWorksheet.GetLastColIndex do
    begin
      cell := MyWorksheet.FindCell(row, col);
      WriteLn(MyWorksheet.ReadAsUTF8Text(cell));
    end;
end;

var
  MyWorksheet: TsWorksheet;
  cell: PCell;
begin
  // Search in all cells
  for cell in Myworksheet.Cells do
    WriteLn(MyWorksheet.ReadAsText(cell));

  // Search in column 0 only
  for cell in MyWorksheet.Cells.GetColEnumerator(0) do
    WriteLn(MyWorksheet.ReadAsText(cell));

  // Search in row 2 only
  for cell in MyWorksheet.Cells.GetRowEnumerator(2) do
    WriteLn(MyWorksheet.ReadAsText(cell));

  // Search in range A1:C2 only (rows 0..1, columns 0..2)
  for cell in MyWorksheet.Cells.GetRangeEnumerator(0, 0, 1, 2) do
    WriteLn(MyWorksheet.ReadAsText(cell));
end;

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

type
  CSVParams: TsCSVParams = record    // W = writing, R = reading, RW = reading/writing
    SheetIndex: Integer;             // W: Index of the sheet to be written
    LineEnding: TsCSVLineEnding;     // W: Specification for line ending to be written
    Delimiter: Char;                 // RW: Column delimiter
    QuoteChar: Char;                 // RW: Character for quoting texts
    Encoding: String;                // RW: Encoding of file
    DetectContentType: Boolean;      // R: try to convert strings to content types
    NumberFormat: String;            // W: if empty write numbers like in sheet, otherwise use this format
    AutoDetectNumberFormat: Boolean; // R: automatically detects decimal/thousand separator used in numbers
    TrueText: String;                // RW: String for boolean TRUE
    FalseText: String;               // RW: String for boolean FALSE
    FormatSettings: TFormatSettings; // RW: add'l parameters for conversion
  end;

uses
  fpstypes, fpspreadsheet, fpscsv;
var
  MyWorkbook: TsWorkbook;
begin
  CSVParams.FormatSettings.DecimalSeparator := '.';
  CSVParams.Delimiter := #9;
  MyWorkbook := TsWorkbook.Create;
  try
    MyWorkbook.ReadFromFile('machine-data.csv', sfCSV);
  finally
    MyWorkbook.Free;
  end;
end;

program db5xls;
 
{$mode delphi}{$H+}
 
uses
  Classes, SysUtils, 
  // add database units
  fpstypes, 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.WriteText(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.WriteText(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.

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.

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;