Click here to Skip to main content
Click here to Skip to main content

Tagged as

Databind a GridView and export to Excel or text file

, 14 Aug 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

Main form Image

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
                {
                }
            }
         
        }

Record SavedForm Image

//
//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
    {

    }
}

Textfile Image

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();
    }
}

Exel image

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nilesh Patil Miraj
Software Developer
India India
No Biography provided

Comments and Discussions

 
Questionadd sum more columns Pinmemberkamil shaikh17-Dec-12 21:20 
after doing this i want to add some more columns and their values in that existing excel in background
please give me any suggestion..
 

thnx
regards
kamil
Questionnice code, Good........... PinmemberPatil Kishor16-Aug-12 17:31 
AnswerThanks PinmemberNilesh Patil Miraj17-Aug-12 0:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 14 Aug 2012
Article Copyright 2012 by Nilesh Patil Miraj
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid