Reading only the first row of a file
Sometimes you need to process a big number of files depending on their data in the first rows. If the data isn't what you want, you skip the file.
For those cases, it might make sense not to load the full file in memory, just to read the cell A1 and then close it. And FlexCel has the right tool for the job: Virtual Mode
Virtual mode is described in more detail in the Performance Guide, but in this tip we will just give you the code to read a file and stop reading after the first cell.
You can also find this example in the TExcelFile.VirtualMode documentation.
type
TVirtualCellReader = class
public
procedure OnCellRead(const sender: TObject; const e: TVirtualCellReadEventArgs);
end;
{ TVirtualCellReader }
procedure TVirtualCellReader.OnCellRead(const sender: TObject;
const e: TVirtualCellReadEventArgs);
begin
if (e.Cell.Row > 1) then
begin
if e.Cell.Sheet < Length(e.SheetNames) then e.NextSheet := e.SheetNames[e.Cell.Sheet]
else e.NextSheet := ''; // Stop reading the file.
end
else
begin
WriteLn('Cell: ', TCellAddress.Create(e.SheetNames[e.Cell.Sheet - 1], e.Cell.Row, e.Cell.Col, false, false).CellRef);
WriteLn('Value: ', e.Cell.Value.ToString.ToString);
end;
end;
...
procedure ReadFirstRowOfAllSheets(const fileName: String);
var
xls: TXlsFile;
CellReader: TVirtualCellReader;
begin
xls := TXlsFile.Create; //Create the object but don't open the file yet.
try
CellReader := TVirtualCellReader.Create; // Create a CellReader to handle reading the cells.
try
xls.VirtualMode := true; //Set the mode to Virtual.
xls.VirtualCellRead:= CellReader.OnCellRead; //Assign the CellReader to the TXlsFile.
xls.Open(fileName);
finally
CellReader.Free;
end;
finally
xls.Free;
end;
end;