我在做EXCEL导入SQL数据库中,EXCEL中有多个表怎么操作?

Question

我在做EXCEL导入SQL数据库中,EXCEL中有多个表,都要导入SQL,可是只能导入第一个工作表,其它的工作表,根本没导入数据库?
procedure TCostForm.Button2Click(Sender: TObject);
var k,i,j:integer;
sheetcount:integer;
begin
//opendialog1.InitialDir:=ExtractFileDir(paramstr(0));//文件的打存放初始路径
opendialog1.Execute;
Try
  ExcelApplication1.Connect;//EXCEL应用程序
Except
  MessageDlg('Excel may not be installed',mtError, [mbOk], 0);
  Abort;
End;
ExcelApplication1.Visible[0]:=false;//True;  //是否显示EXCEL
ExcelApplication1.Caption:='Excel Application';
try
  excelapplication1.Workbooks.Open(opendialog1.FileName,
  null,null,null,null,null,null,null,null,null,null,null,null,null,null,0);//打开指定的EXCEL 文件
except
begin
  ExcelApplication1.Disconnect;//出现异常情况时关闭
  ExcelApplication1.Quit;showmessage('请选择EXCEL电子表格!');
  exit;
end;
end;
Try
//ExcelWorkbook1与Eexcelapplication1建立连接
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
sheetcount:=ExcelWorkbook1.Sheets.Count;
 //Excelworksheet1与Excelworkbook1建立连接for k:=1 to sheetcount+1 do
begin
 ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[k] as _Worksheet);
//  excelworksheet1.Activate(k);
//  ExcelApplication1.Workbooks[k].Activate;
  ExcelApplication1.Workbooks[k].ActiveSheet;
  adoquery1.Active:=true;
  for i:=3 to 1000 do  //最大取值1000
  begin
    if trim(excelworksheet1.Cells.Item[i,1])<>'' then
    begin
      adoquery1.Append;
      for j:=2 to 51 do   //
         adoquery1.Fields[j-2].Value:=ExcelWorksheet1.Cells.Item[i,j];
      adoquery1.Post;
    end
    else exit;
  end;
  excelworksheet1.Disconnect;
end;
finally
  ExcelApplication1.Disconnect;
  ExcelApplication1.Quit;
end;end;

Answer 1

还有,怎么获得EXCEL文档中的图片

Answer 2

用sql可将execl表直接导入到sqlserver中,见
http://www.delphibbs.com/keylife/iblog_show.asp?xid=19064
关于图片,没用过,不过好像XLSReadWriteII可以做到

Answer 3

的确,看了一下,XLSReadWriteII的Samples中有ReadPicture/WritePicture的例子
XLSReadWriteII是一套非常好的操作excel的控件,它是基于文件的,效率要比OLE的方式高很多,且不要求在系统上安装office,在delphi盒子上有下载

Answer 4

zczb(zczb):
我就是用循环写,可是不行,也不知哪错了

Answer 5

这是循环:可是它只能导出第一个工作表
for k:=1 to sheetcount+1 do
begin
 ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[k] as _Worksheet);
//  excelworksheet1.Activate(k);
//  ExcelApplication1.Workbooks[k].Activate;
  ExcelApplication1.Workbooks[k].ActiveSheet;
  adoquery1.Active:=true;
  for i:=3 to 1000 do  //最大取值1000
  begin
    if trim(excelworksheet1.Cells.Item[i,1])<>'' then
    begin
      adoquery1.Append;
      for j:=2 to 51 do   //
         adoquery1.Fields[j-2].Value:=ExcelWorksheet1.Cells.Item[i,j];
      adoquery1.Post;
    end
    else exit;
  end;
  excelworksheet1.Disconnect;
end;

Answer 6

你参考一下
//导入Excel文件
Function TImportForm.ImportExcel(const FileName:string; var ImportRec:TImportRec):integer;
var eclApp,WorkBook, sheet:olevariant; i, j, n, m, k, Colcount, Rowcount:integer; s:string;
    procedure xlsend;
    begin
        eclapp.quit;
        sheet:=Unassigned;
        workbook:=Unassigned;
        eclapp:=Unassigned;
        self.ProgressBar1.Visible:=false;
        screen.Cursor:=0;
        self.StatusBar1.Panels[0].Text:=inttostr(self.StringGrid1.RowCount-1)+' Records';
        self.StatusBar1.Panels[2].Text:=FileName;
    end;
begin
    result:=0;
    self.StatusBar1.Panels[0].Text:='Running...';
    if not fileexists(FileName) then begin Result:=1050; exit; end;
    try
      eclapp:=CreateOleObject('excel.application');
      workbook:=eclapp.workbooks.open(FileName);
    except
      Result:=1051;                                                //没有安装Excel
      xlsend;
      exit;
    end;
    
    try
        n:=workbook.Sheets.Count;
        setlength(ImportRec.InFields,n);
        for i:=1 to n do
        begin
            sheet:=workbook.sheets[i];
            ImportRec.InFields[i-1].SheetName:=sheet.name;
            k:=sheet.usedrange.columns.count;
            j:=1;
            while j<=k do
            begin
                s:=sheet.Cells[1,j].value;
                setlength(ImportRec.InFields[i-1].Fields,j);
                ImportRec.InFields[i-1].Fields[j-1].ColIndex:=j;
                ImportRec.InFields[i-1].Fields[j-1].Caption:=s;
                inc(j);
            end;
        end;
    except
        xlsend;
    end;
    if not GetImportField.GetImportExcel(ImportRec,FileName) then begin xlsend; exit; end;    try
        sheet:=workbook.sheets[ImportRec.SheetIndex+1];     //可以在这里加循环
        Colcount:=length(ImportRec.Fields);
        Rowcount:=sheet.usedrange.rows.count;
        self.StringGrid1.ColCount:=Colcount+3;
        self.StringGrid1.RowCount:=Rowcount;
        self.StringGrid1.ColWidths[0]:=10+length(inttostr(Rowcount))*5;
        screen.Cursor:=-11;
        self.ProgressBar1.Visible:=true;
        self.ProgressBar1.Max:=Rowcount;
        application.ProcessMessages;        for i:=1 to Rowcount do
        begin
            self.StringGrid1.Cells[0,i]:=inttostr(i);
            self.StringGrid1.Cells[1,i]:='1';
        end;
        self.ProgressBar1.Position:=Rowcount div (Colcount+2);        for i:=0 to Colcount-1 do
        begin
            for j:=1 to Rowcount do
            begin
                if ImportRec.Fields[i].ImportIndex>0 then
                   self.StringGrid1.Cells[i+3,j]:=sheet.Cells[j+1,ImportRec.Fields[i].ImportIndex].value
                else
                   self.StringGrid1.Cells[i+3,j]:='';
            end;
            self.ProgressBar1.Position:=Rowcount*(i+3) div (Colcount+3);
            application.ProcessMessages;            
        end;
    finally
        self.ProgressBar1.Position:=self.ProgressBar1.Max;
        xlsend;
    end;
end;

Answer 7

XLSREADWRITEii有问题:
XLSReadWriteII 我下了,已装上了,可是我一打开它的例子SAMPLES\READPICTURE这个例子DELPHI就自动关闭?
我一把它的图标拖到FORM中DLEPHI就自动关闭了!