unit Unit1;

interface

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

type
  TfrmEaM = class(TForm)
    SG1: TStringGrid;
    ADOConnection1: TADOConnection;
    ADOTable1: TADOTable;
    ADOQuery1: TADOQuery;
    OpenDialog1: TOpenDialog;
    ExcelApplication1: TExcelApplication;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    BtnOpenExcel: TButton;
    lb: TLabel;
    BtnOpenMDB: TButton;
    BtnForward: TButton;
    BtnNext: TButton;
    BtnToMDB: TButton;
    BtnToExcel: TButton;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    edtID: TEdit;
    edtName: TEdit;
    edtGender: TEdit;
    edtAge: TEdit;
    procedure BtnOpenExcelClick(Sender: TObject);
    procedure BtnOpenMDBClick(Sender: TObject);
    procedure BtnNextClick(Sender: TObject);
    procedure BtnForwardClick(Sender: TObject);
    procedure SG1Click(Sender: TObject);
    procedure BtnToMDBClick(Sender: TObject);
    procedure BtnToExcelClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmEaM: TfrmEaM;

implementation

{$R *.dfm}

procedure TfrmEaM.BtnOpenExcelClick(Sender: TObject);
var
  i : integer;
  j : integer;
  EParam : OleVariant;
  DocuType : OleVariant;
  wkbk : _WorkBook;
begin
  sg1.RowCount:=1;
  opendialog1.InitialDir:=ExtractFileDir(paramstr(0));
  //文件打開的初始路徑
  opendialog1.Execute;

  Try
    ExcelApplication1.Connect;
  Except
    Showmessage('Excel文件打開失敗！');
    Exit;
  End;

  ExcelApplication1.Visible[0]:=False;
  ExcelApplication1.Caption:='Excel數據文件';

  EParam:=EmptyParam;
  DocuType:=0;
  try
    wkBk:=ExcelApplication1.Workbooks.Add(EParam, DocuType);

    wkBk:=ExcelApplication1.WorkBooks.Open(opendialog1.FileName,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,DocuType,EmptyParam,DocuType);
  except
    begin
      ExcelApplication1.Disconnect;//出現異常情況時關閉
      ExcelApplication1.Quit;
      showmessage('請選擇EXCEL數據表格！');
      exit;
    end;
  end;

  ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);//ExcelWorkbook1與Eexcelapplication1建立連接
  ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1與Excelworkbook1建立連接

  //開始從EXCEL中取數，放到stringgrid中，取完數后關閉EXCEL
  for i:=1 to 10 do
  begin
    for j:=1 to 4 do
    begin
        if trim(excelworksheet1.cells.item[i+1,1])<>'' then
        begin
            sg1.rowCount:=i+2;
            sg1.Cells[j,i]:=ExcelWorksheet1.Cells.Item[i,j];
        end ;
    end;
  end;
    //關閉
    ExcelApplication1.Disconnect;
    ExcelApplication1.Quit;
end;

procedure TfrmEaM.BtnOpenMDBClick(Sender: TObject);
var
  i : integer;
  j : integer;
begin
  //開始從MDB數據表中取數，放到stringgrid中
  sg1.rowcount:=1; 
  adotable1.First;
  while not adotable1.eof do
    begin
      sg1.rowCount:=i+2;
      sg1.Cells[1,i]:=adotable1.fieldbyname('ID').Value;
      sg1.Cells[2,i]:=adotable1.fieldbyname('Name').Value;
      sg1.Cells[3,i]:=adotable1.fieldbyname('Gender').Value;
      sg1.Cells[4,i]:=adotable1.fieldbyname('Age').Value;

      adotable1.Next; 
    end;

  //將第一條數據賦給編輯框
  edtID.text:=sg1.Cells[1,1];
  edtName.text:=sg1.Cells[2,1];
  edtGender.text:=sg1.Cells[3,1];
  edtAge.text:=sg1.Cells[4,1];
end;

procedure TfrmEaM.BtnNextClick(Sender: TObject);
var
  i:integer;
begin
  i:=sg1.row+1;

  if i<>sg1.RowCount then
    begin
      sg1.row:=sg1.row+1;

      edtID.text:=sg1.Cells[1,i];
      edtName.text:=sg1.Cells[2,i];
      edtGender.text:=sg1.Cells[3,i];
      edtAge.text:=sg1.Cells[4,i];

      exit;
    end
  else
    showmessage('已到第一條記錄!');
end;


procedure TfrmEaM.BtnForwardClick(Sender: TObject);
var
  i:integer;
