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;

}

// For each row, print the values of each column.

for(int rowNo=0;rowNo<ds.Tables[0].Rows.Count;rowNo++)

{

for(int colNo=0;colNo<ds.Tables[0].Columns.Count;colNo++)

{

oSheet.Cells[iRow,colNo+1]=ds.Tables[0].Rows[rowNo][colNo].ToString();

}

}

iRow++;

}

oRng = oSheet.get_Range("A1", "IV1");

oRng.EntireColumn.AutoFit();

oXL.Visible = false;

oXL.UserControl = false;

string strFile ="report"+ DateTime.Now.Ticks.ToString() +".xls";//+

oWB.SaveAs( strCurrentDir +
strFile,XlFileFormat.xlWorkbookNormal,null,null,false,false,XlSaveAsAccessMode.xlShared,false,false,null,null);

// Need all following code to clean up and remove all references!!!

oWB.Close(null,null,null);

oXL.Workbooks.Close();

oXL.Quit();

Marshal.ReleaseComObject (oRng);

Marshal.ReleaseComObject (oXL);

Marshal.ReleaseComObject (oSheet);

Marshal.ReleaseComObject (oWB);

string strMachineName = Request.ServerVariables["SERVER_NAME"];

Response.Redirect("http://" + strMachineName +"/"+"ViewNorthWindSample/reports/"+strFile);

}

catch( Exception theException )

{

Response.Write(theException.Message);

}

}