Accessing the worksheet in FPSpreadsheetDataset lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- maurobio · 2021-09-25 00:45 Hi, @wp! Could the worksheet associated to the FPSpreadsheetDataset be made accessible by means of a public property? Currently it is declared as private. With best wishes. ---------------------------------------------------------------------- wp · 2021-09-25 08:08 Hmmm... Giving the user access to the worksheet means that he will be able to do anything with it: He can rename the worksheet, he can even delete it, he can edit cells and thus bypass the dataset, he can insert rows and columns etc etc. All these actions interfere with settings on which the dataset relies. Why do you need the worksheet? ---------------------------------------------------------------------- maurobio · 2021-09-25 11:07 Hi, @wp! Yes, you are quite right! Just after posting that message, it also occurred to me that exposing the worksheet would not really be a good idea.  Anyway, what I want to achieve can surely be done by using the dataset itself. In sum, what I would like is to have a version of the algorithm below (which you wrote yourself!) adapted to using FPSpreadSheet. Also, I would like to have the summary data stored in a two-dimensional array instead of a workbook. Can this be done without access to the worksheet? Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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.   I attach the complete sample (including the source spreadsheet). With best wishes, ---------------------------------------------------------------------- wp · 2021-09-25 12:00 You still can access the worksheet by opening the workbook from disk separately by using the fpspreadsheet routines - under your own responsibility. You'll have to close the dataset in order to flush the current workbook to disk. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button2Click(Sender: TObject);var  destBook: TsWorkbook;  destSheet: TsWorksheet;  srcBook: TsWorkbook;  srcSheet: TsWorksheet;  bm: TBookmark;  [...]begin  bm := sWorksheetDataset.GetBookmark;  sWorksheetDataset.Close;   srcBook := TsWorkbook.Create;  try    srcBook.LoadFromFile(sWorksheetDataset.FileName);    srcSheet := srcBook.GetFirstWorkshet;    // or: srcSheet := srcBook.GetWorksheetByName(sWorksheetDataset.SheetName);   // etc.  finally    sWorkbook.Free;  end;   sWorksheetdataset.Open;  sWorksheetDataset.GotoBookmark(bm);  Or you can access the field values from the dataset directly - straight database technology... So, instead of iterating through the srcSheet, iterate through the WorksheetDataset and get the values from there. Replace the "for r := 1 to lastSrcRow" loops by a loop "while not WorksheetDataset.EoF do", and get the field values from WorksheetDataset.FieldByName('PLOT') rather than from srcSheet.ReadAsText(r, colMaster). ---------------------------------------------------------------------- maurobio · 2021-09-25 12:13 Hi, @wp! QuoteOr you can access the field values from the dataset directly - straight database technology... So, instead of iterating through the srcSheet, iterate through the WorksheetDataset and get the values from there. Replace the "for r := 1 to lastSrcRow" loops by a loop "while not WorksheetDataset.EoF do", and get the field values from WorksheetDataset.FieldByName('PLOT') rather than from srcSheet.ReadAsText(r, colMaster). Sure, I was thinking precisely along these lines, which are safer and easier! Will give it a try. Thank you very much! With best wishes, ---------------------------------------------------------------------- maurobio · 2021-09-25 21:11 Hi, @wp! I tried the code below:  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  var  masterKey, detailKey: string;  arr: arrar of array of double;  r, c, counter: integer; begin    r := 0;    c := 0;    while not sWorksheetDataset1.EOF do    begin      masterKey := sWorksheetDataset1.FieldByName('PLOT').AsString;      while not sWorksheetDataset1.EOF do      begin        detailKey := sWorksheetDataset1.FieldByName('SPECIES').AsString;        // ... and count the occurrence of the cross-over cells in the source table      counter := 0;        while not sWorksheetDataset1.EOF do        begin          if sWorksheetDataset1.FieldByName('PLOT').AsString <> masterKey then            continue;          if sWorksheetDataset1.FieldByName('SPECIES').AsString <> detailKey then          continue;          Inc(counter);          sWorksheetDataset1.Next;        end;        // ... store counter to destination array        arr[r, c] := counter;        Inc(c);        sWorksheetDataset1.Next;      end;      Inc(r);      SetLength(arr, r, c);      sWorksheetDataset1.Next;    end;end;  It looks simple enough, but creates an infinite loop. Any suggestions? Thanks in advance! With best wishes, ---------------------------------------------------------------------- wp · 2021-09-25 21:35 Can you post a sample data file so that I can think about it? ---------------------------------------------------------------------- maurobio · 2021-09-25 21:47 Hi, @wp! Oops, sorry! I forgot to attach the sample code (which includes a data file) to my previous message. Please notice that this code is highly experimental and therefore there are plenty of commented out parts! Here it is! Thanks you very much. With best wishes, ---------------------------------------------------------------------- wp · 2021-09-25 22:12 What do you want to achieve? Some kind of cross-table like in the attachment, i.e. count how many items have the same value in the PLOT and in the SPECIES columns? ---------------------------------------------------------------------- maurobio · 2021-09-25 22:20 Hi, @wp, Attached is an example of what I want to achieve: a table with the number of cases (specimens) of each species in each plot. Thank you! With best wishes, ---------------------------------------------------------------------- wp · 2021-09-25 22:55 I'd do it like in the attachment ---------------------------------------------------------------------- maurobio · 2021-09-25 23:14 Hi, @wp! That's it, but I don't want to display the results in another grid neither to save the resulting table "as is". Instead, I just want to store the table (without the label headers) in a numeric array that I can manipulate in several ways. But I think I can achieve that on the basis of your very clear code (in fact, the array "sums" seems to be already what I need). Thanks a lot! With best wishes,