Re: Error

Sounds like a bug in your code (or other software libs you use?) which
call the writeExcelToStream method.

1. Are you using shared workbooks? Macros are either disabled or very
error-prone in shared workbooks, which is where multiple users can open
a workbook at a single location for read-write access, using NTFS
non-locking write mechanism.

2. Are you opening multiple workbooks with this method and plugging in
the hard-coded path "C:\temp\temp.xls"? Obviously, if you have one
workbook open on your system, then open another workbook and try to save
it to the same path, you'll get an error -- non-shared workbooks open
with the NTFS lock write mechanism, preventing other programs from
overwriting the data.

3. How do you know that the client has the folder c:\temp? It's possible
that it doesn't exist, or their system drive is not C: but Q: or
something. If possible, use the Java environment to get an environment
variable like %TEMP% or %TEMPDIR% -- see if TEMP/TEMPDIR is a non-zero,
non-null string; and if it is, use that location instead of C:\temp.

4. I would recommend adding a random number or random string after the
name of the file, like c:\temp\temp1239092490.xls. That way, you can
have multiple instances of the client web application, multiple
instances of this routine, etc. all running in tandem on a single system
without encountering file naming problems.

5. If you get other error messages when dealing with absolutely huge
files, check to make sure that the getBytesFromFile() method is
correctly writing the file output. If there are more bytes in the file
than Integer.MAX_VALUE, you will overflow the array bounds, right? You
might have to split it up into Integer.MAX_VALUE chunks. For your
reference, 2.14 gigabytes (2147483647 bytes) would be the limit of bytes
you could have if your array is dimensioned from 0 to Integer.MAX_VALUE.


The problem in #5 is actually pretty interesting - I learned something
about it to verify my intuition. Launch a BeanShell and try the
following:

boolean[] hugearray =3D new boolean[Byte.MAX_VALUE]; //allocates 255 =
bits
of storage
print(hugearray.length); //prints 255
hugearray =3D new boolean[Integer.MAX_VALUE/2]; /*allocates about a
billion bits, or 125 million bytes, or 125 megs of storage. This line of
code was taking quite a long time on my system, so I stopped it. I can't
imagine how long it would take to allocate this many bytes.*/
hugearray =3D new boolean[Integer.MAX_VALUE]; /*allocates the maximum
array possible, about 250 megs of storage -- note this is because we are
allocating booleans, which take 1 bit, instead of bytes like you use,
which take 1 byte each!*/
hugearray =3D new boolean[Integer.MAX_VALUE+1]; //generates an =
exception.
Any value larger than Integer.MAX_VALUE generates an exception

Since getBytesFromFile returns a byte array, and since each byte of your
Excel file consumes one space in the array, any files larger than 2.1 GB
will generate an exception. I can see a 2.1 GB Excel file containing
either billions of rows across hundreds of worksheets, or hundreds of
OLE objects like Word documents, bitmaps, or PDF files. I believe the
structured storage file format does support this file size, particularly
in Excel 2003 and later.

Use the capabilities of the processor and don't allocate all that space
in RAM at once -- decide on a block size (maybe 1 megabyte or so);
allocate an array of that size in getBytesFromFile; and return that
array into a loop that continually writes the array until
getBytesFromFile returns null. Then, implement getBytesFromFile to
return null if there are no more bytes to write. Make sure that you
don't get a byte array containing empty zero-bytes at the end when you
allocate the last chunk -- re-initialize the array to have the exact
number of elements that is the remaining amount in the stream.



Again, this error really doesn't sound like a problem with Jawin, or
even a problem with the way you automate Excel -- all that seems fine; I
have read and understand your Jawin/Excel code. Besides -- you would get
an automation error of a different sort if your Excel code were causing
some problem. This sounds like a file I/O problem or file size problem,
one way or the other.

Good luck,

-Sean

