to CJ：
我真的服了你！！！
你以为很多人有足够的网络速度看完这些东西吗？
<!-- #BeginEditable "Page%20Body" -->
<DIV ALIGN="left">
<H2><A NAME="Top"></A>Automating Microsoft Excel</H2>
<H3>Contents</H3>
<UL>
<LI><A HREF="#ExcelSourceInfo">Sources of information</A></LI>
<LI><A HREF="#Downloads">Downloads</A></LI>
<LI><A HREF="#HowDoI">How do I?</A></LI>
<LI><A HREF="ExcelPrbs.htm">Common problems</A></LI>
</UL>
</DIV>
<HR>
<H3><A NAME="ExcelSourceInfo"></A>Sources of information</H3>
<TABLE WIDTH="80%" BORDER="1" ALIGN="center" SUMMARY="Useful links for Excel">
<TR BORDERCOLOR="#FFFF99">
<TD ALIGN="left" VALIGN="top" WIDTH="22%">Web sites</TD>
<TD WIDTH="78%" VALIGN="top"> <P>Delphi sites<BR>
<A HREF="http://www.borland.com/delphi/papers/microexcel/">Borland's papers</A>
<BR>
<A
HREF="http://community.borland.com/delphi/article/1,1410,10043,00.html">Chapter
17 of C. Calvert's D4 Unleashed</A><BR>
<A HREF="http://vzone.virgin.net/graham.marshall/excel.htm">Graham Marshall's
Delphi 3 and Excel</A> <BR>
</P>
<P>For catching Excel events, or general COM concepts, see also<BR>
<A HREF="http://www.techvanguards.com/">Binh Ly's tutorials</A> </P>
<P>Non-Delphi sites<BR>
<A HREF="http://www.microsoft.com/officedev/articles/Opg/intro/intro.htm">MS
Visual Basic Programmer's Guide</A><BR>
<A HREF="http://msdn.microsoft.com/default.asp"> Microsoft Developer's
Network</A><BR>
<A HREF="http://www.BMSLtd.co.uk">Stephen Bullen's web site</A> </P>
</TD>
</TR>
<TR BORDERCOLOR="#FFFF99">
<TD ALIGN="left" VALIGN="top" WIDTH="22%">Books</TD>
<TD WIDTH="78%" VALIGN="top">Charlie Calvert's Delphi 4 Unleashed</TD>
</TR>
</TABLE>
<BR>
<DIV ALIGN="center">
<A HREF="#Top"><FONT SIZE="2">Back to top</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H3><A NAME="HowDoI"></A>How do I ... ?</H3>
<UL>
<LI><A HREF="#StartingExcel">Start Excel</A></LI>
<LI><A HREF="#CloseExcel"> Close Excel</A></LI>
<LI><A HREF="#CreateWorkbook">Create a workbook</A></LI>
<LI><A HREF="#OpenWorkbook">Open a workbook</A></LI>
<LI><A HREF="#CloseWorkbook">Close a workbook</A></LI>
<LI><A HREF="#EnterData">Enter data</A></LI>
<LI><A HREF="#CopyData">Copy data</A></LI>
<LI><A HREF="#FormatRange">Format a range</A></LI>
<LI><A HREF="#AddName">Add a name to a workbook</A></LI>
<LI><A HREF="#AddMacro">Add a macro to a workbook</A></LI>
</UL>
</DIV>
<DIV ALIGN="center">
<A HREF="#Top"><FONT SIZE="2">Back to top</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="StartingExcel"></A><FONT
COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How to start Excel<FONT
COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT></H4>
<UL>
<LI><A HREF="#StartingComponents">Using D5's components</A></LI>
<LI><A HREF="#ExcelStartingNoExceptions">Using the type library( early binding)
</A></LI>
<LI><A HREF="#ExcelStartingLateBinding">Without using the type library (late
binding)</A></LI>
</UL>
<P>The last two methods check to see if Excel is already running before
starting a new instance. </P>
<HR WIDTH="50%" ALIGN="center">
<P><I><A NAME="StartingComponents"></A><B>Using Delphi 5's Excel
components</B></I></P>
<P>The new Delphi 5 components make starting Excel very simple. Drop an
ExcelApplication component on your form. If the AutoConnect property is true,
Excel will start automatically when your program starts; if it's false, just
call</P>
<PRE> ExcelApplication1.Connect;
</PRE>
<P>when you want to start Excel. To use a running instance of Excel, if there
is one, set the ConnectKind property of TExcelApplication to<B>
ckRunningOrNew</B>, or to <B>ckRunningInstance</B> if you don't want to start a
new instance if Excel isn't running.</P>
<P> Once Excel has started, you can connect other components, such as
TExcelWorkbook, using their ConnectTo methods:</P>
<PRE> ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);
ExcelWorksheet1.ConnectTo(ExcelApplication1.ActiveSheet as _Worksheet);
ExcelWorksheet2.ConnectTo(ExcelApplication1.Worksheets.Item['Sheet2'] as _Worksheet);
</PRE>
Note that a workbook or worksheet must be open before you can connect to it!
See <A HREF="#OpenWorkbook">How to open a workbook</A> or
<A HREF="#CreateWorkbook">How to create a workbook</A> for code to do this.
<P>I advise you not to try to start Excel using any component except the
application component, however. At least on some setups, calling the Connect
method (NB not the ConnectTo method!) of a Workbook or Worksheet component will
cause an exception. </P>
<HR WIDTH="50%" ALIGN="center">
<P><I><A NAME="ExcelStartingNoExceptions"></A><B>Opening Excel (early
binding)</B></I></P>
<P>Before you can use this method, you must have imported the type library
(Excel8.olb for Excel 97). </P>
<P>One way of starting Excel is to <B>try </B>the GetActiveObject call, to get
a running instance of Excel, but put a call to CoApplication.Create in an
<B>except</B> clause. But <B>except</B> clauses are slow, and can cause
problems within the IDE for people who like Break On Exceptions set to True.
The following code removes the need for a <B>try...except</B> clause, by
avoiding using OleCheck on GetActiveObject in the case when Excel is not
running.</P>
<PRE> uses Windows, ComObj, ActiveX, Excel_TLB;
</PRE>
<PRE>
<B>var</B>
Excel: _Application;
AppWasRunning: boolean; // <I>tells you if you can close Excel when you've finished</I>
lcid: integer;
Unknown: IUnknown;
Result: HResult;
<B>begin</B>
lcid := GetUserDefaultLCID;
AppWasRunning := False;
<I> {$IFDEF VER120} // Delphi 4</I>
Result := GetActiveObject(CLASS_Application_, nil, Unknown);
<B>if</B> (Result = MK_E_UNAVAILABLE) <B>then</B>
Excel := CoApplication_.Create
<I> {$ELSE} // Delphi 5</I>
Result := GetActiveObject(CLASS_ExcelApplication, nil, Unknown);
<B>if</B> (Result = MK_E_UNAVAILABLE) <B>then</B>
Excel := CoExcelApplication.Create
<I> {$ENDIF} </I>
<B>else begin</B>
<I>{ make sure no other error occurred during GetActiveObject }</I>
OleCheck(Result);
OleCheck(Unknown.QueryInterface(_Application, Excel));
AppWasRunning := True;
<B>end</B>;
Excel.Visible[lcid] := True;
... </PRE>
<P>There is one problem that you should be aware of, however: starting Excel
with GetActiveObject may result in Excel's main frame showing, but its client
area remaining hidden. Although you can restore the client area by setting
Excel to full screen view and back again, this is obviously unattractive
behaviour. It seems to happen only when Excel is running invisibly at the time
of the GetActiveObject call.</P>
<P>Excel may be running invisibly, even after you think you've freed it, if any
of your Workbook or Worksheet variables are still 'live'. (Check this by
pressing Ctrl-Alt-Del once and looking at the list of running tasks.) If you
make sure that you free <I>all </I>Excel variables when you close the
application, Excel will close down properly. Then starting the application
again will not normally cause problems, at least if users of your software
won't be running Excel invisibly by any other method.</P>
<P>If your users may be using other software that automates Excel invisibly,
however, you may prefer to avoid all calls to GetActiveObject (or
GetActiveOleObject), and simply call CoApplication.Create.</P>
<HR WIDTH="50%" ALIGN="center">
<P><I><A NAME="ExcelStartingLateBinding"></A><B>Without using the type
library</B></I></P>
<P>Automation is <I>so</I> much easier and faster using type libraries (early
binding) that you should avoid managing without if at all possible. But if you
really can't, here's how to get started:</P>
<PRE> var
Excel: Variant;
begin
try
Excel := GetActiveOleObject('Excel.Application');
except
Excel := CreateOleObject('Excel.Application');
end;
Excel.Visible := True;
</PRE>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="CloseExcel"></A><FONT
COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How to close Excel<FONT
COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT></H4>
<P> Assuming an application variable, Excel, and an integer variable LCID that
you've assigned the value GetUserDefaultLCID:</P>
<P><I>Early binding::</I></P>
<PRE><I> { Uncomment the next line if you want Excel to quit without asking
</I><I> whether to save the worksheet }
</I> // Excel.DisplayAlerts[LCID] := False;
Excel.Quit;
</PRE>
If you're using the D5 server component, you should disconnect it:
<PRE>
Excel.Disconnect;
</PRE>
If you're using an _Application interface variable, you should set it to nil
instead:
<PRE>
Excel := nil;
</PRE>
<P><I>Late binding:</I> </P>
<PRE>
<I> { Uncomment the next line if you want Excel to quit without asking
</I><I> whether to save the worksheet }
</I> // Excel.DisplayAlerts := False;
Excel.Quit;
Excel := Unassigned;
</PRE>
Note, however, that Excel will hang around in memory, running invisibly, unless
you've released <I>all</I> your workbook and worksheet variables. Disconnect
any components, set any interface variables to nil, and set any variants to
Unassigned to prevent this.
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="CreateWorkbook"></A><FONT
COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How to create a workbook<FONT
COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT></H4>
<P> Assuming an application variable, Excel, and an integer variable LCID that
you've assigned the value GetUserDefaultLCID:</P>
<I>Early binding:</I> <PRE> var
WBk: _Workbook;
...
WBk := Excel.Workbooks.Add(EmptyParam, LCID); </PRE>
<P><BR>
Putting EmptyParam as the first parameter means that a new workbook with a
number of blank sheets will be created. If you pass a filename as the first
parameter, that file will be used as the template for the new workbook.
Alternatively, you can pass in one of the following constants:
<B>xlWBATChart</B>, <B>xlWBATExcel4IntlMacroSheet</B>,
<B>xlWBATExcel4MacroSheet</B>, or <B>xlWBATWorksheet</B>. This will create a
new workbook with a single sheet of the specified type.</P>
<P> <I>Late binding:</I> </P>
<P>In late binding, you don't have to specify optional parameters or LCIDs, so
you can just do this:</P>
<PRE> WBk := Excel.WorkBooks.Add;
</PRE>
<P>If you're not using the type library, but want to use one of the constants
mentioned above, you can define them in your code like this:</P>
<PRE> const
xlWBATChart = $FFFFEFF3;
xlWBATExcel4IntlMacroSheet = $00000004;
xlWBATExcel4MacroSheet = $00000003;
xlWBATWorksheet = $FFFFEFB9;
</PRE>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="OpenWorkbook"></A><FONT
COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How to open a workbook<FONT
COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT></H4>
<P> Assuming an application variable, Excel, and an integer variable LCID that
you've assigned the value GetUserDefaultLCID:</P>
<I>Early binding:</I> <PRE> var
WBk: _Workbook;
WS: _WorkSheet;
Filename: OleVariant;
...
Filename := 'C:/Test.xls';
WBk := Excel.Workbooks.Open(Filename, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam,
EmptyParam, LCID);
WS := WBk.Worksheets.Item['Sheet1'] as _Worksheet;
WS.Activate(LCID);</PRE>
<P><BR>
<I>Late binding:</I> <BR>
</P>
<P>In late binding, you don't have to specify optional parameters, so you can
just do this:</P>
<PRE> var
WBk, WS, SheetName: OleVariant;
...
WBk := Excel.WorkBooks.Open('C:/Test.xls');
WS := WBk.Worksheets.Item['SheetName'];
WS.Activate;</PRE>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="CloseWorkBook"></A><FONT
COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How to close a workbook<FONT
COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT></H4>
<P> Assuming a _Workbook variable, WBk, and an integer variable LCID that
you've assigned the value GetUserDefaultLCID:</P>
<P><I>Early binding</I></P>
<PRE> var
SaveChanges: OleVariant;
...
SaveChanges := True;
WBk.Close(SaveChanges, EmptyParam, EmptyParam. LCID);
</PRE>
<P>If you use EmptyParam as the SaveChanges parameter, the user will be asked
whether to save the workbook. The second parameter allows you to specify a
filename, and the third specifies whether the workbook should be routed to the
next recipient.<BR>
</P>
<P><I>Late binding</I></P>
<P>In late binding, it isn't necessary to specify the optional parameters or
the LCID, so you can just put this:</P>
<PRE> WBk.Close(SaveChanges := True);
</PRE>
<P> or this:</P>
<PRE>
WBk.Close;
</PRE>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="EnterData"></A><FONT
COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How to enter data<FONT
COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT></H4>
<P> Assuming a _Worksheet variable, WS:</P>
<PRE> WS := Excel.ActiveSheet <B>as</B> _Worksheet;
WS.Range['A1', 'A1'].Value := 'The meaning of life, the universe, and everything, is';
WS.Range['B1', 'B1'].Value := 42;
</PRE>
You can enter data into many cells at once:
<PRE> WS.Range['C3', J42'].Formula := '=RAND()';
</PRE>
You can access cells with row and column numbers or variables, like this:
<PRE> var
Row, Col: integer;
...
WS.Cells.Item[1, 1].Value := 'The very first cell';
WS.Cells.Item[Row, Col].Value := 'Some other cell';
</PRE>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="CopyData"></A><FONT COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How
to copy data<FONT COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT></H4>
<P><I>From one range to another</I></P>
<PRE>
var
DestRange: OleVariant;
begin
DestRange := Excel.Range['C1', 'D4'];
Excel.Range['A1', 'B4'].Copy(DestRange);
</PRE>
Make sure you're not still editing a cell when you try to copy the range, or
you'll get a 'Call was rejected by callee' exception.
<P><I>From one sheet to another</I></P>
This example will copy the first column of one sheet to the second column of
another: <PRE>
var
DestSheet: _Worksheet;
DestRange: OleVariant;
begin
DestSheet := Excel.WorkBooks['Test.xls'].Worksheets['Sheet1'] as _Worksheet;
DestRange := Destsheet.Range['B1', 'B1'].EntireColumn;
Excel.Range['A1', 'A1'].EntireColumn.Copy(DestRange);
</PRE>
<P><I>Via the clipboard</I></P>
Using the Copy method without a destination parameter will place the cells in
the Windows clipboard: <PRE>
Excel.Range['A1', 'B4'].Copy(EmptyParam);
</PRE>
You can then paste the cells to another range, like this:
<PRE>
var
WS: _Worksheet;
begin
WS := Excel.Activesheet as _Worksheet;
WS.Range['C1', 'D4'].Select;
WS.Paste(EmptyParam, EmptyParam, lcid);
</PRE>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="FormatRange"></A><FONT
COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How to format a range<FONT
COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT> </H4>
<P> Assuming a _Worksheet variable, WS:</P>
<PRE> <B>var</B>
Format: OleVariant;
...
WS := Excel.ActiveSheet <B>as</B> _Worksheet;
</PRE>
<I>To format one cell in the General number style </I>
<PRE> Format := 'General';
WS.Range['A1', 'A1'].NumberFormat := Format;
</PRE>
<I>To format a range in the 'Text' style, aligned right</I>
<P>Weirdly enough, to give a range a 'text' style you have to set its
NumberFormat property to '@': </P>
<PRE>
<B>with</B> WS.Range['A1', 'M10'] <B>do</B>
<B>begin</B>
NumberFormat := '@';
HorizontalAlignment := xlHAlignRight;
<B>end</B>;
</PRE>
<I>To format a range of cells with the 'March 4, 1999' date style</I>
<PRE>
Format := 'mmmm d, yyyy';
WS.Range['B1', 'C10'].NumberFormat := Format;
</PRE>
<I>To format an entire column in a customized currency style</I>
<PRE> Format := '$#,##0.00_);[Red]($#,##0.00)';
WkSheet.Range['C1', 'C1'].EntireColumn.NumberFormat := Format;
</PRE>
<I>To set the text in a cell to 20pt Arial, bold, and fuchsia</I>
<PRE>
<B>with</B> Excel.ActiveCell.Font <B>do</B>
<B>begin</B>
Size := 20;
FontStyle := 'Bold';
Color := clFuchsia;
Name := 'Arial';
<B>end</B>;
</PRE>
<P><I>To change the cell's colour</I></P>
<PRE> Excel.ActiveCell.Interior.Color := clBtnFace; </PRE>
or
<PRE> Excel.Range['B2', 'C6'].Interior.Color := RGB(223, 123, 123); </PRE>
<I>To make the first three characters in a cell bold</I>
<PRE> <B>var</B>
Start, Length: OleVariant;
...
Start := 1;
Length := 3;
Excel.ActiveCell.Characters[Start, Length].Font.FontStyle := 'Bold';
Start := 4;
Length := 16;
Excel.ActiveCell.Characters[Start, Length].Font.FontStyle := 'Regular';
</PRE>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A>
</DIV>
<HR>
<DIV ALIGN="left">
<H4><A NAME="AddName"></A><FONT COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How
to add a name to a workbook<FONT COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT>
</H4>
<PRE>
<B>var</B>
WB: _Workbook;
N: Excel_TLB.Name; // <I>or N := Excel97.Name; if you're using D5 </I>
<B>begin</B>
WB := Excel.ActiveWorkbook;
N := WB.Names.Add('AddedName', '=Sheet1!$A$1:$D$3', EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam);
</PRE>
The first parameter is the new name, the second is what the name refers to. If
the third parameter is set to False, the name will be hidden and won't appear
in the Define Name or Goto dialogs.
<P> Two possible problems to note here. First, to declare a variable of type
'Name', you'll probably need to scope it explicitly, so: </P>
<PRE> N: Excel_TLB.Name; </PRE>
Secondly, note that the $ signs in the RefersTo parameter are essential.
Leaving them out will cause a varied assortment of unexpected results.
<I>(Thanks to Airy Magnien for pointing this out.)</I>
<P></P>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="AddMacro"></A><FONT COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>How
to add a macro to a workbook<FONT COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT>
</H4>
<P> You can modify Excel VBA code at run time, by writing directly to the code
module in the Excel VBA editor, adding or deleting lines and events. The
important object here is the CodeModule object (cunningly made hard to find in
the Excel VBA help). This is declared in the VBIDE97.pas file. Here's an
example of how to use it: </P>
<PRE>
<B>uses</B>
VBIDE97; // <I>or VBIDE_TLB for Delphi 4</I>
<B>var</B>
LineNo: integer;
CM: CodeModule;
<B>begin</B>
CM := WBk.VBProject.VBComponents.Item('ThisWorkbook').Codemodule;
LineNo := CM.CreateEventProc('Activate', 'Workbook');
CM.InsertLines(LineNo + 1, ' Range(&quot;A1&quot
.Value = &quot;Workbook activated!&quot;');
</PRE>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A> <HR>
</DIV>
<DIV ALIGN="left">
<H4><A NAME="Downloads"></A><FONT
COLOR="#FF6666">&gt;&gt;&gt;&gt;&gt;</FONT>Downloads<FONT
COLOR="#FF6666">&lt;&lt;&lt;&lt;&lt;</FONT></H4>
<P> You can download an example project for Excel automation from <a href="D5Excel.zip">here</a>.</P>
<P> <a href="http://www.stefancr.yucom.be">Stefan Cruysberghs</a> has written
a component to help in exporting data from a TDataset to Excel. Download TscExcelExport
<a href="excelexport.zip">here</a>.</P>
</DIV>
<DIV ALIGN="center">
<A HREF="#HowDoI"><FONT SIZE="2">Back to 'HowDoI'</FONT></A>
</DIV>
<HR>
<!-- #EndEditable -->
10"].Borders[xlEdgeLeft].LineStyle=xlDashDot