Very silly dumb question on populating a stringgrid with a spreadsheet!

[maurobio]

Dear ALL,

Here is a pretty stupid question on how I could populate a stringgrid with the contents of an Excel spreadsheet (.xlsx) using the great fpspreadsheet library (https://wiki.freepascal.org/FPSpreadsheet).

Not sure if iterating through cells is the best way to proceed.

I understand that this should be quite simple, but I am struck with it!

Sample code with a simple example spreadsheet is attached.

Thanks in advance for any assistance you can provide!

With warmest regards,

[wp]

You almost got it. What's missing is setting the number of columns and row in the stringgrid. And you are confusing the col/row indices of the worksheet with those of the grid. For some historic reason, the worksheet cells are addressed by two indices in the order "row first, column second". The grid, however, has the order "column first, row second". And since the grid usually has FixedCols and FixedRows which are "normal" grid cells", but the worksheet does not, the indices are usually offset by 1.

Here is the correct code (tested):

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

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   MyWorkbook: TsWorkbook;
4.   MyWorksheet: TsWorksheet;
5.   col, row: cardinal;
6.   cell: PCell;
7. begin
8.   MyWorkbook := TsWorkbook.Create;
9.   try
10.     MyWorkbook.ReadFromFile('cellar.xlsx');
11.     MyWorksheet := MyWorkbook.GetWorksheetByIndex(0);
12.     StringGrid1.ColCount := MyWorksheet.GetlastColIndex + 2;
13.     StringGrid1.RowCount := MyWorksheet.GetLastRowIndex + 2;
14.     for row := 0 to MyWorksheet.GetLastRowIndex do
15.       for col := 0 to MyWorksheet.GetLastColIndex do
16.       begin
17.         cell := MyWorksheet.FindCell(row, col);
18.         StringGrid1.Cells[col+1, row+1] := MyWorksheet.ReadAsText(cell);
19.       end;
20.   finally
21.     MyWorkbook.Free;
22.   end;
23. end;

A simpler way is to use the TsWorksheetGrid which can also display cell formatting, column widths, row heights, etc: https://wiki.lazarus.freepascal.org/TsWorksheetGrid

[maurobio]

Hi, @wp!

Thanks for your help! Given the subtleties in the addressing of rows and cols in stringgrid/worksheet, at least I do not feel so dumb!

The reason I am not using to TsWorkseetGrid is that for that particular application, I cannot use the visual components.

With best regards,