Read range of Excel spreadsheet cells as a Free Pascal array

[matandked]

I have an Excel spreadsheet that stores matrix of numeric data.
First value is written to cell "B2", so that once i will execute

MyWorksheet.GetLastRowIndex

and

MyWorksheet.GetLastColIndex

of fpspreadsheet package I will know the exact dimensions of my array.

Is there any ready to use function which allow me to provide an Excel range of cells as an argument and returns Free Pascal array?

I can't find appropriate function in fpspreadsheet:
http://wiki.freepascal.org/FPSpreadsheet

and I'm worried I will need to define dynamic array and write for loops again

[wp]

No - there is no built-in method for that. The main reason is that the data type stored in cells can change from cell to cell, and such a function therefore would require a array of variant which I wanted to avoid as much as possible (variants are there in the formula parser, though). But really: what's the problem of writing code using "for" loops?

[matandked]

Sorry for lack of response, but I had other duties, not related with Free Pascal 

I just wish to not reinvent the wheel if not necessary.
If there's no built-in function, I would like to separate code for reading spreadsheet range as arrays.
I wrote following function - hope that somebody will find it useful:

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

1. program project1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   {$IFDEF UNIX}{$IFDEF UseCThreads}
7.   cthreads,
8.   {$ENDIF}{$ENDIF}
9.   Classes, sysutils, fpstypes, fpSpreadsheet, laz_fpspreadsheet;
10.  
11. type TDynFloat = array of array of Real;
12.  
13. var
14.   MyWorkbook: TsWorkbook;
15.   MyWorksheet: TsWorksheet;
16.   myReturnedArray : TDynFloat;
17.  
18. function ReadSpreadsheetRange
19.          (MyWorksheet: TsWorksheet; startCell : string; endCell: string = '')
20.                        : TDynFloat;
21. // Function reads numbers FROM range of cells (startCell : endCell)
22. // and RETURNS them as a dynamical array of Floats.
23. // If endCell is not provided, it reads all cells from startCell to the last.
24.  
25. // Example usage:
26. // ReadSpreadsheetRange(MyWorksheet,'B2', 'F3')
27.  
28.  var
29.    LastColumn, LastRow, row, col : integer;
30.    cell : PCell;
31.    myArray : TDynFloat;
32.  begin
33.    if MyWorksheet.FindCell(startCell) = nil then
34.       begin
35.         // ToDo: Maybe I should return an empty array before exit (???)
36.         WriteLn('Cell ' + startCell + ' does not exist in ' + MyWorksheet.Name);
37.         exit;
38.       end;
39.  
40.    cell := MyWorksheet.FindCell(startCell);
41.  
42.    // Set last column and rows depending on whether endCell provided
43.    if ( endCell = '' ) or ( MyWorksheet.FindCell(endCell) = nil ) then
44.       begin
45.         LastColumn := MyWorksheet.GetLastColIndex();
46.         LastRow := MyWorksheet.GetLastRowIndex();
47.       end
48.    else
49.      begin
50.        LastColumn := MyWorksheet.FindCell(endCell)^.Col;
51.        LastRow := MyWorksheet.FindCell(endCell)^.Row;
52.      end;
53.  
54.    SetLength(myArray,LastRow - cell^.Row + 1,LastColumn - cell^.Col + 1);
55.    for row := cell^.Row to LastRow do
56.        for col := cell^.Col to LastColumn do
57.            begin
58.              myArray[row - cell^.Row, col - cell^.Col] :=
59.                          MyWorksheet.ReadAsNumber(row,col);
60.            end;
61.  
62.    Result := myArray;
63.  end;
64.  
65. begin
66.  
67.   ////////////////////////////////
68.   // Read worksheet;
69.   MyWorkbook := TsWorkbook.Create;
70.   MyWorkbook.ReadFromFile('testData.xls');
71.   MyWorksheet := MyWorkbook.GetWorksheetByIndex(0);
72.   ///////////////////////////////
73.  
74.   //////////////////////////////
75.   // Use function above and test it
76.   myReturnedArray := ReadSpreadsheetRange(MyWorksheet,'B2');
77.  
78.   WriteLn('Size of the returned array is: ' +
79.                 FloatToStr(Length(myReturnedArray)));
80.   // value in this cell (C2): 42,4842
81.   WriteLn(myReturnedArray[0,1]);
82.   // value in this cell (D2): 45
83.   WriteLn(myReturnedArray[0,2]);
84.   // value in this cell (E2): THIS IS NOT A NUMBER
85.   WriteLn(myReturnedArray[0,3]);
86.  
87.   MyWorkbook.Free;
88.  
89.   ReadLn;
90.  
91. end.
92.  

It seems to work properly. I tested it only very quickly and only with attached spreadsheet under Ubuntu 16.04 64-bit