Recent

Author Topic: Solved: Spreadsheet to combobox  (Read 2456 times)

paracelsusx

  • New Member
  • *
  • Posts: 13
Solved: Spreadsheet to combobox
« on: January 28, 2020, 02:50:15 am »
I’m working on my first app which is a simple fitness tracker for myself and I’m trying to read a simple ods spreadsheet that just has one column of names of exercises and then show those exercises listed in a  drop down combo box. I also want to use a seperate edit box to add exercises to the same spreadsheet. I thought this would be a good introductory way to learn to read and write from sheets and display them. After researching I downloaded and installed fpsspreadsheet packages. I believe I have set the spreadsheet as the correct data source using tsworkbook source. I’m a little lost on how to proceed and any help would be appreciated.
« Last Edit: January 29, 2020, 02:22:08 am by paracelsusx »

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Re: Spreadsheet to combobox
« Reply #1 on: January 28, 2020, 03:13:40 am »
Hello paraceslsux,
Welcome to the forum.

We need more information, can you show us the source code? If you're not willing to publicize it, you can write a simple demo project that can show the issue.

Create a new folder, copy and paste all the necessary files except: the binary (exe file), *.bak, lib and backup folders. Compress the folder and attach the zip to the forum.

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Re: Spreadsheet to combobox
« Reply #2 on: January 28, 2020, 04:46:56 am »
You need to put fpsallformats in uses clause in your unit4.pas.

paracelsusx

  • New Member
  • *
  • Posts: 13
Re: Spreadsheet to combobox
« Reply #3 on: January 28, 2020, 05:05:00 am »
Thanks I’ll try that

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Re: Spreadsheet to combobox
« Reply #4 on: January 28, 2020, 05:15:54 am »
I do not use TsWorkbookSource. It caused some minor issues on my test, maybe file path issue.

Instead, I modified your code to use TWorkbook.ReadFromFile directly. You can try the modified code and press the "Click Me !" button.

Code: Pascal  [Select][+][-]
  1. procedure Texerciselist.btnClickMeClick(Sender: TObject);
  2. const
  3.   DataFile = 'exlist.ods';
  4. var
  5.   MyWorkbook : TsWorkbook;
  6.   S          : string;
  7.   i          : Integer;
  8. begin
  9.   S := '';
  10.   MyWorkbook := TsWorkbook.Create;
  11.   MyWorkbook.ReadFromFile(DataFile, sfOpenDocument);
  12.   for i := 0 to MyWorkbook.ActiveWorksheet.GetLastRowIndex do
  13.     S := S + MyWorkbook.ActiveWorksheet.ReadAsText(i, 0) + LineEnding;
  14.   ShowMessage(S);
  15.   MyWorkbook.Free;
  16. end;

You should modify and put the code in the form's OnCreate event to load the data to the combobox. And of course, with some modifications it should work with TsWorkbookSource too. My code only shows you how to load the data, I believe you know how to store the data to the combobox.

Without fpsallformats, the code can't run properly on my Linux machine. Not sure about Windows.
« Last Edit: January 28, 2020, 05:58:28 am by Handoko »

paracelsusx

  • New Member
  • *
  • Posts: 13
Re: Spreadsheet to combobox
« Reply #5 on: January 28, 2020, 11:52:23 pm »
Made a bit more progress. I changed It to the following:
Code: Pascal  [Select][+][-]
  1. const
  2.   DataFile = 'exlist.ods';
  3. var
  4.   MyWorkbook : TsWorkbook;
  5.   S          : string;
  6.   i          : Integer;
  7. begin
  8.   S := '';
  9.   MyWorkbook := TsWorkbook.Create;
  10.   MyWorkbook.ReadFromFile(DataFile, sfOpenDocument);
  11.   for i := 0 to MyWorkbook.ActiveWorksheet.GetLastRowIndex do
  12.     S := S + MyWorkbook.ActiveWorksheet.ReadAsText(i, 0) + LineEnding;
  13.   ComboBox1.items.AddStrings(s);

This works to populate the combobox, but it puts all the data into one line of the combobox. I just have to figure out how to separate it into separate lines for each one.

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Re: Spreadsheet to combobox
« Reply #6 on: January 29, 2020, 01:27:58 am »
Have you figured out how to properly populate the data to the combobox?

You do not need the variable S, you just provide the data from ReadAsText directly to the combobox. Also the for-do loop, it should start from 1 because you do not need the title of the table.

You can download the test3.zip to learn how to do it. There I haven't move them to the form's OnCreate event.

About the fpsallformats unit. Removing it causes error on Linux, I've just tested it. I use Linux.

paracelsusx

  • New Member
  • *
  • Posts: 13
Solved: Spreadsheet to combobox
« Reply #7 on: January 29, 2020, 02:21:42 am »
Thanks this worked perfectly. This helped me figure out quite a bit of how this works as well. I appreciate your help very much. I had to leave fpsallformats for it to work as well. I am working on setting Lazarus up right now on my Linux mint virtual machine and plan to test this on there as well.

Thanks again.

paracelsusx

  • New Member
  • *
  • Posts: 13
Re: Solved: Spreadsheet to combobox
« Reply #8 on: January 29, 2020, 03:52:08 am »
Thought I add I tried this in my Linux vm as well on mint and the code works fine on there as well.

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: Spreadsheet to combobox
« Reply #9 on: January 30, 2020, 09:41:53 am »
About the fpsallformats unit. Removing it causes error on Linux, I've just tested it. I use Linux.
Just to put this right. fpsallformats has nothing to do with Linux or Windows or so. fpspreadsheet supports a variety of spreadsheet file formats; for each format there is a unit with the code for the reader/writer class. In order to avoid blowing up a program by unused code each format must be registered by adding its reader/writer unit to the uses clause of a project unit. So, when some one only wants to read xlsx files it is sufficient to added the corresponding unit xlsxooxml to uses. For LibreOffice Calc's ods it is fpsOpenDocument, and for old binary Excel (Excel 97) it is xlsBiff8, or xlsbiff5 for Excel95 or xlsbiff2 for really very old Excel 2.1, etc. Alternatively you can add fpsallformats which registers all file formats to your program (but as I said: this will add code for the reader/writers for wiki, html, Excel2.1, and Excel 95 which you probably will not use).

 

TinyPortal © 2005-2018