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.
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.
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
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:
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.
Jessy Crix
Member
13 Points
70 Posts
Microsoft.office.interop.excel, while exporting to excel ? Pls Help ?
Feb 26, 2013 01:43 AM|LINK
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!
Rajneesh Ver...
All-Star
37134 Points
6810 Posts
Re: Microsoft.office.interop.excel, while exporting to excel ? Pls Help ?
Feb 26, 2013 03:42 AM|LINK
1. It looks like excel COM component is not registered properly.
2. In Application pool Enable 32 Applications to True.
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.
vinay13mar
Star
7756 Points
1626 Posts
Re: Microsoft.office.interop.excel, while exporting to excel ? Pls Help ?
Feb 26, 2013 03:50 AM|LINK
hi try this link for export to excel
http://www.dotnetpools.com/Article/ArticleDetiail/?articleId=22&title=Gridview-Export-To-Excel-In-Asp.Net-C#
V.K.Singh
Jessy Crix
Member
13 Points
70 Posts
Re: Microsoft.office.interop.excel, while exporting to excel ? Pls Help ?
Feb 26, 2013 07:21 AM|LINK
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 !
Jessy Crix
Member
13 Points
70 Posts
Re: Microsoft.office.interop.excel, while exporting to excel ? Pls Help ?
Feb 26, 2013 07:53 AM|LINK
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
Alicebaby
Member
36 Points
7 Posts
Re: Microsoft.office.interop.excel, while exporting to excel ? Pls Help ?
Feb 27, 2013 12:45 AM|LINK
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 SubC#
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/
Rajneesh Ver...
All-Star
37134 Points
6810 Posts
Re: Microsoft.office.interop.excel, while exporting to excel ? Pls Help ?
Feb 27, 2013 04:19 AM|LINK
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
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.
rasadulalam
Member
119 Points
66 Posts
Re: Microsoft.office.interop.excel, while exporting to excel ? Pls Help ?
Mar 13, 2013 11:24 AM|LINK
Good reference regarding this: http://cybarlab.blogspot.com/2013/03/import-csv-file-into-database-in-c.html
Rasadul Alam Rashed
Software Engineer
Dhaka, Bangladesh