TXlsFile.ColToIndex Method
Overloads
TXlsFile.ColToIndex(Integer, Integer)
This is the inverse of TExcelFile.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 TExcelFile.ColCountInRow(Integer) and TExcelFile.ColFromIndex(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.ColToIndex(const row: Integer; const col: Integer): Integer; overload; override;
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 TExcelFile.LoopOverUsedRange, so you might want to directly call TExcelFile.LoopOverUsedRange instead of pasting this example in your code.
See also
- TXlsFile
- TExcelFile.ColCountInRow(Integer)
- TExcelFile.ColFromIndex(Integer, Integer)
- TExcelFile.LoopOverUsedRange
TXlsFile.ColToIndex(Integer, Integer, Integer)
This is the inverse of TExcelFile.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 TExcelFile.ColCountInRow(Integer) and TExcelFile.ColFromIndex(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.ColToIndex(const sheet: Integer; const row: Integer; const col: 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 | 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 TExcelFile.LoopOverUsedRange, so you might want to directly call TExcelFile.LoopOverUsedRange instead of pasting this example in your code.