📄 excel_automation.shtml.htm
字号:
<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 makro 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 ommit 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 RETURNING ANYTHING 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&
Index)</TT>
<BR><TT>{</TT>
<BR><TT> VARIANT result;</TT>
<BR><TT> <FONT COLOR="#3333FF">static </FONT>BYTE parms[]
= VTS_VARIANT;</TT>
<BR><TT> InvokeHelper(0x1ee, DISPATCH_METHOD, VT_VARIANT,
(<FONT COLOR="#3366FF">void</FONT>*)&result, parms, &Index);</TT>
<BR><TT> <FONT COLOR="#3333FF">return</FONT> result;</TT>
<BR><TT>}</TT>
</FONT>
<P>and create two new methods (the second with an omitted parameter)
<FONT COLOR="#990000">
<P><TT>LPDISPATCH Application::Workbooks(const VARIANT& Index)</TT>
<BR><TT>{</TT>
<BR><TT> <FONT COLOR="#009900">// function changed by
T.B.</FONT></TT>
<BR><TT> LPDISPATCH result;</TT>
<BR><TT> <FONT COLOR="#3333FF">static </FONT>BYTE parms[]
= VTS_VARIANT;</TT>
<BR><TT> InvokeHelper(0x23c, DISPATCH_METHOD, VT_DISPATCH,
(<FONT COLOR="#3333FF">void</FONT>*)&result, parms,</TT>
<BR><TT> &Index);</TT>
<BR><TT> <FONT COLOR="#3333FF">return </FONT>result;</TT>
<BR><TT>}</TT><TT></TT>
<P><TT>LPDISPATCH Application::Workbooks()</TT>
<BR><TT>{</TT>
<BR><TT><FONT COLOR="#009900"> // new by T.B.</FONT></TT>
<BR><TT> LPDISPATCH result;</TT>
<BR><TT> InvokeHelper(0x23c, DISPATCH_METHOD, VT_DISPATCH,
(<FONT COLOR="#3333FF">void</FONT>*)&result, NULL);</TT>
<BR><TT> <FONT COLOR="#3333FF">return </FONT>result;</TT>
<BR><TT>}</TT>
</FONT>
<P>To keep in mind, that you did something with the type library it is
good practice to enter a short comment saying "I did something".
<P>Do this anytime you are using a return value implicit or explicit.
<P>My makro, ported to C++ now looks like this
<FONT COLOR="#990000">
<P><TT><FONT COLOR="#3333FF">void</FONT> CMyDemoDlg::DoExcelConversion(CString
File)</TT>
<BR><TT>{</TT>
<BR><TT> CString XlsFile =</TT>
<BR><TT>
Do.Some.Operations.To.Calculate.The.Excel.File.Name(File);</TT>
<BR><TT> </TT>
<BR><TT><FONT COLOR="#009900"> // or say simply:</FONT></TT>
<BR><TT><FONT COLOR="#009900"> // XlsFile="C:\\temp\\demo.xls";</FONT></TT><TT></TT>
<P><TT> COleVariant FilenameOpen(File),</TT>
<BR><TT> FilenameSave(XlsFile),</TT>
<BR><TT> Origin((<FONT COLOR="#3333FF">short</FONT>)2),
<FONT COLOR="#009900">// xlWindows</FONT></TT>
<BR><TT> StartRow((<FONT COLOR="#3333FF">short</FONT>)1),</TT>
<BR><TT> DataType((<FONT COLOR="#3333FF">shor</FONT>t)1),
<FONT COLOR="#009900">// xlDelimited</FONT></TT>
<BR><TT> TextQualifier((<FONT COLOR="#3333FF">short</FONT>)1),
<FONT COLOR="#009900">// xlDoubleQuote</FONT></TT>
<BR><TT> ConsecutiveDelimiter((<FONT COLOR="#3333FF">long</FONT>)FALSE,
VT_BOOL),</TT>
<BR><TT> Tab((<FONT COLOR="#3333FF">long</FONT>)FALSE,
VT_BOOL),</TT>
<BR><TT> Semicolon((<FONT COLOR="#3333FF">long</FONT>)TRUE,
VT_BOOL),</TT>
<BR><TT> Fileformat((<FONT COLOR="#3333FF">short</FONT>)33),
<FONT COLOR="#009900">// xlExcel4</FONT></TT>
<BR><TT> Save((<FONT COLOR="#3333FF">long</FONT>)FALSE,
VT_BOOL);</TT><TT></TT>
<P><TT> TRY</TT>
<BR><TT> {</TT>
<BR><TT> Workbooks Wbs(m_Excel_en.Workbooks());</TT><TT></TT>
<P><TT> Wbs.OpenText(FilenameOpen, Origin,
StartRow, DataType,</TT>
<BR><TT>
TextQualifier, ConsecutiveDelimiter, Tab, Semicolon);</TT><TT></TT>
<P><TT> Range ran(m_Excel_en.Columns(COleVariant("A:Z")));</TT>
<BR><TT> </TT>
<BR><TT> ran.AutoFit();</TT><TT></TT>
<P><TT> Workbook Wb(m_Excel_en.Workbooks(COleVariant((short)1)));</TT><TT></TT>
<P><TT> Wb.SaveAs(FilenameSave, Fileformat);</TT><TT></TT>
<P><TT> Wb.Close(Save);</TT><TT></TT>
<P><TT><FONT COLOR="#009900"> // perhaps
you want to delete the text file,</FONT></TT>
<BR><TT><FONT COLOR="#009900"> // because
now you have an Excel sheet</FONT></TT>
<BR><TT><FONT COLOR="#009900"> // DeleteFile(File);</FONT></TT>
<BR><TT> }</TT>
<BR><TT> CATCH(COleDispatchException, e)</TT>
<BR><TT> {</TT>
<BR><TT> TRACE(e->m_strDescription);</TT>
<BR><TT> MessageBox("Error
creating Excel-file:\n"+e->m_strDescription,</TT>
<BR><TT>
"My Demo Dialog", MB_OK);</TT>
<BR><TT> }</TT>
<BR><TT> END_CATCH;</TT>
<BR><TT>}</TT>
<BR>
</FONT>
<H4>Step five: At the end, put all together</H4>
Now you are nearly done. Somehow retrieve the full path of the file you
wish to convert and do the following tree lines:
<FONT COLOR="#990000">
<P><TT> OpenExcel(); <FONT COLOR="#009900">// may
be called more than one time</FONT></TT>
<BR><TT> DoExcelConversion(FullPathName);</TT>
<BR><TT> CloseExcel(); <FONT COLOR="#009900">// may be
called without a preceeding OpenExcel()</FONT></TT>
<BR>
</FONT>
<P>Now, at the end of my 200 lines page, I hope you are successful creating
your Excel classes and all the rest. If you someday have created a neatly
sold application using this knowledge and have made a billion dollar, remember
me and send me a check :)
<P>Posted: March, 8, 98
<P>
<HR>
<TABLE BORDER=0 WIDTH="100%" >
<TR>
<TD WIDTH="33%"><FONT SIZE=-1><A HREF="../index.htm" tppabs="http://www.codeguru.com/">Goto HomePage</A></FONT></TD>
<TD WIDTH="33%">
<CENTER><FONT SIZE=-2>© 1997 Zafir Anjum</FONT> </CENTER>
</TD>
<TD WIDTH="34%">
<DIV ALIGN=right><FONT SIZE=-1>Contact me: <A HREF="mailto:zafir@home.com">zafir@home.com</A> </FONT></DIV>
</TD>
</TR>
</TABLE>
<CENTER><FONT SIZE=-2>3394</FONT></CENTER>
</BODY>
</HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -