How to export data to Excel?

Hi Jack,


We have to add a reference to the Microsoft Excel object library.
Right click on your project and select Add Reference menu. After that go to COM tab and select and add Microsoft Excel 12.0 object library.



Try this...


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace Export_to_Excel_in_windows
{
    public partial class Form1 : Form
    {
       
        string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["dsn"];
        OleDbCommand com;
        DataSet ds;
        OleDbDataAdapter oledbda;
        DataTable dt;
        string str;

        public Form1()
        {
            InitializeComponent();
        }

        private void btndisplay_Click(object sender, EventArgs e)
        {
           
            try
            {
                OleDbConnection con = new OleDbConnection(ConnectionString);
                con.Open();
                str = "select * from student";
                com = new OleDbCommand(str, con);
                ds = new DataSet();
                oledbda = new OleDbDataAdapter(com);
                oledbda.Fill(ds, "student");
                con.Close();
                DataGridView1.DataSource = ds;
                DataGridView1.DataMember = "student";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn_export_Click(object sender, EventArgs e)
        {
            //We have to add a reference to the Microsoft Excel object library.
            //Right click on your project and select Add Reference menu. After that go to COM tab and select and add Microsoft Excel 12.0 object library.

            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;

            try
            {

                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
                worksheet.Name = "Exported from DataGridView";

                for (int i = 1; i < DataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = DataGridView1.Columns[i - 1].HeaderText;
                }

                for (int i = 0; i < DataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < DataGridView1.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = DataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }


                string fileName = String.Empty;

                saveFileExcel.Filter = "Excel files |*.xls|All files (*.*)|*.*";
                saveFileExcel.FilterIndex = 2;
                saveFileExcel.RestoreDirectory = true;

                if (saveFileExcel.ShowDialog() == DialogResult.OK)
                {
                    fileName = saveFileExcel.FileName;

                    workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                }
                else
                    return;

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



Thanks

Hi Jack,


This is a web application.I think you need windows version.


Thanks

Hi, Satyapriya,Thanks very much for helping me. And I like VB much better than C#,haha.Your are very kind for offering me VB codes.
But I am a very beginner.I don't know what is a .aspx file and the usage of it. What is your "_default" class, a Form Class? If,yes. I guss the form's name is "Page". And there is  a button named "ImageButton1", a GridView control named "g1". Am I right? But I did't find any control named "GridView"? I have only "DataGridView" control listed on my toolbox.Can you explain a little more about your circumstance.

Hi Jack,

Default.aspx code

 

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

 

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Untitled Pagetitle>

head>

<body>

    <form id="form1" runat="server">

    <div>

   

    div>

    <asp:GridView ID="g1" runat="server">

    asp:GridView>

    <br />

    <br />

    <asp:Label ID="Label1" runat="server" Text="Export data to Excel"

        BackColor="Yellow" Font-Bold="True" ForeColor="#FF3300">asp:Label><br />

  

    <asp:ImageButton ID="ImageButton1" runat="server"  ImageUrl= "~/images/Excel2.JPG" />

 

    form>

body>

html>

 

Default.aspx.vb code

 

Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

 

Partial Class _Default

    Inherits System.Web.UI.Page

    Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()

    Dim con As New SqlConnection(strConnString)

    Dim str As String

    Dim com As SqlCommand

    Dim sqlda As SqlDataAdapter

    Dim ds As DataSet

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        bindgrid()

        g1.Visible = False

    End Sub

    Sub bindgrid()

        con.Open()

        str = "select * from SampleCustomer"

        com = New SqlCommand(str, con)

        sqlda = New SqlDataAdapter(com)

        ds = New DataSet

        sqlda.Fill(ds, "SampleCustomer")

        g1.DataSource = ds

        g1.DataMember = "SampleCustomer"

        g1.DataBind()

        con.Close()

    End Sub

    Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)

        Response.Clear()

        Response.Buffer = True

        Response.ContentType = "application/vnd.ms-excel"

        Response.Charset = ""

        Me.EnableViewState = False

        Dim oStringWriter As New System.IO.StringWriter

        Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

 

        g1.RenderControl(oHtmlTextWriter)

 

        Response.Write(oStringWriter.ToString())

        Response.[End]()

 

    End Sub

 

    Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click

        g1.Visible = True

        ExportToExcel("Report.xls", g1)

    End Sub

    Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

 

    End Sub

End Class

Thanks