Reader Level:
ARTICLE

Writing Dataset to Excel in ASP.NET

Posted by Krishnan LN Articles | Office Development March 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.
  • 0
  • 0
  • 133746

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

          }

}

Article Extensions
Contents added by cesar Flores on May 07, 2010
hi i try to fill an excel file from asp.net 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)
        {
            cnn.Open();
            adt.SelectCommand = cmd;
            adt.Fill(ds);
            cmd.ExecuteNonQuery();
            cnn.Close();
            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();
                        cel++;
                    }
                    ROW++;
                    R++;
                

                }
               
            }
        }

      
        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;
        excelP.Start();
        ExApp.Visible = true;
        ExApp.UserControl = true;
        oWBook.Save();
     
        ExApp = null;





COMMENT USING

Trending up