begin
  i:=sg1.row-1;

  if i<>0 then
    begin
      sg1.row:=sg1.row-1;

      edtID.text:=sg1.Cells[1,i];
      edtName.text:=sg1.Cells[2,i];
      edtGender.text:=sg1.Cells[3,i];
      edtAge.text:=sg1.Cells[4,i];

      exit;
    end
  else
    showmessage('已到第一條記錄!');
end;

procedure TfrmEaM.SG1Click(Sender: TObject);
var
  i:integer;
begin
  i:=sg1.row;

  edtID.text:=sg1.Cells[1,i];
  edtName.text:=sg1.Cells[2,i];
  edtGender.text:=sg1.Cells[3,i];
  edtAge.text:=sg1.Cells[4,i];
end;

procedure TfrmEaM.BtnToMDBClick(Sender: TObject);
var
  i : integer;
  j : integer;
  EParam : OleVariant;
  DocuType : OleVariant;
  wkbk : _WorkBook;
begin
  opendialog1.InitialDir:=ExtractFileDir(paramstr(0));
  //文件打開的初始路徑
  opendialog1.Execute;

  Try
    ExcelApplication1.Connect;
  Except
    Showmessage('Excel文件打開失敗！');
    Exit;
  End;

  ExcelApplication1.Visible[0]:=False;
  ExcelApplication1.Caption:='Excel數據文件';

  EParam:=EmptyParam;
  DocuType:=0;
  try
    wkBk:=ExcelApplication1.Workbooks.Add(EParam, DocuType);

    wkBk:=ExcelApplication1.WorkBooks.Open(opendialog1.FileName,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,DocuType,EmptyParam,DocuType);
  except
    begin
      ExcelApplication1.Disconnect;//出現異常情況時關閉
      ExcelApplication1.Quit;
      showmessage('請選擇EXCEL數據表格！');
      exit;
    end;
  end;

  ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);//ExcelWorkbook1與Eexcelapplication1建立連接
  ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1與Excelworkbook1建立連接

  //開始從EXCEL中取數，放到stringgrid中，取完數后關閉EXCEL
  for i:=1 to 10 do
    begin
      if trim(excelworksheet1.cells.item[i+1,1])<>'' then
        begin
          AdoTable1.Append;
          AdoTable1.fieldbyname('ID').value:=ExcelWorksheet1.Cells.Item[i,1];
          AdoTable1.fieldbyname('Name').value:=ExcelWorksheet1.Cells.Item[i,2];
          AdoTable1.fieldbyname('Gender').value:=ExcelWorksheet1.Cells.Item[i,3];
          AdoTable1.fieldbyname('Age').value:=ExcelWorksheet1.Cells.Item[i,4];
          AdoTable1.Post;
        end
    end;

  ExcelApplication1.Disconnect;
  ExcelApplication1.Quit;
  showmessage('數據轉換完畢！');  
end;

procedure TfrmEaM.BtnToExcelClick(Sender: TObject);
var
  i : integer;
  EParam : OleVariant;
  DocuType : OleVariant;
  wkbk : _WorkBook;
  FileName : OleVariant;
begin
  Try
    ExcelApplication1.Connect;
  Except
    Showmessage('Excel文件打開失敗！');
    Exit;
  End;

  ExcelApplication1.Visible[0]:=False;
  ExcelApplication1.Caption:='Excel數據文件';

  EParam:=EmptyParam;
  DocuType:=0;
  try
    wkBk:=ExcelApplication1.Workbooks.Add(EParam, DocuType);
  except
    begin
      ExcelApplication1.Disconnect;//出現異常情況時關閉
      ExcelApplication1.Quit;
      showmessage('創建EXCEL數據表格失敗！');
      exit;
    end;
  end;

  ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);//ExcelWorkbook1與Eexcelapplication1建立連接
  ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1與Excelworkbook1建立連接

  AdoTable1.First;
  i:=1;
  while not AdoTable1.Eof do
    begin
      ExcelWorksheet1.Cells.Item[i,1]:=AdoTable1.fieldbyname('ID').value;
      ExcelWorksheet1.Cells.Item[i,2]:=AdoTable1.fieldbyname('Name').value;
      ExcelWorksheet1.Cells.Item[i,3]:=AdoTable1.fieldbyname('Gender').value;
      ExcelWorksheet1.Cells.Item[i,4]:=AdoTable1.fieldbyname('Age').value;
      AdoTable1.next;
      i:=i+1;
    end;

  wkBk:=ExcelApplication1.ActiveWorkbook;
  DocuType:=0;
  FileName:='d:\mywb.xls';
  wkBk.Close(True, FileName, EmptyParam, DocuType);

  ExcelApplication1.Disconnect;
  ExcelApplication1.Quit;
  showmessage('數據轉換完畢！');
end;

end.