📄 excel_automation.shtml
字号:
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 macro, 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>
<P>Some people wondered how I know the numerical value of the Excel constants, like xlWindows,
xlExcel4 and so on. This is simple, but not trivial. Follow this step by step procedure:
<p>A) Run Excel with an empty (or not) worksheet
<p>B) Use Insert - Macro - Visual Basic Module
<p>C) Type in a short dummy function. For example
<PRE><TT><FONT COLOR="#990000">
<p> Sub Dummy()
<br> test = 1
<br> End Sub
</FONT></TT></PRE>
<p> Make sure you have the proper language settings, this is important
only for users having a localized (non-english) version. For these
languages Excel allows you to use the VBA with language-dependend
keywords. If you are somewhat used in Basic with it'`s english keywords
then it feels HORRIBLE to use the (badly translated)
german-or-whatever-language-you-use keywords :)
<p>[For our german-tongued friends:
<PRE><TT><FONT COLOR="#990000">
<p>For i=1 To 11 Step 2
<br>Do.Some.Thing()
<br>Next i
</FONT></TT></PRE>
<p>will become
<PRE><TT><FONT COLOR="#990000">
<p>F黵 i=1 bis 11 Schrittweite 2
<br>Tue.Irgend.Etwas()
<br>N鋍hste i
</FONT></TT></PRE>
<p>horrible...... :(
]
<p>D) Put the cursor back to the second line (test=1) and start the single
step debugger(F8 key on my german system, or use Execute - single Step)
<p>E) The debugger window pops up. Go to the "Watch" tab, make a right
mouse click into the upper half (the watch area) and choose "Add Watch",
just like you do in DevStudio. As the watch expression just enter the
constant you need the value of, for example xlExcel4.
<p>F) Ready. The watch window now tells you the value of xlWhateverYouLike
<p>Some people wondered how I know the numerical value of the Excel constants. This is easy: just start excel with a short makro like this one:
<PRE><TT><FONT COLOR="#990000">Sub dummy()
anotherDummy = 1
End Sub </FONT></TT></PRE>
<p>Then trace into this macro with the single step debugger, go to the watch window and add as watch variable the constant you need. If you enter xlWindows,
for example the watch window will tell you it has the value 2. There you are!<br>
<p><H3>Step five: At the end, put all together</H3>
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 :)
<br><p>IMPORTANT NOTE (due to a lot of emails concerning this): this demonstration code DOES NOT WORK with Excel 97. It was only tested and proved
working with Excel 5.0 and Excel 7.0 (some call it Excel 95) on both Windows NT 4.0 and Windows 95. I do not have Excel 97, if anyone is willing to
sponsor me a licence, so please, and I will try to do a good job....
If you find any errors or typos, please feel free to drop a mail.
<P>Posted: 15 May 1998
<P>
<HR>
<TABLE BORDER=0 WIDTH="100%" >
<TR>
<TD WIDTH="33%"><FONT SIZE=-1><A HREF="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><!--#exec cgi="../cgi/counters/counter.cgi"--></FONT></CENTER>
</BODY>
</HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -