* * *

Author Topic: Read range of Excel spreadsheet cells as a Free Pascal array  (Read 331 times)

matandked

  • New member
  • *
  • Posts: 14
Read range of Excel spreadsheet cells as a Free Pascal array
« on: February 05, 2017, 07:37:43 pm »
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

  • Hero Member
  • *****
  • Posts: 3278
Re: Read range of Excel spreadsheet cells as a Free Pascal array
« Reply #1 on: February 05, 2017, 07:55:49 pm »
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?
Lazarus trunk / fpc 3.0.0 / Win32

matandked

  • New member
  • *
  • Posts: 14
Re: Read range of Excel spreadsheet cells as a Free Pascal array
« Reply #2 on: February 11, 2017, 11:31:20 pm »
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

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus