get rectangle of Excel cell range

FLX TMS FlexCel for VCL & FMX
1
How can I write down the following OLE-based code to get the rectangle of an Excel cell range in FlexCel?


lxRange  := lxExcel.Range[ptFCell,ptTCell];
viTop    := lxRange.Top;
viHeight := lxRange.Height;
viLeft   := lxRange.Left;
viWidth  := lxRange.Width;
2

Hi,

It would help to know a little more about the context of what you are trying to do, as there is probably a different way to do the same task in FlexCel using different methods. But to do exactly this, you would have to implement it since there is no built-in method to do it. You would create a method like this:



procedure DimensionsInPoints(const xls: TExcelFile; const row1, col1, row2, col2: integer; out viTop, viLeft, viHeight, viWidth: double);
var
  row, col: integer;
begin
  viTop := 0;
  for row := 1 to row1 - 1 do
  begin
    viTop := viTop + xls.GetRowHeight(row, true) / 20.0;
  end;


  viLeft := 0;
  for col := 1 to col1 - 1 do
  begin
    viLeft := viLeft + xls.GetColWidth(col, true) / TExcelMetrics.ColMultDisplay(xls) * 72.0 / 100.0;
  end;


  viHeight := 0;
  for row := row1 to row2 do
  begin
    viHeight := viHeight + xls.GetRowHeight(row, true) / 20.0;
  end;


  viWidth := 0;
  for col := col1 to col2 do
  begin
    viWidth := viWidth + xls.GetColWidth(col, true) / TExcelMetrics.ColMultDisplay(xls) * 72.0 / 100.0;
  end;


end;

And then call it like this:



procedure TForm33.Button1_Click(Sender: TObject);
var
  viTop, viLeft, viWidth, viHeight: double;
  xls: TExcelFile;
  addr1, addr2: TCellAddress;
begin
  addr1 := TCellAddress.Create('B3');
  addr2 := TCellAddress.Create('E5');
  xls := TXlsFile.Create(1, TExcelFileFormat.v2019, true);
  try
    DimensionsInPoints(xls, addr1.Row, addr1.Col, addr2.Row, addr2.Col, viTop, viLeft, viHeight, viWidth);


  finally
    xls.Free;
  end;
end;

Note that in rows, you will get the exact same value as Excel, because rows are stored in points*20, and as range.top/left/etc are in points, it is just the row height/20.
But in columns it is a little more complex, since Excel reports different values depending in the resolution of your screen. FlexCel will return the "Print" column width, in the average of a 600dpi printers. Excel will return whatever is what it is displaying in the current screen. (And if you do a print preview and get a screen capture and superimpose it with the screen, you will see that Excel uses bigger columns at print time).

FlexCel normally runs on servers, where there might be not even any screen attached, so it just displays the print width. For more information about the different column sizes see http://www.tmssoftware.biz/flexcel/doc/vcl/guides/api-developer-guide.html#autofitting-rows-and-columns
Adrian Gallero2019-03-14 14:25:43
3

Edit:

I updated the original post so it is:
TExcelMetrics.ColMultDisplay(xls) * 72.0 / 100.0;
instead of
TExcelMetrics.ColMultDisplay(xls) * 72.0 / 96.0;

I thought ColMultDisplay converted to "device independent pixels" (that is 1/96 of an inch), but looking at the docs: http://www.tmssoftware.biz/flexcel/doc/vcl/api/FlexCel.Core/TExcelMetrics/ColMultDisplay.html@I realized it is in "display" units, that is 1/100 of an inch. So to convert to points (1/72 of an inch) you need to divide by 100 and multiply by 72, not divide by 96.
4
First of all thanks for your fast and excellent support and for the great tool itself which we now even started to use to replace (slow) OLE-based code on the client side of our application suite.
My question arose in the context of migrating shapes from OLE to FlexCel before I realized using ApiMate that shapes are placed using client anchors which relate to cell coordinates, not to absolute pixel-based positions as with OLE.