Imports Microsoft.Office.Interop

Imports Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        '根据电脑已安装office版本，先引用 

        '下面的代码为VB.net 读取或写入 Excel 

        Dim ExcelApp As Excel.Application '定义Excel的Application 

        Dim wkBook As Excel.Workbook '定义Excel的Application 

        Dim wkSheet As Excel.Worksheet

        On Error Resume Next

        ExcelApp = GetObject(, "Excel.application") '"Excel.application" 

        wkBook = ExcelApp.ActiveWorkbook '"workbooks" 

        wkSheet = wkBook.ActiveSheet

        If ExcelApp.Workbooks.Count = 0 Then '注：如果新建未保存的工作簿不算打开 

            MsgBox("没有工作簿打开")

            'Exit Sub 

            ExcelApp = CreateObject("Excel.application")

            wkBook = ExcelApp.Workbooks.Add

            wkSheet = wkBook.Worksheets.Add

            ExcelApp.Visible = True

        End If



        With wkSheet

            For i = 1 To 10

                For j = 1 To 10

                    .Cells(i, j) = i

                Next j

            Next i

            .Range("A1").Value = "Excel 表格控制，好玩吗"

            .Cells(6, 6).Value = "Excel 表格控制"

        End With

        'ExcelSheet.SaveAs("F:\test.xlsx") 

        ExcelApp.ScreenUpdating = True

        wkSheet = Nothing

        wkBook = Nothing

        ExcelApp = Nothing

    End Sub

End Class

Imports System.IO

Imports NPOI.HSSF.UserModel

Imports NPOI.SS.UserModel

Imports System.Windows.Forms



Public Class DataToExcel

    Public Shared Sub ExportXLs(ByVal data As DataGridView)  '导出数据至excel

        Dim MyfilePath As String       '定义保存路径变量

        Dim saveFileDialog As New SaveFileDialog     '另存为对话框

        If data.Rows.Count = 0 Then                  '判断窗口是否有可导出数据

            MsgBox("无可导数据！")

            'Return 0

            Exit Sub

        Else

            With saveFileDialog

                .DefaultExt = "*.xls"

                .Filter = "Excel 97-2003 工作簿(*.xls)|*.xls"

                .FilterIndex = 1

                .FileName = "新建文件夹"

                If .ShowDialog() = DialogResult.OK Then

                    MyfilePath = .FileName                                              '赋值文件保存路径

                Else

                    'Return 0

                    Exit Sub

                End If

            End With

        End If

        Try

            Dim workbook As HSSFWorkbook = New HSSFWorkbook()                   '声明工作簿变量

            Dim sht As HSSFSheet = workbook.CreateSheet("Sheet1")               '声明工作表变量

            With data

                Dim row As HSSFRow = sht.CreateRow(0)

                For j = 0 To .Columns.Count - 1

                    row.CreateCell(j).SetCellValue(.Columns(j).HeaderText.ToString)    '将Datagridview表头写入工作表

                Next

                For i As Integer = 1 To .Rows.Count                                 '将datagridview的数据一行行的写入工作表

                    row = sht.CreateRow(i)

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

                        If IsNothing(data(j, i - 1).Value) = False Then

                            row.CreateCell(j).SetCellValue(data(j, i - 1).Value.ToString)

                        End If

                    Next j

                Next i

            End With

            Dim fs As FileStream = New FileStream(MyfilePath, FileMode.Create)

            workbook.Write(fs)

            fs.Close()

            workbook.Close()

            MsgBox("保存成功")

        Catch Exc As Exception

            MsgBox(Exc.Message)

        End Try

    End Sub