使用Delphi 5中的TExcelApplication操纵Excel 97/2000
ExcelApplication1.Connect;
ExcelApplication1.Visible[0]:=True;ExcelApplication1.Workbooks.Add(NULL,0);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Sheets[1] as _WorkSheet);for idx:=1 to 30 do begin
ExcelWorksheet1.Cells.Item[idx,1]:='Hello '+IntToStr(idx);
ExcelWorksheet1.Cells.Item[idx,2]:= idx;
end;ExcelWorksheet1.Cells.Item[31,2]:='=SUM(B1:B30)';
ShowMessage('Total is '+ExcelWorksheet1.Cells.Item[31,2]); ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
ExcelApplication1.Connect;
ExcelApplication1.Visible[0]:=True;ExcelApplication1.Workbooks.Add(NULL,0);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Sheets[1] as _WorkSheet);for idx:=1 to 30 do begin
ExcelWorksheet1.Cells.Item[idx,1]:='Hello '+IntToStr(idx);
ExcelWorksheet1.Cells.Item[idx,2]:= idx;
end;ExcelWorksheet1.Cells.Item[31,2]:='=SUM(B1:B30)';
ShowMessage('Total is '+ExcelWorksheet1.Cells.Item[31,2]); ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
<2>另外,我想你可以试试OLE的方法
<3>如果不强求连带格式都输入Excel中,
那么采用DDE会话技术也不失为一种好方案的。
2.可以谈谈OLE及DDE的实现方法吗?我对这些了解甚少。
1)先存为text文件,需要分字段存的之间用Tab相隔
2)把text文件如a.txt改名为a.xls即可
程序实现以上两步即可
另外,OLE就是COM的同义词,呵呵。
我想既然必须用到它的COM对象,完全可以采用DCOM的技术,让N台机子(N>2)来出来数据的整理和格式的定义等事务,只让DCOM服务器处理数据的插入,这样可能会提高速度。
下载 http://go.163.com/~chunpeng/project/Export.zip
是用rs.open select * into [FoxPro 2.6;database=c:\temp].1 from 花名册
实现的,说白了就是用SQL语句。要是DELPHI里也能用SQL语句,用SELECT INTO就
能将查寻结果生成一个文件,比如DBC或是XLS。
现在我知道一个办法,就是先生成一个文本文件及一个格式XLS,再用XLS的导入文本文件功能。
象这种COM对象做的server最好利用他本身的特性,象SQL——SERVER和ORACLE最好用存储过程,一样的!
下面是EXCEL的宏 :
/////////////////////////////////////////////***********Public Sub ReadAndFillingWithPara(Textpath As String, ColumCount As Long)
ThisWorkbook.Application.ScreenUpdating = False
ThisWorkbook.Application.DisplayAlerts = False
ThisWorkbook.Application.ScreenUpdating = False
Dim ThisPath As String
Dim j As Integer
Dim RowCount As Integer
ThisPath = Textpath
Dim ArrayRow(100) As String, Result(100) As String
Dim searchChar As String
Dim fso As Scripting.FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject") Dim readFile As Scripting.TextStream
Dim readString As String If Not fso.FileExists(ThisPath) Then
MsgBox ("原文件不存在")
Exit Sub
Else
Set readFile = fso.OpenTextFile(ThisPath)
'赋初值
RowCount = 0
'读写文件
Do While Not readFile.AtEndOfStream Dim k, l, l0, p1, p2 As Integer
readString = readFile.ReadLine
searchChar = "$"
l = 0 '字段长度
l0 = 0 '总长度
p1 = 1
p2 = 0 '下一个位置
Dim Value
Dim temp_value
Dim Sub_value
Dim temp_result
temp_value = "222"
Sub_value = "333"
temp_result = "444"
For j = 1 To ColumCount '每一字段
If InStr(p1, readString, searchChar, 1) = 0 Then
If (Sub_value = temp_value) Then
Value = ""
Else
Value = Mid(readString, p1, Len(readString) - p1 + 1)
temp_value = Value
Sub_value = Value
End If
Else
p2 = InStr(p1, readString, searchChar, 1)
Value = Mid(readString, p1, p2 - p1)
End If
ArrayRow(j) = Value
p1 = p2 + 1
Next j
For j = 1 To ColumCount
Result(j) = ArrayRow(j)
Next j
ThisWorkbook.Sheets("例子页").Range("Rag").Offset(RowCount) = Result
RowCount = RowCount + 1
Loop
End If readFile.Close
Set readFile = Nothing
Set fso = Nothing
Dat2SQL = True
ThisWorkbook.Application.ScreenUpdating = True
ThisWorkbook.Application.DisplayAlerts = True
ThisWorkbook.Application.ScreenUpdating = True
End Sub/////////////////////////////////////////***********88unit MyPublic;interface
Uses
Windows, Messages, SysUtils, Classes, Dialogs,
StdCtrls, ADOdb, ComObj, forms ,DB ;
type
TExecExcel = Class
private
FDesData:TCustomADODataSet;// 目标数据源
FDesModalName :String; //目标模版名
FSheetName :String; //目标 工作表sheet 名
FDesMacroName :String; // 目标宏名
procedure WriteDataWithTitle(SourceData: TCustomADODataSet; DestFile : String);
function GetSheet(ExcelTamplateName, SheetName : String): OLEVariant;
published
procedure WriteData(SourceData: TCustomADODataSet; DestFile : String);
Function GetClToken (var S: string ; delim : string ):string;
public
constructor Create(DesData:TCustomADODataSet;// 目标数据源
DesModalName :String; //目标模版名
aSheetName :String; //目标 工作表sheet 名
DesMacroName :String // 目标宏名
);
procedure ExecExcel(ColumCount :integer;
DesFileName :String //目标文本文件
) ;end ;
function GetPathFromFullPath(fp:string):string;
function GetsheetName(WorkName,SheetName :string):OLEVariant;
procedure Exe_excel(WorkName,SheetName :String ) ;implementationfunction GetsheetName(WorkName,SheetName :string):OLEVariant;
var
aExcel, aWorkbook, aSheet : OLEvariant;
begin
result := UnAssigned;
try
aExcel := GetActiveOleObject('Excel.Application');
except
try
aExcel := CreateOLEObject('Excel.Application');
except
ShowMessage('无法启动Excel!');
exit;
end;
end;
aWorkBook := aExcel.Workbooks.Open(WorkName);
aSheet := aWorkBook.Worksheets.item[SheetName];
result := aSheet;
end;procedure Exe_excel(WorkName,SheetName :String ) ;
var app,aSheet :OleVariant ;
begin
aSheet := GetSheetName
(ExtractFilePath(Application.EXEName)+ WorkName ,SheetName); aSheet.Application.Interactive := False;
aSheet.Application.DisplayAlerts := False;
aSheet.Application.ScreenUpdating := False; if VarIsNull(aSheet) then exit; app := aSheet.Application;
aSheet.Application.Interactive := True;
aSheet.Application.DisplayAlerts := True;
aSheet.Application.ScreenUpdating := True;
aSheet.Application.visible := true;
end;{ TExecExcel }constructor TExecExcel.Create(DesData: TCustomADODataSet; DesModalName,
aSheetName, DesMacroName: String);
begin
FDesData:=DesData ;// 目标数据源
FDesModalName :=DesModalName; //目标模版名
FSheetName :=aSheetName; //目标 工作表sheet 名
FDesMacroName :=DesMacroName; // 目标宏名
end;procedure TExecExcel.ExecExcel(ColumCount: integer; DesFileName: String);
var
AppPath: String;
aSheet, App: Variant;
begin
AppPath:= GetPathFromFullPath(Application.ExeName) ;
WriteDataWithTitle(FDesData,AppPath + DesFileName); aSheet := GetSheet(ExtractFilePath(Application.EXEName)+ FDesModalName ,FSheetName);
if VarIsNull(aSheet) then exit; aSheet.Application.Interactive := False;
aSheet.Application.DisplayAlerts := False;
aSheet.Application.ScreenUpdating := False; app := aSheet.Application;
app.Run(FDesMacroName ,AppPath+DesFileName,ColumCount);
aSheet.Application.Interactive := True;
aSheet.Application.DisplayAlerts := True;
aSheet.Application.ScreenUpdating := True;
aSheet.Application.visible := true;
end;function TExecExcel.GetClToken(var S: string; delim: string): string;
var I: integer;
begin
I:=pos(delim,s);
if I<>0 then
begin
result:=Copy(S,1,I-1);
S:=Copy(S,I+length(delim),length(S));
end
else
begin
Result:=S;
S:='';
end;
end;function GetPathFromFullPath(fp: string): string;
var path :String;
L,I :integer;
begin
path:='';
L:=0;
For I:=length(fp) downto 1 do
begin
if fp[i]='\' then
begin
L:=I;
break;
end;
end;
path:=fp;
setlength(path,L);
result:=path;
end;function TExecExcel.GetSheet(ExcelTamplateName,
SheetName: String): OLEVariant;
var
aExcel, aWorkbook, aSheet : OLEvariant;
begin
result := UnAssigned;
try
aExcel := GetActiveOleObject('Excel.Application');
except
try
aExcel := CreateOLEObject('Excel.Application');
except
ShowMessage('无法启动Excel!');
exit;
end;
end;
aWorkBook := aExcel.Workbooks.Add(ExcelTamplateName);
aSheet := aWorkBook.Worksheets.item[SheetName];
result := aSheet;
end;
procedure TExecExcel.WriteData(SourceData: TCustomADODataSet; DestFile : String);
var F: TextFile;
S: String;
I:integer;
AppPath:String;
begin
S:='';
AppPath:= GetPathFromFullPath(Application.ExeName) ;
AssignFile(F, AppPath+DestFile);
Rewrite(F);
SourceData.First;
Try
SourceData.DisableControls;
While not SourceData.eof do
begin
for I:=0 to SourceData.FieldCount-1 do
begin
if SourceData.Fields[I].DataType=FtString then
S:=S+'"'+SourceData.Fields[I].asstring+'"'+','
Else
S:=S+SourceData.Fields[I].asstring+','; end;
S:=Copy(S,1,Length(S)-1);
SourceData.next;
Writeln(F,S);
Flush(f);
s:='';
end;
Finally
SourceData.First;
SourceData.EnableControls;
end;
CloseFile(F);
end;procedure TExecExcel.WriteDataWithTitle(SourceData: TCustomADODataSet;
DestFile: String);
var F: TextFile;
S: String;
I:integer;
begin
S:='';
AssignFile(F, DestFile);
Rewrite(F); for I:=0 to SourceData.FieldCount-1 do
begin
S:=S+SourceData.Fields[I].displayLabel+'$' ;
end; S:=Copy(S,1,Length(S)-1);
Writeln(F,S);
Flush(f);
s:=''; SourceData.First;
Try
SourceData.DisableControls;
While not SourceData.eof do
begin
for I:=0 to SourceData.FieldCount-1 do
S:=S+SourceData.Fields[I].asstring+'$'; S:=Copy(S,1,Length(S)-1);
SourceData.next;
Writeln(F,S);
Flush(f);
s:='';
end;
Finally
SourceData.First;
SourceData.EnableControls;
end;
CloseFile(F);
end;end.
楼上的楼上的大虾写的东东我实在看不懂。
可以说点我懂的吗?
谢谢。
在第四句时出错。
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
错误为:Undeclared identifier: 'ExcelWorkbook1'是不是还有什么工序没有做,急盼解答,谢谢。
不是我故意,只因我太菜。大虾们多多包涵哈。 :)我现在的问题是,一个格式已经定好的excel文档,我要调用它,并在里面加数据。
如何做。