locked
I'm having problems getting my Excel interop to work under IIS RRS feed

  • Question

  • The code below works on my win 7 box using the VS2010 internal web server but Now that I've "published" the app and installed it on my web server (win 2008 r2, standard ed. IIS 7.5, Excel 2010), I can't seem to get it to work.

    I've gotten past configuring the Excel dcom object on the server, so the app can create the Excel.Application object and configured the app in the server to full trust in an effort to get this thing running. I've checked that the Excel file I'm trying to open has a valid pathname and can be opened by Excel, and set the directory (and file) permissions so "everyone" has full access in an effort to get past the error I get when I try to open the file ( wb=ExcelObj.workbooks.open(path) ), but all I get is the following exception message:

    Microsoft Excel cannot access the file 'c:\data\Broadmead Nursery Availability 12.17.10.xls'. 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.

    I've checked with task manager that there are no other Excel processes running

    I've run out of ideas as to what to try next or how to further diagnose what the problem is.

    I'd appreciate any help or direction anyone can provide. My client really wants to see this app in action over the web.

    Here's the offending code:

     Function ReadSpreadsheet(ByVal path As String) As Object(,)

            'NOte this function only returns the first worksheet in a workbook
            'TODO modify this routine to return an 3 dimensional array with the 1st dimension
            '  representing the worksheets (plural as needed)
            ' Reference - http://www.codeproject.com/KB/office/fasterexcelaccesstoc.aspx?fid=168114&df=90&mpp=25&noise=3&sort=Position&view=Quick&select=2146526#Findingtherangeofdata4
            ' Faster MS Excel Reading using Office Interop Assemblies by Dusty Candland
            '

            Dim ExcelObj As Microsoft.Office.Interop.Excel.Application = Nothing
            ExcelObj = New Microsoft.Office.Interop.Excel.Application()
            ' these settings are suppose to speed things up
            ExcelObj.Visible = False
            ExcelObj.ScreenUpdating = False
            ExcelObj.DisplayAlerts = False


            ' Attempt to open up the file referenced by path as an Excel workbook
            ' if it fails leave a trail of the 'error' as a warning in the trace log and return nothing
            Dim wb As Workbook
            Dim contents(,) As Object
            Try
                Trace.Write("Opening Excel file:" + path)
                Label1.Text = "Opening Excel File:" + path
                wb = ExcelObj.Workbooks.Open(path)
            Catch ex As Exception
                Trace.Warn("Missing file or non-excel file with xls ending. Path provided-" + path +
                           vbCrLf + ex.Message)
                Label2.Text = "problem opening excel file Exception:" + ex.Message
                ExcelObj.Quit()
                ExcelObj = Nothing
                Return Nothing
            End Try

            ' If the attempt was successful then read the file into the application's workbook structure

            Label1.Text = wb.Worksheets.Count.ToString() + " in " + path
            Dim sheet As Worksheet = wb.Worksheets(1)
            Dim wrkArea As Range = sheet.UsedRange
            ' read the worksheet contents as a 2 dimensional array of (rows,columns)
            contents = wrkArea.Value2
            ' now that we've got the data we should be able to close the connection
            ' and release the interop objects back to memory
            ExcelObj.Workbooks.Close()
            ExcelObj = Nothing

            Return contents
        End Function

     

    I've found and tried these postings but they don't seem to work with IIS 7.5

     

    Excel 2007 automation on top of a Windows Server 2008 x64

     

    Pasted from <http://social.msdn.microsoft.com/Forums/en/innovateonoffice/thread/b81a3c4e-62db-488b-af06-44421818ef91 >

     

    Problem running Windows Service with Excel Object on Windows Server 2008 (64 bit)

     

    Pasted from <http://social.technet.microsoft.com/Forums/en-US/office2007deploymentcompatibility/thread/334c9f30-4e27-4904-9e71-abfc65975e23?prof=required

     

    One more related question around this interop effort:

    As I understand it the worker process User account is that of the application pool under IIS 7.5.

    The default for the ASP 4.0 app pool is: IIS APPPOOL\ASP.NET v4.0

    but This user id does not show up in any of the dialogs when trying to set the file permissions. In the "old" days the local service account (or Network Service account) was used.  Prior to this there was the ASPNET account and various IUSER, IWAM accounts. My point is What is the account I need to grant permissions in setting up directory permissions (and DCOM permissions)?


    Developer Frog Haven Enterprises
    Tuesday, February 22, 2011 5:17 PM

