Link to home
Start Free TrialLog in
Avatar of Sudhanshum
Sudhanshum

asked on

Microsoft.Office.Interop not working from IIS

Hi I am using Microsoft.Office.Interop, I have added its DLL on BIN folder and using its object, which is working fine when I run code through Visual Studio but when I run exact same code from IIS then It gets failed, I tried to find solution but its not working, Please suggest what I can do to fix that. Below is my code:
Dim xlapp As Excel.Application
            Dim xlworkbook As Excel.Workbook
            Dim xlworksheet As Excel.Worksheet
            Dim misvalue As Object = Reflection.Missing.Value
            Dim sheetIndex As Integer = 0
           
            xlapp = New Excel.Application
            xlworkbook = xlapp.Workbooks.Add(misvalue)
            xlworksheet = xlworkbook.Sheets("Sheet1")
         
            sheetIndex += 1

            ' Copy the DataTable to an object array
            Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object

            ' Copy the column names to the first row of the object array
            For col = 0 To dt.Columns.Count - 1
                rawData(0, col) = dt.Columns(col).ColumnName
            Next

            ' Copy the values to the object array
            For col = 0 To dt.Columns.Count - 1
                For row = 0 To dt.Rows.Count - 1
                    rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
                Next
            Next
         
            ' Calculate the final column letter
            Dim finalColLetter As String = String.Empty
            Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
            Dim colCharsetLen As Integer = colCharset.Length

            If dt.Columns.Count > colCharsetLen Then
                finalColLetter = colCharset.Substring( _
                 (dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
            End If

            finalColLetter += colCharset.Substring( _
              (dt.Columns.Count - 1) Mod colCharsetLen, 1)

            ' Create a new Sheet
            xlworksheet = CType( _
                xlworkbook.Sheets.Add(xlworkbook.Sheets(sheetIndex), _
                Type.Missing, 1, Excel.XlSheetType.xlWorksheet), Excel.Worksheet)

            xlworksheet.Name = "Test"

            ' Fast data export to Excel
            Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
            xlworksheet.Range(excelRange, Type.Missing).Value2 = rawData

            ' Mark the first row as BOLD
            CType(xlworksheet.Rows(1, Type.Missing), Excel.Range).Font.Bold = True

            xlworksheet = Nothing

       
            xlworkbook.SaveAs("C:\Log\" + txtName.Text + ".xlsx")

            xlworkbook.Close(True, Type.Missing, Type.Missing)

            xlworkbook = Nothing


            ' Release the Application object
            xlapp.Quit()
            xlapp = Nothing
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Is Excel installed on the IIS server? Interop files are only kind of dictionaries between .Net and the COM DLL of Office.

Since it is for a server and you shouldn't install on a computer without a license, and Office does not really support multithreaded apps, you should rely on 3rd party components like https://products.aspose.com/total/net

From there site:
Aspose.Total for .NET APIs are built using managed code, and does never need Microsoft Office to be installed on the machine to work with supported document formats. It is a perfect Microsoft Office automation alternative in terms of supported features, security, stability, scalability, speed and price.
Hi Sudhanshum,

For Server Side manipulation of Office documents, I strongly recommend that you use OpenXML SDK https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk

This SDK is provided by Microsoft at free of cost and is used in many applications world wide. It has a little learning curve but it is a low-risk - high-gain case. Once you have understood how it works, you can manipulate all three - Word, Excel and PowerPoint files.

They have couple of Excel examples listed here: https://docs.microsoft.com/en-us/office/open-xml/spreadsheets

If you want to explore this option, please have a look at my article: https://www.experts-exchange.com/articles/33048/A-helper-class-to-perform-CRUD-on-Microsoft-Office-documents-Custom-Properties-without-headache.html. It has steps which you can use to get started with OpenXml SDK.

All the best.

Regards,
Chinmay.
MIcrosoft InterOp should not be used in server applications as they ARE NOT thread safe because they use COM technology to interact with your Installed Office products.
You could use a third party commercial library like Gembox  or Aspose.
ASKER CERTIFIED SOLUTION
Avatar of Sudhanshum
Sudhanshum

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial