I can't just import an Excel sheet as it is. I must check each cell to determine if it contains numerical data, which I then import into arrays (type double, in D7). The only way I see to do that is to check each cell in an Excel sheet, column by column -- up to the maximum number of data points my arrays will hold (at present, 10000), and the maximum number of arrays (at present 100). Which means I must cycle through a 10000 x 100 block (or matrix) in the Excel sheet, cell by cell, checking to see if I want to import what's in that cell. XLS does that fairly quickly. But, if I substantially expand the size of my arrays (as I will in the final product), it does take a bit too long.
This is mostly unnecessary because the data in the Excel sheet are generally only of moderate size: typically, a few hundred data points (rows), in only a few columns. Importing those data would be much more efficient if I could ascertain, in the code which governs the import, how many cells in each column contain anything. Then I only need to check those cells. In other words, what is the length of the column in the Excel sheet? Length meaning the final row after which all cells are empty. (I wouldn't want to automatically stop at the first empty cell -- it might be "missing data", with more data in subsequent cells.)
This would also determine if a column is completely empty, so I could skip it. And it would be nice to identify the last column which contains any data.
Can this be done?
Thanks,
Ben Crain
Getting column lengths, and number of columns
Hello
As the cell data in the component is stored in a hash table, you can iterate trough this table. This is much quicker than scan trough a cell grid.
This is not documented, so it's not easy to find.
Here is an example.
Lars Arvidsson
As the cell data in the component is stored in a hash table, you can iterate trough this table. This is much quicker than scan trough a cell grid.
This is not documented, so it's not easy to find.
Here is an example.
Code: Select all
var
Cell: TCell;
begin
XLS[0]._Int_Cells.BeginIterate;
repeat
Cell := XLS[0]._Int_Cells.GetNext;
// Is it a numeric cell?
// ctNumberFormula is formula cells that returns a numeric value.
if (Cell <> Nil) and (Cell.CellType in [ctInteger,ctFloat,ctNumberFormula]) then begin
// Do I want this cell?
if (Cell.Col = 1) and (Cell.Row = 2) then begin
// ...
end;
end;
until (Cell = Nil);
end;