Search Results for

    Show / Hide Table of Contents

    TExcelFile.ColToIndex Method

    Overloads

    • TExcelFile.ColToIndex(Integer, Integer)
    • TExcelFile.ColToIndex(Integer, Integer, Integer)

    TExcelFile.ColToIndex(Integer, Integer)

    This is the inverse of ColFromIndex(Integer, Integer). It will return the index on the internal column array from the row for an existing column. If the column doesn't exist, it will return the index of the "LAST existing column less than col", plus 1. You can use this together with ColCountInRow(Integer) and ColFromIndex(Integer, Integer) to iterate faster on a block. Or you can call LoopOverUsedRange for a method that does the looping for you using those methods.

    Syntax

    Unit: FlexCel.Core

    function TExcelFile.ColToIndex(const row: Integer; const col: Integer): Integer; overload; virtual; abstract;

    Parameters

    <-> Parameter Type Description
    const row Integer Row (1 based)
    const col Integer Column (1 based)

    Returns

    The index on the column list for the row. (1 based)

    Examples

    To loop on all the existing cells 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 LoopOverUsedRange, so you might want to directly call LoopOverUsedRange instead of pasting this example in your code.

    See also

    • TExcelFile
    • ColCountInRow(Integer)
    • ColFromIndex(Integer, Integer)
    • LoopOverUsedRange

    TExcelFile.ColToIndex(Integer, Integer, Integer)

    This is the inverse of ColFromIndex(Integer, Integer). It will return the index on the internal column array from the row for an existing column. If the column doesn't exist, it will return the index of the "LAST existing column less than col", plus 1. You can use this together with ColCountInRow(Integer) and ColFromIndex(Integer, Integer) to iterate faster on a block. Or you can call LoopOverUsedRange for a method that does the looping for you using those methods.

    Syntax

    Unit: FlexCel.Core

    function TExcelFile.ColToIndex(const sheet: Integer; const row: Integer; const col: Integer): Integer; overload; virtual; abstract;

    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 col Integer Column (1 based)

    Returns

    The index on the column list for the row. (1 based)

    Examples

    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 LoopOverUsedRange, so you might want to directly call LoopOverUsedRange instead of pasting this example in your code.

    See also

    • TExcelFile
    • ColCountInRow(Integer)
    • ColFromIndex(Integer, Integer)
    • LoopOverUsedRange
    In This Article
    Back to top FlexCel Studio for VCL and FireMonkey v7.24
    © 2002 - 2025 tmssoftware.com