Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Save problem from C# application
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)applicat ionProcess.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[i].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\\Cl sid 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_x lPrecision\\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.xlPasteSpecialOperationNon e, 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)applicat ionProcess.Worksheets[3]; wrkSht.Select(); wrkSht.UsedRange.Copy(); excelRng = wrkSht.UsedRange; excelRng.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNon e, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Application.Quit problem | Excel Programming | |||
Command Button Save As Application.ExecuteExcel4Macro ("SAVE.AS?() | Excel Discussion (Misc queries) | |||
Problem quitting excel when it is used by ASP.NET application | Excel Programming | |||
Problem on the ASP using the Excel.Application Object | Excel Programming | |||
Problem with closing the excel application | Excel Programming |