VB.NET向Excel写入并保存数据

原代码已经经过测试通过，有问题或者有更佳的代码，上传一起学习

Private Sub bttnToExcel_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles bttnToExcel.Click

'DataGridView1导出到Excel

If DataGridView1.Rows.Count = 0 Then

MsgBox("数据列表中没有数据！",MsgBoxStyle.OkOnly,"温馨提示！")

Exit Sub

Else

Dim strFName As String

strFName = "G:/Temperature/Temperature" & Format(Now,"yyyyMMddhhmmss") & ".xls" '" & Now.ToString("yyMMddhhmmss") & "

Dim xlsApp As New Excel.Application

Dim xlsBook As Excel.Workbook

Dim xlsSheet As Excel.Worksheet

Try

xlsApp = CreateObject("Excel.Application") '生成Excel

xlsBook = xlsApp.Workbooks.Add

xlsSheet = xlsBook.Worksheets(1)

xlsSheet.Columns.AutoFit() '自动换行

xlsSheet.Name = "温度统计"

Dim RowCount As Integer

Dim ColCount As Integer

Dim k As Integer

RowCount = DataGridView1.Rows.Count

ColCount = DataGridView1.Columns.Count

For k = 0 To ColCount - 1 'DataGridView所有列数

xlsSheet.Cells(1,k + 1) = DataGridView1.Columns(k).HeaderText

xlsSheet.Cells(1,k + 1).Font.Size = 12

xlsSheet.Cells(1,k + 1).Font.FontStyle = "加粗"

xlsSheet.Cells(1,k + 1).HorizontalAlignment = 1

Next

xlsSheet.Columns("E:E").ColumnWidth = 17.5 '设置单元格宽度

xlsSheet.Columns("D:D").ColumnWidth = 11.5

xlsSheet.Columns("C:C").ColumnWidth = 10.25

xlsSheet.Columns("B:B").ColumnWidth = 11.25

xlsSheet.Columns("A:A").ColumnWidth = 10.25

Dim i As Integer,j As Integer

For i = 0 To RowCount - 2 '向Excel填充数据

For j = 0 To ColCount - 1

xlsSheet.Cells(i + 2,j + 1) = IIf(IsDBNull(DataGridView1.Item(j,i).Value.ToString),DataGridView1.Item(j,i).Value.ToString)

Next

Next

MsgBox("生成" & RowCount & "数据已经成功导出到：" & strFName & "","温馨提示")

xlsBook.SaveAs(strFName) ‘保存上面生成excel

xlsSheet = Nothing '释放资源

xlsBook.Close() '关闭工作簿

xlsBook = Nothing '释放资源

xlsApp.Quit() '退出excel应用程序

xlsApp = Nothing

Catch ex As Exception

MsgBox(ex.Message)

End Try

End If

End Sub

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!