VB.net datagridview转Excel,datatable转数组
VB.net datagridview转Excel,datatable转数组
VB.net datagridview转Excel,datatable转数组,简单说就是通过datatable转换成二维数组,然后导出数据到Excel文件,可以瞬间导出N多条数据;
用法1:传入一个datatable就能导出数据到Excel
用法2:
把datagridview 数据转换成datatable传入然后导出Excel
datagridview 用法如下:
sub dgvtoexcel()
Dim dt As New DataTable
dt = prt_dgv.DataSource
xl.datatable2excel(dt)
end sub
'-----------------------------------------------------------------------------------------
'------------------------------------------------------------------------------------------
''' <summary>
''' 导出Excel
''' </summary>
''' <param name="dt"> datagridview</param>
''' <remarks></remarks>
Public Sub datatable2excel(ByVal dt As DataTable)
'-----------------------------------------------------
'datatable转数组
Dim arr(dt.Rows.Count, dt.Columns.Count - 1)
Dim brr(dt.Columns.Count - 1)
Dim i As Integer = 0
Dim j As Integer = 0
For k As Integer = 0 To dt.Columns.Count - 1
brr(k) = dt.Columns(k).ColumnName
Next
While i < dt.Rows.Count
For j = 0 To dt.Columns.Count - 1
arr(i, j) = dt.Rows(i)(j)
Next
i += 1
End While
'-------------------------------------------------
Dim crr(dt.Rows.Count, 0)
For g As Integer = 1 To dt.Rows.Count
crr(g - 1, 0) = g
Next
'-------------------------------------------------
Dim xlapp As New Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
'-------------------------------------------------
xlapp.Application.Workbooks.Add(True)
xlapp.Visible = True
xlapp.Range("b1").Resize(1, dt.Columns.Count).Value = brr
xlapp.Range("b2").Resize(dt.Rows.Count, dt.Columns.Count).Value = arr
xlapp.Range("a1").Value = "序号"
xlapp.Range("a2:a" & dt.Rows.Count + 1).Value = crr
''--------------------------------------------------
xlbook = xlapp.ActiveWorkbook
xlsheet = xlbook.ActiveSheet
'xl表格样式
xlsheet.Cells.Borders.Color = Color.White
With xlsheet.UsedRange
.Borders.Color = Color.Black
.Font.Name = "微软雅黑"
.Font.Size = 11
.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter
End With
With xlsheet.Range("a1").Resize(1, dt.Columns.Count + 1)
.Interior.Color = Color.DarkGray
.Font.Size = 13
.Font.Name = "微软雅黑"
End With
xlsheet.Cells.EntireColumn.AutoFit()
xlsheet.Select()
'---------------------------------------------------
xlsheet = Nothing
xlbook = Nothing
xlapp = Nothing
End Sub