曾经做过一个个通用的,用到 DevExpres 控件,DevExpres 其中关键的部分://打开数据库表,读入字段名到 combox procedure TfrmImportFromExcel.FormShow(Sender: TObject); var tmpTcxComboBox1,tmpTcxComboBox2 : TcxComboBoxProperties ; i : integer ; begin inherited ; //自动选择目标字段 if (gridDataset<>nil) and (gridDataset.Active=True) then begin if cxGridImportMapDBTableView1F_DESTFIELD.PropertiesClass =TcxComboBoxProperties then begin tmpTcxComboBox1:=TcxComboBoxProperties(cxGridImportMapDBTableView1F_DESTFIELD.Properties) ; tmpTcxComboBox1.Items.Clear ; tmpTcxComboBox2:=TcxComboBoxProperties(cxGridImportMapDBTableView1F_DESTFIELDCAPTION.Properties) ; tmpTcxComboBox2.Items.Clear ; for i:=0 to gridDataset.FieldCount -1 do begin tmpTcxComboBox1.Items.Add( gridDataset.Fields[i].FieldName ); tmpTcxComboBox2.Items.Add( gridDataset.Fields[i].DisplayName ); end; end; end; SetButtonStatus ; end; //打开一个 excel 文件,然后把表头读入 另一个 combox procedure TfrmImportFromExcel.ActOpenExcelExecute(Sender: TObject); var i,j : integer ; tmpTcxComboBox1,tmpTcxComboBox2 : TcxComboBoxProperties ; begin ImportGridDialog.DefaultExt :='xls'; ImportGridDialog.Filter :='excel file|*.xls'; if ImportGridDialog.Execute then begin dxMemDataImportMap.Active:=False ; varSheet:=unAssigned ; varApp:=unAssigned ; try varSheet := CreateOleObject( 'Excel.Sheet' ) ; except InfoOk( '系统未安装 Excel 系统!' ) ; Exit ; end ; try varApp := varSheet.Application ; varApp.WorkBooks.Open( ImportGridDialog.FileName ); varSheet := varApp.ActiveSheet ; except InfoOk( '错误的 Excel 文件' ) ; varApp:=unAssigned ; SetButtonStatus ; Exit ; end; tmpTcxComboBox1:=TcxComboBoxProperties(cxGridImportMapDBTableView1F_DESTFIELD.Properties) ; tmpTcxComboBox2:=TcxComboBoxProperties(cxGridImportMapDBTableView1F_DESTFIELDCAPTION.Properties) ; dxMemDataImportMap.DisableControls ; dxMemDataImportMap.Active:=True ; try For i := 1 To 255 Do Begin If String( VarSheet.Cells[1,I].Value )<>'' Then Begin dxMemDataImportMap.Append ; dxMemDataImportMap.FieldbyName('F_SOURCEFIELD').Value := varSheet.Cells[1,i] ; dxMemDataImportMap.FieldbyName('F_CHECKED').Value := False ; For j:=0 To TmpTcxComboBox1.Items.Count-1 Do Begin If ( dxMemDataImportMap.FieldbyName('F_SOURCEFIELD').Value=tmpTcxComboBox1.Items[j] )Or( dxMemDataImportMap.FieldbyName('F_SOURCEFIELD').Value=tmpTcxComboBox2.Items[j] ) Then Begin dxMemDataImportMap.FieldbyName( 'F_DESTFIELD' ).Value := tmpTcxComboBox1.Items[j] ; dxMemDataImportMap.FieldbyName( 'F_DESTFIELDCAPTION' ).Value := tmpTcxComboBox2.Items[j] ; dxMemDataImportMap.FieldbyName( 'F_CHECKED' ).Value := True ; End ; End ; dxMemDataImportMap.Post ; End Else Begin Break ; End ; End ; except end; dxMemDataImportMap.EnableControls ; //在这里不能关闭 Excel , 导入时需要用到它 end; SetButtonStatus ; end;//执行真正的导入 procedure TfrmImportFromExcel.ActApplyExecute(Sender: TObject); var i,j : integer ; okRows,totalRows : integer ; begin if dxMemDataImportMap.Active=True then begin //行数,最多支持 65535 行的导入,要从第二行开始,第一行是字段名 dxMemDataImportMap.DisableControls ; okRows:=0 ; totalRows := 0 ; For i:=2 To 65535 Do Begin Inc( totalRows ) ; If String( VarSheet.Cells[I,1].value )='' Then Break ; End ; ProgressBar1.Visible := True ; ProgressBar1.Properties.Max := totalRows-1 ; ProgressBar1.Position :=0; for i:=2 to totalRows do begin //以每一行的第一列是否为空作为终止条件 if String(varSheet.Cells[i,1].Value)='' then begin break ; end; //处理一行 try gridDataset.Append ; j:=1 ; dxMemDataImportMap.First ; while not dxMemDataImportMap.Eof do begin if (dxMemDataImportMap.FieldbyName('F_CHECKED').Value=True) and (dxMemDataImportMap.FieldbyName('F_DESTFIELD').AsString<>'') then begin try gridDataset.FieldByName(dxMemDataImportMap.FieldbyName('F_DESTFIELD').AsString).Value :=String(varSheet.Cells[i,j].Value) ; except end; end; Inc(j); dxMemDataImportMap.Next ; end; gridDataset.Post ; Inc(okRows); except gridDataset.Cancel ; end; ProgressBar1.Position:=ProgressBar1.Position+1; if i div 10=0 then begin Self.Update ; end; end; dxMemDataImportMap.EnableControls ; InfoOk( '成功导入 '+InttoStr(okRows)+'行 !' ) ; Self.Close ; end else begin InfoOk( '未有数据 !' ) ; end; end;
其中关键的部分://打开数据库表,读入字段名到 combox
procedure TfrmImportFromExcel.FormShow(Sender: TObject);
var
tmpTcxComboBox1,tmpTcxComboBox2 : TcxComboBoxProperties ;
i : integer ;
begin
inherited ;
//自动选择目标字段
if (gridDataset<>nil) and (gridDataset.Active=True) then
begin
if cxGridImportMapDBTableView1F_DESTFIELD.PropertiesClass =TcxComboBoxProperties then
begin
tmpTcxComboBox1:=TcxComboBoxProperties(cxGridImportMapDBTableView1F_DESTFIELD.Properties) ;
tmpTcxComboBox1.Items.Clear ;
tmpTcxComboBox2:=TcxComboBoxProperties(cxGridImportMapDBTableView1F_DESTFIELDCAPTION.Properties) ;
tmpTcxComboBox2.Items.Clear ;
for i:=0 to gridDataset.FieldCount -1 do
begin
tmpTcxComboBox1.Items.Add( gridDataset.Fields[i].FieldName );
tmpTcxComboBox2.Items.Add( gridDataset.Fields[i].DisplayName );
end;
end;
end;
SetButtonStatus ;
end;
//打开一个 excel 文件,然后把表头读入 另一个 combox
procedure TfrmImportFromExcel.ActOpenExcelExecute(Sender: TObject);
var
i,j : integer ;
tmpTcxComboBox1,tmpTcxComboBox2 : TcxComboBoxProperties ;
begin
ImportGridDialog.DefaultExt :='xls';
ImportGridDialog.Filter :='excel file|*.xls';
if ImportGridDialog.Execute then
begin
dxMemDataImportMap.Active:=False ;
varSheet:=unAssigned ;
varApp:=unAssigned ;
try
varSheet := CreateOleObject( 'Excel.Sheet' ) ;
except
InfoOk( '系统未安装 Excel 系统!' ) ;
Exit ;
end ;
try
varApp := varSheet.Application ;
varApp.WorkBooks.Open( ImportGridDialog.FileName );
varSheet := varApp.ActiveSheet ;
except
InfoOk( '错误的 Excel 文件' ) ;
varApp:=unAssigned ;
SetButtonStatus ;
Exit ;
end;
tmpTcxComboBox1:=TcxComboBoxProperties(cxGridImportMapDBTableView1F_DESTFIELD.Properties) ;
tmpTcxComboBox2:=TcxComboBoxProperties(cxGridImportMapDBTableView1F_DESTFIELDCAPTION.Properties) ; dxMemDataImportMap.DisableControls ;
dxMemDataImportMap.Active:=True ; try
For i := 1 To 255 Do
Begin
If String( VarSheet.Cells[1,I].Value )<>'' Then
Begin
dxMemDataImportMap.Append ;
dxMemDataImportMap.FieldbyName('F_SOURCEFIELD').Value := varSheet.Cells[1,i] ;
dxMemDataImportMap.FieldbyName('F_CHECKED').Value := False ;
For j:=0 To TmpTcxComboBox1.Items.Count-1 Do
Begin
If ( dxMemDataImportMap.FieldbyName('F_SOURCEFIELD').Value=tmpTcxComboBox1.Items[j] )Or( dxMemDataImportMap.FieldbyName('F_SOURCEFIELD').Value=tmpTcxComboBox2.Items[j] ) Then
Begin
dxMemDataImportMap.FieldbyName( 'F_DESTFIELD' ).Value := tmpTcxComboBox1.Items[j] ;
dxMemDataImportMap.FieldbyName( 'F_DESTFIELDCAPTION' ).Value := tmpTcxComboBox2.Items[j] ;
dxMemDataImportMap.FieldbyName( 'F_CHECKED' ).Value := True ;
End ;
End ;
dxMemDataImportMap.Post ;
End
Else
Begin
Break ;
End ;
End ;
except
end;
dxMemDataImportMap.EnableControls ;
//在这里不能关闭 Excel , 导入时需要用到它
end;
SetButtonStatus ;
end;//执行真正的导入
procedure TfrmImportFromExcel.ActApplyExecute(Sender: TObject);
var
i,j : integer ;
okRows,totalRows : integer ;
begin
if dxMemDataImportMap.Active=True then
begin
//行数,最多支持 65535 行的导入,要从第二行开始,第一行是字段名
dxMemDataImportMap.DisableControls ;
okRows:=0 ;
totalRows := 0 ;
For i:=2 To 65535 Do
Begin
Inc( totalRows ) ;
If String( VarSheet.Cells[I,1].value )='' Then Break ;
End ;
ProgressBar1.Visible := True ;
ProgressBar1.Properties.Max := totalRows-1 ;
ProgressBar1.Position :=0;
for i:=2 to totalRows do
begin
//以每一行的第一列是否为空作为终止条件
if String(varSheet.Cells[i,1].Value)='' then
begin
break ;
end;
//处理一行
try
gridDataset.Append ;
j:=1 ;
dxMemDataImportMap.First ;
while not dxMemDataImportMap.Eof do
begin
if (dxMemDataImportMap.FieldbyName('F_CHECKED').Value=True) and
(dxMemDataImportMap.FieldbyName('F_DESTFIELD').AsString<>'') then
begin
try
gridDataset.FieldByName(dxMemDataImportMap.FieldbyName('F_DESTFIELD').AsString).Value :=String(varSheet.Cells[i,j].Value) ;
except
end;
end;
Inc(j);
dxMemDataImportMap.Next ;
end;
gridDataset.Post ;
Inc(okRows);
except
gridDataset.Cancel ;
end; ProgressBar1.Position:=ProgressBar1.Position+1;
if i div 10=0 then
begin
Self.Update ;
end;
end;
dxMemDataImportMap.EnableControls ;
InfoOk( '成功导入 '+InttoStr(okRows)+'行 !' ) ;
Self.Close ;
end
else
begin
InfoOk( '未有数据 !' ) ;
end;
end;