main.~pas

按一定格式把excel表格导入SQL Server数据库表

unit main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxControls, cxSSheet, StdCtrls, ExtCtrls, DB, ADODB;

type
  TFrmImExcel = class(TForm)
    OpnDlgExcel: TOpenDialog;
    Panel1: TPanel;
    btnOpenExcel: TButton;
    btnImport: TButton;
    Panel2: TPanel;
    cxSpreadSheetBook1: TcxSpreadSheetBook;
    ADOQ: TADOQuery;
    procedure btnOpenExcelClick(Sender: TObject);
    procedure btnImportClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FrmImExcel: TFrmImExcel;

implementation
uses
  pubDB;
{$R *.dfm}

procedure TFrmImExcel.btnOpenExcelClick(Sender: TObject);
var
  strFileName:String;
begin
  try
    if OpnDlgExcel.Execute then
    begin
      {Application.CreateForm(TfrmOpenExcelWait, frmOpenExcelWait);
      frmOpenExcelWait.Caption:='打开导入文件';
      frmOpenExcelWait.Label1.Caption:='正在打开导入文件,请稍候...';
      frmOpenExcelWait.Show;
      Application.ProcessMessages;
      frmOpenExcelWait.Update; }

      strFileName := OpnDlgExcel.FileName;

      cxSpreadSheetBook1.LoadFromFile(strFileName);

    end;
  except
    ;
  end;
end;

procedure TFrmImExcel.btnImportClick(Sender: TObject);
var
  i:integer;
  AgentID: string;
  State:string;
begin
  for i:=2 to cxSpreadSheetBook1.ActiveSheet.RowCount-1 do
  begin
    if Trim(cxSpreadSheetBook1.ActiveSheet.GetCellObject(0,i).DisplayText) <> '' then
    begin
      Try
        ADOQ.Close;
        ADOQ.SQL.Clear;
        DBpub.ADOCon.BeginTrans;
        if Trim(cxSpreadSheetBook1.ActiveSheet.GetCellObject(10,i).DisplayText)='鞍山' then
          AgentID:='12'
        else
          AgentID:='11';
        try
          if  strtoint(Trim(cxSpreadSheetBook1.ActiveSheet.GetCellObject(5,i).DisplayText))<=0 then
            State := '0'
          else
            State := '1';
        except
          State := '0';
        end;
        ADOQ.SQL.Text := ' insert into Telecard_User (UserID,PassWord,AgentID,Balance,GN,BindNumber,Valid,GJ,State) '
                       + ' values ('''+cxSpreadSheetBook1.ActiveSheet.GetCellObject(1,i).DisplayText+''', '
                       + ' '''+cxSpreadSheetBook1.ActiveSheet.GetCellObject(3,i).DisplayText+''', '
                       + ' '''+AgentID+''','''+cxSpreadSheetBook1.ActiveSheet.GetCellObject(4,i).DisplayText+''', '
                       + ' 1, '''+cxSpreadSheetBook1.ActiveSheet.GetCellObject(2,i).DisplayText+''','
                       + ' '''+cxSpreadSheetBook1.ActiveSheet.GetCellObject(5,i).DisplayText+''',0,'''+State+''') ';
        ADOQ.ExecSql;

        if DBpub.ADOCon.Errors.Count = 0 then
        begin
           DBpub.ADOCon.CommitTrans;
           //uCALLID := '';
        end
        else begin
           DBpub.ADOCon.RollbackTrans;
           //gc_PublicCode.Writelog('数据库错误，出错位置：FormClose过程;'+'[SQL语句]：'+DBpub.adoQry.SQL.Text+'。') ;
           Application.Messagebox('操作失败，请与系统管理员联系！',PChar('操作失败'),MB_OK+MB_ICONINFORMATION+MB_TASKMODAL);
           //b_ReConn := true;
           //Exit;
        end;
      Except on E : Exception do
         begin
           try
             DBpub.ADOCon.RollbackTrans;
           except

           end;
           //gc_PublicCode.Writelog('数据库错误，出错位置：FormClose过程,'+E.Message+'[SQL语句]：'+DBpub.adoQry.SQL.Text+'。') ;
           Application.Messagebox('操作失败，请与系统管理员联系！',PChar('操作失败'),MB_OK+MB_ICONINFORMATION+MB_TASKMODAL);
           //b_ReConn := true;
         end;
      End;
    end;
  end;
  Application.Messagebox('导入完毕！',PChar('导入'),MB_OK+MB_ICONINFORMATION+MB_TASKMODAL);
end;

end.