VB.net 读取或写入 Excel

2017-06-03 08:55:06 2k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

vb.net ADO快速读取excel 一:写在之前其实我还在写erlang, vb什么的才和我没关系,要不是为了妹子,才不会看二:关于vb的看法感觉vb的资料非常乱,由于时间太久,而且有vb,vb.net的分别,需要花费更多的时间去筛选资料就比如查找读取excel,查找出大量..2000年多有的资料,简直惨..接口已经变过了.. 三:vb 基本的读取excel方法 Dim xlapp A 阅读详情



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

 

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

Imports Microsoft.Office.Interop 

Imports Microsoft.Office.Interop.Excel 

Public Class Form1 

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

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

...全文

VB.NET向Excel写入并保存数据原代码已经经过测试通过，有问题或者有更佳的代码，上传一起学习 Private Sub bttnToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnToExcel.Click 'DataGridView1导出到Excel If DataGridView1.Rows.Count = 阅读详情

c999000

VB 16,718 成员 110,520 内容

+ 关注 + 加入社区

切换为时间正序

那该多好啊！！！ 2018-09-07

打赏
举报

zjcat35 2018-08-30

打赏
举报

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

这是我导出的一个例子，可供你参考

zjcat35 2018-08-30

打赏
举报

使用npoi，不受是否安装office限制

nicholasvb 2018-08-29

打赏
举报

用npoi for.net很方便！

良朋 2018-08-29

打赏
举报

如果你用的是Office 2007,你需要装SP3.

智造引领未来 2018-08-28

打赏
举报

在引用里，添加了Microsoft Excel 16.0 Object Library 后可以运行

智造引领未来 2018-08-28

打赏
举报

新建了一个VB.net程序



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

报错如下：
错误 3 未定义类型“Excel.Application”。 C:\Users\GSZ\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb 7 25 WindowsApplication1
错误 4 未定义类型“Excel.Workbook”。 C:\Users\GSZ\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb 8 23 WindowsApplication1
错误 5 未定义类型“Excel.Worksheet”。 C:\Users\GSZ\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb 9 24 WindowsApplication1

××××
系统WIn7 64位旗舰版，VS2012，Office2013

VB.net 读取或写入 Excel

相关推荐