How Convert GridView to Word/Excel Asp.Net C# Code

Task : How convert a gridview to Word Document or in Excel Sheet using asp.net/c#  code.

Description: Some time we need to generate different kind of reports & need to save it in Excel or word Document for Local Reference, Or sending to other's. So here I create a code for achieve this task the first one is conversion data into Excel & second one is for Word Document.

// For pdf converstion we can use third party dll like ITextSharp

C# Code : For GridView To Excel

 protected void btn_excel_Click(object sender, EventArgs e)

    {

          string attachment = "attachment; filename=myreport.xls";

          Response.Cache.SetCacheability(HttpCacheability.NoCache);

          Response.AddHeader("content-disposition", attachment);

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

          StringWriter swriter = new StringWriter();

          HtmlTextWriter htmlwriter = new HtmlTextWriter(swriter);

 

          // Create a form to contain the gridview(MyGridView)

          HtmlForm mynewform = new HtmlForm();

          MyGridView.Parent.Controls.Add(mynewform);

          mynewform.Attributes["runat"] = "server";

          mynewform.Controls.Add(MyGridView);

          mynewform.RenderControl(htmlwriter);

          Response.Write(swriter.ToString());

          Response.End();

    }

C# Code : For GridView To Word

    protected void btn_word_Click(object sender, EventArgs e)

    {

          Response.AddHeader("content-disposition", "attachment;filename=myreport.doc");

          Response.Cache.SetCacheability(HttpCacheability.NoCache);

          Response.ContentType = "application/vnd.word";

          System.IO.StringWriter swriter = new System.IO.StringWriter();

          System.Web.UI.HtmlTextWriter htmlwriter = new HtmlTextWriter(swriter);

 

          // Create a form to contain the gridview(MyGridView)

          HtmlForm mynewform = new HtmlForm();

          MyGridView.Parent.Controls.Add(mynewform);

          mynewform.Attributes["runat"] = "server";

          mynewform.Controls.Add(MyGridView);

          mynewform.RenderControl(htmlwriter);

          Response.Write(swriter.ToString());

          Response.End();

    }

How to set Database Connection in C#.NET - ASP.NET for Excel, Access and Sql Server

// How to set connection string in c#.net /asp.net for 1)Excel , 2)MS-Acess and  3)Sql Server
// You can set connection string in your web.config file as describe below...

//Below Code describe the first step of ADO.Net , Set Connection string to connect .Net to database for communication between them.

<connectionStrings>
<add name="hhcsnot"  connectionString="Server=database_server_ip;Initial Catalog=databasename;User ID=username;Password=yourpassword"/>
</connectionStrings>



//1. EXCEL TO .NET-[Top]
// How Retrieve Data From Excel to Gridview in C#.NET , ASP.NET
//Database Connection String for Excel 2003 for C#.NET, ASP.NET HELP 
//
using System.Data.OleDb;
 protected void btn_showst_Click()
    {
        OleDbConnection con;
        try
        {
            string file = "book";
            string fn = "Sheet1";
            string fname2 = "[" + fn + "$]";
            con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\" + file + ".xls;Extended Properties=Excel 8.0;");
            con.Open();
            OleDbCommand cmd = new OleDbCommand("select * from " + fname2 + "", con);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            GridView1.DataSource = dr;
            GridView1.DataBind();
            con.Close();
        }
        catch (Exception ex)
        {
            Label2.Text = ex.Message;
        }
    }


/// 2. MS-ACCESS  TO .NET-[Top]
//Connection String for ms-access to C#.NET, ASP.NET 
// How Retrieve Data From Access to Gridview 


using System.Data.OleDb;
protected void btn_showst_Click()
    {
    OleDbConnection con;

    con = new OleDbConnection("Microsoft.Jet.OLEDB.4.0"      connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\databasename.mdb");

   // Here Access database should be in App_Data Folder..


  con.Open();
  OleDbCommand cmd = new OleDbCommand("select * from  tablename", con);
  OleDbDataReader dr;
  dr = cmd.ExecuteReader();
  GridView1.DataSource = dr;
  GridView1.DataBind();
  con.Close();
}

// 3. Sql Server To .NET -[Top]
// Connection String For Sql Server to C#.NET, ASP.NET
// How retrieve Data From Sql Server to Gridview


using System.Data.SqlClient;

protected void btn_showst_Click()
    {

SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Database.mdf; Integrated Security=True;User Instance=True");
 // Here Sql Database file should be in App_Data Folder..

// or
con.Open();

SqlCommand com = new SqlCommand("select * from " + tablename + " ", con);

SqlDataReader dr = com.ExecuteReader();

GridView1.DataSource = dr;
GridView1.DataBind();
 con.Close();
}

NOTE : //////   Two Type of SqlConnection String First for sql file and Second for sql server you have to almost use 2nd type. Passing IP address of Database server , database name , User id & password.

SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Database.mdf; Integrated Security=True;User Instance=True");

// In below Server name or ip address is come there , Initial Catalog for Database name; user id -password // is sql user id and password by defaul sql user is 'sa' and password you should be set it 
//
SqlConnection con = new SqlConnection( "Server=127.0.0.1;Initial Catalog=Database;User ID=sa;Password=123456" );

-[Top]