Excel Save problem from C# application

[Sandeep Gaikwad]

I am writing a Windows service in C# 4.0.
The application functions as follows:
1) Reads a input stream from some other application
2) Saves the stream in a .tmp file
3) Opens the .tmp in excel object
4) Copies all contents in all worksheet and pastes it as values
5) Save the file
6) Return the .tmp file contents as stream to calling application

I am using Excel 2007

90/100 times it works fine, but on few occasions when the execution
code hits the Excel.SaveAs(outputFilename), the execution seems to be
at stand still. No exception, no alert, nothing and it doesn't seems
to move ahead.

The Excel instance is seen in the task manager.

I am at lost to understand what could be the issue.

Here is the code snippet:

void processWorkbook(Application applicationProcess, string
inFileName, string outFileName, Dictionary<String, String>
requestParms)
{
String xlPrecisionAddInPath = "";
String isDebugOn = "";
Microsoft.Office.Interop.Excel.Worksheet wrkSht =null;
Microsoft.Office.Interop.Excel.AddIn xlAddIn = null;
Microsoft.Office.Interop.Excel.Range excelRng = null;
try
{
File.Copy(inFileName, outFileName, true);
excelLogger.writeLogFile("before opening file in
Excel");
applicationProcess.Workbooks.Open(outFileName);
excelLogger.writeLogFile("After opening file in
Excel");
wrkSht =
(Microsoft.Office.Interop.Excel.Worksheet)applicationProcess.Worksheets[1];
excelLogger.writeLogFile("After loading worksheet");
wrkSht.Select();
excelLogger.writeLogFile("Total Number of Add-ins
found on server= " + applicationProcess.AddIns.Count);
for (int i = 1; i <= applicationProcess.AddIns.Count; i
++)
{
excelLogger.writeLogFile("Name of Add-in = " +
applicationProcess.AddIns.Name.ToString());
}
/*
* When excel object is created through an application,
the Add-ins are not loaded by default, even if we see them in the
* add-in window. Hence we need to explicitly un-install
and install the xlPrecision add-in, this allows the xlPrecision
* to load properly and all the excel formulae works as
expected
*
* get the path where xlprecision is installed on the
server by reading following registry entries:
* <Clsid> = HKEY_CLASSES_ROOT
\xlPrecision.cls_xlPrecision\Clsid
* <file path> = HKEY_CLASSES_ROOT\CLSID\<Clsid>
\InprocServer32
*/
excelLogger.writeLogFile("Attempting to read registry
path HKEY_CLASSES_ROOT\\xlPrecision.cls_xlPrecision\\Clsid to find
where xlprecision is installed");
logging.logInformation("Debug", "POIHandler",
"processWorkbook", "Attempting to read registry path HKEY_CLASSES_ROOT\
\xlPrecision.cls_xlPrecision\\Clsid to find where xlprecision is
installed");
excelLogger.writeLogFile("before reading registry");
RegistryKey rKey =
Registry.ClassesRoot.OpenSubKey("xlPrecision.cls_xlPrecision\\Clsid");
if (rKey.Equals(null))
{
excelLogger.writeLogFile("No registry entry found
for xlprecision. xlPrecision cannot be loaded");
logging.logInformation("Debug", "POIHandler",
"processWorkbook", "No registry entry found for xlprecision at
registry path: Software\\VB and VBA Program Settings\\xlPrecision.
xlPrecision cannot be loaded");
//If regisrty entry not found/ cannot be read.
Give a shot by reading where xlprecision is installed as below
xlPrecisionAddInPath =
applicationProcess.AddIns[addInName].FullName;
}
else
{
String Clsid = (String)rKey.GetValue("");
excelLogger.writeLogFile("After reading registry,
value of clsid= " + Clsid);
excelLogger.writeLogFile("Now reading
registry :CLSID\\" + Clsid + "\\InprocServer32");
rKey = Registry.ClassesRoot.OpenSubKey("CLSID\\" +
Clsid + "\\InprocServer32");
xlPrecisionAddInPath =
(String)rKey.GetValue("").ToString().Replace("dll", "xla");
}
rKey.Close();
// xlPrecisionAddInPath = "C:\\Program Files\\xlPrecision\
\xlPrecision.xla"; //xlPrecision add-inpath should be like this (path
may differ as per installations)
excelLogger.writeLogFile("Found xlPrecision add-in
installed at path: " + xlPrecisionAddInPath);
logging.logInformation("Debug", "POIHandler",
"handleRequest", "Found xlPrecision add-in installed at path: " +
xlPrecisionAddInPath);
if (xlPrecisionAddInPath.Equals("")) //if it is still
blank, means could not found where xlPrecision is installed or
xlprecision is not installed
{
excelLogger.writeLogFile("No registry entry found
for xlprecision. xlPrecision cannot be loaded");
logging.logInformation("Error", "POIHandler",
"processWorkbook", "No registry entry found for xlprecision at
registry path: Software\\VB and VBA Program Settings\\xlPrecision.
xlPrecision cannot be loaded");
throw new Exception("No registry entry found for
xlprecision. xlPrecision cannot be loaded");
}
else
{
excelLogger.writeLogFile("Explicitly adding
xlPrecision to excel object");
xlAddIn =
applicationProcess.AddIns.Add(xlPrecisionAddInPath,
System.Type.Missing);
xlAddIn.Installed = false;
excelLogger.writeLogFile("After unloading
xlPrecision from the excel object");
xlAddIn.Installed = true;
excelLogger.writeLogFile("After loading
xlPrecision from the excel object");
applicationProcess.CalculateFull();//this method
will invoke the Ctrl+Alt+F9 of excel ( i.e execute all formulae in all
sheets of excel)
excelLogger.writeLogFile("After call to calculate
full");
if (requestParms.ContainsKey("IsDebugOn"))//this
key will consist of flag which will indicate wheather to write all
excel cells as value
{
isDebugOn =
requestParms["IsDebugOn"].ToUpper();
}
if (isDebugOn.Equals("FALSE"))
{
//select all as value and paste it back to the
same sheet
//hardcode the sheets, since the data sheet is
always at third position for RTK reports
//Sheet1 = Control Sheet, Sheet3 = Report
sheet
wrkSht.UsedRange.Copy();
excelRng = wrkSht.UsedRange;

excelRng.PasteSpecial(XlPasteType.xlPasteValues,
XlPasteSpecialOperation.xlPasteSpecialOperationNone,
System.Type.Missing, System.Type.Missing);
excelLogger.writeLogFile("After pasting first
sheet's data as value");
wrkSht.Range["A1"].Select();

wrkSht =
(Microsoft.Office.Interop.Excel.Worksheet)applicationProcess.Worksheets[3];
wrkSht.Select();
wrkSht.UsedRange.Copy();
excelRng = wrkSht.UsedRange;

excelRng.PasteSpecial(XlPasteType.xlPasteValues,
XlPasteSpecialOperation.xlPasteSpecialOperationNone,
System.Type.Missing, System.Type.Missing);
excelLogger.writeLogFile("After pasting third
sheet's data as value");
wrkSht.Range["A1"].Select();
}
excelRng = null;
xlAddIn = null;
wrkSht = null;
excelLogger.writeLogFile("Check-in Status " +
applicationProcess.Workbooks[1].CanCheckIn());
excelLogger.writeLogFile("Thread Status " +
Thread.CurrentThread.GetHashCode().ToString() + "-" +
Thread.CurrentThread.IsAlive);
excelLogger.writeLogFile("IsThreadPoolThread = " +
Thread.CurrentThread.IsThreadPoolThread);
excelLogger.writeLogFile("Workbook count " +
applicationProcess.Workbooks.Count);
excelLogger.writeLogFile("Saving file " +
applicationProcess.Workbooks[1].Name);

excelLogger.writeLogFile("Before call to save");
excelLogger.writeLogFile("File Readonly Status: "
+ applicationProcess.Workbooks[1].ReadOnly);

applicationProcess.Workbooks[1].CheckCompatibility
= false;
excelLogger.writeLogFile("Set CheckCompatibility
to false");

applicationProcess.Workbooks[1].SaveAs(someFileName));
excelLogger.writeLogFile("After Save");
applicationProcess.Workbooks.Close();
excelLogger.writeLogFile("End POIHandler");
}
}
catch (Exception exception)
{
logging.logInformation("Error", "POIHandler",
"processWorkbook", exception.Message);
excelLogger.writeLogFile("Error in function
processWorkbook of POIHandler class " + exception.Message);
throw exception;
}
finally
{
//clean up
/* excelRng = null;
xlAddIn = null;
wrkSht = null;*/
}
}



I am acquiring the excel instance as:
applicationProcess.Interactive = false;
applicationProcess.Visible = false;


As I mentioned above the code work most of times, but on few occasions
the SaveAs call suspends the execution.
There is no exception generated, nothing, it is as if the code is
stuck at that line.

I fail to understand why its working most of the times and fails
occasionally
If there is something wrong in the code it should fail everytime

When I run the application on my local PC (Windows XP 32-bit, Excel
2007) it works and never fails.
But when I install it on the server (Windows server 2003, excel 2007 )
as a service, it occasinaly fails

I am using .NET framework 4.0 on the server