Recent

Author Topic: help exporting 2 data columns to .csv  (Read 2402 times)

howardpc

  • Hero Member
  • *****
  • Posts: 4144
help exporting 2 data columns to .csv
« on: June 01, 2023, 09:39:31 pm »
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

  • Hero Member
  • *****
  • Posts: 11831
Re: help exporting 2 data columns to .csv
« Reply #1 on: June 01, 2023, 11:19:53 pm »
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

  • Hero Member
  • *****
  • Posts: 4144
Re: help exporting 2 data columns to .csv
« Reply #2 on: June 02, 2023, 12:32:43 am »
Yes I would like to (optionally) display the tabsheet parented to my dialog as a visual check if possible.

wp

  • Hero Member
  • *****
  • Posts: 11831
Re: help exporting 2 data columns to .csv
« Reply #3 on: June 02, 2023, 01:43:54 am »
See attached demo.

paweld

  • Hero Member
  • *****
  • Posts: 966
Re: help exporting 2 data columns to .csv
« Reply #4 on: June 02, 2023, 01:54:08 am »
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
« Last Edit: June 02, 2023, 01:55:45 am by paweld »
Best regards / Pozdrawiam
paweld

howardpc

  • Hero Member
  • *****
  • Posts: 4144
Re: help exporting 2 data columns to .csv
« Reply #5 on: June 02, 2023, 06:32:47 am »
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

  • Hero Member
  • *****
  • Posts: 4144
Re: help exporting 2 data columns to .csv
« Reply #6 on: June 05, 2023, 10:52:55 am »
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

  • Hero Member
  • *****
  • Posts: 966
Re: help exporting 2 data columns to .csv
« Reply #7 on: June 05, 2023, 11:17:23 am »
@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
Best regards / Pozdrawiam
paweld

wp

  • Hero Member
  • *****
  • Posts: 11831
Re: help exporting 2 data columns to .csv
« Reply #8 on: June 05, 2023, 11:30:29 am »
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

  • Hero Member
  • *****
  • Posts: 4144
Re: help exporting 2 data columns to .csv
« Reply #9 on: June 05, 2023, 01:37:57 pm »
Thank you both - mysteries solved.

 

TinyPortal © 2005-2018