数据从网格到Excel [英] Data From Grid to Excel

问题描述

您好，我正在使用vb.net中的网格，我想以正确的格式将数据从网格传输到excel.

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

推荐答案

示例VB.NET代码，可在按钮单击事件中将数据传输到excel

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

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

数据网格进行MS-EXCEL数据传输


私有子LoadReportStampReceipt()
试试
昏暗的dtstock，dtOpBal，dtParam，dtTempReport，dtTrial作为新数据表
如果objCommon什么都没有，则objCommon =新DataCommon

StrSql =从STAMP_REC A，ACCT_MAST B WHERE(A.ACCT_CODE = B.ACCT_CODE)ORDER BY A.srno订购SELECT A.*，B.ACCT_NAME account_name"
dtReport = objCommon.FillTable(StrSql，"LoadReportStampReceipt")

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


异常捕获
ErrorHandler(例如消息，Me.Name，"LoadReportStampReceipt")
结束尝试

结束子
私有Sub btnExpToXLS_Click(ByVal发送者作为System.Object，ByVal e作为System.EventArgs)处理btnExpToXLS1.Click

KillAllExcels()

firstlevel()

SaveFileDialog1.Filter ="Excel文件(* .xls)| * .xls |所有文件(*.*)| *.*"
SaveFileDialog1.Title =另存为"
SaveFileDialog1.FilterIndex = 0
SaveFileDialog1.OverwritePrompt = True

如果SaveFileDialog1.ShowDialog()= Windows.Forms.DialogResult.OK，然后
xlWorkSheet.SaveAs(SaveFileDialog1.FileName)
MsgBox(导出成功！!!!!!!")
xlWorkBook.Close()
xlApp.Quit()
SaveFileDialog1.FileName ="

如果结束
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
结束子

Sub KillAllExcels()

Dim proc作为System.Diagnostics.Process

对于System.Diagnostics.Process.GetProcessesByName("EXCEL.EXE")中的每个proc
proc.Kill()
proc.Refresh()
下一个

结束子

私有子版本releaseObject(ByVal obj作为对象)
试试
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj =没什么
异常捕获
obj =没什么
终于
GC.Collect()
结束尝试
结束子
私有Sub dgvReport1_DoubleClick(ByVal发送者作为对象，ByVal e作为System.EventArgs)处理dgvReport1.DoubleClick

objCommon.cnToSqlServer()

adpt =新的SqlDataAdapter(从acc_adr中选择acct_code，address_code，address1，address2，address3，city，其中acct_code ="& dgvReport1.SelectedCells(2).Value&""，objCommon.cnToSql)
ds =新数据集
adpt.Fill(ds，"Acc_Adr")

如果ds.Tables(0).Rows().Count> 0然后
dgvReport2.DataSource = ds.Tables(0)
dgvReport2.Refresh()
Tab1.SelectedIndex = 1

dgvReport2.Visible = True
btnExpToXLS2.Visible = True
btnViewExcel2.Visible = True
其他
MsgBox(没有数据......."，MsgBoxStyle.OkOnly)
Tab1.SelectedIndex = 0
dgvReport2.DataSource =没什么
dgvReport2.Refresh()
如果结束
objCommon.cnToSql.Close()

结束子
私有Sub btnViewExcel3_Click(ByVal发送者作为System.Object，ByVal e作为System.EventArgs)处理btnViewExcel3.Click

thirdlevel()

xlApp.Visible = True
xlApp.DisplayAlerts = False

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

结束子
私人Sub thirdlevel()
昏暗为Excel.Name
作为对象的Dim misValue = System.Reflection.Missing.Value
Dim i，j作为整数
作为Excel.Range的昏暗

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

使用xlWorkSheet.Range("D1")
.Value ="WINDSOR MACHINES LIMITED-VATVA"
.Font.Bold = True
字体大小= 16
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

结尾为

使用xlWorkSheet.Range("D2")
.Value =帐户详细信息"
.Font.Bold = True
.Font.Size = 14
结尾为

使用xlWorkSheet.Range("G1")
.Value =现在
.Font.Bold = True
.Font.Size = 14
结尾为

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

对于每个col作为dgvReport3.Columns中的DataGridViewColumn
xlWorkSheet.Cells(4，col.Index +1)= col.HeaderText

对于每个行，作为dgvReport3.Rows中的DataGridViewRow
xlWorkSheet.Cells(rowa.Index + 5，col.Index + 1)= rowa.Cells(col.Index).Value

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

使用xlWorkSheet.Range("A4"，"Z4")
字体大小= 12
.Font.Bold = True
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
结尾为
下一个
下一个

昏暗的双倍

对于i = 0到dgvReport3.RowCount-1
tot + = dgvReport3.Rows(i).Cells(1).Value
下一个

Dim t As Integer = dgvReport3.Rows.Count

如果tot = 0则
使用xlWorkSheet.Range("B"& t + 6)
.Font.Bold = True
字体大小= 12
值= 0
r.EntireColumn.AutoFit()
结尾为
其他
使用xlWorkSheet.Range("B"& t + 6)
.Font.Bold = True
字体大小= 12
.Value = tot
r.EntireColumn.AutoFit()
结尾为
如果结束

使用xlWorkSheet.Range("A"& t + 6)
.Value ="SUM"
.Font.Bold = True
字体大小= 12
r.EntireColumn.AutoFit()
结尾为

昏暗的tot1为Double

对于d As Integer = 0到dgvReport3.RowCount-1
tot1 + = dgvReport3.Rows(d).Cells(2).Value
下一个



Dim t1 As Integer = dgvReport3.Rows.Count
如果tot1 = 0则
使用xlWorkSheet.Range("C"& t1 + 6)
.Font.Bold = True
字体大小= 12
值= 0
r.EntireColumn.AutoFit()
结尾为
其他
使用xlWorkSheet.Range("C"& t1 + 6)
.Font.Bold = True
字体大小= 12
.Value = tot1
r.EntireColumn.AutoFit()
结尾为
如果结束

Dim tot2为Double

对于q As整数= 0到dgvReport3.RowCount-1
tot2 + = dgvReport3.Rows(q).Cells(3).Value
关于错误继续下一个
下一个

Dim t2 As Integer = dgvReport3.Rows.Count

如果tot2 = 0则
使用xlWorkSheet.Range("D"& t1 + 6)
.Font.Bold = True
字体大小= 12
值= 0
r.EntireColumn.AutoFit()
结尾为
其他
使用xlWorkSheet.Range("D"& t2 + 6)
.Font.Bold = True
字体大小= 12
.Value = tot2
r.EntireColumn.AutoFit()
结尾为
如果结束
结束Sub

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

这篇关于数据从网格到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！