unit Unit1;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls, Grids,
fpspreadsheetctrls, fpspreadsheetgrid, fpsTypes, fpsallformats;
type
{ TForm1 }
TForm1 = class(TForm)
Button2: TButton;
sWorkbookSource1: TsWorkbookSource;
sWorksheetGrid1: TsWorksheetGrid;
procedure Button2Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
public
end;
var
Form1: TForm1;
implementation
{$R *.lfm}
uses
fpspreadsheet;
{ TForm1 }
procedure TForm1.Button2Click(Sender: TObject);
var
destBook: TsWorkbook;
destSheet: TsWorksheet;
srcSheet: TsWorksheet;
colMaster: Cardinal;
colDetail: Cardinal;
lastSrcRow, lastDestRow, lastDestCol: Cardinal;
r, c, rs: Cardinal;
i: Integer;
L: TStringList;
counter: Integer;
masterKey, detailKey: String;
begin
srcSheet := sWorkbookSource1.Worksheet;
colMaster := 0; // Index of column "PLOT"
colDetail := 3; // Index of column "SPECIES"
lastSrcRow := srcSheet.GetLastOccupiedRowIndex;
destBook := TsWorkbook.Create;
try
destSheet := destBook.AddWorksheet('Results');
L := TStringList.Create;
try
L.Sorted := true;
L.Duplicates := dupIgnore;
// Get list of unique items in colMaster column
for r := 1 to lastSrcRow do
L.Add(srcSheet.ReadAsText(r, colMaster));
// Write list to first column of destination table
for i := 0 to L.Count-1 do
destSheet.WriteText(i+1, 0, L[i]);
// Get list unique items in colDetails column
L.Clear;
for r := 1 to lastSrcRow do
L.Add(srcSheet.ReadAsText(r, colDetail));
// Write list to first row of destination table
for i := 0 to L.Count-1 do
destSheet.WriteText(0, i+1, L[i]);
finally
L.free;
end;
lastDestRow := destSheet.GetLastOccupiedRowIndex;
lastDestCol := destSheet.GetLastOccupiedColIndex;
// Now iterate through all combination of master and detail values
// of the destination table ...
for r := 1 to lastDestRow do
begin
masterKey := destSheet.ReadAsText(r, 0);
for c := 1 to lastDestCol do
begin
detailKey := destSheet.ReadAsText(0, c);
// ... and count the occurrence of the cross-over cells in the source table
counter := 0;
for rs := 1 to lastSrcRow do
begin
if srcSheet.ReadAsText(rs, colMaster) <> masterKey then continue;
if srcSheet.ReadAsText(rs, colDetail) <> detailKey then continue;
inc(counter);
end;
// ... write counter to destination sheet
destSheet.WriteNumber(r, c, counter, nfFixed, 0);
end;
end;
// Display result workbook in a separat worksheet grid
//sWorksheetGrid2.LoadFromWorkbook(destBook);
// Save workbook to CSV file as requested
destBook.WriteToFile('Results-wp.csv', sfCSV, true);
finally
destBook.Free; // <-- do no destroy destination workbook when it is displayed in sWorksheetGrid2
end;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
sWorkbookSource1.FileName := 'PlantList.xls';
end;
end.