Delphi Ado读写Excel文件

Excel包含2003的*.xls格式、2007以上的*.xlsx格式文件

DbGrid标题、文本居中显示

测试环境

Delphi 10.3.3

Win10 专业版

主要代码

uses

 System.StrUtils;

 

procedure TForm1.Button1Click(Sender: TObject);

var

  SL: TStringList;

  i: integer;

begin

  if OpenDialog1.Execute then

    Edit1.Text := OpenDialog1.FileName;

  if Edit1.Text <> '' then

  begin

   ADOConnection1.Connected := False;

   if RightStr(Edit1.Text,1) = 's' then

   ADOConnection1.ConnectionString :=

    'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + Edit1.Text + ';Extended Properties=Excel 8.0' //如果是Excel2003 *.xls文件

   else

   ADOConnection1.ConnectionString :=

    'Provider=Microsoft.Ace.OLEDB.12.0;Data Source=' + Edit1.Text + ';Extended Properties=Excel 12.0'; //如果是Excel2007以上 *.xlsx文件

   ADOConnection1.Connected := True;

    try

      SL := TStringList.Create;

      ComboBox1.clear;

      ADOConnection1.GetTableNames(SL);

      for i := 0 to SL.Count - 1 do

      begin

        ComboBox1.Items.Add(SL.Strings[i])

      end;

      ComboBox1.ItemIndex:= 0;

    finally

      SL.Free;

    end;

  end;

end;

 

procedure TForm1.Button2Click(Sender: TObject);

 var

  i:integer;

begin

  if ComboBox1.Items.Count < 1 then

    exit;

  ComboBox1.ItemIndex:= 0;

  with ADOQuery1 do

  begin

    close;

    Connection := ADOConnection1;

    sql.clear;

    sql.Add('Select * from [' + ComboBox1.Items.Strings[ComboBox1.ItemIndex] + ']');

    open;

  end;

 

  for i := 0 to DBGrid1.Columns.Count -1 do

  begin

  DBGrid1.Columns[i].Width:=100;  //单元格宽度

  DBGrid1.Columns.Items[i].Title.Alignment:=taCenter; //表头居中

  DBGrid1.Columns.Items[i].Alignment:=taCenter; //文本居中

  end;

end;

 

procedure TForm1.FormCreate(Sender: TObject);

var

  SL: TStringList;

  i: integer;

begin

  Path := ExtractFilePath(Application.Exename);

  if FileExists(Path + 'Demo.xls') then

  begin

    Edit1.Text := Path + 'Demo.xls';

    ADOConnection1.ConnectionString :=

      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + Edit1.Text +

      ';Extended Properties=Excel 8.0';

    try

      SL := TStringList.Create;

      ComboBox1.clear;

      ADOConnection1.GetTableNames(SL);

      for i := 0 to SL.Count - 1 do

      begin

        ComboBox1.Items.Add(SL.Strings[i])

      end;

      ComboBox1.ItemIndex:= 0;

    finally

      SL.Free;

    end;

  end;

end;

来源：https://www.amingstudio.com/delphi/663.html