help exporting 2 data columns to .csv

[howardpc]

I've not used fpspreadsheet before.

If I construct a dialog dynamically with a tsWorkbookSource, tsWorksheet, tsWorkbookTabControl, tsWorksheetGrid and use fpscsv for export (perhaps I don't need all of these?), what methods do I need to use to select a named .ods tab from a named .ods file?
I then want to select 3 columns, one with cell date strings, another with numeric data, the third with description strings, and restrict the selection to a given date range before I export the strings to a new .csv file which I can subsequently parse and analyse. I would like to do all this in code if possible.

If you can point me in the right direction I would be very grateful.

[wp]

TsWorkbookSource, TsWorksheetGrid etc are only needed if you want to "see" the spreadsheet data in your form. Before I continue with my answer, can you confirm this?

[howardpc]

Yes I would like to (optionally) display the tabsheet parented to my dialog as a visual check if possible.

[wp]

See attached demo.

[paweld]

CopyCell

Code: Pascal  [Select][+][-]

1. uses
2.   fpspreadsheet, fpsTypes, fpsAllFormats;
3.  
4. procedure TForm1.Button1Click(Sender: TObject);
5. var
6.   wbsrc, wbdst: TsWorkbook;
7.   wssrc, wsdst: TsWorksheet;
8.   i, j: Integer;
9.   dt: TDateTime;
10.   fs: TFormatSettings;
11. begin
12.   fs.ShortDateFormat := 'dd/mm/yyyy';
13.   fs.DateSeparator := '/';
14.   //source
15.   wbsrc := TsWorkbook.Create;
16.   wbsrc.ReadFromFile('D:\Downloads\file_example_XLSX_5000.xlsx');   //number -> col 7, text -> col 4, date -> col 6
17.   wssrc := wbsrc.GetWorksheetByIndex(0);
18.   //destination
19.   wbdst := TsWorkbook.Create;
20.   wsdst := wbdst.AddWorksheet('copy');
21.   //copy data
22.   j := 0;
23.   for i := 0 to wssrc.GetLastRowIndex() do
24.   begin
25.     //filter by date
26.     if TryStrToDate(wssrc.ReadAsText(i, 6), dt, 'dd/mm/yyyy', '/') and (dt >= EncodeDate(2015, 7, 1)) and (dt <= EncodeDate(2017, 6, 30)) then
27.     begin
28.       wsdst.CopyCell(i, 7, j, 0, wssrc);   //from col 7 to 0
29.       wsdst.CopyCell(i, 4, j, 1, wssrc);   //from 4 to 1
30.       wsdst.CopyCell(i, 6, j, 2, wssrc);   //from 6 to 2
31.       Inc(j);
32.     end;
33.   end;
34.   //format csv
35.   CSVParams.Delimiter := #9;
36.   //save csv
37.   wbdst.WriteToFile('copy.csv', sfCSV, True);
38.   wbdst.Free;
39.   wbsrc.Free;
40. end;          
41.  

Edit: @wp was first

[howardpc]

wp and paweld, thanks for your time and generous support as ever.
This must be one of the best places in the world of programming fora to get top notch support in the middle of the night from people who have become unwitting friends. Thanks so much.

[howardpc]

Werner, when I turn on heaptrc in debugging your little project (which has been a great help in getting me started with fpspreadsheeet) I see memory leaks.
Do you see the same, and if so, do you have any idea how they arise, or how they might be removed?

paweld, how is the TFormatSettings variable fs which you initialise used thereafter? Is there some hidden connection to the rest of the data reading/writing code?

[paweld]

@howardpc: I was supposed to use to convert text to date, but in the end I gave up and used date formats as a string, and forgot to remove the variable

[wp]

when I turn on heaptrc in debugging your little project (which has been a great help in getting me started with fpspreadsheeet) I see memory leaks.
Do you see the same, and if so, do you have any idea how they arise, or how they might be removed?

I don't see a memory leak. But I work with the svn version of fpspreadsheet - I dimly remember that I fixed a memory leak some time ago...

[howardpc]

Thank you both - mysteries solved.