help exporting 2 data columns to .csv
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
howardpc · 2023-06-01 19:39

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 · 2023-06-01 21:19

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 · 2023-06-01 22:32

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

----------------------------------------------------------------------
wp · 2023-06-01 23:43

See attached demo.

----------------------------------------------------------------------
paweld · 2023-06-01 23:54

CopyCellCode: Pascal  [Select][+][-]window.onload = function(){var x1
= document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses 
fpspreadsheet, fpsTypes, fpsAllFormats; procedure
TForm1.Button1Click(Sender: TObject);var  wbsrc, wbdst: TsWorkbook; 
wssrc, wsdst: TsWorksheet;  i, j: Integer;  dt: TDateTime;  fs:
TFormatSettings;begin  fs.ShortDateFormat := 'dd/mm/yyyy'; 
fs.DateSeparator := '/';  //source  wbsrc := TsWorkbook.Create; 
wbsrc.ReadFromFile('D:\Downloads\file_example_XLSX_5000.xlsx');  
//number -> col 7, text -> col 4, date -> col 6  wssrc :=
wbsrc.GetWorksheetByIndex(0);  //destination  wbdst :=
TsWorkbook.Create;  wsdst := wbdst.AddWorksheet('copy');  //copy data 
j := 0;  for i := 0 to wssrc.GetLastRowIndex() do  begin    //filter
by date    if TryStrToDate(wssrc.ReadAsText(i, 6), dt, 'dd/mm/yyyy',
'/') and (dt >= EncodeDate(2015, 7, 1)) and (dt <= EncodeDate(2017, 6,
30)) then    begin      wsdst.CopyCell(i, 7, j, 0, wssrc);   //from
col 7 to 0      wsdst.CopyCell(i, 4, j, 1, wssrc);   //from 4 to 1   
 wsdst.CopyCell(i, 6, j, 2, wssrc);   //from 6 to 2      Inc(j);   
end;  end;  //format csv  CSVParams.Delimiter := #9;  //save csv 
wbdst.WriteToFile('copy.csv', sfCSV, True);  wbdst.Free; 
wbsrc.Free;end;           Edit: @wp was first

----------------------------------------------------------------------
howardpc · 2023-06-02 04:32

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 · 2023-06-05 08:52

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 · 2023-06-05 09:17

@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 · 2023-06-05 09:30

Quote from: howardpc on June 05, 2023, 10:52:55 amwhen 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 · 2023-06-05 11:37

Thank you both - mysteries solved.