Populate Range with an array and set Format for Range

FLX TMS FlexCel for VCL & FMX
1

Sheet.Range['A1',String(IntToXlCol(iExcelCol))+IntToStr(iExcelRow)].Value:=ArrV;
Sheet.Range['A1',String(IntToXlCol(iExcelCol))+IntToStr(iExcelRow)].Borders.Weight := xlThin;

Using "xls : TXlsFile;", how do I populate Range with an array and set Format for Range for the above?

2

Hi,
FlexCel doesn't have a method to enter an array of values, and that is mostly because it isn't needed. Loading the values into an array and then setting Sheet.Range['A1',String(IntToXlCol(iExcelCol))+IntToStr(iExcelRow)].Value = array

is a known pattern in OLE Automation, because calling OLE methods is very slow, so you try to minimize the OLE calls. (Instead of setting each cell value, calling an OLE method for each, you load an array and set .Value only once).
But when migrating this code to FlexCel, this is not only not needed, but also will slow things down. See FlexCel Performance Guide | FlexCel Studio for VCL and FireMonkey documentation

About setting format for a range, you can do it with SetCellFormat:
https://doc.tmssoftware.com/flexcel/vcl/api/FlexCel.Core/TExcelFile/SetCellFormat.html#texcelfilesetcellformatinteger-integer-integer-integer-tflxformat-tflxapplyformat-boolean

but again, that is not a good idea when converting from OLE to FlexCel. In our case, the fastest way is to just set the cell value and format at the same time in a loop. You can of course also create your own helper method to do this, something like:

procedure SetValueAndFormatArray(const xls: TExcelFile; const row, col: integer; const arrV: TArray<TArray<TCellValue>>);
var
  r, c: Integer;
  fmt: TFlxFormat;
begin
  for r := 0 to Length(arrV)- 1 do
  begin
    for c := 0 to Length(arrV[r]) - 1 do
    begin
      fmt := xls.GetCellVisibleFormatDef(row + r, col + c);
      fmt.Borders.SetAllBorders(TFlxBorderStyle.Thin, Colors.Black);
      xls.SetCellValue(row + r, col + c, arrV[r][c], xls.AddFormat(fmt));
    end;
  end;
end;

In this case, we suppose the values are in an array of array of TCellValue. Your values are probably in some other kind of array (one of the reasons doing a generic SetCellValue(array) in FlexCel isn't much helpful). Use whatever type your array are. But if you were creating those arrays just to fill a range, don't create the arrays at all, just loop the same way I did here, and set the values from whatever source your data is.

1 Like