Prev: mySQL and cftransaction
Next: cfreport
From: kruse on 7 Mar 2006 03:30
I have tried to locate the error.

The com connection works fine


<CFTRY>
<!--- If it exists, connect to it --->
<CFOBJECT
ACTION="CONNECT"
CLASS="Excel.Application"
NAME="objExcel"
TYPE="COM"
>
<CFCATCH>
<!--- The object doesn't exist, so create it --->
<CFOBJECT
ACTION="CREATE"
CLASS="Excel.Application"
NAME="objExcel"
TYPE="COM"
>
</CFCATCH>


I have tried to comment some of the lines out when I execute commands on the
object.
The result is shown beneath.

<CFSCRIPT>
objExcel.Visible = false;
/*objExcel.DisplayAlerts = false; */
objWorkBook = objExcel.Workbooks;
/* objOpenedBook = objWorkBook.Open("#Attributes.FilePath#");*/
/* objSheets = objOpenedBook.Sheets;*/
/* numOfSheets = objSheets.Count;*/
</CFSCRIPT>

I think it is strange that the Visible and Workbooks does work. But the rest
does throw an error if they are used?


From: kruse on 7 Mar 2006 03:50
I have made some more tests.
<CFSET test = objExcel.Visible > WORKS
<CFSET test = objExcel.DisplayAlerts> DOES NOT WORK.
throws the well known error 0x80010105.

This is verry strange. When I do the test locally on my dev env it works.



From: ksmith on 7 Mar 2006 09:56
Hi,
Well I find a bunch of things here. First, I am able to make your original
code work here. I am using cfmx7.01 and msoffice2003 sp2 also. The first
thing I find goes along with microsoft's warning about not using a clientless
account. Everything works if I have started cfmx from the commandline or I
setup the CFMX appserver service to run as a domain account. Without the
domain account it fails and I see instances of DW20.exe running. DW20 is
microsoft's error and reporting module for office. So, I guess you should try
running CFMX under a named account per the following
http://www.macromedia.com/go/tn_17279

Next, I would start using some of the new features of CFMX to make your COM
calls a little more robust. In CFMX it is possible to use java stubs to make
your calls to the msoffice programs. This can speed things up and even
sometimes cure specific method call failures. In your case, all that is needed
to do this is to replace the programID you are using in the cfobject calls from
"Excel.Application" to "Excel.Application.10". You can read about this at
http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001582.htm#1148538.
Just realize for msoffice the stubs and xml file are already created for you
and ready to use.

The next enhancement is the releasecomobject function. CFMX has this function
and it will release excel.application from memory. When I use this function
call at the end of your page, I never see more than 1 instance of excel.
Without I see 2 or more.

I also see on the cfobject/create code working, never the connect. That is
probably a good thing. I would suspect that cfobject/connect could put you in
the situation where you temporarilly connect to a COM object that is about to
shutdown to be garbage collected. The developer's guide does mention putting
COM objects into the application scope as a way to make things more efficient.
You could test it. The procedure is discussed in the same chapter of the
Developer's Guide that I refered you to already.

Finally, you might consider completely moving away from COM. COM is somewhat
inefficient when called from java, as CFMX does. It also requires that you
have msoffice on the CFMX server and puts you at its mercy as changes are made
(see first paragraph). Apache has a project http://jakarta.apache.org/poi/
which replaces the calls to COM object with calls to Java objects. Often the
exact same syntax for you COM object method calls will work with POI. You
simply do a cfobject/java instead of cfobject/COM. You will probably find all
methods and properties that expect a visible window will go away with POI. So,
things like "objExcel.visble=false" and displayAlerts should not be called.

HTH

From: ksmith on 7 Mar 2006 10:33
It turns out the POI jar file is already installed with cfmx7. So, here is a
quick working example doing the NumberOfSheets in your test. Run that attached
code after you point it to an excel file on your system. You should still
download POI from Apache to get the docs and API guide.



<CFSCRIPT>
fs =
createobject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem");
fsSTR=createobject("java","java.io.FileInputStream");
fs.init(fsSTR.init("C:\Documents and Settings\ksmith\My
Documents\current.xls"));
//POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream("workbook.xls"));
wb = createobject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook");
wb.Init(fs);
</CFSCRIPT>

<H2>number sheets: <cfoutput>#wb.getNumberOfSheets()#</cfoutput></H2>

From: kruse on 8 Mar 2006 02:22
Thanks alot Ken.

I was starting the CF service with a domain user.
When I tried Excel.Application.10 instead of Excel.Application it worked just
fine.

Yesterday I started to look at the customtag cfx_excel which does the job
great through POI.
But I think that I will give my own version through POI i try. I think that It
will be faster than the COM version.

Thank you for your great help.

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: mySQL and cftransaction
Next: cfreport