Reader Level:
Office Development

Writing Dataset to Excel in ASP.NET

By Krishnan LN on Mar 01 2006
This article is helpful for those who would like to write dataset contents to a excel file and try to display it in the browser.
    • Like
    • Love It
    • Awesome
    • Interesting
    • It's Okay
    • Thumbs Down
  • 153k
  • 0

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.


private void createDataInExcel(DataSet ds)


          Application oXL;

          _Workbook oWB;

          _Worksheet oSheet;

          Range oRng;

          string strCurrentDir = Server.MapPath(".") + "\\reports\\";



                   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;



                             //     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++)




                             // 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++)







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


                    oXL.Visible = false;

                    oXL.UserControl = false;

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

                    oWB.SaveAs( strCurrentDir + 

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




                   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 )