将EXCEL表格导入DBF数据库的实例.docx

下面一段代码是将EXCEL表格导入DBF数据库的实例uses comobj;procedure Texcelfrm.Button1Click(Sender: TObject);varoe:variant;n1:string;i,j:integer;beginoe:=CreateOleObject(Excel.Application);oe.visible:=true;oe.workbooks.open(FileListBox1.FileName);j:=2;/假定标题在第一行try with adoQuery1 dobeginn1:=insert into tablename(fieldslist) values(:fieldslist);close;sql.Clear;sql.Add(n);while oe.worksheetssheetname.cellsj,1.text dobeginfor i:=1 to 列数 doParameters.Itemsi-1.Value:=oe.worksheetssheetname.cellsj,i.text;execsql;j:=j+1;end;oe.visible:=false;showmessage(导入完毕);end;exceptoe.visible:=false;showmessage(数据库错误或有重复编号,请检查);end;end;回复人：gg1335回复时间：2004-8-20 9:05:00下面的代码是导出为EXCELunit Unit1;interfaceusesWindows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,Grids, DBGrids, Db, DBTables, StdCtrls, Excel97, OleServer, ComObj, ActiveX;typeTForm1 = class(TForm)Button1: TButton;Table1: TTable;DataSource1: TDataSource;DBGrid1: TDBGrid;ExcelApplication1: TExcelApplication;ExcelWorkbook1: TExcelWorkbook;ExcelWorksheet1: TExcelWorksheet;SaveDialog1: TSaveDialog;procedure Button1Click(Sender: TObject);procedure FormCreate(Sender: TObject);procedure FormDestroy(Sender: TObject);private Private declarations public Public declarations ExcelFormatNum: TStrings; /ExcelFormatNumExcelFormatStr: TStrings; /ExcelFormatStrfunction ExportDBGrid(DBGrid: TDBGrid; SheetName: string): boolean;/直接保存，不显示EXCELfunction ExportDataToExcelV(SheetName: string; DBGrid: TDBGrid; ExcelApplication: TExcelApplication;ExcelWorkbook: TExcelWorkbook; ExcelWorksheet: TExcelWorksheet): boolean; /显示EXCELfunction ConvertIntToCharacters(IntNumber: Integer): string;function GetNumberFormat(s: string): string; /判断字段的格式function FindExcelFormatStr(s: string): Boolean; /找字符格式function FindExcelFormatNum(s: string): Boolean; /找数字格式end;varForm1: TForm1;implementation$R *.DFMfunction TForm1.ExportDataToExcelV(SheetName: string; DBGrid: TDBGrid; ExcelApplication: TExcelApplication;ExcelWorkbook: TExcelWorkbook; ExcelWorksheet: TExcelWorksheet): boolean; /显示EXCEL/引用：ActiveXvarRow, Col: integer;RowFirst, ColEnd: string;lcid: integer;vNumberFormat: string;beginresult := false;if DBGrid.DataSource = nil thenexit;if DBGrid.DataSource.DataSet = nil thenexit;if DBGrid.DataSource.DataSet.IsEmpty thenexit;tryExcelApplication.Disconnect;exceptend;trytrylcid := 1; /GetUserDefaultLCID;ExcelApplication.ScreenUpdatinglcid := false;ExcelApplication.ConnectKind := ckNewInstance;ExcelApplication.Connect;exceptApplication.MessageBox(系统检测到此机器没有安装EXCEL！如果需要导出功能，请先安装EXCEL！,警告,MB_OK);exit;end;screen.Cursor := crHourGlass;ExcelWorkbook.ConnectTo(ExcelApplication.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid);ExcelWorksheet.ConnectTo(ExcelWorkbook.Worksheets1 as _Worksheet);if SheetName thenExcelWorksheet.Name := SheetName;ExcelWorksheet.Cells.Font.Size := 10;DBGrid.DataSource.DataSet.DisableControls;/导入报头for Col := 1 to DBGrid.Columns.Count doExcelWorksheet.Cells.Item1, Col.value := DBGrid.ColumnsCol - 1.Title.caption;/导入库数据DBGrid.DataSource.DataSet.First;for Col := 1 to DBGrid.Columns.Count dobeginRowFirst := ConvertIntToCharacters(Col) + 1;ColEnd := ConvertIntToCharacters(Col) + inttostr(DBGrid.DataSource.DataSet.RecordCount + 1);if DBGrid.FieldsCol - 1.DataSize 200 thenExcelWorksheet.RangeRowFirst, ColEnd.ColumnWidth := DBGrid.FieldsCol - 1.DataSizeelseExcelWorksheet.RangeRowFirst + 1, ColEnd.ColumnWidth := 21;vNumberFormat := GetNumberFormat(DBGrid.ColumnsCol - 1.Title.Caption);if vNumberFormat thenExcelWorksheet.RangeRowFirst, ColEnd.NumberFormat := vNumberFormat;for Row := 1 to DBGrid.DataSource.DataSet.RecordCount dobeginExcelWorksheet.Cells.ItemRow + 1, Col.value := trim(DBGrid.FieldsCol - 1.AsString);DBGrid.DataSource.DataSet.Next;end;DBGrid.DataSource.DataSet.First;end;ExcelApplication.Visiblelcid := True;ExcelApplication.ScreenUpdatinglcid := true;DBGrid.DataSource.DataSet.EnableControls;result := true;finallyscreen.Cursor := crDefault;end;end;function TForm1.ConvertIntToCharacters(IntNumber: Integer): string;beginif IntNumber 702 thenResult := ZZelsebeginif IntNumber 26 thenbeginif (IntNumber mod 26) = 0 thenResult := Chr(64 + (IntNumber div 26) - 1)elseResult := Chr(64 + (IntNumber div 26);if (IntNumber mod 26) = 0 thenresult := result + chr(64 + 26)elseresult := Result + Chr(64 + (IntNumber mod 26);endelseResult := Chr(64 + IntNumber);end;end;end;function TForm1.GetNumberFormat(s: string): string; /判断字段的格式begins := Uppercase(s);if FindExcelFormatNum(s) thenbeginresult := 0.00;Exit;end;if FindExcelFormatStr(s) thenbeginresult := ;Exit;end;result := ;end;function TForm1.FindExcelFormatStr(s: string): Boolean; /找字符格式vari: integer;beginResult := False;for i := 0 to ExcelFormatStr.Count - 1 dobeginif Pos(ExcelFormatStri, s) 0 thenbeginResult := True;Exit;end;end;end;function TForm1.FindExcelFormatNum(s: string): Boolean; /找数字格式vari: integer;beginResult := False;for i := 0 to ExcelFormatNum.Count - 1 dobeginif Pos(ExcelFormatNumi, s) 0 thenbeginResult := True;Exit;end;end;end;function TForm1.ExportDBGrid(DBGrid: TDBGrid; SheetName: string): boolean;/直接保存，不显示EXCEL/引用：ComObjvarc, r, i, j: integer;app: Olevariant;TempFileName, ResultFileName: string;begintryresult := True;app := CreateOLEObject(Excel.application);app.WorkBooks.Add(xlWBatWorkSheet);exceptApplication.MessageBox(Excel没有正确安装！,警告,MB_OK);result := False;exit;end;SaveDialog1.DefaultExt := xls;SaveDialog1.FileName := SheetName;if SaveDialog1.Execute thenTempFileName := SaveDialog1.FileNameelseExit;app.Workbooks.add;app.Visible := false;Screen.Cursor := crHourGlass;DBGrid.DataSource.DataSet.First;c := DBGrid.DataSource.DataSet.FieldCount;r := DBGrid.DataSource.DataSet.RecordCount;Application.ProcessMessages;for i := 0 to c - 1 doapp.cells(1, 1 + i) := DBGrid.DataSource.DataSet.Fieldsi.DisplayLabel;for j := 1 to r dobeginfor i := 0 to c - 1 doapp.cells(j + 1, 1 + i) := DBGrid.DataSource.DataSet.Fieldsi.AsString;DBGrid.DataSource.DataSet.Next;end;ResultFileName := TempFileName;if ResultFileName = thenResultFileN