Reader Level:

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.

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 )





Article Extensions
Contents added by cesar Flores on May 07, 2010
hi i try to fill an excel file from the data comes from a sql database server but I want an error to open my web server.
loa tells me the following error

Error retrieving COM class generator for component with CLSID (00024500-0000-0000-C000-000000000046) because the following error: 80040154.

I hope you can help me and I leave my code to see if I can help.
excel file is an Office 2007
special thank you can help me

string archivo = Server.MapPath("~\\excel\\Concentrado_de_24_PCE.xls");
        Microsoft.Office.Interop.Excel.Application ExApp;
        ExApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel._Workbook oWBook;
        Microsoft.Office.Interop.Excel._Worksheet oSheet;
        oWBook = ExApp.Workbooks.Open(archivo, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWBook.ActiveSheet;
        SqlConnection cnn = new SqlConnection(cadena);
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter adt = new SqlDataAdapter();
        DataSet ds = new DataSet();
        cmd.CommandText = "Execute imp_mecasut " + int.Parse(lblanio.Text) + "," + int.Parse(lblperiodo.Text) + "";
        cmd.Connection = cnn;
        if (cnn.State == 0)
            adt.SelectCommand = cmd;
            if (ds.Tables[0].Rows.Count > 0)
                int ROW = 9, cel = 3, R = 0;
                for (int r = 0; r < 71; r++)
                    cel = 3;
                    for (int i = 1; i < 8; i++)
                        oSheet.Cells[ROW, cel] = ds.Tables[0].Rows[R].ItemArray[i].ToString();


        Process excelP = new Process();
        excelP.EnableRaisingEvents = false;
        string url = Server.MapPath("~\\excel\\Concentrado_de_24_PCE.xls");
        excelP.StartInfo.FileName = url;
        excelP.StartInfo.CreateNoWindow = true;
        excelP.StartInfo.WindowStyle = ProcessWindowStyle.Maximized;
        ExApp.Visible = true;
        ExApp.UserControl = true;
        ExApp = null;

Krishnan LN
Krishnan LN

Krishnan L. N. is working in Cognizant, Chennai, INDIA. He has 7 years experience working with Microsoft .NET technologies.