VB.net DataGridView到Excel

问题描述

我正在Microsoft Visual Studio 2017上使用VB.net来创建一个小应用程序，并且我用来导出Datagridview到excel的代码存在问题。它导出除我的数据的最后一行以外的所有内容。知道我该如何解决吗？

I’m using VB.net on Microsoft visual studio 2017, to create a little App and I’m having a problem with the code that I’m using to export my Datagridview to excel. It exports everything but the last row of my data. Any idea how I can fix this?

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports Microsoft.Office.Interop
Imports System.IO

Private Sub ExportToExcel()
        ' Creating a Excel object.
        Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
        Dim workbook As Microsoft.Office.Interop.Excel._Workbook = excel.Workbooks.Add(Type.Missing)
        Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing

        Try

            worksheet = workbook.ActiveSheet

            worksheet.Name = "ExportedFromDatGrid"

            Dim cellRowIndex As Integer = 1
            Dim cellColumnIndex As Integer = 1

            'Write headers
            For j As Integer = 0 To DataGridView_Kontakte.Columns.Count - 2
                worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView_Kontakte.Columns(j).HeaderText
                cellColumnIndex += 1
            Next
            cellColumnIndex = 1
            cellRowIndex += 1

            'Loop through each row and read value from each column.
            For i As Integer = 0 To DataGridView_Kontakte.Rows.Count - 2
                For j As Integer = 0 To DataGridView_Kontakte.Columns.Count - 1
                    ' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                    worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView_Kontakte.Rows(i).Cells(j).Value.ToString()
                    cellColumnIndex += 1
                Next
                cellColumnIndex = 1
                cellRowIndex += 1
            Next

            'Getting the location and file name of the excel to save from user.
            Dim saveDialog As New SaveFileDialog()
            saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
            saveDialog.FilterIndex = 2

            If saveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                workbook.SaveAs(saveDialog.FileName)
                MessageBox.Show("Export Successful")
            End If
        Catch ex As System.Exception
            MessageBox.Show(ex.Message)
        Finally
            excel.Quit()
            workbook = Nothing
            excel = Nothing
        End Try

    End Sub

推荐答案

您可以尝试在EXCEL中导出我的函数

You can try my function to export in EXCEL

Sub ExportExcel(ByVal obj As Object)
    Dim rowsTotal, colsTotal As Short
    Dim I, j, iC As Short
    System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
    Dim xlApp As New Excel.Application
    Try
        Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
        Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
        xlApp.Visible = True

        rowsTotal = obj.RowCount
        colsTotal = obj.Columns.Count - 1
        With excelWorksheet
            .Cells.Select()
            .Cells.Delete()
            For iC = 0 To colsTotal
                .Cells(1, iC + 1).Value = obj.Columns(iC).HeaderText
            Next
            For I = 0 To rowsTotal - 1
                For j = 0 To colsTotal
                    .Cells(I + 2, j + 1).value = obj.Rows(I).Cells(j).Value
                Next j
            Next I
            .Rows("1:1").Font.FontStyle = "Bold"
            .Rows("1:1").Font.Size = 12

            .Cells.Columns.AutoFit()
            .Cells.Select()
            .Cells.EntireColumn.AutoFit()
            .Cells(1, 1).Select()
        End With
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Finally
        'RELEASE ALLOACTED RESOURCES
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
        xlApp = Nothing
    End Try
End Sub

这篇关于VB.net DataGridView到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！