Excel和DataGridView数据的相互导入与导出
来源:互联网 发布:unity3d 绑定脚本 编辑:程序博客网 时间:2024/06/05 04:09
这是一个简单的把Excel和DataGridView数据的相互导入与导出的实例,很详细,已经在vs2010中测试通过的。Imports Microsoft.Office.Interop.ExcelImports System.Collections.GenericImports System.DiagnosticsImports Microsoft.Office.Interop '把Excel数据导入到DataGridView里 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1.Click Dim excel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application() Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim fileDialog As OpenFileDialog = New OpenFileDialog() Dim FileName As String fileDialog.Filter = "Microsoft Excel files (*.xls)|*.xls" If fileDialog.ShowDialog = System.Windows.Forms.DialogResult.Cancel Then Exit Sub If fileDialog.FileName = Nothing Then MsgBox("请选择要导入的excel文件", , "提示") End If FileName = fileDialog.FileName xlBook = excel.Application.Workbooks.Open(FileName) xlSheet = xlBook.Application.Worksheets(1) Dim col As Integer = 0 Dim i As Integer = 2 Me.dgv.Rows.Clear() Do While i < 3 dgv.Rows.Add(i) dgv.Rows(col).Cells(0).Value = xlSheet.Cells(i, 1).value dgv.Rows(col).Cells(1).Value = xlSheet.Cells(i, 2).value dgv.Rows(col).Cells(2).Value = xlSheet.Cells(i, 3).value dgv.Rows(col).Cells(3).Value = xlSheet.Cells(i, 4).value dgv.Rows(col).Cells(4).Value = xlSheet.Cells(i, 5).value dgv.Rows(col).Cells(5).Value = xlSheet.Cells(i, 6).value i += 1 col += 1 Loop excel.ActiveWorkbook.Close(False) xlSheet = Nothing xlBook = Nothing excel = Nothing If dgv.Rows.Count >= 1 Then MessageBox.Show("导入成功") Else MessageBox.Show("导入失败") End If End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2.Click Dim saveExcel As SaveFileDialog saveExcel = New SaveFileDialog saveExcel.Filter = "Excel文件(.xls)|*.xls" Dim filename As String If saveExcel.ShowDialog = System.Windows.Forms.DialogResult.Cancel Then Exit Sub filename = saveExcel.FileName Dim excel As New Application excel = New Excel.Application excel.DisplayAlerts = False excel.Workbooks.Add(True) excel.Visible = False Dim i As Integer For i = 0 To dgv.Columns.Count - 1 excel.Cells(1, i + 1) = dgv.Columns(i).HeaderText Next '设置标题 Dim j As Integer For i = 0 To dgv.Rows.Count - 1 '填充数据 For j = 0 To dgv.Columns.Count - 1 excel.Cells(i + 2, j + 1) = dgv(j, i).Value Next Next excel.Workbooks(1).SaveCopyAs(filename) '保存 Me.Close() End Sub