TXlsFile.ColFromIndex Method

Overloads

TXlsFile.ColFromIndex(Integer, Integer)

This is the column (1 based) for a given ColIndex. You can use this together with TExcelFile.ColCountInRow(Integer) and TExcelFile.ColToIndex(Integer, Integer) to iterate faster on a block. Or you can call TExcelFile.LoopOverUsedRange for a method that does the looping for you using those methods.

Syntax

Unit: FlexCel.XlsAdapter

function TXlsFile.ColFromIndex(const row: Integer; const colIndex: Integer): Integer; overload; override;

Parameters

<-> Parameter Type Description
const row Integer Row (1 based)
const colIndex Integer The index on the column list for the row. (1 based)

Returns

The column (1 based) corresponding to the index when the index is inside the bounds. If the index is <= 0 or the index is > TExcelFile.ColCountInRow(Integer), it will return 0.

Examples

Instead of writing:

var
  RowCount: Int32;
  ColCount: Int32;
  row: Int32;
  col: Int32;
...
  RowCount := xls.RowCount;
  ColCount := xls.ColCount;
  for row := 1 to RowCount do
  begin
    for col := 1 to ColCount do  //It would be faster to use ColCountInRow. See https://doc.tmssoftware.com/flexcel/vcl/guides/performance-guide.html#avoid-calling-colcount
    begin
      DoSomething(row, col);
    end;
  end;

You can use:

var
  RowCount: Int32;
  row: Int32;
  ColCountInRow: Int32;
  colIndex: Int32;
...
  RowCount := xls.RowCount;
  for row := 1 to RowCount do
  begin
    ColCountInRow := xls.ColCountInRow(row);
    for colIndex := 1 to ColCountInRow do
    begin
      DoSomething(row, xls.ColFromIndex(row, colIndex));
    end;

  end;

To loop on all the existing cells on a range you can use:

var
  LastCIndex: Int32;
  XF: Int32;
  cIndex: Int32;
  LastUsedRow: Int32;
...
  // Loop at most until the last used row in the sheet.
  // If LastRow is for example 1.000.000, but there are only
  // 3 used rows, it makes no sense to loop over all the empty rows after row 3.
  LastUsedRow := Math.Min(LastRow, xls.RowCount);

  for row := FirstRow to LastUsedRow do
  begin
    LastCIndex := xls.ColToIndex(row, LastColumn);
    LastColFromIndex := xls.ColFromIndex(row, LastCIndex);
    if (LastColFromIndex > LastColumn) or (LastColFromIndex = 0) then  // LastColumn does not exist.
    begin
      Dec(LastCIndex);
    end;

    if LastCIndex = 0 then  // This row is empty. Move to the next row.
      continue;

    XF := -1;
    for cIndex := xls.ColToIndex(row, FirstColumn) to LastCIndex do
    begin
      DoSomething(row, xls.ColFromIndex(row, cIndex), xls.GetCellValueIndexed(row, cIndex, XF));
    end;

  end;

Note that this example is the implementation of TExcelFile.LoopOverUsedRange, so you might want to directly call TExcelFile.LoopOverUsedRange instead of pasting this example in your code.

See also

TXlsFile.ColFromIndex(Integer, Integer, Integer)

This is the column (1 based) for a given ColIndex and sheet. You can use this together with TExcelFile.ColCountInRow(Integer) and TExcelFile.ColToIndex(Integer, Integer) to iterate faster on a block. Or you can call TExcelFile.LoopOverUsedRange for a method that does the looping for you using those methods.

Syntax

Unit: FlexCel.XlsAdapter

function TXlsFile.ColFromIndex(const sheet: Integer; const row: Integer; const colIndex: Integer): Integer; overload; override;

Parameters

<-> Parameter Type Description
const sheet Integer Sheet where we are working. It might be different from ActiveSheet.
const row Integer Row (1 based)
const colIndex Integer The index on the column list for the row. (1 based)

Returns

The column (1 based) corresponding to the index when the index is inside the bounds. If the index is <= 0 or the index is > TExcelFile.ColCountInRow(Integer), it will return 0.

Examples

Instead of writing:

var
  RowCount: Int32;
  ColCount: Int32;
  row: Int32;
  col: Int32;
...
  RowCount := xls.RowCount;
  ColCount := xls.ColCount;
  for row := 1 to RowCount do
  begin
    for col := 1 to ColCount do  //It would be faster to use ColCountInRow. See https://doc.tmssoftware.com/flexcel/vcl/guides/performance-guide.html#avoid-calling-colcount
    begin
      DoSomething(row, col);
    end;
  end;

You can use:

var
  RowCount: Int32;
  row: Int32;
  ColCountInRow: Int32;
  colIndex: Int32;
...
  RowCount := xls.RowCount;
  for row := 1 to RowCount do
  begin
    ColCountInRow := xls.ColCountInRow(row);
    for colIndex := 1 to ColCountInRow do
    begin
      DoSomething(row, xls.ColFromIndex(row, colIndex));
    end;

  end;

To loop on all the existing cells on a range you can use:

var
  LastCIndex: Int32;
  XF: Int32;
  cIndex: Int32;
  LastUsedRow: Int32;
...
  // Loop at most until the last used row in the sheet.
  // If LastRow is for example 1.000.000, but there are only
  // 3 used rows, it makes no sense to loop over all the empty rows after row 3.
  LastUsedRow := Math.Min(LastRow, xls.RowCount);

  for row := FirstRow to LastUsedRow do
  begin
    LastCIndex := xls.ColToIndex(row, LastColumn);
    LastColFromIndex := xls.ColFromIndex(row, LastCIndex);
    if (LastColFromIndex > LastColumn) or (LastColFromIndex = 0) then  // LastColumn does not exist.
    begin
      Dec(LastCIndex);
    end;

    if LastCIndex = 0 then  // This row is empty. Move to the next row.
      continue;

    XF := -1;
    for cIndex := xls.ColToIndex(row, FirstColumn) to LastCIndex do
    begin
      DoSomething(row, xls.ColFromIndex(row, cIndex), xls.GetCellValueIndexed(row, cIndex, XF));
    end;

  end;

Note that this example is the implementation of TExcelFile.LoopOverUsedRange, so you might want to directly call TExcelFile.LoopOverUsedRange instead of pasting this example in your code.

See also