How to Export Data from Gridview to Excel in Asp.net Using C# and VB Programing

To export gridview data to excel we need to write the code like as shown below

C# Coding

using System;
using System.Data;
using System.IO;
using System.Web.UI;

protected void Page_Load(object sender, EventArgs e)
{
 if (!IsPostBack)
 {
  BindGridview();
 }
}
protected void BindGridview()
{
 DataTable dt = new DataTable();
 dt.Columns.Add("UserId", typeof(Int32));
 dt.Columns.Add("UserName", typeof(string));
 dt.Columns.Add("Education", typeof(string));
 dt.Columns.Add("Location", typeof(string));
 dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai");
 dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur");
 dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu");
 dt.Rows.Add(4, "Rohini", "MSC", "Chennai");
 dt.Rows.Add(5, "Mahendra", "CA", "Guntur");
 dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur");
 gvDetails.DataSource = dt;
 gvDetails.DataBind();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
protected void btnExport_Click(object sender, EventArgs e)
{
 Response.ClearContent();
 Response.Buffer = true;
 Response.AddHeader("content-disposition", string.Format("attachment; filename={0}","Customers.xls"));
 Response.ContentType = "application/ms-excel";
 StringWriter sw = new StringWriter();
 HtmlTextWriter htw = new HtmlTextWriter(sw);
 gvDetails.AllowPaging = false;
 BindGridview();
 //Change the Header Row back to white color
 gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
 //Applying stlye to gridview header cells
 for (int i = 0; i < gvDetails.HeaderRow.Cells.Count; i++)
  {
   gvDetails.HeaderRow.Cells[i].Style.Add("background-color", "#df5015");
  }
  gvDetails.RenderControl(htw);
  Response.Write(sw.ToString());
  Response.End();
}

If you observe above code I added one function that is VerifyRenderingInServerForm this function is used to avoid the error like “control must be placed in inside of form tag”. If we setVerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.

VB.NET Coding

Imports System.Data
Imports System.IO
Imports System.Web.UI

Partial Class ExportGridviewDatainVB
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
 BindGridview()
End If
End Sub

Protected Sub BindGridview()
 Dim dt As New DataTable()
 dt.Columns.Add("UserId", GetType(Int32))
 dt.Columns.Add("UserName", GetType(String))
 dt.Columns.Add("Education", GetType(String))
 dt.Columns.Add("Location", GetType(String))
 dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai")
 dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur")
 dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu")
 dt.Rows.Add(4, "Rohini", "MSC", "Chennai")
 dt.Rows.Add(5, "Mahendra", "CA", "Guntur")
 dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur")
 gvDetails.DataSource = dt
 gvDetails.DataBind()
End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
' Verifies that the control is rendered

End Sub

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)
 Response.ClearContent()
 Response.Buffer = True
 Response.AddHeader("content-disposition", String.Format("attachment; filename={0}","Customers.xls"))
 Response.ContentType = "application/ms-excel"
 Dim sw As New StringWriter()
 Dim htw As New HtmlTextWriter(sw)
 gvDetails.AllowPaging = False
 BindGridview()
'Change the Header Row back to white color
 gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF")
'Applying stlye to gridview header cells
 For i As Integer = 0 To gvDetails.HeaderRow.Cells.Count - 1
 gvDetails.HeaderRow.Cells(i).Style.Add("background-color", "#df5015")
Next
 gvDetails.RenderControl(htw)
 Response.Write(sw.ToString())
 Response.[End]()
End Sub
End Class

================================================================================

Demo

Whenever we run application we will see the screen like as shown below

Once we click on Export to Excel button we will see data in excel file like as shown below

Storing Images to Database and Retrieving to GridView

Creating the File Upload page:

We will start with the default page, from where we will provide the user the functionality to upload the images. Open Default.aspx and switch to design-view. Drag-n-drop controls from the toolbox onto the page to create a similar form:

In the above page, the controls are as follows:

1. fileUpload - To select the file to upload.
2. txtTitle - The title of the image.
3. btnUpload - On click uploads the selected image.
4. Hyperlink - The View Images link that loads the images from the databases inside a GridView.

Creating DataBase as shown below

Switch to the code-behind class of Default.aspx and add a button click event handler for btnUpload.

Program for Uploading File

Protected Sub btnUpload_Click(..., ...) Handles btnUpload.Click
        Dim intLength As Integer
        Dim arrContent As Byte()

        If fileUpload.PostedFile Is Nothing Then
            lblStatus.Text = "No file specified."
            Exit Sub
        Else
            Dim fileName As String = fileUpload.PostedFile.FileName
            Dim ext As String = fileName.Substring(fileName.LastIndexOf("."))
            ext = ext.ToLower

            Dim imgType = fileUpload.PostedFile.ContentType
            If ext = ".jpg" Then
            ElseIf ext = ".bmp" Then
            ElseIf ext = ".gif" Then
            ElseIf ext = "jpg" Then
            ElseIf ext = "bmp" Then
            ElseIf ext = "gif" Then
            Else
                lblStatus.Text = "Only gif, bmp, or jpg format files supported."
                Exit Sub
            End If

            intLength = Convert.ToInt32(fileUpload.PostedFile.InputStream.Length)
            ReDim arrContent(intLength)

            fileUpload.PostedFile.InputStream.Read(arrContent, 0, intLength)

            If Doc2SQLServer(txtTitle.Text.Trim, arrContent, intLength, imgType) = True Then
                lblStatus.Text = "Image uploaded successfully."
            Else
                lblStatus.Text = "An error occured while uploading Image... Please try again."
            End If
        End If
