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

          }

}