Databind a GridView and export to Excel or text file

• Download source - 134 KB

Introduction

This article is about binding a database table to a GridView and exporting the GridView data to an Excel text file. An Excel file is used to store information. Data export to Excel is an easy way to maintain record sheets like reports. Similarly, the generated text file is used to extract data and used to process data or pass to an application as input data.

The application

Create a Windows application in C# .NET 2010 and designed as shown below:

Import Namespaces as follows:

using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.VisualBasic;

Create a database in SQL Server 2008 or attach the provided sample Export_DB.bak file.

Field Name  FiledType
RowID       int (IDENTITY (1,1))
Name        nvarchar(50)
Address     nvarchar(20)
City        nvarchar(20)
State       nvarchar(20)

Using the code

We have created an Excel sheet of GridView data and stored it as a report, and we have customised that Excel like its background color, size, and graphical representations on the Excel file.

//
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.SqlClient;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.VisualBasic;
namespace Export_Demo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlConnection Con = new SqlConnection("Data Source=.;Initial Catalog=Export_DB;" + 
            "Persist Security Info=True;User ID=sa;                pwd=ezc;Connect Timeout=30");
            //Enter Your sql Server Password eg.pwd=xyz
        private void Form1_Load(object sender, EventArgs e)
        {
         
        }

        private void btnADDRecord_Click(object sender, EventArgs e)
        {
            if ((textBox1.Text == "") || (textBox2.Text == "") || 
                   (textBox3.Text == "") || (textBox4.Text == ""))
            {
                MessageBox.Show("Enter all Records");
            }
            else
            {
                string SQLcmd = "";
                SQLcmd = "INSERT INTO Table1(Name, Address, City, State)Values('" + 
                  textBox1.Text + "', '" + textBox2.Text + "', '" + 
                  textBox3.Text + "', '" + textBox4.Text + "')";
                SqlCommand Cmd = new SqlCommand(SQLcmd, Con);
                Con.Open();
                Cmd.ExecuteNonQuery();
                textBox1.Text="" ;
                textBox2.Text ="";
                textBox3.Text ="";
                textBox4.Text = "";
                SqlDataAdapter SQLadp = new SqlDataAdapter("SELECT * FROM Table1", Con);
                //Create and fill a  Datatable.
                DataTable dtusers = new DataTable();
                SQLadp.Fill(dtusers);

                if (dtusers.Rows.Count > 0)
                {
                    dataGridView1.DataSource = dtusers;
                    dataGridView1.AutoGenerateColumns = false;
                    dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
                    Con.Close();
                }
                else
                {
                }
            }
         
        }

//
//Export To Text files 
private void btnExportText_Click(object sender, EventArgs e)
{
    if (dataGridView1.RowCount > 0)
    { 
  
    String RowcCount = "";
    string Startuppath = Application.StartupPath + "/";
    //File Extension as your requirement .dat or .txt 
    string Destinationpath = Startuppath + ""+ DateTime.Now.ToString("dd-MMM-yyy") +".txt";
    using (StreamWriter Streamwrite = File.CreateText(Destinationpath))
    {

        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            RowcCount = "";
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                if (RowcCount.Length > 0)
                {
                    RowcCount = RowcCount + "|" + dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
                else
                {
                    RowcCount = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }
            Streamwrite.WriteLine(RowcCount);
        }
        Streamwrite.WriteLine(Streamwrite.NewLine);
        Streamwrite.Close();
        MessageBox.Show("File Created Successfully");
    }

    }
    else
    {

    }
}

Export to Excel file:

private void btnExportExel_Click(object sender, EventArgs e)
{
    CreateExel();
}
private void CreateExel()
{
    int colIndex=1;
    int rowIndex =1;
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;
    xlApp = new Excel.Application();
    Excel.Range ExelRange;
    SqlDataAdapter sqlADP = new SqlDataAdapter("SELECT * FROM Table1", Con);
    //Create and fill a  Datatable.
    DataTable DTtable = new DataTable();
    sqlADP.Fill(DTtable);
    dataGridView1.DataSource = DTtable;

    dataGridView1.AutoGenerateColumns = false;
    dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
  
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    foreach (DataRow theRow in DTtable.Rows)
    {
        rowIndex = rowIndex + 1;
        colIndex = 0;
        foreach (DataColumn dc in DTtable.Columns)
        {
            colIndex = colIndex + 1;
            xlWorkSheet.Cells[rowIndex + 1, colIndex] = theRow[dc.ColumnName];
            xlWorkSheet.Rows.AutoFit();
            xlWorkSheet.Columns.AutoFit();
        }
    }

    xlWorkSheet.get_Range("b2", "e2").Merge(false);

    ExelRange = xlWorkSheet.get_Range("b2", "e2");
    ExelRange.FormulaR1C1 = "Exel Title or Table Name ";
    
    ExelRange.HorizontalAlignment = 3;
    ExelRange.VerticalAlignment = 3;

    xlApp.Visible = true;
    ObjectRelease(xlWorkSheet);
    ObjectRelease(xlWorkBook);
    ObjectRelease(xlApp);

}
private void ObjectRelease(object objRealease)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(objRealease);
        objRealease = null;
    }
    catch (Exception ex)
    {
        objRealease = null;
        MessageBox.Show("Error_" + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}