export quickly .NET Datagrid to excel

Posted on 2008-11-10
Last Modified: 2013-12-17
i am trying to write code to export.NET  datagrid to excel in C#

I have written the code below. the code works fine if the record count is less...
However for reccords count greater than 1000, it takes 10-20 minutes!!!!...
is there any other way to export datagrid to excel or pl help me to make this run faster!!

i have tried exporting it to a CSV file but my data has lot of commas and semicolons!!! so that wonot help..

MY Code:
         private void butExportExcel_Click(object sender, EventArgs e)
            this.Cursor = Cursors.WaitCursor;
                OracleConnection oc = new OracleConnection(this.OraConnectionString);
                OracleCommand ocmd=new OracleCommand(this.MainQueryString,oc);
                OracleDataReader odr= ocmd.ExecuteReader();
                Excel.ApplicationClass excel = new Excel.ApplicationClass();                
                Excel.Workbook wb = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                int ColumnIndex = 0;
                int totfield=odr.FieldCount;
                //excel.Visible = true;
                for (int i = 0; i < totfield; i++)
                    String strfield = odr.GetName(i);
                    excel.Cells[1, ColumnIndex] = strfield;
                int rowIndex=1;
                while (odr.Read())
                    ColumnIndex = 0;
                    for (int i = 0; i < totfield; i++)
                        System.Object obj;
                        obj = odr.GetValue(i);
                        String StrValue = obj.ToString();
                        excel.Cells[rowIndex, ColumnIndex] = StrValue;
                excel.Visible = true;
                Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
            catch (OracleException oex)
            catch (Exception ex)
                MessageBox.Show(ex.ToString(), "Error exporting data to excel", MessageBoxButtons.OK, MessageBoxIcon.Error);

            this.Cursor = Cursors.Default;  


Question by:at999
    LVL 1

    Accepted Solution

    Plz excuse the VB.
    This one is actually getting the DataSource passed from another page in the session object (the other page allows the user to filter the date pretty extensively), but otherwise it's the same.

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
            Dim sqlIssues As SqlDataSource
            sqlIssues = DirectCast(Session("datasource"), SqlDataSource)
            gvProjects.DataSource = sqlIssues
        End Sub
        Protected Sub exportRoutine()
            Response.ContentType = "application/"
            Response.Charset = ""
            Me.EnableViewState = False
            Dim tw As New System.IO.StringWriter()
            Dim hw As New System.Web.UI.HtmlTextWriter(tw)
            Dim frm As HtmlForm = New HtmlForm()
        End Sub
        Protected Sub goBack()
        End Sub

    Open in new window


    Author Comment

    Thanks btumer,,,

    I am not doing this in ASP website..
    I am looking code for  a .NET Windows Application = to export datagrid to excel...


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now