locked
Microsoft.office.interop.excel, while exporting to excel ? Pls Help ? RRS feed

  • Question

  • User-788355444 posted

    Hi, I am searching web for more than 2 days but can't find a right solution for the Microsoft.office.interop.excel error, can any one please help ??

    I have a export to excel function from a datagrid in my project and it works fine in my local, when i try to upload in my IIS server 6.0 and run, I am getting a error msg as below when I click the export button.  I have tried adding permisson in dcomcnfg and regedit but still I can't reolve this I have also added Microsoft.office.interop.excel.dll to my published bin folder, still cannot ?

    Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154.

    Please Help, Thanks in Advance!

    Monday, February 25, 2013 9:43 PM

Answers

  • User1992938117 posted

    Jessy Crix

    And for your answer 1, How can I test excel component is installed properly or not, because I tried to look in dcomcnfg but I can't find and dll's like excel.

    Do as:

    1.it a platform issue just compile your code with 'X86' rather than 'Any CPU'
    to do that click on ProjectMenu --> windows form properties --> Build tab --> platform target change it to X86
     
    --------------------------------------------------------
     
    2. click on Start --> Run 
    type dcomcnfg component services window will open.
    component services --> computers --> MyComputer --> DCOM Config --> select microsoft EXCEL application
     
    right click on it --> properties --> security --> Lunch and activation permission TAB
     
    select customize TAB --> click on edit add Network Services user and Users group give permission.
     
    do same for access permission and configuration permission.

    Link : http://www.codeproject.com/Questions/257802/VB-NET-Error-Retrieving-the-COM-class-factory-for

    Check this also http://rameshjpc.blogspot.in/2011/10/retrieving-com-class-factory-for.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 27, 2013 12:19 AM

All replies

  • User1992938117 posted

    Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154.

    1. It looks like excel COM component is not registered properly.

    2. In Application pool Enable 32 Applications to True.

    Monday, February 25, 2013 11:42 PM
  • Monday, February 25, 2013 11:50 PM
  • User-788355444 posted

    Hi Rajneesh, Thanks for your reply can you please tell me the procedure how to enable the Application pool ??

    And for your answer 1, How can I test excel component is installed properly or not, because I tried to look in dcomcnfg but I can't find and dll's like excel.

    Can you clarify, thanks !

    Tuesday, February 26, 2013 3:21 AM
  • User-788355444 posted

    Hi Vinay,

    Thanks for the link it helped me a lot, But after the export I need to replace the empty cells to "Null" and to remove the hyperlinks in the excel is there any way to do that ?

    Source:

    btnexport_onlclick:

    ExportToExcel("Report.xls", GridView1)

    Funtion:

    Private Sub ExportToExcel(ByVal strFileName As String, ByVal gv As GridView)
            Response.ClearContent()
            Response.AddHeader("content-disposition", "attachment; filename=" & strFileName)
            Response.ContentType = "application/excel"
            Dim sw As New System.IO.StringWriter()
            Dim htw As New HtmlTextWriter(sw)
            gv.RenderControl(htw)
            Response.Write(sw.ToString())
            Response.End()
        End Sub

    Tuesday, February 26, 2013 3:53 AM
  • User-751615625 posted

    I do not know what happened to you, but as for export to Excel issue, I think I should suggest you this data export library, it can export data from database, listview and command to Excel and generate 15 types of charts, to html, PDF,Word, xml, ms access,csv, dif,txt, sylk etc without using MS Excel or any other third party libraries. If you have few data, I think the free version of this library is suitable for you, you can see below code:

    VB.NET:

    Private Sub btnLoad_Click(sender As Object, e As EventArgs)
    	Using oleDbConnection As New OleDbConnection()
    		oleDbConnection.ConnectionString = Me.textBox1.Text
    		Dim oleDbCommand As New OleDbCommand()
    		oleDbCommand.CommandText = Me.textBox2.Text
    		oleDbCommand.Connection = oleDbConnection
    		Using da As New OleDbDataAdapter(oleDbCommand)
    			Dim dt As New DataTable()
    			da.Fill(dt)
    			dataGridView1.DataSource = dt
    		End Using
    	End Using
    End Sub
    Private Sub btnRun_Click(sender As Object, e As EventArgs)
    	Dim cellExport As New Spire.DataExport.XLS.CellExport()
    	Dim worksheet1 As New Spire.DataExport.XLS.WorkSheet()
    	worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable
    	worksheet1.DataTable = TryCast(Me.dataGridView1.DataSource, DataTable)
    	worksheet1.StartDataCol = CByte(0)
    	cellExport.Sheets.Add(worksheet1)
    	cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
    	cellExport.SaveToFile("20110223.xls")
    End Sub

    C#

    private void btnLoad_Click(object sender, EventArgs e)
            {
                using(OleDbConnection oleDbConnection = new OleDbConnection())
                {
                    oleDbConnection.ConnectionString = this.textBox1.Text;
                    OleDbCommand oleDbCommand = new OleDbCommand();
                    oleDbCommand.CommandText = this.textBox2.Text;
                    oleDbCommand.Connection = oleDbConnection;
                    using(OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
                    {
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }
                }
            }
     private void btnRun_Click(object sender, EventArgs e)
            {
                Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();
                Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();
                worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                worksheet1.DataTable = this.dataGridView1.DataSource as DataTable;
                worksheet1.StartDataCol = ((System.Byte)(0));
                cellExport.Sheets.Add(worksheet1);
                cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
                cellExport.SaveToFile("20110223.xls");
            }
    

    If you are not very clear witrh the code above, you can see details in the source code page oon codeplex:

    http://exportdata.codeplex.com/

    Tuesday, February 26, 2013 8:45 PM
  • User1992938117 posted

    Jessy Crix

    And for your answer 1, How can I test excel component is installed properly or not, because I tried to look in dcomcnfg but I can't find and dll's like excel.

    Do as:

    1.it a platform issue just compile your code with 'X86' rather than 'Any CPU'
    to do that click on ProjectMenu --> windows form properties --> Build tab --> platform target change it to X86
     
    --------------------------------------------------------
     
    2. click on Start --> Run 
    type dcomcnfg component services window will open.
    component services --> computers --> MyComputer --> DCOM Config --> select microsoft EXCEL application
     
    right click on it --> properties --> security --> Lunch and activation permission TAB
     
    select customize TAB --> click on edit add Network Services user and Users group give permission.
     
    do same for access permission and configuration permission.

    Link : http://www.codeproject.com/Questions/257802/VB-NET-Error-Retrieving-the-COM-class-factory-for

    Check this also http://rameshjpc.blogspot.in/2011/10/retrieving-com-class-factory-for.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 27, 2013 12:19 AM
  • User2120624532 posted

    Good reference regarding this: http://cybarlab.blogspot.com/2013/03/import-csv-file-into-database-in-c.html<o:p></o:p>

    Wednesday, March 13, 2013 7:24 AM