In this article, we are going to see how to open a write a dataset to a excel file and open the excel file in the browser.
In order for this to work, there is an important modification in web.config file. We have to add <identity impersonate="true"> else you will get an 'Access is denied' error.
In the application, we have to add a reference for a COM component called "Microsoft Excel 9.0 object library".
Now we have to just loop through the dataset records and populate to each cell in the excel.
Code:
private void createDataInExcel(DataSet ds)
{
Application oXL;
_Workbook oWB;
_Worksheet oSheet;
Range oRng;
string strCurrentDir = Server.MapPath(".") + "\\reports\\";
try
{
oXL = new Application();
oXL.Visible = false;
//Get a new workbook.
oWB = (_Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (_Worksheet)oWB.ActiveSheet;
//System.Data.DataTable dtGridData=ds.Tables[0];
int iRow =2;
if(ds.Tables[0].Rows.Count>0)
{
// for(int j=0;j<ds.Tables[0].Columns.Count;j++)
// {
// oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;
//
for(int j=0;j<ds.Tables[0].Columns.Count;j++)
{
oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;
}
abhi sPosted Apr 13, 2009, 5:55 PM
I am working in C # 2005 windows application please send me the code if you have . write the data to .xls file fromdataset or data table.
AbhijeetPosted Apr 8, 2009, 6:42 AM
Hello, I am running thsi code in my application to create Excel file by writting dataset into it. But unfortunately i am getting the following error- System.Runtime.InteropServices.COMException (0x800A03EC) Its urgent kindly help me out. Thanks in advance for your help Abhijeet
Deepak VirdiPosted Dec 6, 2008, 9:28 AM
HI, I'm using the same code for exporting to excel. but my requirment to open excel in browser or pop should be there of open, save, cancel option. I dont want it to save on disk. How it can be done using same code
hanifPosted Feb 20, 2008, 5:02 AM
Application oXL; Workbook oWB; Worksheet oSheet; Range oRng; oXL = new Application(); oXL.Visible = false; //Get a new workbook. oWB = (_Workbook)(oXL.Workbooks.Add( Missing.Value )); oSheet = (_Worksheet)oWB.ActiveSheet; this line is shown error to me....i dont know the basic idea of file Export so help me soon
alina etenaPosted Jun 28, 2007, 9:43 AM
i have the same problems as Asad, please answer to me too
subodh kumarPosted Nov 2, 2006, 1:10 AM
Hi, Application oXL;<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> _Workbook oWB;<o:p></o:p> _Worksheet oSheet;<o:p></o:p> Range oRng;<o:p></o:p> string strCurrentDir = Server.MapPath(".") + "\\reports\\";<o:p></o:p> try<o:p></o:p> {<o:p></o:p> oXL = new Application();<o:p></o:p> i am using the code . the same code is runing in my pc , but same code is not runing in other pc. it gives error message "Access is denied" while creating new Application. I am using Microsoft excel 11.0 object library. i have add <identity impersonate="true"> in web config. send me solution. Subodh kumar [email protected]
Asad KhanPosted Jun 5, 2006, 5:32 AM
Hai....I can't get the code to work first of all you didnt mention where exactly in web.config to write the identity impersonate.. statement. Secondly the compiler doesnot identify any of the Marshal. statements. Thirdly you have used something called Missing.Value the compiler doesnt undertand that either is their a namespace or something I should be using ?? with regards, alyeasad