delphi利用ADO读取Excel 显示在cxgrid

先上代码，不成功的可以在下面评论

1.利用OpenDialog通过用户打开文件得到文件名字
2.得到了文件名字可以拼ADOConnection的String
3.连上数据库 通过ADOQuery.FieldByName 或 ADOQuery.Fields[index] 获得数据
4.TcxGridTableView.DataController.Append获取Index，通过Index来定位cxGrid的具体哪一行，从而展示数据

unit ADOLoadExcel;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData,
  cxDataStorage, cxEdit, DB, cxDBData, StdCtrls, cxGridLevel, cxClasses,
  cxControls, cxGridCustomView, cxGridCustomTableView, cxGridTableView,
  cxGridDBTableView, cxGrid, ExtCtrls, DBCtrls, ADODB, Grids, DBGrids;

type
  TForm1 = class(TForm)
    conn: TADOConnection;
    ADOTabXLS: TADOTable;
    DSXLS: TDataSource;
    NavXLS: TDBNavigator;
    cxgrdlvlGrid1Level1: TcxGridLevel;
    cxgrd1: TcxGrid;
    btn1: TButton;
    dlgOpen1: TOpenDialog;
    GridXLS: TDBGrid;
    qry1: TADOQuery;
    cxgrdtblvw1: TcxGridTableView;
    cxgrdclmn1: TcxGridColumn;
    cxgrdclmn2: TcxGridColumn;
    cxgrdclmn3: TcxGridColumn;
    cxgrdclmn4: TcxGridColumn;
    cxgrdclmn5: TcxGridColumn;
    cxgrdclmn6: TcxGridColumn;
    cxgrdclmn7: TcxGridColumn;
    cxgrdclmn8: TcxGridColumn;
    cxgrdclmn9: TcxGridColumn;
    procedure btn1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btn1Click(Sender: TObject);
const
   cSql = 'select * from [%s]';
var FileName : string;
    flag : Boolean;
    _TableList,AData: TStringList;
    _TableName : string;
    arr1 : array of string;
    _Index : Integer;
begin
  dlgOpen1.Create(nil);
  //指定默认打开路径
  dlgOpen1.InitialDir:='C:\Users\Administrator\桌面';
  dlgOpen1.Filter:='Excel文件|*.xls';
  flag:=dlgOpen1.Execute;
    if not flag then
    begin
      dlgOpen1.Free;
    end;
  FileName:=dlgOpen1.FileName;
  conn := TADOConnection.Create(nil);
  conn.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+FileName+';Extended Properties=excel 8.0;Persist Security Info=False';
  conn.LoginPrompt := False;
  conn.Connected := True;
  //返回Excel里的表名 默认第一张表示Sheet
  _TableList := TStringList.Create;
  conn.GetTableNames(_TableList);
  //SetLength(arr1,9);
  AData := TStringList.Create;
  if _TableList.Count > 0 then
  begin
    _TableName := _TableList.Strings[0]; //+'$'
    qry1.Connection := conn;
    qry1.Close;
    qry1.sql.Text := Format(cSql,[_TableName]);
    qry1.Open;
    DSXLS.DataSet := qry1;
    //GridXLS.Datasource :=DSXLS;
  end;
  if qry1.RecordCount>0 then
  begin
    while not qry1.Eof do
    begin
      AData.Add(qry1.Fields[0].AsString);
      AData.Add(qry1.Fields[1].AsString);
      AData.Add(qry1.Fields[2].AsString);
      AData.Add(qry1.Fields[3].AsString);
      AData.Add(qry1.Fields[4].AsString);
      AData.Add(qry1.Fields[5].AsString);
      AData.Add(qry1.Fields[6].AsString);
      AData.Add(qry1.Fields[7].AsString);
      AData.Add(qry1.Fields[8].AsString);
//      arr1[0] := qry1.Fields[0].Value;
//      arr1[1] := qry1.Fields[1].Value;
//      arr1[2] := qry1.Fields[2].Value;
//      arr1[3] := qry1.Fields[3].Value;
//      arr1[4] := qry1.Fields[4].Value;
//      arr1[5] := qry1.Fields[5].Value;
//      arr1[6] := qry1.Fields[6].Value;
//      arr1[7] := qry1.Fields[7].Value;
//      arr1[8] := qry1.Fields[8].Value;
      if (AData.Strings[0]<>'合计1')and(AData.Strings[0]<>'总计2')and(AData.Strings[0]<>'总计3') then
      begin
        _Index := cxgrdtblvw1.DataController.AppendRecord;
        cxgrdtblvw1.DataController.Values[_Index,0] := AData.Strings[0];
        cxgrdtblvw1.DataController.Values[_Index,1] := AData.Strings[1];
        cxgrdtblvw1.DataController.Values[_Index,2] := AData.Strings[2];
        cxgrdtblvw1.DataController.Values[_Index,3] := AData.Strings[3];
        cxgrdtblvw1.DataController.Values[_Index,4] := AData.Strings[4];
        cxgrdtblvw1.DataController.Values[_Index,5] := AData.Strings[5];
        cxgrdtblvw1.DataController.Values[_Index,6] := AData.Strings[6];
        cxgrdtblvw1.DataController.Values[_Index,7] := AData.Strings[7];
        cxgrdtblvw1.DataController.Values[_Index,8] := AData.Strings[8];
        AData.Clear;
      end;
      qry1.Next;
    end;
    cxgrdtblvw1.DataController.Post;
  end;
end;

end.