Problem with FlexcellVCL in Delphi2007

Hello,

I use Delphi Berlin with FlexcellVCL NT and Delphi 2007 with FelxcellVCL.
In my Delphi Berlin projects I can access cells of a Excel sheet via letters for columns using the TCellAddress. For Example I do something like that:

var
   addr : TCellAddress;
   cell : TCellValue;

begin
addr := TCellAddress.Create('A1');
cell := xls.GetCellValue(addr.Row, addr.Col);
showmessage (cell.ToString);
end;

I did not find a way to do so in Delphi2007 with non NT version FlexcellVCL. It does not know TCellAddress. How can I get CellValues from addresses like 'A1', 'BH7' or somethng like that in the non NT version of FlexcellVCL?

There is no TCellAddress in FlexCel 3: While it gets some ocasional bug fixes, FlexCel 3 is really old by now (more than 10 years since any significant development was done) and a lot of convenience methods were added after that. It might make more sense to move the code to FlexCel dll, which still supports Delphi 7+ but has all the newer stuff like xlsx support and calculation. ( https://www.tmssoftware.com/site/flexceldll.asp )

But well, even if there is no TCellAddress, you can code it yourself: It isn't too complex. From what I remember, to convert from numbers to letters:

uses UFlxMessages;
  CellAddress := EncodeColumn(Column) + '$' + IntToStr(Row); 


To convert in the other direction I will be sincere, I believe there was a way but I couldn't find it (and more than a decade has passed, so my memory is a little fuzzy). I think it was a method in the examples or something. But anyway, it is simpler to just rewrite it. Below is a method that will give you the row and column of a cell reference, and return false if the string is not a cell ref. 



function ParseAddress(const CellRef: String; const allowAbsolute: Boolean; out aCol, aRow: Integer): boolean;
var
  i: Integer;
  c: Char;
const
  Delta = ($5A - $41) + 1;
begin
  Result := false;
  aCol := 0;
  aRow := 0;
  i := 0;
  while i < Length(CellRef) do
  begin
    c := CellRef[1 + i];
    if (c = '$') and allowAbsolute then
    begin
      Inc(i);
      continue;
    end;


    if (c >= 'a') and (c <= 'z') then
      c := Char(ord(c) - (ord('a') - ord('A')));


    if (c < 'A') or (c > 'Z') then
      break;


    if (i) > 3 then
      exit;


    aCol:= aCol * Delta;
    aCol:= aCol + ((ord(c) - ord('A')) + 1);
    Inc(i);
  end;
  if (aCol < 1) or (aCol > 16384) then exit;


  while (i < Length(CellRef))  and (aRow<=1048576 ) do
  begin
    c := CellRef[1 + i];
    if (not (c in ['0'..'9'])) then exit;
    aRow:= aRow * 10 + (ord(c) - ord('0'));
    Inc(i)
  end;


  if (i < Length(CellRef)) then exit;
  if (aRow < 1) or (aRow > 1048576) then exit;


  Result := true;
end;


Ps: Just a hint:
In FlexCel 6 you could just do:
  
  cell := xls.GetCellValue('A1');

Thank you so much for your detailed answer. I will try the different methods you mentioned.


In my Delphi2007 code, I used the OLEVariant approach to read Excel files, but that has some drawbacks, like Excel sometimes not released from memory or Excel not installed on a PC. So I am about to bring those import routines to FlexCell.
I have to use the "A1", etc. method, because I have a import interface to a database, where fields in the database are defined to be a certain column in the Excel sheet like:
CustomerID = A
CustomerName = H
Street = B
.....
So every user of my program can define which column in his Excel file represents which data field.
It would be less intuitiv for the user to define it via column numbers when the Excel sheet displays letters.
So I have to stick with the alphabetical column names.

I used your ParseAddress function and it works like a charm.

Many thanks for your great support.