贴一段代码哈~~刚弄好,能用 procedure TForm1.Button2Click(Sender: TObject); var i,j:integer; sheet1:variant; begin excelapplication1.Connect; //建立EXCEL驱动 excelapplication1.Workbooks.Open('C:\Documents and Settings\new\桌面\excel\hedao1.xls',EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0,0,0); //使指定EXCEL表变成可操作//excelapplication1.Workbooks.Add(xlWBATWorkSheet,0); //新建一个EXCEL表sheet1:=excelapplication1.Worksheets.Item[1]; //建立EXCEL表中工作薄驱动(连接) with adoquery2 do begin first; sheet1.cells[4,2].value:=fieldbyname('河道名称').AsString; sheet1.cells[4,6].value:=fieldbyname('断面序').AsString; sheet1.cells[4,8].value:=fieldbyname('断面桩号').AsString; //插入表头 i:=0; //读记录序号 j:=7; //写记录序号 while i<recordcount do begin if i mod 2=1 then begin sheet1.cells[j,5].value:=fieldbyname('测点').AsString; sheet1.cells[j,6].value:=fieldbyname('距离').asstring; sheet1.cells[j,7].value:=fieldbyname('高程').AsString; j:=j+1; end else begin sheet1.cells[j,1].value:=fieldbyname('测点').AsString; sheet1.cells[j,2].value:=fieldbyname('距离').asstring; sheet1.cells[j,3].value:=fieldbyname('高程').AsString; end; i:=i+1; next; end; //将查询的记录写到EXCEL表中 end; excelapplication1.Visible[0]:=true; //打开EXCEL表 {SaveDialog1.Execute; if SaveDialog1.FileName<>'noname' then begin sheet1.SaveAs(SaveDialog1.FileName); ShowMessage('文件已保存為'+SaveDialog1.FileName); end; } sheet1.Application.Quit; end;
用 TcxShellTreeView 和 TcxSpreadSheet 控件, 前者找路径,后者可预览我的代码: load:=cxShellTreeView1.AbsolutePath;//load:string len:=Length(load); //len:integer name:=copy(load,len-2,3); //name 文件名 if (LowerCase(name)='xls') then begin //0 cxSpreadSheet1.LoadFromFile(load); else begin //9 Application.MessageBox('请选择Excel文件!','提示!',MB_OK); exit; end;
楼上的是导出为Excel文件啊! 而不是导入!呵呵~~~~~~~~~~~我也正在搞这种导入的功能,搞了一点,不是很理想,现在贴出来大家一起看看, 看有什么更好的方法!var xlsFilename: string; MyWorkBook,ExcelApp: OleVariant; J,i : integer; begin OpenDialog1.Title := '请选择相应的Excel文件'; OpenDialog1.Filter := 'Excel(*.xls)|*.xls'; if OpenDialog1.Execute then if (OpenDialog1.FileName = '') then begin //MessageBox(GetActiveWindow(), '请正确选择相关路径!', '警告', MB_OK+MB_ICONWARNING); exit; end else begin xlsFilename := OpenDialog1.FileName; end; ExcelApp:=CreateOLEObject('Excel.Application'); MyWorkBook:=ExcelApp.WorkBooks.Open(xlsFilename); J:=MyWorkBook.ActiveSheet.UsedRange.Rows.Count; try i:=1; while i<j+1 do begin ADOQuery1.ConnectionString:=connetstr; ADOQuery1.SQL.Clear; if Trim(MyWorkBook.WorkSheets[1].Cells[i,1])<>'' then begin ADOQuery1.SQL.Add('insert into book(bookID,bookcaption,Hmoney,Writer,Pub,TF)'); ADOQuery1.SQL.Add('values('''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,1]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,2]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,3]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,4]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,5]))+''',cast('''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,6]))+''' as int))'); ADOQuery1.ExecSQL; //CONVERT (data_type[(length)], expression [, style]) i:=i+1; end; end; except ShowMessage('Excel文件格式不对或存在其他问题!请修正后再进行导入.'); Exit; end; Application.MessageBox('数据导入成功!','恭喜',MB_OK + MB_ICONINFORMATION);
procedure TForm1.Button2Click(Sender: TObject);
var i,j:integer;
sheet1:variant;
begin
excelapplication1.Connect;
//建立EXCEL驱动
excelapplication1.Workbooks.Open('C:\Documents and Settings\new\桌面\excel\hedao1.xls',EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0,0,0);
//使指定EXCEL表变成可操作//excelapplication1.Workbooks.Add(xlWBATWorkSheet,0);
//新建一个EXCEL表sheet1:=excelapplication1.Worksheets.Item[1];
//建立EXCEL表中工作薄驱动(连接)
with adoquery2 do
begin
first;
sheet1.cells[4,2].value:=fieldbyname('河道名称').AsString;
sheet1.cells[4,6].value:=fieldbyname('断面序').AsString;
sheet1.cells[4,8].value:=fieldbyname('断面桩号').AsString;
//插入表头
i:=0;
//读记录序号
j:=7;
//写记录序号
while i<recordcount do
begin
if i mod 2=1 then
begin
sheet1.cells[j,5].value:=fieldbyname('测点').AsString;
sheet1.cells[j,6].value:=fieldbyname('距离').asstring;
sheet1.cells[j,7].value:=fieldbyname('高程').AsString;
j:=j+1;
end
else
begin
sheet1.cells[j,1].value:=fieldbyname('测点').AsString;
sheet1.cells[j,2].value:=fieldbyname('距离').asstring;
sheet1.cells[j,3].value:=fieldbyname('高程').AsString;
end;
i:=i+1;
next;
end;
//将查询的记录写到EXCEL表中
end;
excelapplication1.Visible[0]:=true;
//打开EXCEL表
{SaveDialog1.Execute;
if SaveDialog1.FileName<>'noname' then begin
sheet1.SaveAs(SaveDialog1.FileName);
ShowMessage('文件已保存為'+SaveDialog1.FileName);
end;
}
sheet1.Application.Quit;
end;
load:=cxShellTreeView1.AbsolutePath;//load:string
len:=Length(load); //len:integer
name:=copy(load,len-2,3); //name 文件名
if (LowerCase(name)='xls') then
begin //0
cxSpreadSheet1.LoadFromFile(load);
else
begin //9
Application.MessageBox('请选择Excel文件!','提示!',MB_OK);
exit;
end;
而不是导入!呵呵~~~~~~~~~~~我也正在搞这种导入的功能,搞了一点,不是很理想,现在贴出来大家一起看看,
看有什么更好的方法!var
xlsFilename: string;
MyWorkBook,ExcelApp: OleVariant;
J,i : integer;
begin OpenDialog1.Title := '请选择相应的Excel文件';
OpenDialog1.Filter := 'Excel(*.xls)|*.xls'; if OpenDialog1.Execute then
if (OpenDialog1.FileName = '') then
begin
//MessageBox(GetActiveWindow(), '请正确选择相关路径!', '警告', MB_OK+MB_ICONWARNING);
exit;
end
else
begin
xlsFilename := OpenDialog1.FileName;
end; ExcelApp:=CreateOLEObject('Excel.Application');
MyWorkBook:=ExcelApp.WorkBooks.Open(xlsFilename); J:=MyWorkBook.ActiveSheet.UsedRange.Rows.Count; try
i:=1;
while i<j+1 do
begin
ADOQuery1.ConnectionString:=connetstr;
ADOQuery1.SQL.Clear;
if Trim(MyWorkBook.WorkSheets[1].Cells[i,1])<>'' then
begin
ADOQuery1.SQL.Add('insert into book(bookID,bookcaption,Hmoney,Writer,Pub,TF)');
ADOQuery1.SQL.Add('values('''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,1]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,2]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,3]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,4]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,5]))+''',cast('''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,6]))+''' as int))');
ADOQuery1.ExecSQL; //CONVERT (data_type[(length)], expression [, style])
i:=i+1;
end;
end;
except
ShowMessage('Excel文件格式不对或存在其他问题!请修正后再进行导入.');
Exit;
end;
Application.MessageBox('数据导入成功!','恭喜',MB_OK + MB_ICONINFORMATION);
不去掉就会处于假死状态,因为一个Excel工作表有6W多行(包括空行),而
J:=MyWorkBook.ActiveSheet.UsedRange.Rows.Count;
是检索所有行的数,所以在这里我还不知道要怎么处理!请知道的高手不吝赐教!谢谢!