Delphi专题 ·

Delphi使用ADO读写Excel文件

Delphi使用ADO读写Excel文件 Delphi专题 第1张

功能

  • Delphi Ado读写Excel文件
  • Excel包含2003的*.xls格式、2007以上的*.xlsx格式文件
  • DbGrid标题、文本居中显示

测试环境

  • Delphi 10.3.3
  • Win10 专业版

主要代码

//codeby amingstudio.com 转载请注明出处
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;

下载地址

完整源代码可在下方『相关下载』模块中下载

相关下载

点击下载

参与评论