用VB.net打开Excel表格

[LauJames]

Option Explicit On Imports Microsoft.Office.Interop Public Class Form1 Dim excelApp As Excel.Application '应用程序 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click excelApp = New Excel.Application Button1.Enabled = False Button1.Text = "正在处理..." Dim excelWBS As Excel.Workbook '源工作簿 Dim excelWBT As Excel.Workbook '目标工作簿 Dim excelSheetS As Excel.Worksheet '源工作表 Dim excelSheetT As Excel.Worksheet '目标工作表 Dim excelRangS, excelRangT As Excel.Range Dim strFileS, strFileT As String '源文件和目标文件的路径 Dim SheetNameS, SheetNameT As String '源Sheet名和目标Sheet名 Dim i As Integer '******************处理“整体沉降”数据 strFileS = Application.StartupPath & "‪F:\打开\打开Excel\data\成果数据.xls" strFileT = Application.StartupPath & "‪F:\打开\打开Excel\data\整体沉降(计算).xls" 'Step1：生成新的数据计算表格（根据前一次的数据表格，复制后改名生成） Me.Visible = True excelWBS = excelApp.Workbooks.Open(strFileS) '打开观测数据模板 excelWBT = excelApp.Workbooks.Open(strFileT) '打开数据处理模板 For i = 1 To 5 excelSheetS = excelWBS.Sheets(i) '获得要复制的表格 excelSheetS.Copy(Before:=excelWBT.Sheets(i)) SheetNameS = excelSheetS.Name excelSheetT = excelWBT.ActiveSheet '获得复制后的表格 SheetNameT = excelWBS.Sheets(i).Name '获得目标表格名 excelSheetT.Name = SheetNameT '更改复制后的表格名 Next 'Step2：导入观测数据 SheetNameS = SheetNameT excelSheetS = excelWBS.Sheets(SheetNameS) '获得最新观测数据表格 '复制粘贴测点高程数据 excelWBS.Sheets("3.7").Range("C3:C11").Copy(excelWBT).Sheets("累计变化量").Range("B3:B11") excelWBS.Sheets("3.8").Range("C3:C11").Copy(excelWBT).Sheets("累计变化量").Range("C3:C11") excelWBS.Sheets("3.16").Range("C3:C11").Copy(excelWBT).Sheets("累计变化量").Range("D3:D11") excelWBS.Sheets("3.17").Range("C3:C11").Copy(excelWBT).Sheets("累计变化量").Range("E3:E11") excelWBS.Sheets("3.26").Range("C3:C11").Copy(excelWBT).Sheets("累计变化量").Range("F3:F11") 'Step3：处理计算结果 Dim DeltValue, tempValue, sulvValue As Double, days As Integer, date1 As Date, date2 As Date Dim intI, intJ As Integer For intJ = 1 To 4 For intI = 1 To 9 DeltValue = excelWBT.Sheets("累计变化量").Cells(2 + intI, 2 + intJ).Value - excelWBT.Sheets("累计变化量").Cells(2 + intI, 1 + intJ).Value excelWBT.Sheets("累计变化量").Cells(2 + intI, 12 + intJ).Value = DeltValue * 1000 excelWBT.Sheets("累计变化量").Cells(2 + intI, 12).Value = 0 '计算隔日沉降量 tempValue = excelWBT.Sheets("累计变化量").Cells(2 + intI, 2 + intJ).Value - excelWBT.Sheets("累计变化量").Cells(2 + intI, 2).Value excelWBT.Sheets("累计变化量").Cells(15 + intI, 2 + intJ).Value = tempValue * 1000 excelWBT.Sheets("累计变化量").Cells(15 + intI, 2).Value = 0 '计算累计沉降量 date1 = excelWBT.Sheets("累计变化量").Cells(2, 2 + intJ).Value date2 = excelWBT.Sheets("累计变化量").Cells(2, 1 + intJ).Value days = DateDiff("d", date1, date2) excelWBT.Sheets("累计变化量").Cells(15 + intI, 12 + intJ).Value = DeltValue * 1000 / Day(days) excelWBT.Sheets("累计变化量").Cells(15 + intI, 12).Value = 0 Next Next excelRangT = excelWBT.Sheets("累计变化量").Range(excelWBT.Sheets("累计变化量").Cells(2, 11), excelWBT.Sheets("累计变化量").Cells(11, 16)) excelWBT.Sheets("累计变化曲线图").ChartObjects(1).Chart.SetSourceData(excelRangT) excelRangT = excelWBT.Sheets("累计变化量").Range(excelWBT.Sheets("累计变化量").Cells(15, 1), excelWBT.Sheets("累计变化量").Cells(24, 6)) excelWBT.Sheets("累计变化曲线图").ChartObjects(2).Chart.SetSourceData(excelRangT) excelRangT = excelWBT.Sheets("累计变化量").Range(excelWBT.Sheets("累计变化量").Cells(15, 11), excelWBT.Sheets("累计变化量").Cells(24, 16)) excelWBT.Sheets("累计变化曲线图").ChartObjects(3).Chart.SetSourceData(excelRangT) excelWBT.Close() MsgBox("处理结束!") Button1.Enabled = True Button1.Text = "处理" excelApp.Quit() excelApp = Nothing End Sub Private Function Day(days As Integer) As Double Throw New NotImplementedException End Function End Class

[sanlide]

路径不对，staruppath 是程序路径，后面还要跟F：\就不对 等于c：\111 F：\222

[LauJames]

引用 1 楼 sanlide的回复:

路径不对，staruppath 是程序路径，后面还要跟F：\就不对 等于c：\111 F：\222

大神能告诉我一下改成什么样吗 谢谢 我的毕业设计我是真的没学过这个

[华芸智森]

        Dim xlApp As Excel.Application = Nothing
        Dim xlBook As Excel.Workbook
        Try
            xlApp = New Excel.Application With {.Visible = False}
            xlBook = CType(xlApp, Excel.Application).Workbooks.Open("E:\test.xlsx")
            xlApp.Visible = True
        Catch ex As Exception
            '//
        End Try

[华芸智森]

Dim xlApp As Excel.Application = Nothing Dim xlBook As Excel.Workbook = Nothing Try xlApp = New Excel.Application With {.Visible = False} xlBook = xlApp.Workbooks.Open("E:\test.xlsx") xlApp.Visible = True Catch ex As Exception '// End Try