Answers

  • Thanks for replying. It's strange to me that in only one place had I encountered that warning while there are dozens of examples showing how to use interop to interact with Excel files.

    At any rate I've abandon this approach. I tried using the OLEdb objects but found that that approach does an incomplete read of the spreadsheet contents. with no explanation as to why it skips cells and their contents. Using the XML technique was out as that requires the files to be generated by Excel version 2007 and later. In my situation I get 100's of files from all over to process with no guarantee as to what version of Excel would be used to generate them.

    The solution I found was to do a "binary" read directly of the Excel file. This class can be found (for anyone else stumbling across this post)

     

    Excel Data Reader - Read Excel files in .NET

     

    Pasted from <http://exceldatareader.codeplex.com/ >

     

    I've tested this with about 2 dozen excel files and haven't found a problem yet reading ALL the content in the cells defined in the spreadsheet. What's even nicer is that you don't need Office installed on the machine to have it work, saving my client from having to buy another licence of MS office.

    I hope this helps others out I've spent days trying to figure out whats up with the 64 bit version of win 2008 and this interop issue (as has several others I've found).

     


    Developer Frog Haven Enterprises
    • Proposed as answer by Bessie Zhao Wednesday, March 2, 2011 8:28 AM
    • Marked as answer by Bessie Zhao Monday, March 7, 2011 8:43 AM
    Wednesday, February 23, 2011 4:57 PM

All replies

  • Hello elbilo,

    Thanks for posting. From what you said, you are using Excel automation in ASP.NET application. If yes, actually, Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment. For more information, please see this KB article: Considerations for server-side Automation of Office.

    Also as said in the first thread you shared above, it introduces some resources about OpenXML. In the above KB article, it also introduces such alternatives to server-side Automation: OpenXML SDK. It does not require office to be installed server-side. For more information about OpenXML SDK, I'd like suggest you could ask in Office OpenXML forum:

    OpenXMLDeveloper.org
    OpenXML SDK forum: http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/threads/.
    BTW, by searching this error, I have found here are two threads. Hope they could help you.

    Microsoft Office Excel cannot access the file:
    http://social.technet.microsoft.com/Forums/en-US/sharepointgeneral/thread/8f3e9969-65d1-446c-a475-f9d3f99bd010/.

    Thread: Error Opening Excel Application in IIS7:
    http://forums.iis.net/t/1101744.aspx.

    If you have any concerns or comments on this post, just feel free to follow up. Have a nice day.


    Bessie Zhao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 23, 2011 3:46 AM
  • Thanks for replying. It's strange to me that in only one place had I encountered that warning while there are dozens of examples showing how to use interop to interact with Excel files.

    At any rate I've abandon this approach. I tried using the OLEdb objects but found that that approach does an incomplete read of the spreadsheet contents. with no explanation as to why it skips cells and their contents. Using the XML technique was out as that requires the files to be generated by Excel version 2007 and later. In my situation I get 100's of files from all over to process with no guarantee as to what version of Excel would be used to generate them.

    The solution I found was to do a "binary" read directly of the Excel file. This class can be found (for anyone else stumbling across this post)

     

    Excel Data Reader - Read Excel files in .NET

     

    Pasted from <http://exceldatareader.codeplex.com/ >

     

    I've tested this with about 2 dozen excel files and haven't found a problem yet reading ALL the content in the cells defined in the spreadsheet. What's even nicer is that you don't need Office installed on the machine to have it work, saving my client from having to buy another licence of MS office.

    I hope this helps others out I've spent days trying to figure out whats up with the 64 bit version of win 2008 and this interop issue (as has several others I've found).

     


    Developer Frog Haven Enterprises
    • Proposed as answer by Bessie Zhao Wednesday, March 2, 2011 8:28 AM
    • Marked as answer by Bessie Zhao Monday, March 7, 2011 8:43 AM
    Wednesday, February 23, 2011 4:57 PM
  • It is not recommended to use any Office automation under IIS, since automation works in another process and could lead to "dead" instances of office running on a server. If you need to read Excel files, you can use ACE OLEDB provider for that or use .NET Managed reader for Excel from my web site. You can find downloads for both of them here

    http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

    http://www.xporttools.net/Downloads.aspx


    Val Mazur (MVP)

    http://www.xporttools.net

    Thursday, February 24, 2011 12:08 AM
  • Thanks for your  suggestions. The first cost $80.00; while I'm sure it's worth it the Binary reader I'm using is free.

    The second only supports office 2007 and as I mentioned I must be able to read all versions of excel even those prior to Excel 2007 as I have a number of vendors that are still using office as far back as 2000.

     


    Developer Frog Haven Enterprises
    Tuesday, March 1, 2011 8:07 PM
  • Hello again elbilo,

    Also here is a thread about a similar topic:

    Excel 2007 automation on top of a Windows Server 2008 x64:
    http://social.msdn.microsoft.com/Forums/en-US/innovateonoffice/thread/b81a3c4e-62db-488b-af06-44421818ef91?prof=required.

    Hope it will give you some idea. Have a nice day.


    Bessie Zhao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 2, 2011 8:29 AM
  • Thanks I had seen that one and tried what was suggested there. It took me another while to back and undo all those different changes. I think I'm a much better programmer than a system admin.

    Anyway I backed up and went around this particular wall.

     


    Developer Frog Haven Enterprises
    Friday, March 4, 2011 11:25 PM