我想在软件中提供一个数据excel导入的功能,可现在实现非常困难,求助于大家。谢谢!
解决方案 »
- delphi ADODataSet 显示字段包含内容
- fastreport问题
- 有珠海地区的DELPHI程序员吗,我有分代码需要修改 需要人手
- 我用access做三层,可是用clientdataset1.locate无法定位中文条件,总是返回false?请各位大侠相助。
- 怎麽給一個表做匯總??快來呀馬上就放分
- 转为汉字问题
- 高中生能学什么
- 请问哪位大侠有Delphi的Messages单元中所有参数的详细中文说明??
- opendialog如何判断一个打开文件的大小???
- 有哪个高手敢到http://www.csdn.net/expert/topic/359/359613.shtm ?
- 各位大虾,如何给应用程序加用户密码登陆界面
- 我有一发票,但作为唯一标识的单号我有一困难,怎样使RZDBLABEL1中在每次单击“新单”时,总能产生一个永有重复的单号?,
如果客户是白痴,我一般在Excel里面用VBA写代码导入数据。
你可以在Execl里面按ALT+F11调出编程窗体,用VB代码就可以搞定了。
ADOConnection := TADOConnection.Create(nil);
ADOConnection.LoginPrompt := False;
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0';
ADOQuery := TADOQuery.Create(nil);
ADOQuery.Connection := ADOConnection;
ADOQuery.SQL.Text := 'Select * from [' + SheetName + '$]';
ADOQuery.Open;
With ADOQuery do
begin
First;
while NOT EOF do
begin
// 导入操作的代码
end;
end;
ADOQuery.Close;
ADOConnection.Close; ADOQuery.Free;
ADOConnection.Free;
里的Extended Properties項設成Excel 8.0(設是95版本以上)或Excel 5.0。然后在
CommandType項里面選cmdTableDirect﹐不要選CmdTable和CmdTxt﹐選CmdTxt﹐它會說找不
到你要的那個物件﹐就像你上面出的錯那樣。然后再選擇CommandText﹐只要你選好了Excel
文件﹐里面自然就有你要的東東了。最后再將Active設為True﹐就可以了。保証沒有問題。
1)Project->Import Type Library:
2)Select "Microsoft ADO Ext. for DDL and Security"
3)重新命名类的名称(TTable, TColumn, TIndex, TKey, TGroup, TUser, TCatalog) in
(TXTable, TXColumn, TXIndex, TXKey, TXGroup, TXUser, TXCatalog)
4)创建一个单元,名字为ADOX_TLB
5)在你自己的单元里Uses ADOX_TLB单元unit DBGridExportToExcel; interface uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, ExtCtrls, StdCtrls, ComCtrls, DB, IniFiles, Buttons, dbgrids, ADOX_TLB, ADODB;
type TScrollEvents = class
BeforeScroll_Event: TDataSetNotifyEvent;
AfterScroll_Event: TDataSetNotifyEvent;
AutoCalcFields_Property: Boolean;
end; procedure DisableDependencies(DataSet: TDataSet; var ScrollEvents: TScrollEvents);
procedure EnableDependencies(DataSet: TDataSet; ScrollEvents: TScrollEvents);
procedure DBGridToExcelADO(DBGrid: TDBGrid; FileName: string; SheetName: string);
implementation //Support procedures: I made that in order to increase speed in
//the process of scanning large amounts
//of records in a dataset //we make a call to the "DisableControls" procedure and then disable the "BeforeScroll" and
//"AfterScroll" events and the "AutoCalcFields" property.
procedure DisableDependencies(DataSet: TDataSet; var ScrollEvents: TScrollEvents);
begin
with DataSet do
begin
DisableControls;
ScrollEvents := TScrollEvents.Create();
with ScrollEvents do
begin
BeforeScroll_Event := BeforeScroll;
AfterScroll_Event := AfterScroll;
AutoCalcFields_Property := AutoCalcFields;
BeforeScroll := nil;
AfterScroll := nil;
AutoCalcFields := False;
end;
end;
end; //we make a call to the "EnableControls" procedure and then restore
// the "BeforeScroll" and "AfterScroll" events and the "AutoCalcFields" property.
procedure EnableDependencies(DataSet: TDataSet; ScrollEvents: TScrollEvents);
begin
with DataSet do
begin
EnableControls;
with ScrollEvents do
begin
BeforeScroll := BeforeScroll_Event;
AfterScroll := AfterScroll_Event;
AutoCalcFields := AutoCalcFields_Property;
end;
end;
end; //This is the procedure which make the work: procedure DBGridToExcelADO(DBGrid: TDBGrid; FileName: string; SheetName: string);
var
cat: _Catalog;
tbl: _Table;
col: _Column;
i: integer;
ADOConnection: TADOConnection;
ADOQuery: TADOQuery;
ScrollEvents: TScrollEvents;
SavePlace: TBook;
begin
//
//WorkBook creation (database)
cat := CoCatalog.Create;
cat._Set_ActiveConnection('Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0');
//WorkSheet creation (table)
tbl := CoTable.Create;
tbl.Set_Name(SheetName);
//Columns creation (fields)
DBGrid.DataSource.DataSet.First;
with DBGrid.Columns do
begin
for i := 0 to Count - 1 do
if Items[i].Visible then
begin
col := nil;
col := CoColumn.Create;
with col do
begin
Set_Name(Items[i].Title.Caption);
Set_Type_(adVarWChar);
end;
//add column to table
tbl.Columns.Append(col, adVarWChar, 20);
end;
end;
//add table to database
cat.Tables.Append(tbl); col := nil;
tbl := nil;
cat := nil; //exporting
ADOConnection := TADOConnection.Create(nil);
ADOConnection.LoginPrompt := False;
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0';
ADOQuery := TADOQuery.Create(nil);
ADOQuery.Connection := ADOConnection;
ADOQuery.SQL.Text := 'Select * from [' + SheetName + '$]';
ADOQuery.Open;
DisableDependencies(DBGrid.DataSource.DataSet, ScrollEvents);
SavePlace := DBGrid.DataSource.DataSet.GetBook;
try
with DBGrid.DataSource.DataSet do
begin
First;
while not Eof do
begin
ADOQuery.Append;
with DBGrid.Columns do
begin
ADOQuery.Edit;
for i := 0 to Count - 1 do
if Items[i].Visible then
begin
ADOQuery.FieldByName(Items[i].Title.Caption).AsString := FieldByName(Items[i].FieldName).AsString;
end;
ADOQuery.Post;
end;
Next;
end;
end; finally
DBGrid.DataSource.DataSet.GotoBook(SavePlace);
DBGrid.DataSource.DataSet.FreeBook(SavePlace);
EnableDependencies(DBGrid.DataSource.DataSet, ScrollEvents); ADOQuery.Close;
ADOConnection.Close; ADOQuery.Free;
ADOConnection.Free; end; end; end.
[Error] DBGridExportToExcel.pas(78): Incompatible types: 'String' and 'IDispatch'
2)Select "Microsoft ADO Ext. for DDL and Security"
选择为2。6的版本。顺便问一下,这程序执行的速度是不是很快?前面你提供的导入的速度是让人满意的。