VB.NET EXCE 操作(打开)

最新推荐文章于 2023-02-16 11:19:12 发布

原创最新推荐文章于 2023-02-16 11:19:12 发布 · 1.1k 阅读

3 ·

CC 4.0 BY-SA版权

vb.net 专栏收录该内容

4 篇文章

订阅专栏

本文总结了如何使用VB.NET打开并操作Excel文件，包括添加引用、访问Excel对象以及设置数据格式的过程。适合需要在VB.NET中与Excel交互的开发者参考。

要求将数据显示在Excel表中，并且要设好数据格式。虽说如此，真正实现了自己的功能，还是费了不少时间。相信还是有很多与我相似的人需要实现这种功能。那就做个小结吧。不妥之处，请指正哦。

1.项目-添加引用–com—microsoft excel 11.0 object library

2在需要访问excel的过程中定义

    dim exapp as excel.application　 '定义excel应用程序
    dim exbook as excel.workbook ‘定义工作簿
    dim exsheet as excel.worksheet ‘定义工作表
   dim exrange as excel.range　　 '定义工作区域

3.有了上面的定义，基本上excel的操作就手到擒来了

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

        Dim xlApp As Object = Nothing
        Dim xlBooks As Excel.Workbooks = Nothing
        Dim xlBook As Excel.Workbook = Nothing
        Dim xlSheets As Excel.Sheets = Nothing
        Dim xlSheet As Excel.Worksheet = Nothing
        Dim myrange As Excel.Range = Nothing

        Dim strFilePath As String = "C:\Users\Dell\Desktop\数据可视化测试\统计2020.3.20.xlsx"
        xlApp = New Excel.Application()

        xlBooks = xlApp.Workbooks

        xlBook = xlBooks.Open(strFilePath)
        xlSheets = xlBook.Worksheets
        xlSheet = xlSheets("Sheet1") '这里是你要读入数据的那个sheet页 
        '第一行第一列 
        myrange = xlSheet.Cells(1, 1)
        ' Dim a As String = myrange.Value

        TextBox1.Text = myrange.Text '读取数据显示在TEXTBOX1中

    End Sub

再次导入代码不能用，按vs提示修改代码：


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
        Dim xlApp As Object = Nothing
        Dim xlBooks As Microsoft.Office.Interop.Excel.Workbooks = Nothing
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = Nothing
        Dim xlSheets As Microsoft.Office.Interop.Excel.Sheets = Nothing
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing
        Dim myrange As Microsoft.Office.Interop.Excel.Range = Nothing

        Dim strFilePath As String = "C:\Users\Dell\Desktop\数据可视化测试\统计2020.3.20.xlsx"
        xlApp = New Microsoft.Office.Interop.Excel.Application()

        xlBooks = xlApp.Workbooks

        xlBook = xlBooks.Open(strFilePath)
        xlSheets = xlBook.Worksheets
        xlSheet = xlSheets("Sheet1") '这里是你要读入数据的那个sheet页 
        '第一行第一列 
        myrange = xlSheet.Cells(1, 1)
        ' Dim a As String = myrange.Value

        TextBox1.Text = myrange.Text '读取数据显示在TEXTBOX1中



    End Sub
End Class