Recent

Author Topic: Accessing the worksheet in FPSpreadsheetDataset  (Read 1346 times)

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Accessing the worksheet in FPSpreadsheetDataset
« on: September 25, 2021, 02:45:58 am »
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.
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 8880
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #1 on: September 25, 2021, 10:08:48 am »
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?
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #2 on: September 25, 2021, 01:07:37 pm »
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][+][-]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls, Grids,
  9.   fpspreadsheetctrls, fpspreadsheetgrid, fpsTypes, fpsallformats;
  10.  
  11. type
  12.  
  13.   { TForm1 }
  14.  
  15.   TForm1 = class(TForm)
  16.     Button2: TButton;
  17.     sWorkbookSource1: TsWorkbookSource;
  18.     sWorksheetGrid1: TsWorksheetGrid;
  19.     procedure Button2Click(Sender: TObject);
  20.     procedure FormCreate(Sender: TObject);
  21.   private
  22.  
  23.   public
  24.  
  25.   end;
  26.  
  27. var
  28.   Form1: TForm1;
  29.  
  30. implementation
  31.  
  32. {$R *.lfm}
  33.  
  34. uses
  35.   fpspreadsheet;
  36.  
  37. { TForm1 }
  38.  
  39. procedure TForm1.Button2Click(Sender: TObject);
  40. var
  41.   destBook: TsWorkbook;
  42.   destSheet: TsWorksheet;
  43.   srcSheet: TsWorksheet;
  44.   colMaster: Cardinal;
  45.   colDetail: Cardinal;
  46.   lastSrcRow, lastDestRow, lastDestCol: Cardinal;
  47.   r, c, rs: Cardinal;
  48.   i: Integer;
  49.   L: TStringList;
  50.   counter: Integer;
  51.   masterKey, detailKey: String;
  52. begin
  53.   srcSheet := sWorkbookSource1.Worksheet;
  54.   colMaster := 0;  // Index of column "PLOT"
  55.   colDetail := 3;  // Index of column "SPECIES"
  56.   lastSrcRow := srcSheet.GetLastOccupiedRowIndex;
  57.   destBook := TsWorkbook.Create;
  58.   try
  59.     destSheet := destBook.AddWorksheet('Results');
  60.  
  61.     L := TStringList.Create;
  62.     try
  63.       L.Sorted := true;
  64.       L.Duplicates := dupIgnore;
  65.       // Get list of unique items in colMaster column
  66.       for r := 1 to lastSrcRow do
  67.         L.Add(srcSheet.ReadAsText(r, colMaster));
  68.       // Write list to first column of destination table
  69.       for i := 0 to L.Count-1 do
  70.         destSheet.WriteText(i+1, 0, L[i]);
  71.  
  72.       // Get list unique items in colDetails column
  73.       L.Clear;
  74.       for r := 1 to lastSrcRow do
  75.         L.Add(srcSheet.ReadAsText(r, colDetail));
  76.       // Write list to first row of destination table
  77.       for i := 0 to L.Count-1 do
  78.         destSheet.WriteText(0, i+1, L[i]);
  79.     finally
  80.       L.free;
  81.     end;
  82.  
  83.     lastDestRow := destSheet.GetLastOccupiedRowIndex;
  84.     lastDestCol := destSheet.GetLastOccupiedColIndex;
  85.     // Now iterate through all combination of master and detail values
  86.     // of the destination table ...
  87.     for r := 1 to lastDestRow do
  88.     begin
  89.       masterKey := destSheet.ReadAsText(r, 0);
  90.       for c := 1 to lastDestCol do
  91.       begin
  92.         detailKey := destSheet.ReadAsText(0, c);
  93.         // ... and count the occurrence of the cross-over cells in the source table
  94.         counter := 0;
  95.         for rs := 1 to lastSrcRow do
  96.         begin
  97.           if srcSheet.ReadAsText(rs, colMaster) <> masterKey then continue;
  98.           if srcSheet.ReadAsText(rs, colDetail) <> detailKey then continue;
  99.           inc(counter);
  100.         end;
  101.         // ... write counter to destination sheet
  102.         destSheet.WriteNumber(r, c, counter, nfFixed, 0);
  103.       end;
  104.     end;
  105.  
  106.     // Display result workbook in a separat worksheet grid
  107.     //sWorksheetGrid2.LoadFromWorkbook(destBook);
  108.  
  109.     // Save workbook to CSV file as requested
  110.     destBook.WriteToFile('Results-wp.csv', sfCSV, true);
  111.  
  112.   finally
  113.     destBook.Free;  // <-- do no destroy destination workbook when it is displayed in sWorksheetGrid2
  114.   end;
  115. end;
  116.  
  117. procedure TForm1.FormCreate(Sender: TObject);
  118. begin
  119.   sWorkbookSource1.FileName := 'PlantList.xls';
  120. end;
  121.  
  122. end.
  123.  
  124.  

