datagridview导入导出excel

 

导入：

    Private Sub ImportExcelData()

        Dim fileName As String = String.Empty

        Dim dataNo As Integer = 0

        Dim dsTempItemCD As New DataSet

        Dim dsTempCompLocationNAME As New DataSet       

        Dim strConn As String = String.Empty

        Dim conn As OleDbConnection

        'Excel信息表

        Dim tblSchema As New DataTable

        'sheet1名

        Dim tbName As String = String.Empty

        '填充 data 和更新data数据源

        Dim oledbadp As OleDbDataAdapter

        '获取Excel中sheet的内容

        Dim data As New DataTable

 

        Try

            OpenFileDialog1.Title = "请选择您要的文件!"

            OpenFileDialog1.Filter = "Excel 2003 files (*.xls)|*.xls|Excel 2007 files (*.xlsx)|*.xlsx"

            If OpenFileDialog1.ShowDialog = DialogResult.OK Then

                fileName = OpenFileDialog1.FileName

            Else

                Exit Sub

            End If

            If OpenFileDialog1.SafeFileName.EndsWith("xls") Then

                strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " +

                           "Data Source=" + fileName + "; " +

                           "Extended Properties='Excel 8.0;IMEX=1'"

            ElseIf OpenFileDialog1.SafeFileName.EndsWith("xlsx") Then

                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +

                          "Data Source=" + fileName + ";" +

                          "Extended Properties=""Excel 12.0;HDR=YES"""

            Else

                MsgBox(Msg_ImportstFile, 1)

                Exit Sub

            End If

            conn = New OleDbConnection(strConn)

            '打开

            conn.Open()

            'Excel信息表

            tblSchema = conn.GetSchema("Tables")

            If (tblSchema.Rows.Count > 0) Then

                'sheet1名

                tbName = tblSchema.Rows(0)("TABLE_NAME").ToString()

                '填充 data 和更新data数据源

                oledbadp = New OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", tbName), conn)

                data = New DataTable(tbName)

                oledbadp.Fill(data)

                conn.Close()

            End If

            grdData.RowCount = data.Rows.Count

            With grdData               

                For i As Integer = 0 To data.Rows.Count - 1                   

                    For j As Integer = 0 To data.Columns.Count - 1

                        .Rows(i).Cells(j).Value =data.Rows(i).Item(j).ToString

                    Next 

                Next

            End With

        Catch ex As Exception

           

        End Try

    End Sub

导出：

Public Shared Sub ExportToExcel(ByVal strFilePath As String, ByVal gdv As DataGridView)

        Dim oExcel As New Excel.Application

        oExcel.UserControl = False

        Dim wb As Excel.Workbook = CType(oExcel.Workbooks.Add(System.Reflection.Missing.Value), Excel.Workbook)

        Dim range As Interop.Excel.Range

        range = Nothing

        Dim totalCount As Integer

        totalCount = gdv.RowCount

        Dim rowRead As Integer

        rowRead = 0

        Dim i As Integer

        '标题

        For i = 0 To gdv.ColumnCount - 1

            oExcel.Cells(1, i + 1) = gdv.Columns(i).HeaderText

            range = oExcel.Cells(1, i + 1)

            '背景色

            range.Interior.Color = Color.LightGreen 'gdv.Columns(i).HeaderCell.Style.BackColor

            '居中

            range.HorizontalAlignment = XlHAlign.xlHAlignCenter

            '加边框

            range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic)

            '自动设列宽

            range.EntireColumn.AutoFit()

            '自动设行高

            range.EntireRow.AutoFit()          

        Next

        '內容

        Dim j As Integer

        For j = 0 To gdv.Rows.Count - 1

            For i = 0 To gdv.Columns.Count - 1

                oExcel.Cells(j + 2, i + 1) = gdv.Rows(j).Cells(i).Value

                range = oExcel.Cells(j + 2, i + 1)

                '列宽自动调整

                range.EntireColumn.AutoFit()

            Next           

        Next

        For i = 0 To gdv.ColumnCount - 1

            If gdv.Columns(i).Visible = False Then

                oExcel.Columns(i + 1).Hidden = True

            End If

        Next

        wb.Saved = True

        oExcel.ActiveWorkbook.SaveCopyAs(strFilePath)

        oExcel.Quit()

        System.GC.Collect()

    End Sub