delphi利用ADO读取Excel 显示在cxgrid

2017-09-08 19:44:53来源:CSDN作者:shenbin6830人点击

分享

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

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

unit ADOLoadExcel;interfaceuses  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.

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台