以前寫的﹐參考 try Screen.Cursor:=crHourGlass; LCID:=GetUserDefaultLCID; ExcelApplication1.ConnectKind := ckNewInstance; ExcelApplication1.Connect; ExcelApplication1.Workbooks.Add(NULL, LCID); ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]); ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet); with ExcelWorksheet1.Cells do begin Y:=ComboBox1.Text; F:=ComboBox2.Text; Item[1,1]:='Deffecive Shoes Return Quantity/Amount List during '+Y; Item[2,1]:=DatetoStr(Date); ExcelWorksheet1.Range['a1','e1'].Font.Bold:=True; ExcelWorksheet1.Range['a1','e1'].Font.Size:=12; ExcelWorksheet1.Range['a1','o1'].Merge(True); ExcelWorksheet1.Range['a2','o2'].Merge(True); Item[3,1]:='Month'; Item[3,3]:='January'; Item[3,4]:='February'; Item[3,5]:='March'; Item[3,6]:='April'; Item[3,7]:='May'; Item[3,8]:='June'; Item[3,9]:='July'; Item[3,10]:='August'; Item[3,11]:='September'; Item[3,12]:='October'; Item[3,13]:='November'; Item[3,14]:='December'; Item[3,15]:='Total'; C:=0; with adoStoredProc2 do begin Close; Parameters.ParamByName('@Year').Value:=Y; Parameters.ParamByName('@factory').Value:=F; Open; if Active and (RecordCount>0)then while not eof do begin Item[4+2*C,1]:=FieldValues['Category']; Item[4+2*C,2]:='$'; C:=C+1; Next; end; K:=3; M:='01'; B:=4; C1:=0; with adoStoredProc3 do begin Close; Parameters.ParamByName('@year').Value:=Y; Parameters.ParamByName('@factory').Value:=F; Open; if Active and (RecordCount>0)then while True do begin IT:=Item[4+2*C1,1]; if Locate('Category;month',Vararrayof([IT,Y+'/'+M]),[])then begin Item[B,K]:=FieldValues['ReturnQty']; Item[B+1,K]:=FieldValues['ReturnMoney']; end; C1:=C1+1; if C1=C then Break else B:=B+2; C2:=GetColumnCharacters(K); S1:='Sum(C4'; S2:='Sum(C5'; I:=1; while I<C do begin S1:=S1+'.'+C2+InttoStr(4+2*i); S2:=S2+'.'+C2+InttoStr(4+2*I); I:=I+1; end; S1:=S1+')'; S2:=S2+')'; Item[4+2*C,K]:=S1; Item[5+2*C,K]:=S2; if K=14 then break else begin K:=K+1; if StrtoInt(M)<10 then M:='0'+InttoStr(StrtoInt(M)+1) else M:=InttoStr(StrtoInt(M)+1); end; end; end; T:=4; while True do begin Item[T,15]:='=Sum('+'C'+InttoStr(T)+':'+GetColumnCharacters(K)+InttoStr(T)+')'; if T=5+2*C then break else T:=T+1; end; end; end; finally adoStoredProc1.Close; ExcelWorksheet1.Range['A3',GetColumnCharacters(15)+'3'].Interior.Color:=clSilver; ExcelWorksheet1.Range['A3',GetColumnCharacters(15)+InttoStr(4+2*C)].Borders.Weight:=2; ExcelApplication1.Visible[LCID]:=True; ExcelWorksheet1.Disconnect; ExcelWorkBook1.Disconnect; ExcelApplication1.Disconnect; ExcelApplication1.Quit; Screen.Cursor:=crDefault; end;
(作者:穆永) 在数据库应用软件的开发过程中,经常需要把数据转换成Excel文件,让用户作进一步的数据处理。而Delphi是开发数据库应用系统的常用工具,那么,如何在Delphi中操作Excel呢? 我们知道,在Microsoft Office软件中有一种内嵌的编程语言VBA,它是一种宏语言,利用它,你可以编写出功能强大的代码,如打开文件、修改数据、保存数据和设置字体等。另一方面,Microsoft Office软件中的宏能以VBA代码的形式记录下你的操作过程。因此借助宏操作,可以很轻松地实现某一功能,并把这些代码稍作修改嵌入到你的软件中。但是VBA也存在一个缺点,它必须有Microsoft Office作平台,在哪里编写,必须在哪里执行。例如在Excel下编写的一段VBA代码,则它只有在Excel下才能运行。因此,笔者的方法就是把VBA代码嵌入到Delphi中,从而实现用Delphi操作Excel。 下面,我们以Delphi程序为例,说明这种调用方法: Unit excel; interface uses Windows,Messages,SysUtils,Classes,Graphics,Controls,Forms,Dialogs,StdCtrls,ComObj; //ComObj是操作OLE对象的函数集 type TForm1 =class(TForm) Button1: TButton; procedure Button1Click(Sender: TObject); private { Private declarations } public { Public declarations } end; var Form1: TForm1; implementation {$R *.DFM} procedure TForm1.Button1Click(Sender: TObject); var eclApp,WorkBook:Variant; //声明为OLE Automation 对象 xlsFileName:string;begin xlsFileName:='ex.xls'; try //创建OLE对象Excel Application与 WorkBook eclApp:=CreateOleObject('Excel.Application'); WorkBook:=CreateOleobject('Excel.Sheet'); except ShowMessage('您的机器里未安装Microsoft Excel。'); Exit; end; try ShowMessage('下面演示:新建一个XLS文件,并写入数据,最后关闭它。'); workBook:=eclApp.workBooks.Add; eclApp.Cells(1 , 1):='字符型'; eclApp.Cells(2 , 1):='Excel文件'; eclApp.Cells(1 , 2):='Money型'; eclApp.Cells(2 , 2):=10.01; eclApp.Cells(1 , 3):='日期型';eclApp.Cells(2 , 3):=Date; WorkBook.saveas(xlsFileName); WorkBook.close; ShowMessage('下面演示:打开刚创建的XLS文件,并修改其中的内容,然后,由用户决定是否保存。'); WorkBook:=eclApp.workBooks.Open(xlsFileName); eclApp.Cells(2 , 1):='Excel文件类型'; if MessageDlg(xlsFileName+'文件已被修改,是否保存?',mtConfirmation, [mbYes, mbNo], 0) = mrYes then WorkBook.save else workBook.Saved := True; //放弃修改 WorkBook.Close; eclApp.Quit; //退出Excel Application //释放VARIANT变量 eclApp:=Unassigned; except ShowMessage('不能正确操作Excel文件。可能是该文件已被其他程序打开,或系统错误。'); WorkBook.close; eclApp.Quit; //释放VARIANT变量 eclApp:=Unassigned; end; end; end. 经过上述操作过程后,我们就可以很放心地将数据库中的数据转换成Excel文件了。
try
Screen.Cursor:=crHourGlass;
LCID:=GetUserDefaultLCID;
ExcelApplication1.ConnectKind := ckNewInstance;
ExcelApplication1.Connect;
ExcelApplication1.Workbooks.Add(NULL, LCID);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);
with ExcelWorksheet1.Cells do
begin
Y:=ComboBox1.Text;
F:=ComboBox2.Text;
Item[1,1]:='Deffecive Shoes Return Quantity/Amount List during '+Y;
Item[2,1]:=DatetoStr(Date);
ExcelWorksheet1.Range['a1','e1'].Font.Bold:=True;
ExcelWorksheet1.Range['a1','e1'].Font.Size:=12;
ExcelWorksheet1.Range['a1','o1'].Merge(True);
ExcelWorksheet1.Range['a2','o2'].Merge(True);
Item[3,1]:='Month';
Item[3,3]:='January';
Item[3,4]:='February';
Item[3,5]:='March';
Item[3,6]:='April';
Item[3,7]:='May';
Item[3,8]:='June';
Item[3,9]:='July';
Item[3,10]:='August';
Item[3,11]:='September';
Item[3,12]:='October';
Item[3,13]:='November';
Item[3,14]:='December';
Item[3,15]:='Total'; C:=0;
with adoStoredProc2 do
begin
Close;
Parameters.ParamByName('@Year').Value:=Y;
Parameters.ParamByName('@factory').Value:=F;
Open;
if Active and (RecordCount>0)then
while not eof do
begin
Item[4+2*C,1]:=FieldValues['Category'];
Item[4+2*C,2]:='$';
C:=C+1;
Next;
end;
K:=3;
M:='01';
B:=4;
C1:=0;
with adoStoredProc3 do
begin
Close;
Parameters.ParamByName('@year').Value:=Y;
Parameters.ParamByName('@factory').Value:=F;
Open;
if Active and (RecordCount>0)then
while True do
begin
IT:=Item[4+2*C1,1];
if Locate('Category;month',Vararrayof([IT,Y+'/'+M]),[])then
begin
Item[B,K]:=FieldValues['ReturnQty'];
Item[B+1,K]:=FieldValues['ReturnMoney'];
end;
C1:=C1+1;
if C1=C then
Break
else
B:=B+2;
C2:=GetColumnCharacters(K);
S1:='Sum(C4';
S2:='Sum(C5';
I:=1;
while I<C do
begin
S1:=S1+'.'+C2+InttoStr(4+2*i);
S2:=S2+'.'+C2+InttoStr(4+2*I);
I:=I+1;
end;
S1:=S1+')';
S2:=S2+')';
Item[4+2*C,K]:=S1;
Item[5+2*C,K]:=S2;
if K=14 then
break
else begin
K:=K+1;
if StrtoInt(M)<10 then
M:='0'+InttoStr(StrtoInt(M)+1)
else
M:=InttoStr(StrtoInt(M)+1);
end;
end;
end;
T:=4;
while True do
begin
Item[T,15]:='=Sum('+'C'+InttoStr(T)+':'+GetColumnCharacters(K)+InttoStr(T)+')';
if T=5+2*C then
break
else
T:=T+1;
end;
end;
end;
finally
adoStoredProc1.Close;
ExcelWorksheet1.Range['A3',GetColumnCharacters(15)+'3'].Interior.Color:=clSilver;
ExcelWorksheet1.Range['A3',GetColumnCharacters(15)+InttoStr(4+2*C)].Borders.Weight:=2;
ExcelApplication1.Visible[LCID]:=True;
ExcelWorksheet1.Disconnect;
ExcelWorkBook1.Disconnect;
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
Screen.Cursor:=crDefault;
end;
看一下它的帮助,SQL命令可以直接对Excel操作,条件是要有一个SQLServer通过路径能访问的excel文件。不过CreateOleVariant那么用语句写,可以直接通过自己写的存储过程来完成,保证速度快快的。:P
1.进入SQL Server Enterpriment
2.进入tables,选中想要转换的表,右键all tasks->export data
3.选择数据源时,source选择excel