Data From Grid to Excel

Question

1.00/5 (1 vote)

See more:

VB

Hello, i am working with the grid in vb.net.i want to transfer data from grid to excel with the proper format.

Posted 24-Mar-12 0:17am

Dipak V Nakum

Add a Solution

Comments

Nelek 24-Mar-12 6:52am

And what have you done so far?

Maciej Los 24-Mar-12 7:39am

Exactly!
And what kind of grid: DataGrid or DataGridView?

Dipak V Nakum 24-Mar-12 7:46am

Dear losmac, see i have written vb.net ,you should know which one control is coming in the vb.net.

2 solutions

Solution 1

Here is the sample VB.NET code to transfer data to excel on button clicked event

VB

Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click       
            Me.lblMessage.Text = ""
            Me.gdvCustomer.AllowPaging = False
 
            Me.gdvCustomer.DataBind()
            Dim tw As New StringWriter()
            Dim hw As New System.Web.UI.HtmlTextWriter(tw)
            Dim frm As HtmlForm = New HtmlForm()
            Page.Response.ContentType = "application/vnd.ms-excel"
           Page.Response.AddHeader("content-disposition", "attachment;Customer.xls")
            Page.Response.Charset = ""
            Page.EnableViewState = False
            frm.Attributes("runat") = "server"
            Controls.Add(frm)
 
            frm.Controls.Add(gdvCustomer)
            frm.RenderControl(hw)
            Response.Write(tw.ToString())
            Response.End()
            Me.gdvCustomer.AllowPaging = True
End Sub

Posted 24-Mar-12 2:40am

Ganesan Senthilvel

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dipak V Nakum · Accepted Answer · 2012-03-25T17:32:00

DATA GRID TO MS-EXCEL DATA TRANSFER

Private Sub LoadReportStampReceipt()
Try
Dim dtstock, dtOpBal, dtParam, dtTempReport, dtTrial As New DataTable
If objCommon Is Nothing Then objCommon = New DataCommon

StrSql = "SELECT A.*,B.ACCT_NAME account_name FROM STAMP_REC A,ACCT_MAST B WHERE(A.ACCT_CODE = B.ACCT_CODE) ORDER BY A.srno"
dtReport = objCommon.FillTable(StrSql, "LoadReportStampReceipt")

dgvReport1.Visible = True
btnExpToXLS1.Visible = True
btnViewExcelFile1.Visible = True
dgvReport1.DataSource = dtReport
dgvReport1.Refresh()

Catch ex As Exception
ErrorHandler(ex.Message, Me.Name, "LoadReportStampReceipt")
End Try

End Sub
Private Sub btnExpToXLS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExpToXLS1.Click

KillAllExcels()

firstlevel()

SaveFileDialog1.Filter = "Excel File(*.xls)|*.xls |All Files(*.*)|*.*"
SaveFileDialog1.Title = "Save As"
SaveFileDialog1.FilterIndex = 0
SaveFileDialog1.OverwritePrompt = True

If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
xlWorkSheet.SaveAs(SaveFileDialog1.FileName)
MsgBox("Export Successfully !!!!!!!")
xlWorkBook.Close()
xlApp.Quit()
SaveFileDialog1.FileName = ""

End If
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End Sub

Sub KillAllExcels()

Dim proc As System.Diagnostics.Process

For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL.EXE")
proc.Kill()
proc.Refresh()
Next

End Sub

Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Private Sub dgvReport1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgvReport1.DoubleClick

objCommon.cnToSqlServer()

adpt = New SqlDataAdapter("select acct_code,address_code, address1,address2,address3,city from acc_adr where acct_code='" & dgvReport1.SelectedCells(2).Value & "'", objCommon.cnToSql)
ds = New DataSet
adpt.Fill(ds, "Acc_Adr")

If ds.Tables(0).Rows().Count > 0 Then
dgvReport2.DataSource = ds.Tables(0)
dgvReport2.Refresh()
Tab1.SelectedIndex = 1