End Sub

What this function does is that it grabs the file selected in the File Input control and gets its extension. If the file is of type jpg, bmp, or gif, then it proceeds otherwise it throws an error. This terminates upload if the selected file is not an image of supported format.
After checking the file format, we get the length of the file and create a Byte Array of that same length. This Byte Array will store our file/image for us. Using the InputStream.Read method of fileUpload control, we load the image into the Byte Array.
After having saved the file into the byte array, we call our function Doc2SQLServer to store the file. We pass in the title that was supplied by the user, the byte array (which is our image), the total length, and the type of image.
In the Doc2SQLServer method, we create a connection to our SQL Express database and create an insertion command. After connecting to the database, we execute the query and store our image and its information to the database. Below is the Doc2SQLServer method.
Protected Function Doc2SQLServer(ByVal title As String, ByVal Content AsByte(), ByVal Length As Integer, ByVal strType As String) As Boolean 

Try
            Dim cnn As Data.SqlClient.SqlConnection
            Dim cmd As Data.SqlClient.SqlCommand
            Dim param As Data.SqlClient.SqlParameter
            Dim strSQL As String

            strSQL = "Insert Into tblImage(imgData,imgTitle,imgType,imgLength) Values(@content,@title,@type,@length)"

            Dim connString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\imgDB.mdf"";Integrated Security=True;User Instance=True"
            cnn = New Data.SqlClient.SqlConnection(connString)

            cmd = New Data.SqlClient.SqlCommand(strSQL, cnn)

            param = New Data.SqlClient.SqlParameter("@content", Data.SqlDbType.Image)

            param.Value = Content
            cmd.Parameters.Add(param)
            param = New Data.SqlClient.SqlParameter("@title",    Data.SqlDbType.VarChar)
            param.Value = title
            cmd.Parameters.Add(param)

            param = New Data.SqlClient.SqlParameter("@type", Data.SqlDbType.VarChar)
            param.Value = strType
            cmd.Parameters.Add(param)

            param = New Data.SqlClient.SqlParameter("@length", Data.SqlDbType.BigInt)
            param.Value = Length
            cmd.Parameters.Add(param)

            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
End Function

Image Grabber

Next we create a web-page that will grab the image whose id is passed to it as a query string. Right-click the project in solution explorer and add a web-page imgGrab.aspx.

Define the page_load method as follows:

Protected Sub Page_Load(...,...) Handles Me.Load
        Try
            Dim ds As New DataSet
            Dim da As SqlClient.SqlDataAdapter
            Dim arrContent As Byte()
            Dim dr As DataRow
            Dim strSql As String
            strSql = "Select * from tblImage Where imgId=" & Request.QueryString("ID")
            Dim connString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\imgDB.mdf"";Integrated Security=True;User Instance=True"
            da = New SqlClient.SqlDataAdapter(strSql, connString)
            da.Fill(ds)
            dr = ds.Tables(0).Rows(0)
            arrContent = CType(dr.Item("imgData"), Byte())
            Dim conType As String = dr.Item("imgType").ToString()
            Response.ContentType = conType
            Response.OutputStream.Write(arrContent, 0, dr.Item("imgLength"))
            Response.End()
        Catch ex As Exception
        End Try
End Sub

What this does is quite simple. It gets the image data whose id is passed in and writes it to the web-page. Response.ContentType sets the web-page as an image content holder and when we write the complete byte array to the response stream, we actually get the image.

Loading Images into Gridview Control

A major problem faced by developers is when loading images back into the datagrid or the new gridview control. I myself faced long hours trying to come up with a solution for this, which in the end was a simple procedure.

Create a new-page, Viewer.aspx and add a GridView onto it. (This page is linked from the Default.aspx page via lnkView). Name the GridView imgGrid and click on the small box located at the upper-right corner of the control.

Click on Edit Columns to open the Fields Dialog as shown below.

In this dialog, uncheck "Auto-generate fields" and add two fields; a bound field and an image field. For the bound field set the column header text to Title and the DataField property to imgTitle (This will bind this column to the imgTitle column in the dataset to which we will load our table data). As for the image field, set the caption to Picture and the DataImageUrlField to imgFile. You might think where this imgFile comes from, especially since the database does not contain a column with such a name.

Switch to the code-behind class and create the page_load event as follows:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)Handles Me.Load
        Dim ds As New DataSet
        Dim da As SqlClient.SqlDataAdapter
        Dim strSQL As String

        strSQL = "Select imgId,imgTitle from tblImage"
        Dim connString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\imgDB.mdf"";Integrated Security=True;User Instance=True"
        da = New SqlClient.SqlDataAdapter(strSQL, connString)
        da.Fill(ds)

        ds.Tables(0).Columns.Add("imgFile")

        For Each tempRow As DataRow In ds.Tables(0).Rows
            tempRow.Item("imgFile") = ("imgGrab.aspx?id=" & tempRow.Item("imgID"))
        Next

        imgGrid.DataSource = ds
        imgGrid.DataBind()
End Sub

What this function does is the simple task of grabbing data from the database. It grabs two columns, imgId and imgTitle. What it does afterwards is the key concept... and the few-lines of code that marks the solution to a big problem.
After filling in the dataset, we know that it contains one table, at index 0 of course. We add a custom column "imgFile" to it.
After that, we traverse through each dataRow in the table. And for each row, we make a call to our imgGrab.aspx page with the id from that row. We set this equal to the record for imgFile for that row.
Thus on display of the GridView, for each row being displayed, the imgGrab.aspx web-page is called and the image is displayed in return.