Export to Excel Issue [Resolved]

Posted by Rahuljgosavi under C# on 3/7/2011 | Points: 10 | Views : 14386 | Status : [Member] | Replies : 7
Dear All,

I want to export database table into excel , I tried below code but it is throwing an exception,

SqlConnection cnn;
string connectionString = null;
string sql = null;
string data = null;
int i = 0;
int j = 0;

Microsoft.Office.Interop.Excel.Application xlApp;
Workbook xlWorkBook;
Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new ApplicationClass();

xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

connectionString = "data source=XYZ;initial catalog=HPBTMU;user id=;password=;";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "select * from RegisterCustomer";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
dscmd.Fill(ds);

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 1, j + 1] = data;
}
}

xlWorkBook.SaveAs("csharp.net-informations.xls", XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");

The exception it is throwing is,
System.InvalidCastException was unhandled
Message=Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).
Source=Microsoft.Office.Interop.Excel

Please help me to fix this issue or let me know is there any other way to export Database table in excel in C# Windows ?




Responses

Posted by: Rahuljgosavi on: 3/8/2011 [Member] Starter | Points: 25

Up
0
Down

Resolved
Good Morning Saravanan,

As you you rightly said there was a problem with my office installation, after reparing it the issue has been resolved.

Thanks for your help, I am marking this post as an answer.

Cheers,
Rahul Gosavi

Rahuljgosavi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 3/7/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

Same issue is discussed in the following links...
http://social.msdn.microsoft.com/Forums/en/vbinterop/thread/29d536f5-e53c-48e7-a33d-c56a57c9f947
http://support.microsoft.com/kb/968263

If you want export to excel method, Refer this link....
http://www.dotnetfunda.com/codes/code1071-export-to-excel-with-grid-color-in-winforms-using-csharp.aspx

Cheers :)

Thanks,
T.Saravanan

Rahuljgosavi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prabhakar on: 3/8/2011 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hi rahul


check this link it's use full for you . .

http://www.dotnetfunda.com/articles/article1150-all-in-one-export-data-in-aspnet-gridview-to-doc-gridview-to-excel-gr-.aspx

Best Regard's
Prabhakar

Rahuljgosavi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rahuljgosavi on: 3/8/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Saravanan,

Thanks for your reply but it is throwing the same exception on this line,
Excel.Workbooks sXLBooks = (Excel.Workbooks)sXLApp.Workbooks;

Prabhakar,
I want a windows or console C# code because I want to create a job of that.

Regards,
Rahul




Rahuljgosavi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 3/8/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

I think in your system MS-Office is not properly installed.I am already posted the link for this issue in my previous answer.
Any how...Check this link its helpful to you...
http://www.c-sharpcorner.com/Forums/Thread/80799/getting-a-error-when-opening-excel-sheet-on-C-Sharp.aspx
http://social.msdn.microsoft.com/Forums/en/vbinterop/thread/29d536f5-e53c-48e7-a33d-c56a57c9f947

Try this..

Thanks,
T.Saravanan

Rahuljgosavi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rahuljgosavi on: 3/8/2011 [Member] Starter | Points: 25

Up
0
Down
I will do that and let you know...

Thanks,
Rahul

Rahuljgosavi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 3/8/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

your problem is solved or not.What error occurred in export method?
Error is 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'..............means
just refer the links from my previous answer its really helpful to you.

Thanks,
T.Saravanan

Rahuljgosavi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response