DataGridView导出数据到Excel

最新推荐文章于 2023-02-09 16:37:14 发布

zhanghongqiao

最新推荐文章于 2023-02-09 16:37:14 发布

阅读量509

点赞数

CC 4.0 BY-SA版权

文章标签： integer exception excel

本文链接：https://blog.youkuaiyun.com/zhanghongqiao/article/details/2686762

本文介绍了一种使用Visual Basic .NET将DataGridView控件中的数据导出到Excel文件的方法。该方法适用于处理大量数据的情况，能够自动将表格数据及其标题转换为Excel格式，并确保数据的完整性和准确性。

Public Shared Sub ExportToExcel(ByVal dgv As System.Windows.Forms.DataGridView)
        Try
            Dim oApp As New Excel.Application
            Dim oBook As Excel.Workbook = oApp.Workbooks.Add
            Dim oSheet As Excel.Worksheet = CType(oBook.Worksheets(1), Excel.Worksheet)
            oApp.Visible = False
            With oSheet
                'ASC码循环列
                Dim columnNamelastLetter As Integer = Asc("A")
                Dim columnNameFirstLetter As Integer = Asc("A") - 1
                Dim loopcolumn As Integer
                For loopcolumn = 0 To dgv.Columns.Count - 1 Step 1
                    If dgv.Columns(loopcolumn).Visible = True Then
                        If columnNamelastLetter < 91 Then
                            '列在A-Z之间
                            Dim looprow As Integer
                            oSheet.Range(Chr(columnNamelastLetter) & "1").Value = dgv.Columns(loopcolumn).HeaderText
                            For looprow = 0 To dgv.Rows.Count - 1 Step 1
                                If Not dgv.Rows(looprow).Cells(loopcolumn).Value Is Nothing Then
                                    oSheet.Range(Chr(columnNamelastLetter) & CStr(looprow + 2)).Value = "'" + dgv.Rows(looprow).Cells(loopcolumn).Value.ToString
                                End If
                            Next
                        Else
                            '列不在A-Z之间
                            columnNameFirstLetter = Asc("A") - 1 + Int(columnNamelastLetter / 90)
                            Dim tempcolumnlastLetter As Integer = Asc("A") - 1 + (columnNamelastLetter Mod 90)
                            Dim looprow As Integer
                            oSheet.Range(Chr(columnNameFirstLetter) & Chr(tempcolumnlastLetter) & "1").Value = dgv.Columns(loopcolumn).HeaderText
                            For looprow = 0 To dgv.Rows.Count - 1 Step 1
                                If Not dgv.Rows(looprow).Cells(loopcolumn).Value Is Nothing Then
                                    oSheet.Range(Chr(columnNameFirstLetter) & Chr(tempcolumnlastLetter) & CStr(looprow + 2)).Value = "'" + dgv.Rows(looprow).Cells(loopcolumn).Value.ToString
                                End If
                            Next
                        End If
                        columnNamelastLetter = columnNamelastLetter + 1
                    End If
                Next
                oApp.Visible = True
            End With
        Catch ex As Exception

            System.Windows.Forms.MessageBox.Show("Source [" & ex.Source & "] Description [" & ex.Message & "]")
        End Try
    End Sub