用OpenDataSource怎么在客户端导入excel数据到SQLServer呢？

Question

晕死了，要辞职的时候才发现这个问题，除了通过把本地目录共享外，还有其它什么办法么？
经理说可以通过ADOQuery先查询，然后再提交到服务器，请问有大虾知道怎么做吗？
这是我的SQL语句：
select top 39 IsNULL( Rtrim(LTrim(ExchAName)),'-1') as ExchAName,IsNULL( Rtrim(LTrim(imDJName)),'-1') as imDJName,IsNULL( Rtrim(LTrim(EA2)),'-1') as EA2,IsNULL( Rtrim(LTrim(imEA3)),'-1') as imEA3,IsNULL( Rtrim(LTrim(EA4)),'-1') as EA4,IsNULL( Rtrim(LTrim(EA5)),'-1') as EA5,IsNULL( Rtrim(LTrim(EA6)),'-1') as EA6,IsNULL( Rtrim(LTrim(EA7)),'-1') as EA7,IsNULL( Rtrim(LTrim(EA8)),'-1') as EA8,IsNULL( Rtrim(LTrim(EA9)),'-1') as EA9,IsNULL( Rtrim(LTrim(EAA)),'-1') as EAA,IsNULL( Rtrim(LTrim(imComeFrom)),'-1') as imComeFrom into #x90skxkImport from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\DOCUME~1\blue\LOCALS~1\Temp\$szLWx0sla\$$$x90skxkImport.xls;User ID=Admin;Password=;Extended properties=Excel 8.0')...[_1$]

Answer 1

好象OpenDataSource不能在客户端导如数据库，除非它们在同一机器上。

Answer 2

unit Unit1;interfaceuses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Excel97, OleServer, ADODB, Db, Grids, StdCtrls;type
  TForm1 = class(TForm)
    Button1: TButton;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    OpenDialog1: TOpenDialog;
    ExcelApplication1: TExcelApplication;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    ADOQuery1Schedule_No: TWideStringField;
    ADOQuery1OrderDate: TWideStringField;
    ADOQuery1Order_no: TWideStringField;
    ADOQuery1Po_No: TWideStringField;
    ADOQuery1Customer: TWideStringField;
    ADOQuery1Part_No: TWideStringField;
    ADOQuery1Qty: TWideStringField;
    ADOQuery1Deliver: TWideStringField;
    ADOQuery1Res: TWideStringField;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;implementation{$R *.DFM}procedure TForm1.Button1Click(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,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:=2 to 10 do
    begin
      if trim(excelworksheet1.cells.item[i,1])<>'' then
        begin
          ADOQuery1.Append;
          ADOQuery1.fieldbyname('Schedule_No').value:=ExcelWorksheet1.Cells.Item[i,1];
          ADOQuery1.fieldbyname('OrderDate').value:=ExcelWorksheet1.Cells.Item[i,2];
          ADOQuery1.fieldbyname('Order_no').value:=ExcelWorksheet1.Cells.Item[i,3];
          ADOQuery1.fieldbyname('Po_No').value:=ExcelWorksheet1.Cells.Item[i,4];
          ADOQuery1.fieldbyname('Customer').value:=ExcelWorksheet1.Cells.Item[i,5];
          ADOQuery1.fieldbyname('Part_No').value:=ExcelWorksheet1.Cells.Item[i,6];
          ADOQuery1.fieldbyname('Qty').value:=ExcelWorksheet1.Cells.Item[i,7];
          ADOQuery1.fieldbyname('Deliver').value:=ExcelWorksheet1.Cells.Item[i,8];
          ADOQuery1.fieldbyname('Res').value:=ExcelWorksheet1.Cells.Item[i,9];
          ADOQuery1.Post;
        end
    end;  ExcelApplication1.Disconnect;
  ExcelApplication1.Quit;
  showmessage('数据导入完毕！');
end;end.

Answer 3

去DELPHIBBS看一下碧血剑的文章就明白了

Answer 4

晕啊，我建了个TADOConnection指向 本地的xls文件，然后这句出错了，说
“SELECT 子句中包含一个保留字、拼写错误或丢失的参数，或标点符号不正确。”
select ExchAName,imDJName,EA2,imEA3,EA4,EA5,EA6,EA7,EA8,EA9,EAA into ##x90skxkImport in [ODBC] [ODBC;Driver=SQL Server;UID=sa;PWD=;Server=192.168.0.7;DataBase=TGateway;] from [_1$]我这样用有问题吗？谢谢指点一下了 :)

Answer 5

select top 39 ExchAName,imDJName,EA2,imEA3,EA4,EA5,EA6,EA7,EA8,EA9,EAA into  [ODBC;Driver=SQL Server;UID=sa;PWD=;Server=192.168.0.7;DataBase=TGateway;].##x90skxkImport from [_1$]
这样就行了，谢谢 zhlwyy(海龙-->有志者事竟成！) 的指点啊 :)