How to Export Datagridview Data Instantly to Excel on Button Click

How to export dataGridView data Instantly to Excel on button click?

I solved this by simple copy and paste method. I don't know it is the best way to do this but,for me it works good and almost instantaneously. Here is my code.

    private void copyAlltoClipboard()
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

Thanks.

Exporting Datagridview to Excel in C#

    private void exportToExcel_Click(object sender, EventArgs e)
    {
        try
        {
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            app.Visible = true;
            worksheet = workbook.Sheets["Sheet1"];
            worksheet = workbook.ActiveSheet;
            worksheet.Name = "Records";

            try
            {
                for (int i = 0; i < transcationTableDataGridView.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = transcationTableDataGridView.Columns[i].HeaderText;
                }
                for (int i = 0; i < transcationTableDataGridView.Rows.Count; i++)
                {
                    for (int j = 0; j < transcationTableDataGridView.Columns.Count; j++)
                    {
                        if (transcationTableDataGridView.Rows[i].Cells[j].Value != null)
                        {
                            worksheet.Cells[i + 2, j + 1] = transcationTableDataGridView.Rows[i].Cells[j].Value.ToString();
                        }
                        else
                        {
                            worksheet.Cells[i + 2, j + 1] = "";
                        }
                    }
                }

                //Getting the location and file name of the excel to save from user. 
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                saveDialog.FilterIndex = 2;

                if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    workbook.SaveAs(saveDialog.FileName);
                    MessageBox.Show("Export Successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                app.Quit();
                workbook = null;
                worksheet = null;
            }
        }
        catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); }
    }

export the gridview to excel sheet by clicking on submit button

Cache sounds like a good solution, could you post your cache code?

Otherwise, I would do it in ViewState. Get the result of your long query, store it in ViewState, bind the result to the GridView, then after your button click, you can access the ViewState-stored data. This isn't the best way to do it for large sets of data, because ViewState is sent across the wire with each PostBack and stored on the client side. It can really slow your application down and cause unexpected errors.

Here's my edit, that I got working locally:

Storing it in Session should work fine. Here's what I did with some dummy data. FYI, you can bind a GridView directly to a DataSet, you don't have to drill down to a DataTable or anything like that.

        protected void Button1_Click(object sender, EventArgs e)
        {

            string RName = Page.Request.QueryString["RName"];
            string typeofquery = "mycommand";
            string pv1 = p1.Text;
            string pv2 = p2.Text;
            string abc = null;
            abc = "" + typeofquery + " @RName=" + RName + ",@P1='" + pv1 + "',@P2='" + pv2 + "'";
            SqlDataAdapter cmdldata = new SqlDataAdapter(abc, sqlconn);

            GridView1.PageSize = 1000;

            cmdldata.SelectCommand.CommandTimeout = 600;

            var dummyDt = new DataTable();
            dummyDt.Columns.Add("Sup");
            dummyDt.Columns.Add("Bro");

            dummyDt.Rows.Add("Test1", "test2");
            dummyDt.Rows.Add("Test1", "test2");
            dummyDt.Rows.Add("Test1", "test2");
            dummyDt.Rows.Add("Test1", "test2");
            dummyDt.Rows.Add("Test1", "test2");

            dsldata = new DataSet();
            dsldata.Tables.Add(dummyDt);
            //ErrorHandling errhandle = new ErrorHandling();
            try
            {
                //cmdldata.Fill(dsldata);
                Session["data"] = dsldata;

                //DataView dataview_ldata = dsldata.Tables[0].DefaultView;
                //DataTable dt = dsldata.Tables[0];
                GridView1.DataSource = dsldata;
                GridView1.DataBind();

            }//end of try
            catch (Exception ex)
            {
                //String errorMessage = errhandle.displayException(ex);
                Response.Write(ex.Message);

            }//end of catch
            finally
            {
                //if (errhandle != null)
                //{
                //    errhandle = null;
                //}
            }//end of finally
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            //DataSet ds0 = new DataSet();
            //ds0 = ;
            //DataView dataview_ldata = dsldata.Tables[0].DefaultView;
            //DataTable dt = dsldata.Tables[0];
            GridView1.DataSource = (DataSet)Session["data"];
            GridView1.DataBind();
            ExportToExcel(GridView1);

        }

        private void ExportToExcel(GridView GrdView)
        {
            try
            {
                Response.Clear();
                Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
                Response.Charset = "";
                // If you want the option to open the Excel file without saving than
                // comment out the line below
                // Response.Cache.SetCacheability(HttpCacheability.NoCache);
                Response.ContentType = "application/vnd.xls";
                System.IO.StringWriter stringWrite = new System.IO.StringWriter();
                System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
                GrdView.RenderControl(htmlWrite);
                Response.Write(stringWrite.ToString());
                Response.End();
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('" + ex.Message + "')</script>");
            }
        }

How to export DatagridView to Excel

Write the headers in a separate loop

    Dim cellRowIndex As Integer = 1
    Dim cellColumnIndex As Integer = 1

    'Write headers
    For j As Integer = 0 To FleetDataGridView.Columns.Count - 1
        worksheet.Cells(cellRowIndex, cellColumnIndex) = FleetDataGridView.Columns(j).HeaderText
        cellColumnIndex += 1
    Next
    cellColumnIndex = 1
    cellRowIndex += 1

    'Loop through each row and read value from each column.
    For i As Integer = 0 To FleetDataGridView.Rows.Count - 2
        For j As Integer = 0 To FleetDataGridView.Columns.Count - 1
            ' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
            worksheet.Cells(cellRowIndex, cellColumnIndex) = FleetDataGridView.Rows(i).Cells(j).Value.ToString()
            cellColumnIndex += 1
        Next
        cellColumnIndex = 1
        cellRowIndex += 1
    Next

---

Related Topics