Writing Dataset to Excel in ASP.NET

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 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

                             //     {

                             //      oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

                             //

                             for(int j=0;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

                             {

                                       for(int colNo=0;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);

          }

}