How to export datagridview to excel sheet?
This resource is about datagridview to excel sheet exporting. In many application clients are demanding for such facility to store their data in excel sheet. This resource represents to create workbook and worksheet according to requirement. Microsoft office links files are necessary to perform this task and very helpful.
One way for fulfilling this requirement is first have to create crystal report and then by using export facility of crystal report we can export our whole data into excel sheet.
Instead of doing such method we are having another way to export whole data into excel sheet by using datagridview so No need to design crystal report
sample of code is given below and .dlls are also use to complete this task.
Imports System.IO
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Dim i, j As Integer
Dim ds As DataSet
Dim sda As New SqlDataAdapter
Dim scon As SqlConnection
Dim str As String = "DATABASE PATH"
Dim scom As SqlCommand
Dim sql As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
sql = "select *from detail"
scon = New SqlConnection(str)
scom = New SqlCommand(sql, scon)
sda = New SqlDataAdapter(scom)
ds = New DataSet
sda.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim exc As New Excel.ApplicationClass
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
wb = exc.Workbooks.Add()
ws = wb.ActiveSheet()
Dim dt As System.Data.DataTable = DataGridView1.DataSource()
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
exc.Cells(1, colIndex) = dc.ColumnName
Next
For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
exc.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
ws.Columns.AutoFit()
Dim strFileName As String = "D:\view_all.xls"
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try
If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If
wb.SaveAs(strFileName)
exc.Workbooks.Open(strFileName)
exc.Visible = True
End Sub
End Class
Hi Dhiraj Solanki,
Nice explanation there are no.of ways to do Export excel data to gridview control.
Please refer below sample also this is very helpful and easy to understand.
private void Display()
{
OleDbConnection oconn = null;
DataTable dt1 = new DataTable();
//Add dummy columns to datatable.
dt1.Columns.Add("ENAME");
dt1.Columns.Add("JOB");
dt1.Columns.Add("MGR");
dt1.Columns.Add("SAL");
dt1.Columns.Add("COMM");
try
{
string FileName = ViewState["FileName"] as string;
string FilePath = ViewState["FilePath"] as string;
oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");
//select file name based upon dropdown selecteditem.
OleDbCommand ocmd = new OleDbCommand("select * from [" + ddlSheet.SelectedItem.ToString() + "]", oconn);
oconn.Open();
//Read all rows and then store in DataTable
OleDbDataReader odr = ocmd.ExecuteReader();
string ENAME=string.Empty;
string JOB=string.Empty;
string MGR=string.Empty;
string SAL=string.Empty;
string COMM=string.Empty;
while (odr.Read())
{
ENAME = odr["ENAME"].ToString();
JOB = odr["JOB"].ToString();
MGR = odr["MGR"].ToString();
SAL = odr["SAL"].ToString();
COMM = odr["COMM"].ToString();
DataRow dr = dt1.NewRow();
dr["ENAME"] = ENAME;
dr["JOB"] = JOB;
dr["MGR"] = MGR;
dr["SAL"] = SAL;
dr["COMM"] = COMM;
dt1.Rows.Add(dr);
}
//Display data to gridview if Records are found
if (dt1.Rows.Count > 0)
{
Session["Table"] = dt1;
gvExcelData.Visible = true;
gvExcelData.DataSource = dt1;
gvExcelData.DataBind();
}
}
catch (DataException ex)
{
}
finally
{
if (oconn != null)
{
oconn.Close();
oconn.Dispose();
}
if (dt1 != null)
{
dt1.Dispose();
}
}
}
//here it is displaying all the records into one gridview control
protected void btnShow_Click(object sender, EventArgs e)
{
Display();
}
}
Hope this will help you..