-----Original Message-----
From: Discussion of Java/Win32/COM integration with Jawin
[mailto:[email protected]] On Behalf Of j s
Sent: Thursday, August 17, 2006 8:16 AM
To: [email protected]
Subject: [JAWIN] Error

Hi .

Im trying to use jawin , for opening an excel workbook , execute the
macros and close the work book.

public  void writeExcelToStream(String xlsFilePath, OutputStream outStr,
int noOfSheets)
        throws Exception
        {
                try
                {
                        Ole32.CoInitialize();
                        DispatchPtr excelApp =3D new
DispatchPtr("Excel.Application");
                        DispatchPtr workbooks =3D
(DispatchPtr)excelApp.get("Workbooks");
                        workbooks.invoke("Open", xlsFilePath);
                        DispatchPtr activeWorkbook =3D
(DispatchPtr)excelApp.get("ActiveWorkbook");

                        if(noOfSheets > 0)
                        {
                                for(int shNo=3D2; shNo <=3D noOfSheets;
shNo++)
                                {
                                        DispatchPtr tmpSheet =3D
(DispatchPtr)activeWorkbook.get("Sheets", new Integer(shNo));
                                        tmpSheet.invoke("Activate");
                                }
                                DispatchPtr tmpSheet =3D
(DispatchPtr)activeWorkbook.get("Sheets", new Integer(1));
                                tmpSheet.invoke("Activate");
                        }

                        activeWorkbook.invoke("Save");
                        activeWorkbook.invoke("Close");
                        excelApp.invoke("Quit");
                        //Ole32.CoUninitialize();

                        File xlFile =3D new File(xlsFilePath);
                        byte[] xlsContent =3D getBytesFromFile(xlFile);
                        outStr.write(xlsContent);
                        xlsContent=3Dnull;
                        outStr.close();
                        xlFile.delete();

                }
                catch (COMException e) {
                  System.out.println("Got Expected Error: " + e);
                }
                catch(Exception ex){
                        logger.fatal("Write Excel to Stream operation
failed ", ex);
                        throw ex;
                }
                finally
                {
                        Ole32.CoUninitialize();
                }
        }

This is the code I have used to open the workbook and push the output
back into Excel .

It works fine when one user logs in and view it .

Now if I have huge worksheet with a lot of data , come across a lot of
errors esp when there are  5 to 10 users trying to execute the same
program from a web front end . Any guidance on this will be helpful.
[com.symphonyrpm.applayer.common.util.JawinUtility]
[writeExcelToStream] [?] - Write Excel to Stream operation failed
org.jawin.COMException: 800a03ec: Microsoft Office Excel cannot access
the file 'C:\Temp\temp'. There are several possible reasons:

* The file name or path does not exist.
* The file is being used by another program.
* The workbook you are trying to save has the same name as a currently
open workbook.[src=3DMicrosoft Office
Excel,guid=3D{00000000-0000-0000-0000-000000000000}]
        at
org.jawin.marshal.GenericStub.dispatchInvoke0(Native
Method)
        at
org.jawin.marshal.GenericStub.dispatchInvoke(GenericStub.java:201)
        at
org.jawin.DispatchPtr.invokeN(DispatchPtr.java:587)
        at
org.jawin.DispatchPtr.invokeN(DispatchPtr.java:555)
        at org.jawin.DispatchPtr.invoke(DispatchPtr.java:501)
        at
com.symphonyrpm.applayer.common.util.JawinUtility.writeExcelToStream(Unk
nown
Source)
        at
com.symphonyrpm.applayer.workspace.util.ExportRenderer.exportPageToExcel
(Unknown
Source)
        at
com.symphonyrpm.applayer.workspace.action.ExportRendererAction.exportExc
el(Unknown
Source)
        at
com.symphonyrpm.applayer.workspace.action.ExportRendererAction.perform(U
nknown
Source)
        at
org.apache.struts.action.Action.execute(Action.java:420)
        at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestPr
ocessor.java:484)
        at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
274)
        at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
        at
com.symphonyrpm.applayer.common.servlets.RPMBaseActionServlet.process(Un
known
Source)
        at
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
        at
javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at
javax.servlet.http.HttpServlet.service(HttpServlet.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.ja
va(Compiled
Code))
        at
com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.ja
va:1239)
        at
com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterCh
ain.java(Compiled
Code))
        at
com.symphonyrpm.applayer.common.filters.ResponseHeaderFilter.doFilter(Un
known
Source)
        at
com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInst
anceWrapper.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterCh
ain.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterC
hain.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrap
per.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheS
ervletWrapper.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java(Com
piled
Code))
        at
com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java(C
ompiled
Code))
        at
com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscriminatio
n(HttpInboundLink.java(Compiled
Code))
        at
com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformatio
n(HttpInboundLink.java(Compiled
Code))
        at
com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpIC
LReadCallback.java(Compiled
Code))
        at
com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueMa
nager.java(Compiled
Code))
        at
com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.
java(Compiled
Code))
        at
com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.
java(Compiled
Code))
        at
com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager
.java(Compiled
Code))
        at
com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled
Code))
[8/17/06 15:41:31:236 IST] 000000a7 SystemOut     O
FATAL {2006-08-17 15:41:31,236} [SERVER]
[com.symphonyrpm.applayer.workspace.util.ExportRenderer]
[exportPageToExcel] [?] - Error occured while exporting page to excel
org.jawin.COMException: 800a03ec: Microsoft Office Excel cannot access
the file 'C:\Temp\temp'. There are several possible reasons:

* The file name or path does not exist.
* The file is being used by another program.
* The workbook you are trying to save has the same name as a currently
open workbook.[src=3DMicrosoft Office
Excel,guid=3D{00000000-0000-0000-0000-000000000000}]
        at
org.jawin.marshal.GenericStub.dispatchInvoke0(Native
Method)
        at
org.jawin.marshal.GenericStub.dispatchInvoke(GenericStub.java:201)
        at
org.jawin.DispatchPtr.invokeN(DispatchPtr.java:587)
        at
org.jawin.DispatchPtr.invokeN(DispatchPtr.java:555)
        at org.jawin.DispatchPtr.invoke(DispatchPtr.java:501)
        at
com.symphonyrpm.applayer.common.util.JawinUtility.writeExcelToStream(Unk
nown
Source)
        at
com.symphonyrpm.applayer.workspace.util.ExportRenderer.exportPageToExcel
(Unknown
Source)
        at
com.symphonyrpm.applayer.workspace.action.ExportRendererAction.exportExc
el(Unknown
Source)
        at
com.symphonyrpm.applayer.workspace.action.ExportRendererAction.perform(U
nknown
Source)
        at
org.apache.struts.action.Action.execute(Action.java:420)
        at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestPr
ocessor.java:484)
        at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
274)
        at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
        at
com.symphonyrpm.applayer.common.servlets.RPMBaseActionServlet.process(Un
known
Source)
        at
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
        at
javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at
javax.servlet.http.HttpServlet.service(HttpServlet.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.ja
va(Compiled
Code))
        at
com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.ja
va:1239)
        at
com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterCh
ain.java(Compiled
Code))
        at
com.symphonyrpm.applayer.common.filters.ResponseHeaderFilter.doFilter(Un
known
Source)
        at
com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInst
anceWrapper.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterCh
ain.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterC
hain.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrap
per.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheS
ervletWrapper.java(Compiled
Code))
        at
com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java(Com
piled
Code))
        at
com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java(C
ompiled
Code))
        at
com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscriminatio
n(HttpInboundLink.java(Compiled
Code))
        at
com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformatio
n(HttpInboundLink.java(Compiled
Code))
        at
com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpIC
LReadCallback.java(Compiled
Code))
        at
com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueMa
nager.java(Compiled
Code))
        at
com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.
java(Compiled
Code))
        at
com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.
java(Compiled
Code))
        at
com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager
.java(Compiled
Code))
        at
com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com