由于我excel数据格式是常规,导入数据库后有乱码或丢失数据,想问一下大家是怎么处理此类问题的,能不能在不改变excel格式前提下,来处理此类问题?原贴http://topic.csdn.net/u/20110409/17/5da85312-084c-4827-837a-acc69b546563.html导入代码
unit Unit2;
interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids;type
TForm2 = class(TForm)
dbgrd1: TDBGrid;
con1: TADOConnection;
qry1: TADOQuery;
qry2: TADOQuery;
ds1: TDataSource;
btn1: TButton;
dlgOpen1: TOpenDialog;
procedure btn1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form2: TForm2;implementation{$R *.dfm}procedure TForm2.btn1Click(Sender: TObject);
var s:string;
ADOConn:TADOConnection;
aDataSet:TADODataSet;
begin
if dlgOpen1.Execute then
begin
ADOConn:=TADOConnection.Create(self);
ADOConn.LoginPrompt:=false;
ADOConn.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
+dlgOpen1.FileName
+';Extended Properties=Excel 8.0;Persist Security Info=False';
aDataSet:=TADODataSet.Create(self);
aDataSet.Connection:=ADOConn;
ADOConn.OpenSchema(siTables,EmptyParam,EmptyParam,aDataSet);
aDataSet.Last;
s:=aDataSet.Fields.Fields[2].AsString;//获取第一工作表表名
aDataSet.Close;
aDataSet.Free;
ADOConn.Close;
ADOConn.Free; qry1.Connection:=con1;
qry1.SQL.Clear;
qry1.SQL.Append('insert stock select * from OPENROWSET(');//将这个‘qqq’字改为你的数据库中的表名
qry1.SQL.Append(quotedstr('MICROSOFT.JET.OLEDB.4.0'));
qry1.SQL.Append(','+quotedstr('Excel 5.0;HDR=YES;Excel 8.0;DATABASE='+dlgOpen1.FileName)+',['+s+'])');
qry1.ExecSQL;
qry2.Active:=True;
showmessage('ok');
end;
end;
end.sql字段数据类型
create table stock
(
st_id varchar(10),
st_dcdate datetime,
st_gc varchar(10),
st_kcd int,
st_zt varchar(10),
st_lx varchar(10),
st_sx varchar(14),
st_gys varchar(20),
st_sku varchar(12),
st_spmc varchar(80),
st_pch varchar(16),
st_tkdate datetime,
st_kw varchar(10),
st_qty float,
st_bzs float,
st_tj float,
st_zl float,
st_tps float,
st_mj float,
st_kcts float,
st_bz varchar(80)
)
unit Unit2;
interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids;type
TForm2 = class(TForm)
dbgrd1: TDBGrid;
con1: TADOConnection;
qry1: TADOQuery;
qry2: TADOQuery;
ds1: TDataSource;
btn1: TButton;
dlgOpen1: TOpenDialog;
procedure btn1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form2: TForm2;implementation{$R *.dfm}procedure TForm2.btn1Click(Sender: TObject);
var s:string;
ADOConn:TADOConnection;
aDataSet:TADODataSet;
begin
if dlgOpen1.Execute then
begin
ADOConn:=TADOConnection.Create(self);
ADOConn.LoginPrompt:=false;
ADOConn.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
+dlgOpen1.FileName
+';Extended Properties=Excel 8.0;Persist Security Info=False';
aDataSet:=TADODataSet.Create(self);
aDataSet.Connection:=ADOConn;
ADOConn.OpenSchema(siTables,EmptyParam,EmptyParam,aDataSet);
aDataSet.Last;
s:=aDataSet.Fields.Fields[2].AsString;//获取第一工作表表名
aDataSet.Close;
aDataSet.Free;
ADOConn.Close;
ADOConn.Free; qry1.Connection:=con1;
qry1.SQL.Clear;
qry1.SQL.Append('insert stock select * from OPENROWSET(');//将这个‘qqq’字改为你的数据库中的表名
qry1.SQL.Append(quotedstr('MICROSOFT.JET.OLEDB.4.0'));
qry1.SQL.Append(','+quotedstr('Excel 5.0;HDR=YES;Excel 8.0;DATABASE='+dlgOpen1.FileName)+',['+s+'])');
qry1.ExecSQL;
qry2.Active:=True;
showmessage('ok');
end;
end;
end.sql字段数据类型
create table stock
(
st_id varchar(10),
st_dcdate datetime,
st_gc varchar(10),
st_kcd int,
st_zt varchar(10),
st_lx varchar(10),
st_sx varchar(14),
st_gys varchar(20),
st_sku varchar(12),
st_spmc varchar(80),
st_pch varchar(16),
st_tkdate datetime,
st_kw varchar(10),
st_qty float,
st_bzs float,
st_tj float,
st_zl float,
st_tps float,
st_mj float,
st_kcts float,
st_bz varchar(80)
)
上面都写了大哥""能不能在不改变excel格式前提下,来处理此类问题?""
st_tj字段 0.000001 导进去就变成1E-06这只是其中一部分!
http://topic.csdn.net/u/20090424/12/d88060a8-692d-4dd1-bf1a-5a529209ce8d.html
begin
try
v:= CreateOleObject('Excel.Application');
v.Workbooks.Open(OpenDialog1.FileName);
Sheet:=v.Workbooks[1].WorkSheets[1];
Sheet.Select;
v.Visible:=false;
Sheet.Columns[4].NumberFormatLocal:='@';//假定你的数据4列需要转换改为文本属性
Sheet.Columns[5].NumberFormatLocal:='@';//假定你的数据5列需要转换改为文本属性
Sheet.Columns[6].NumberFormatLocal:='@';//假定你的数据6列需要转换改为文本属性 //跟着放你原来数据导出代码:
//.........
finally
Showmessage('初始化Excel失败,可能没装Excel,或者其他错误;请重起再试。');
v.DisplayAlerts:=false;
v.Quit;
exit;
end;
Application.Restore;
Application.BringToFront;
end;
这样导出数据就ok了,且因退出excel前没保留改变,你原来的文件就跟没被改过一样啦。