Delphi如何打开并读取xls文件,然后写入SQL2000里?(面试题!)(100)

第一次去软件公司面试软件开发,他们给我出了一道题:用Delphi打开并读取D:/1.xls的文件并显示出来,然后把xls的信息写入到SQL 2000里.他们允许我上网找答案(40分钟)在网上搜索了下.找不到满意的结果!请前辈们指教!

http://hjy2099.javaeye.com/blog/236092读取excel数据的，写入到SQL 2000应该很容易找吧

读出显示到DBGRID里不难吧?然后写DBGRID的数据到数据库应该也不难吧!

http://www.delphibbs.com/delphibbs/dispq.asp?lid=1691966

[red]看来还是没有牛人冲出来...[/red]

TMS控件中有一个 AdvStringGrid 控件，可以直接导入、导出 Excel 文件，它的读取 Excel 文件的代码如下，你自己参考一下即可。procedure TAdvStringGrid.LoadXLS(Filename,sheetname:string);{$IFNDEF TMSDOTNET}var FExcel: Variant; FWorkbook: Variant; FWorksheet: Variant; FCell: Variant; FArray: Variant; s,z: Integer; rangestr:string; startstr,endstr:string; code: Integer; sr,er,sc,ec: Integer; strtCol,strtRow: Integer; ulc: Boolean; FOldFixedCols,FOldFixedRows: Integer; OldCursor: TCursor;{$ENDIF}begin {$IFNDEF TMSDOTNET} OldCursor := Screen.Cursor; Screen.Cursor := crHourGlass; try FExcel := CreateOleObject('excel.application'); except Screen.Cursor := OldCursor; raise EAdvGridError.Create('Excel OLE server not found'); Exit; end; try //FExcel.visible:=True; FWorkBook := FExcel.WorkBooks.Open(FileName); if SheetName = '' then FWorkSheet := FWorkBook.ActiveSheet else begin FWorkSheet:=unAssigned; for s := 1 to FWorkbook.Sheets.Count do if FWorkBook.Sheets.Name = SheetName then FWorkSheet := FWorkBook.Sheets; if VarIsEmpty(FWorksheet) then begin Screen.Cursor := OldCursor; raise EAdvGridError.Create('Excel worksheet '+sheetname+' not found'); Exit; end; end; rangestr := FWorkSheet.UsedRange.Address; {$IFDEF TMSDEBUG} DbgStr('Excel used range',rangestr); {$ENDIF} //decode here how many cells are required, $A$1:$D$8 for example startstr := ''; endstr := ''; sc := -1; ec := -1; if Pos(':',rangestr) > 0 then begin startstr := Copy(rangestr,1,pos(':',rangestr)-1); endstr := Copy(rangestr,pos(':',rangestr)+1,255); if pos('$',startstr) = 1 then Delete(startstr,1,1); if pos('$',endstr) = 1 then Delete(endstr,1,1); ulc := not (Pos('$',startstr) > 0); if pos('$',startstr) > 0 then Val(copy(startstr,pos('$',startstr)+1,255),sr,code) else Val(startstr,sr,code); if code <> 0 then sr := -1; if pos('$',endstr) > 0 then Val(copy(endstr,pos('$',endstr)+1,255),er,code) else Val(endstr,er,code); if code <> 0 then er := -1; // now decode the Columns if ulc then begin sc := 1; ec := 256; end else begin if pos('$',startstr) > 0 then startstr := Copy(startstr,1,pos('$',startstr)-1); if pos('$',endstr) > 0 then endstr := Copy(endstr,1,pos('$',endstr) - 1); if startstr <> '' then sc := ord(startstr[1]) - 64; if Length(startstr)>1 then sc := sc * 26 + ord(startstr[2]) - 64; if endstr<>'' then ec := ord(endstr[1]) - 64; if Length(endstr)>1 then ec := ec * 26 + ord(endstr[2]) - 64; end; end else begin sc := 1; sr := 1; ec := 1; er := 1; end; {$IFDEF TMSDEBUG} DbgMsg('Rows from '+inttostr(sr)+' to '+inttostr(er)); DbgMsg('Cols from '+inttostr(sc)+' to '+inttostr(ec)); {$ENDIF} FOldFixedCols := FixedCols; FOldFixedRows := FixedRows; if (sr <> -1) and (er <> -1) and (sc <> -1) and (ec <> -1) then begin ColCount := ec - sc + 1; RowCount := er - sr + 1; end; //farray := VarArrayCreate([1,1 + ec - sc,1,1 + er - sr],varVariant); //rangestr:='A1:'; rangestr := Chr(ord('A') - 1 + sc) + IntToStr(sr)+':'; if (ColCount > 26) then begin rangestr := rangestr + chr(ord('A') - 1 + ((ec - sc) div 26)); rangestr := rangestr + chr(ord('A') + ((ec - sc) mod 26)); end else rangestr := rangestr + Chr(ord('A') - 1 + ec); rangestr := rangestr + IntToStr(er); FArray := FWorkSheet.Range[RangeStr].Value; if FSaveFixedCells then begin strtCol := 0; strtRow := 0; end else begin StrtCol := FOldFixedCols; StrtRow := FOldFixedRows; ColCount := ColCount + FOldFixedCols; RowCount := RowCount + FOldFixedRows; end; if ColCount > FOldFixedCols then FixedCols := FOldFixedCols; if RowCount >FOldFixedRows then FixedRows := FOldFixedRows; for s := 1 to RowCount - StrtRow do begin for z := 1 to ColCount - StrtCol do begin try FCell := FArray[s,z]; if VarIsClear(FCell) then FCell := EmptyStr; if not (VarType(FCell) in [varEmpty,varDispatch,varError]) then LoadCell(z - 1 + StrtCol,s - 1 + StrtRow,FCell); except raise Exception.Create('Incorrect XLS file'); end; end; end; FWorkBook.Close(SaveChanges:=False); finally FExcel.Quit; FExcel := UnAssigned; Screen.Cursor := OldCursor; CellsChanged(Rect(0,0,ColCount,RowCount)); CellsLoaded; end; {$ENDIF}end;

如果要读取并显示,用ado很容易吧.如果是直接导入到sql数据库中,一句话就解决了.SELECT * into aaFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[sheet1$]

procedure TForm1.Button1Click(Sender: TObject);begin ADOQuery2.Active := false; ADOQuery2.SQL.Clear; ADOQuery2.SQL.Add('use MyCAI'); ADOQuery2.SQL.Add('select * into MyTest from OpenRowSet(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=D:/abc.xls'', ''select * from [Sheet1$]'')'); ADOQuery2.ExecSQL; ADOQuery2.SQL.Clear; ADOQuery2.SQL.Add('use MyCAI'); ADOQuery2.SQL.Add('select * from MyTest'); ADOQuery2.Active := true;end;前题是使用SQL的数据库。下面是显示xls里面的数据。procedure TForm1.Button2Click(Sender: TObject);begin ADOConnection1.Connected := false; ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/abc.xls;Extended Properties=Excel 8.0;'; ADOConnection1.Connected := true; ADOQuery2.SQL.Clear; ADOQuery2.SQL.Add('SELECT * FROM [Sheet1$]'); ADOQuery2.ExecSQL; ADOQuery2.Active := true;end;