VB.net中DataGrid导出为Excel文件函数

Public Function ExportXLsD(ByVal datagrid As DataGrid) ',ByVal Title As String)
        'Dim Mytable As New DataTable
        'Mytable = CType(datagrid.DataSource,DataTable)
        If mytable Is Nothing Then
            MessageBox.Show("没有记录不能导出数据","PurpleStar",MessageBoxButtons.OK,MessageBoxIcon.Information)
            Exit Function
        End If
        If mytable.Rows.Count > 0 Then
            Dim MyFileName As String
            Dim FileName As String
            With SaveFileDialog1
                .AddExtension = True '如果用户忘记添加扩展名，将自动家上
                .DefaultExt = "xls" '默认扩展名
                .Filter = "Excel文件(*.xls)|*.xls"
                .Title = "文件保存到"
                If .ShowDialog = DialogResult.OK Then
                    FileName = .FileName
                End If
            End With
            MyFileName = Microsoft.VisualBasic.Right(FileName,4)
            If MyFileName = "" Then
                Exit Function
            End If
            If MyFileName = ".xls" Or MyFileName = ".XLS" Then
                Dim FS As FileStream = New FileStream(FileName,FileMode.Create)
                Dim sw As StreamWriter = New StreamWriter(FS,System.Text.Encoding.Default)
                sw.WriteLine(vbTab & FileName & vbTab & Date.Now)
                Dim i,j As Integer
                Dim str As String = ""
                For i = 0 To mytable.Columns.Count - 1
                    str = mytable.Columns(i).Caption
                    sw.Write(str & vbTab)
                Next
                sw.Write(vbCrLf)
                For j = 0 To mytable.Rows.Count - 1
                    For i = 0 To mytable.Columns.Count - 1
                        Dim strColName,strRow As String
                        strRow = IIf(mytable.Rows(j).Item(i) Is DBNull.Value,"",mytable.Rows(j).Item(i))
                        sw.Write(strRow & vbTab)
                    Next
                    sw.Write(vbLf)
                Next
                sw.Close()
                FS.Close()
                MessageBox.Show("数据导出成功！",MessageBoxIcon.Information)
            Else
                Exit Function
            End If
        Else
            MessageBox.Show("没有记录不能导出数据",MessageBoxIcon.Information)
        End If
    End Function
 
 
Private Sub OK_Button_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles OK_Button.Click
        Dim saveExcel As SaveFileDialog
        saveExcel = New SaveFileDialog
        saveExcel.Filter = "Excel文件(.xls)|*.xls"
        Dim filename As String
        If saveExcel.ShowDialog = Windows.Forms.DialogResult.Cancel Then Exit Sub
        filename = saveExcel.FileName

        Dim excel As Excel.Application
        excel = New Excel.Application

        excel.DisplayAlerts = False
        excel.Workbooks.Add(True)
        excel.Visible = False
        Dim i As Integer
        For i = 0 To DataGridView1.Columns.Count - 1
            excel.Cells(1,i + 1) = DataGridView1.Columns(i).HeaderText
        Next
        '设置标题
        Dim j As Integer
        For i = 0 To DataGridView1.Rows.Count - 1 '填充数据
            For j = 0 To DataGridView1.Columns.Count - 1
                excel.Cells(i + 2,j + 1) = DataGridView1(j,i).Value
            Next
        Next
        excel.Workbooks(1).SaveCopyAs(filename) '保存
        Me.Close()
    End Sub

（编辑：李大同）

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