📄 excel_automation.shtml
字号:
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Author" CONTENT="Zafir Anjum">
<TITLE>Misc - Doing Excel Automation witch MSVC</TITLE>
</HEAD>
<body background="../fancyhome/back.gif" bgcolor="#FFFFFF" link="#B50029" vlink="#8E2323" alink="#FF0000" bgproperties="fixed">
<table WIDTH="100%">
<tr WIDTH="100%">
<td><!--#exec cgi="/cgi/ads.cgi"--><td>
</tr>
</table>
<CENTER>
<H3>
<FONT COLOR="#AOAO99">Doing Excel Automation witch MSVC</FONT></H3></CENTER>
<HR>
This article was contributed by <A HREF="mailto:blenkers@anachem.ruhr-uni-bochum.de"> Thomas Blenkers</A>.
<P>Now fasten your seatbelt and be prepared for a real fast flight across
MSVC, Excel and Automation. I don't really want to teach you understanding
automation, rather I like to have your job done. :)
<P>Please bear in mind, that I will describe one special task here: storing
some information as an Excel sheet with auto-sized coloumns. I am using
MSVC 5.0 and the MFC.
<P>I already have a prepared text file containing the information I would
like to store. I did this using semikolon (;) separated ascii-output with
new-line\carriage-return pairs at the end of each line. (To say it in plain
MFC, I used the CStdioFile class with the WriteString method)
<P>If you are working with the Excel classes, you need to deal with the
VARIANT data type or, more convient when using MFC, with the COleDispatch
class, which has a built-in interface and is more easy to use.
<P>A caveat of this approach that you have to modify the automation classes
by hand, but this caveat is supersed by the ability of removing unneeded
(and sometime ununderstood) parameters from the method calls.
<P>Before going through the step-by-step procedure, something tells me
that you probably like to <A HREF="excel_automation.zip">download my project
file</A> (96K).
<H4>Step zero: Record your task as Excel macro</H4>
First of all, you will have to be used to Excel's VBA macro language. It
is VERY helpful if you first do the task you'd like to do in Excel while
recording this task as a macro.
<P>The macro will look somewhat like this, hence, that VBA is nicely object
oriented:
<FONT COLOR="#990000">
<P><TT>Sub Macro1()</TT>
<BR><TT> Workbooks.OpenText Filename:="C:\Example.txt",
Origin:= _</TT>
<BR><TT> xlWindows, StartRow:=1,
DataType:=xlDelimited, TextQualifier _</TT>
<BR><TT> :=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _</TT>
<BR><TT> Semicolon:=True, Comma:=False,
Space:=False, Other:=False, _</TT>
<BR><TT> FieldInfo:=Array(1,
1)</TT>
<BR><TT> Cells.Select</TT>
<BR><TT> Selection.Columns.AutoFit</TT>
<BR><TT> Workbook(1).SaveAs Filename:="C:\temp\README.xls",
FileFormat:= _</TT>
<BR><TT> xlNormal, Password:="",
WriteResPassword:="", _</TT>
<BR><TT> ReadOnlyRecommended:=False,
CreateBackup:=False</TT>
<BR><TT> ActiveWindow.Close</TT>
<BR><TT>End Sub</TT>
</FONT>
<BR>
<H4>Step one: Create the Excel automation classes</H4>
In your Excel program directory you will find the Type Library for the
Excel automation. I think, all of you will have the english version, Xl5en32.olb,
along with perhaps some localized version of this (e.g. the german version
is shipped with a Xl5de32.olb).
<P>Using the Class Wizard, choose "New Class" and "from a type library"
(I have the german version, so I don't know the exact english menu commands).
You are then asked to enter the path and filename of the type library.
Having done you get a list of classes. Looking at the Excel macro you see
which classes are needed. I needed five flasses (Application, Range, Workbook,
Workbooks, Worksheet). When in doubt you may choose all classes, but then
you have a bunch of classes that you don't need.
<BR>
<H4>Step two: Add methods for opening and closing Excel</H4>
In your class needing Excel create a member variable holding the Excel's
application object
<FONT COLOR="#990000">
<P><TT><FONT COLOR="#3366FF">class</FONT> CMyDemoDlg : <FONT COLOR="#3366FF">public
</FONT>CDialog</TT>
<BR><TT>{</TT>
<BR><TT><FONT COLOR="#009900"> // the ususal things</FONT></TT>
<BR><TT> ...</TT>
<P><TT><FONT COLOR="#3366FF">private:</FONT></TT>
<BR><TT> Application m_Excel_en;</TT>
<P><TT> ...</TT>
<BR><TT><FONT COLOR="#009900"> // other usual things</FONT></TT>
<BR><TT>}</TT>
</FONT>
<P>and the two functions OpenExcel() and CloseExcel() as follows:
<FONT COLOR="#990000">
<P><TT><FONT COLOR="#3366FF">bool </FONT>CMyDemoDlg::OpenExcel()</TT>
<BR><TT>{</TT>
<BR><TT><FONT COLOR="#3366FF"> if </FONT>(!m_bExcelStarted)
<FONT COLOR="#009900">// this is a member-variable of type bool</FONT></TT>
<BR><TT><FONT COLOR="#3333FF">
if </FONT>(m_Excel.CreateDispatch("Excel.Application"))</TT>
<BR><TT>
{</TT>
<BR><TT><FONT COLOR="#009900">//</FONT></TT>
<BR><TT><FONT COLOR="#009900">// un-comment these lines, is you want to
see what Excel is doing</FONT></TT>
<BR><TT><FONT COLOR="#009900">//</FONT></TT>
<BR><TT><FONT COLOR="#009900">//
CWnd *pExelWnd = CWnd::FindWindow("XLMAIN", NULL);</FONT></TT>
<BR><TT><FONT COLOR="#009900">//
if (pExelWnd )</FONT></TT>
<BR><TT><FONT COLOR="#009900">//
{</FONT></TT>
<BR><TT><FONT COLOR="#009900">//
pExelWnd->ShowWindow(SW_SHOWNORMAL);</FONT></TT>
<BR><TT><FONT COLOR="#009900">//
pExelWnd->UpdateWindow();</FONT></TT>
<BR><TT><FONT COLOR="#009900">//
pExelWnd->BringWindowToTop();</FONT></TT>
<BR><TT><FONT COLOR="#009900">//
}</FONT></TT>
<BR><TT><FONT COLOR="#009900">//
BringWindowToTop();</FONT></TT>
<BR><TT>
m_bExcelStarted=<FONT COLOR="#3366FF">true</FONT>;</TT>
<BR><TT>
}</TT>
<BR><TT><FONT COLOR="#3366FF"> return </FONT>m_bExcelStarted;</TT>
<BR><TT>}</TT>
<P><TT><FONT COLOR="#3333FF">bool </FONT>CMyDemoDlg::CloseExcel()</TT>
<BR><TT>{</TT>
<BR><TT><FONT COLOR="#3333FF"> if </FONT>(m_bExcelStarted)</TT>
<BR><TT> {</TT>
<BR><TT> m_Excel_en.Quit();</TT>
<BR><TT> m_Excel_en.ReleaseDispatch();</TT>
<BR><TT> m_bExcelStarted=false;</TT>
<BR><TT> }</TT>
<BR><TT> <FONT COLOR="#3333FF">return </FONT>m_bExcelStarted;</TT>
<BR><TT>}</TT>
</FONT>
<P>In you application class add to the following lines to InitInstance():
<FONT COLOR="#990000">
<BR><TT>BOOL CMyDemoApp::InitInstance()</TT>
<BR><TT>{</TT>
<BR><TT> // initialize OLE libraries</TT>
<BR><TT> <FONT COLOR="#3333FF">if </FONT>(!AfxOleInit())</TT>
<BR><TT> {</TT>
<BR><TT> AfxMessageBox(IDP_OLE_INIT_FAILED);</TT>
<BR><TT> <FONT COLOR="#3333FF">return</FONT>
FALSE;</TT>
<BR><TT> }</TT><TT></TT>
<P><TT> // do all the other things</TT>
<BR><TT> ...</TT>
<BR><TT> <FONT COLOR="#3333FF">return </FONT>TRUE;</TT>
<BR><TT>}</TT><TT></TT>
</FONT>
<P>In your stdafx-Header you need to add the following line to include
the MFC automation support
<FONT COLOR="#990000">
<BR><TT>...</TT>
<BR><TT><FONT COLOR="#3333FF">#include </FONT><afxdisp.h>
<FONT COLOR="#009900">// MFC OLE-automation classes</FONT></TT>
<BR><TT>...</TT>
<BR><TT></TT>
</FONT>
<H4>Step three-and-four-combined: Start building the Excel macro as C++ Code while modifying the automation classes.</H4>
For every parameter you need, create a local variable of type COleVariant.
In Excel's VBA you can omit parameter not needed. In the Excel automation
classes you can omit the parameters you do not need IF AND ONLY IF they
are at the end of the method's parameters list. In the Excel-class create
a new overloaded member function with fewer parameters.
<P>All automation functions, whose return value is used to create another automation
object should be modified to return a LPDISPATCH pointer rather than a VARIANT!
<P>So, for example, you should take the method
<FONT COLOR="#990000">
<BR><TT>VARIANT Application::Worksheets(<FONT COLOR="#3366FF">const </FONT>VARIANT&
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -