C# code hangs on Excel.Wookbooks.Open

[TeaAddictedGeek]

I'm attempting to open up an Excel workbook, and it hangs on the following code:

Excel.Workbook wb = exapp.Workbooks.Open(filename,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);

I can't figure out what the problem is, and debugging has not proved to be very fruitful.


Thanks!



"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[jmeckley]

you need to dispose of excel objects when your done with them. If excel is hanging then it may be in use already.
either use the [tt]using(){}[/tt] keyword or [tt]try{}catch{}finally{excel.dispose();}[/tt]

Also you will need MS Excel/Office installed on the server if your using the InterOp libraries.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TeaAddictedGeek]

Microsoft Excel as well as the entire Office package has been installed and the appropriate permissions have already been set up for the user calling the application within Component Services/DCOM. That part is not the issue.

It's already within a try/catch block with lines of code beneath it. It's not even making it to those lines of code. Should it be within its own try/catch block?



"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[jmeckley]

it doesn't need to be in it's own try/catch. check your process list to make sure all instances of excel are closed, and then try launching the application. whatch for when excel is first instantiated and make sure it's then disposed of.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TeaAddictedGeek]

Excel is indeed launched, but it still hangs. Do you think that Excel itself is hanging and preventing the rest of the C# code from being executed? If the rest isn't being executed, it's not going to reach any of the disposal methods.



"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[jmeckley]

It may be the that file it's attempting to open is hanging the process. place an excel file with the App_Data directory of the website. then have the process open that excel file. If that doesn't hang then the problem may be with accessing the file.

another test is to open/close excel just to make sure it's not excel that is hanging, but the file excel is attempting to open.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TeaAddictedGeek]

Still no luck.

I've tried opening up Excel normally, that's fine.
I've tried directly opening up the file it's attempting to access in the code, and that opens fine.
I've tried making sure that the permissions are all set in the folders the code is trying to access, and that's also fine.

I can't think of what I could be missing or overlooking. The file is fine, Excel is fine, and the permissions are fine.



"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[jmeckley]

what do you mean by normally?
do you mean opening the file manually (either double clicking the file, or opening excel and then the file)?

Or do you mean normally to mean via code/website you opened excel, and then closed excel?

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TeaAddictedGeek]

By normally, I mean manually to make sure nothing was causing Excel itself to hang.

I also tried double clicking on the file to see if Excel would open it up normally, and that worked out fine also.

The only line of code this program gets to that runs fine thus far is oXL = new Excel.ApplicationClass(). Then it tries to execute oWB = oXL.Workbooks.Open(...) and it just hangs. I see Excel in the task manager, but that's about it.



"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[jmeckley]

this means you can launch excel without any issues from the web app. you can probally close it as well. This leads me to the actual file itself. and possiblly a permissions issue.

add the following code just before you open attempt to open the workbooks

if (!System.IO.File.Exists(filename))
{
   throw new System.IO.IOException("cannot see file " + filename);
}
else
{
   using(Stream stream = new FileStream(filename)) { }
}

then step through your code. if this excpetion is thrown then you have a permissions issue accessing the file via the website.
if not note how long it takes for the stream to open/close. if this doesn't take a long time the problem is most likely with the excel ojects. If it does take a long time to open the problem is accessing the file.

Is the file on a remote box? Remote meaning not on the same server as the website? If so then you probally need to activate delegation between the IIS server and the remote box.


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TeaAddictedGeek]

I tested the file and the code sees it fine, no errors, and took almost no time to process.

The file is on the same server as the code that is running.



"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[jmeckley]

Then that leads me to believe it's an excel interop issue. try this

excel = new Excel.ApplicationClass();
foreach(WorkBook workbook in excel.WorkBooks) workbook.Close();
workbook = oXL.Workbooks.Open(filename);

if that still gives you problems try creating simple console app which would do the same thing. if the console works it's a problem between asp.net and excel. If it doesn't work it is just an excel interop issue.

if your still having problems. you may want to try forum68 or forum707.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TeaAddictedGeek]

There is no Workbook.Close() method as you describe. It expects parameters. I've also tried running it as a windows service but get the same problem.

I had issues with the Interop.Excel transporting the code from one server to another as this server first had .NET 2.0 installed with a different version of the dll which was incompatible with this version of the code (.NET 1.1 and a different Interop.Excel dll), and initially it was grabbing the .NET 2.0 Excel dll and not the one we wanted. I did a quick workaround of putting the right dll into an includes directory and referencing that instead, but do you think that the double installation of the .NET and the dll issue is at fault here? If so, this is a great argument for doing what I have been trying to convince the network admins to do, which is uninstall .NET 2.0 and reinstall .NET 1.1 to make sure that the right dlls are being called from GAC.

Please let me know if this makes any sense. Sorry to be so long-winded.



"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[jmeckley]

1.1 and 2.0 are suppose to run parrallel to each other so that shouldn't be an issue. however some configuration may need to be done to ensure the correct version is used. (not sure how that's done, but I have heard about it).

At this point I'm out of ideas. In a last ditch effort you could try Carlos Args ExcelWriter. this tool uses the excel xml format to minipulate excel files. it's faster than the interops. and simple to implment. however I don't know where the excel file comes from, or how it's generated. this may affect the tool. I don't think it can open excel binary files.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TeaAddictedGeek]

That would sadly require rewriting an awful lot of code so I hope to not go down that route.

Another question: I have Excel 2003 installed on this machine and was originally working with the dll for 2002. Is it possible that I can't open 2003 with the older Interop.Excel dll?



"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum

 

[jmeckley]

that's certainly possible.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TeaAddictedGeek]

Solved the problem a few days ago--apparently you cannot use open an Excel workbook from a web app. This code works just fine in a windows console program.



"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum