How to Upload data of DataGridView in Microsoft Excel

How to Upload data of DataGridView in Microsoft Excel

create database test

use test

create table StudentDetails(Name nvarchar(50),FatherName nvarchar(50), MobileNo nvarchar(50))

insert into StudentDetails values('Anurag Sir','Mr Mudgal','08802200402')

insert into StudentDetails values('Kush Tiwari','Jai Prakash Tiwari','9451119029')

First we take a Windows Form after it we add add reference Microsoft  Excel 12.0 object Library 1.6    in our  application then take  a DataGridView and bind it with table   and when you click the button  you will see the all data inside DataGridView   in Microsoft Excel format

You  can also save  it if youwant to

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;

namespace GridViewWithExcelSheet

{

    public partial class DataGridwithExcelSheet : Form

    {

        public DataGridwithExcelSheet()

        {

            InitializeComponent();

        }

  private void DataGridwithExcelSheet_Load(object sender, EventArgs e)

 {

   //create code to connectivity with table show  data  in gridview on Page Load

 SqlConnection con = new SqlConnection(@"Data Source=KUSH-PC\KUSH;Initial Catalog=test;Integrated Security=True");

 SqlCommand cmd = new SqlCommand("select * from StudentDetails", con);

 SqlDataAdapter da = new SqlDataAdapter(cmd);

 DataSet ds = new DataSet();

 da.Fill(ds, "StudentDetails");

 dataGridView1.DataSource = ds.Tables["StudentDetails"];

 }

private void btnUpload_Click(object sender, EventArgs e)

{

 try

{

  Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();

  Microsoft.Office.Interop.Excel._Workbook ExcelBook;

  Microsoft.Office.Interop.Excel._Worksheet ExcelSheet;

  int i = 0;

  int j = 0;

 //create object of excel

 ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)ExcelApp.Workbooks.Add(1);

 ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
 //export header

 for (i = 1; i <= this.dataGridView1.Columns.Count; i++)

{

 ExcelSheet.Cells[1, i] = this.dataGridView1.Columns[i - 1].HeaderText;

 }

 //export data

 for (i = 1; i <= this.dataGridView1.RowCount; i++)

 {

  for (j = 1; j <= dataGridView1.Columns.Count; j++)

  {

 ExcelSheet.Cells[i + 1, j] = dataGridView1.Rows[i - 1].Cells[j - 1].Value;

   }

 }

ExcelApp.Visible = true;

//set font Khmer OS System to data range
 Microsoft.Office.Interop.Excel.Range myRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, 1], ExcelSheet.Cells[this.dataGridView1.RowCount + 1, this.dataGridView1.Columns.Count]);

Microsoft.Office.Interop.Excel.Font x = myRange.Font;

x.Name = "Arial";

x.Size = 10;

//set bold font to column header
 myRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, 1], ExcelSheet.Cells[1,this.dataGridView1.Columns.Count]);

                
x = myRange.Font;
 x.Bold = true;

               
//autofit all columns

 myRange.EntireColumn.AutoFit();
 //

 ExcelSheet = null;

ExcelBook = null;

ExcelApp = null;
  }

 catch (Exception ex)

 {

  LMessage.Text = ex.Message;

  }

}

}