dgvReport2.Visible = True
btnExpToXLS2.Visible = True
btnViewExcel2.Visible = True
Else
MsgBox("There Is No Data .......", MsgBoxStyle.OkOnly)
Tab1.SelectedIndex = 0
dgvReport2.DataSource = Nothing
dgvReport2.Refresh()
End If
objCommon.cnToSql.Close()

End Sub
Private Sub btnViewExcel3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewExcel3.Click

thirdlevel()

xlApp.Visible = True
xlApp.DisplayAlerts = False

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

End Sub
Private Sub thirdlevel()
Dim a As Excel.Name
Dim misValue As Object = System.Reflection.Missing.Value
Dim i, j As Integer
Dim r As Excel.Range

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")

With xlWorkSheet.Range("D1")
.Value = "WINDSOR MACHINES LIMITED - VATVA"
.Font.Bold = True
.Font.Size = 16
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

End With

With xlWorkSheet.Range("D2")
.Value = "Account Details"
.Font.Bold = True
.Font.Size = 14
End With

With xlWorkSheet.Range("G1")
.Value = Now
.Font.Bold = True
.Font.Size = 14
End With

xlWorkSheet.Range("C1", "E1").Merge()
xlWorkSheet.Range("D2", "E2").Merge()
xlWorkSheet.Range("G1", "H1").Merge()

For Each col As DataGridViewColumn In dgvReport3.Columns
xlWorkSheet.Cells(4, col.Index + 1) = col.HeaderText

For Each rowa As DataGridViewRow In dgvReport3.Rows
xlWorkSheet.Cells(rowa.Index + 5, col.Index + 1) = rowa.Cells(col.Index).Value

r = xlWorkSheet.Range("A4", "Z4")
r.EntireColumn.AutoFit()

With xlWorkSheet.Range("A4", "Z4")
.Font.Size = 12
.Font.Bold = True
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
Next
Next

Dim tot As Double

For i = 0 To dgvReport3.RowCount - 1
tot += dgvReport3.Rows(i).Cells(1).Value
Next

Dim t As Integer = dgvReport3.Rows.Count

If tot = 0 Then
With xlWorkSheet.Range("B" & t + 6)
.Font.Bold = True
.Font.Size = 12
.Value = 0
r.EntireColumn.AutoFit()
End With
Else
With xlWorkSheet.Range("B" & t + 6)
.Font.Bold = True
.Font.Size = 12
.Value = tot
r.EntireColumn.AutoFit()
End With
End If

With xlWorkSheet.Range("A" & t + 6)
.Value = "SUM"
.Font.Bold = True
.Font.Size = 12
r.EntireColumn.AutoFit()
End With

Dim tot1 As Double

For d As Integer = 0 To dgvReport3.RowCount - 1
tot1 += dgvReport3.Rows(d).Cells(2).Value
Next

Dim t1 As Integer = dgvReport3.Rows.Count
If tot1 = 0 Then
With xlWorkSheet.Range("C" & t1 + 6)
.Font.Bold = True
.Font.Size = 12
.Value = 0
r.EntireColumn.AutoFit()
End With
Else
With xlWorkSheet.Range("C" & t1 + 6)
.Font.Bold = True
.Font.Size = 12
.Value = tot1
r.EntireColumn.AutoFit()
End With
End If

Dim tot2 As Double

For q As Integer = 0 To dgvReport3.RowCount - 1
tot2 += dgvReport3.Rows(q).Cells(3).Value
On Error Resume Next
Next

Dim t2 As Integer = dgvReport3.Rows.Count

If tot2 = 0 Then
With xlWorkSheet.Range("D" & t1 + 6)
.Font.Bold = True
.Font.Size = 12
.Value = 0
r.EntireColumn.AutoFit()
End With
Else
With xlWorkSheet.Range("D" & t2 + 6)
.Font.Bold = True
.Font.Size = 12
.Value = tot2
r.EntireColumn.AutoFit()
End With
End If
End Sub