I attach the complete sample (including the source spreadsheet).

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 8880
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #3 on: September 25, 2021, 02:00:54 pm »
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][+][-]
  1. procedure TForm1.Button2Click(Sender: TObject);
  2. var
  3.   destBook: TsWorkbook;
  4.   destSheet: TsWorksheet;
  5.   srcBook: TsWorkbook;
  6.   srcSheet: TsWorksheet;
  7.   bm: TBookmark;
  8.   [...]
  9. begin
  10.   bm := sWorksheetDataset.GetBookmark;
  11.   sWorksheetDataset.Close;
  12.  
  13.   srcBook := TsWorkbook.Create;
  14.   try
  15.     srcBook.LoadFromFile(sWorksheetDataset.FileName);
  16.     srcSheet := srcBook.GetFirstWorkshet;
  17.     // or: srcSheet := srcBook.GetWorksheetByName(sWorksheetDataset.SheetName);
  18.  
  19.   // etc.
  20.   finally
  21.     sWorkbook.Free;
  22.   end;
  23.  
  24.   sWorksheetdataset.Open;
  25.   sWorksheetDataset.GotoBookmark(bm);
  26.  

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).
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #4 on: September 25, 2021, 02:13:38 pm »
Hi, @wp!

Quote
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).

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,

UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #5 on: September 25, 2021, 11:11:11 pm »
Hi, @wp!

I tried the code below:

 
Code: Pascal  [Select][+][-]
  1.  
  2. var
  3.   masterKey, detailKey: string;
  4.   arr: arrar of array of double;
  5.   r, c, counter: integer;
  6.  
  7. begin
  8.     r := 0;
  9.     c := 0;
  10.     while not sWorksheetDataset1.EOF do
  11.     begin
  12.       masterKey := sWorksheetDataset1.FieldByName('PLOT').AsString;
  13.       while not sWorksheetDataset1.EOF do
  14.       begin
  15.         detailKey := sWorksheetDataset1.FieldByName('SPECIES').AsString;
  16.         // ... and count the occurrence of the cross-over cells in the source table
  17.         counter := 0;
  18.         while not sWorksheetDataset1.EOF do
  19.         begin
  20.           if sWorksheetDataset1.FieldByName('PLOT').AsString <> masterKey then
  21.             continue;
  22.           if sWorksheetDataset1.FieldByName('SPECIES').AsString <> detailKey then
  23.             continue;
  24.           Inc(counter);
  25.           sWorksheetDataset1.Next;
  26.         end;
  27.         // ... store counter to destination array
  28.         arr[r, c] := counter;
  29.         Inc(c);
  30.         sWorksheetDataset1.Next;
  31.       end;
  32.       Inc(r);
  33.       SetLength(arr, r, c);
  34.       sWorksheetDataset1.Next;
  35.     end;
  36. end;
  37.  

It looks simple enough, but creates an infinite loop. Any suggestions?

Thanks in advance!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 8880
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #6 on: September 25, 2021, 11:35:28 pm »
Can you post a sample data file so that I can think about it?
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #7 on: September 25, 2021, 11:47:51 pm »
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,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 8880
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #8 on: September 26, 2021, 12:12:31 am »
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?
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #9 on: September 26, 2021, 12:20:15 am »
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,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 8880
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #10 on: September 26, 2021, 12:55:56 am »
I'd do it like in the attachment
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Accessing the worksheet in FPSpreadsheetDataset
« Reply #11 on: September 26, 2021, 01:14:53 am »
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,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

 

TinyPortal © 2005-2018