Author

Jack Smith
posted 3 posts
since Feb 07, 2012
from

How to export data to Excel?

Posted on: 07 Feb 2012

Hello,everyone. I have Lots of datas in my App. So, I want to export those datas to Excel. So, I can edit those data in the Excel rather than in my App? How to do this? I use C#2008 and Excel 2007.
Thanks.

Satyapriya Nayak
posted 1902 posts
since Mar 24, 2010
from

Re: How to export data to Excel?

Posted on: 07 Feb 2012

export to excel.rar

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">

<title>Untitled Page</title>

</head>

<body>

<div>

</div>

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

</asp:GridView>

<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

Jack Smith
posted 3 posts
since Feb 07, 2012
from

Re: How to export data to Excel?

Posted on: 07 Feb 2012

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.

Satyapriya Nayak
posted 1902 posts
since Mar 24, 2010
from

Re: How to export data to Excel?

Posted on: 07 Feb 2012

Hi Jack,

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

Thanks

Satyapriya Nayak
posted 1902 posts
since Mar 24, 2010
from

Re: How to export data to Excel?

Posted on: 07 Feb 2012

export to excel in windows.rar

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

Jack Smith
posted 3 posts
since Feb 07, 2012
from

Re: How to export data to Excel?

Posted on: 08 Feb 2012

Hi, Satyapriya.Thanks.That helps.Haha.
But can you help me again. I want to set the alignment to be centre in Excel. How to do this?