|
|
 |
|
|
|
|
|
|
Join the Discussion
|
"Post your views, comments, questions and doubts to this article."
Discuss!
|
|
|
|
|
|
|
|
|
After we have finished the discussion on connecting to an Excel workbook, retrieving sheet data, it's time for a quick look into Excel data types (as provided by ADO). This page also discusses the editing of an Excel work sheet using Ado and Delphi.
A "story" about column data types
In traditional database application, Delphi provides us with a TField object - it encapsulates the fundamental behavior common to all field components. Field components are non-visual objects that represent fields of the dataset at run (and design) time.
Contrary to the traditional (relational database) approach, when using Excel as the datasource for you application, there is no direct way to specify the data types for columns in retrieved Excel ranges (sheets).
Excel as a data "provider", does not provide ADO with the information about the data it contains. The Jet must scan through the referenced range to guess the type of data it caries (the formatting of the cells does not count). The number of rows Jet scans is 8, by default. If you need more rows to be examined (before field types are determined), you can add a "MaxScanRows=X" to the connection string, where X specifies the value from 0 to sixteen 16. A value of zero tells the Jet to scan all existing rows.
Caution: Some problems may occur if you have mixed string values with numeric values "under" the same column. If this is the case, the Jet could return mixed null-string or null-number values.
Now, that we know possible problems, let's see what field types are available when working with Excel data.
Excel data types
The easies way to see what data types the retrieved Excel range consist of is to use the properties and methods of Delphi's TField object. In our project, this is done in the GetFieldInfo procedure (I suppose you remember we are creating an Excel spreadsheet editor using Delphi and ADO). Recall that the GetFieldInfo is called inside the button's "Re-fetch" OnClick event handler. This is how the GetFieldInfo procedure looks:
uses typinfo; //don't forget
...
procedure TForm1.GetFieldInfo;
var
i : integer;
ft : TFieldType;
sft : string;
fname : string;
begin
ListBox1.Clear;
for i := 0 to AdoQuery1.Fields.Count - 1 do
begin
ft := AdoQuery1.Fields[i].DataType;
sft := GetEnumName(TypeInfo(TFieldType), Integer(ft));
fname:= AdoQuery1.Fields[i].FieldName;
ListBox1.Items.Add(Format('%d) NAME: %s TYPE: %s,
[1+i, fname, sft]));
end;
end;
|
The result of this procedure call is visible here:
As you can see from the picture above, field types returned by the Jet are those expected. However, every field in an Excel data source can be only one of the following:
- Numeric, ADO type adDouble, Delphi type ftFloat - precision 15,
- Text, ADO type adVarChar, Delphi type ftWideString - max length 255,
- Memo, ADO type adVarWChar, Delphi type ftMemo,
- Currency, ADO type adCurrency, Delphi type ftCurrency,
- Boolean, ADO type adBoolean, Delphi type ftBoolean,
- Date, ADO type adDate, Delphi type ftDateTime,
This concludes the discussion on Excel columns, and data types it provides when queried by the Jet. I think we are now more that ready to see how to actually edit the Excel data from a Delphi application...
Note: given you previous Delphi knowledge, you could expect the field "Function" to act as a calculated field, however this is not the case; as you will see on the next page.
Next page > How to modify Excel sheets: edit, add, and delete rows > Page 1, 2, 3, 4, 5, 6, 7
|
