批量修改excel文件代码

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, OleServer, Excel2000, ExtCtrls, Grids, DBGrids, DB,
  ADODB;

type
  TForm1 = class(TForm)
    Panel1: TPanel;
    Button1: TButton;
    Button2: TButton;
    Edit1: TEdit;
    ComboBox1: TComboBox;
    ExcelWorksheet1: TExcelWorksheet;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    cb_field: TComboBox;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button2Click(Sender: TObject);
    procedure Edit1KeyPress(Sender: TObject; var Key: Char);
    procedure Panel1Click(Sender: TObject);
  private
    { Private declarations }
    FXls: OleVariant;
    St: OleVariant;
    ConnectStr: String;
  public
    { Public declarations }
  end;

const
   ExcelConnStr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Extended Properties=Excel 8.0;Persist Security Info=False';
var
  Form1: TForm1;

implementation

uses ComObj, StrUtils;

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  op: TOpenDialog;
  i: integer;
begin  
  OP := TOpenDialog.Create(nil);
  try
    Op.Filter := 'Ms Excel(*.xls)|*.xls';
    if not Op.Execute then exit;
    cb_field.Items.Clear;
    ConnectStr := AnsiReplaceStr(ExcelConnStr, '%s', op.FileName);
    FXls.WorkBooks.Add(op.FileName);
  
    St := FXls.ActiveSheet;
    for i := 1 to st.UsedRange.Columns.Count do
      cb_field.Items.Add(st.UsedRange.cells[1, i]);
    ADOQuery1.Close;
    ADOQuery1.ConnectionString := ConnectStr;
    ADOQuery1.SQL.Text := 'select * from ['+ St.Name +'$]';
    ADOQuery1.Open;
  finally
    OP.Free;
  end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  FXls := CreateOleObject('Excel.Application');
  FXls.Visible := FAlse;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  ADOQuery1.Close;
  FXls.quit;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  if (cb_field.ItemIndex > -1) and (Edit1.Text <> '') then
  begin
    ADOQuery1.Close;
    ADOQuery1.SQL.Text := 'update ['+ St.Name +'$] set ' + cb_field.Items.Strings[cb_field.itemIndex] +
                          ' =' + cb_field.Items.Strings[cb_field.itemIndex] + ComboBox1.Text + Edit1.Text;
    try
      ADOQuery1.ExecSQL;
      ShowMessage(cb_field.Items.Strings[cb_field.itemIndex] + '列的所有行都' +ComboBox1.Text + Edit1.Text);
      ADOQuery1.Close;
      ADOQuery1.SQL.Text := 'select * from ['+ St.Name +'$]';
      ADOQuery1.Open
    except
      On E: Exception do
      ShowMessage(E.Message);
    end;
  end;
end;

procedure TForm1.Edit1KeyPress(Sender: TObject; var Key: Char);
begin
  if not (((key >= #48) and (key <= #57)) or (key=#8)) then
    Key := #0;
end;

procedure TForm1.Panel1Click(Sender: TObject);
begin
  DBGrid1.Options := DBGrid1.Options + [dgEditing];
end;

end.