excelimport.pas

将Excel中的数据导入Access数据库

unit ExcelImport;

interface
uses ExcelXP,SysUtils, Variants, Classes, Forms, ZmDialog, Dialogs,  cxGraphics,
cxCustomData, cxStyles, cxTL, cxControls, cxInplaceContainer;

type
  TExcelImportObj = class(TComponent)
  private
    FTreelist: TcxTreelist;
    procedure ImportToExcel;
  public
    constructor Create(aTreelist: TcxTreelist);
  end;

implementation


{ TExcelImportObj }

constructor TExcelImportObj.Create(aTreelist: TcxTreelist);
begin
  FTreelist := aTreelist;
  ImportToExcel;
end;

procedure TExcelImportObj.ImportToExcel;
var
  i, iCol : integer;
  j : integer;
  iExit: Integer;
  EParam : OleVariant;
  DocuType : OleVariant;
  wkbk : _WorkBook;

  OpenDialog: TOpenDialog;
  ExcelApplication: TExcelApplication;
  ExcelWorksheet: TExcelWorksheet;
  ExcelWorkbook: TExcelWorkbook;
  uForm: TForm;
  cNode: TcxTreelistNode;
begin

  iCol := FTreelist.ColumnCount;

  OpenDialog := TOpenDialog.Create(self);
  OpenDialog.InitialDir:=ExtractFileDir(paramstr(0));
  //文件打开的初始路径
  OpenDialog.Execute;

  //防止取消弹出错误
  if OpenDialog.FileName = '' then
  begin
    OpenDialog.Free;
    Exit;
  end;

  //uForm := WaitBox('正在导入Excel数据,请稍候......');
  
  if not Assigned(ExcelApplication) then
    ExcelApplication := TExcelApplication.Create(self);

  Try
    ExcelApplication.Connect;
  Except
    Showmessage('Excel文件打开失败！');
    Exit;
  End;

  ExcelApplication.Visible[0]:=False;
  ExcelApplication.Caption:='Excel数据文件';

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

    wkBk:=ExcelApplication.WorkBooks.Open(opendialog.FileName,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,EmptyParam,EmptyParam,
      EmptyParam,EmptyParam,DocuType,EmptyParam,DocuType);
  except
    begin
      ExcelApplication.Disconnect;//出现异常情况时关闭
      ExcelApplication.Quit;
      showmessage('请选择EXCEL数据表格！');
      exit;
    end;
  end;
  ExcelWorkbook := TExcelWorkbook.Create(self);

  ExcelWorksheet := TExcelWorksheet.Create(self);


  ExcelWorkbook.ConnectTo(ExcelApplication.ActiveWorkbook);//ExcelWorkbook1与Eexcelapplication1建立连接
  ExcelWorksheet.ConnectTo(ExcelWorkbook.Worksheets[1] as _Worksheet);//Excelworksheet1与Excelworkbook1建立连接

  //开始从EXCEL中取数，放到stringgrid中，取完数后关闭EXCEL
  iExit := 0;
  i := 2;
  while iExit < 10 do
  begin
    if trim(excelworksheet.cells.item[i,1])<>'' then
    begin
      cNode := FTreelist.Add;
      iExit := 0;
    end else
      Inc(iExit);

    for j := 1 to iCol do
      if trim(excelworksheet.cells.item[i,1])<>'' then
        cNode.Values[j - 1] := ExcelWorksheet.Cells.Item[i,j];

    Inc(i);
  end;
  ExcelApplication.Disconnect;
  ExcelApplication.Quit;

  OpenDialog.Free;
  ExcelWorkbook.Free;
  ExcelWorksheet.Free;

  //UnWaitBox(uForm);

end;

end.