Axolot Data

Posted: **Mon Mar 19, 2007 8:06 pm**

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

Posted: **Mon Mar 19, 2007 8:51 pm**

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.

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;

Lars Arvidsson

Posted: **Tue Mar 20, 2007 12:58 am**

This looks promising, but I can't compile it. I get the error message:

[Error] DATA.pas(734): Undeclared identifier: '_Int_Cells'

Perhaps I need to declare something in Uses? I now declare "XLSReadWriteII2" and "Cell2".

Posted: **Wed Mar 21, 2007 10:15 am**

Hello

Are you using the latest update of the component?

Lars Arvidsson

Posted: **Wed Mar 21, 2007 1:27 pm**

I'm using version 3.00.00.

Axolot Data

Getting column lengths, and number of columns

Getting column lengths, and number of columns