excel - 将 Visual Basic 文本输入导入 Excel 电子表格

Question

因此，我将其视觉基本方面全部覆盖了输入内容和所有由我编写的内容以及Microsoft.Office.Interop.Excel.Application从 Microsoft VB 参考中添加的内容。VB 没有错误，但我仍然无法弄清楚如何将输入放入oSheet.Cells(1, 2).Value = txtCustomer.Text输入中以转到 Excel。我没有看到任何将 Excel 电子表格添加到 VB 的选项。我尝试通过 Excel 开发人员模式导入 VB 代码，但它不允许我正确保存我的 VB 代码，所以我宁愿将 VB 保留在原处。这是VB代码

Public Class Form1

    Private Sub BtnCalc_click(sender As Object, e As EventArgs) Handles BtnCalc.Click

        Dim stringDate As String
        Dim house, roof, paver, drive As Integer
        Dim dblhouse, dblroof, dblpaver, dbldrive As Double
        Dim dbllocale As Double
        Dim tax As Integer
        Dim customer As String

        customer = txtCustomer.Text
        stringDate = TxtDate.Text
        Dim LstSize As String
        Dim dbltotal As Double
        Dim location As Integer

        '''house sizes

        If location = "1800" Then
            dbllocale = 1.0
        End If

        If location = "2400" Then
            dbllocale = 1.2
        End If

        If location = "3000" Then
            dbllocale = 1.65
        End If

        If location = "4000" Then
            dbllocale = 2.2
        End If

        ''checks for which parts of the house get power washed
        If ChkCredit.Checked = True Then
            tax = 1.07
        Else
            tax = 1
        End If

        ''check for house
        If Chkhouse.Checked = True Then
            house = 1
        Else
            house = 0
        End If

        ''check for roof
        If ChkRoof.Checked = True Then
            roof = 1
        Else
            roof = 0
        End If

        ''check for pavers/sidewalks
        If ChkPaver.Checked = True Then
            paver = 1
        Else
            paver = 0
        End If

        ''check for driveways/large patios
        If ChkDrive.Checked = True Then
            drive = 1
        Else
            drive = 0
        End If

        dblhouse = (house * 350.0) * (dbllocale)
        dblroof = (roof * 450.0) * (dbllocale)
        dblpaver = (paver * 125.0)
        dbldrive = (drive * 165.0)
        dbltotal = tax * ((dblhouse) + (dblroof) + (dblpaver) + (dbldrive))
        TxtTotal.Text = (dbltotal)
    End Sub

    Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LstSize.SelectedIndexChanged
        Location = LstSize.SelectedItem
    End Sub

    Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles BtnClose.Click
        Me.Close()
        'closes application
    End Sub

    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles BtnClear.Click
        txtCustomer.Text = ""
        Chkhouse.Checked = False
        ChkRoof.Checked = False
        ChkPaver.Checked = False
        ChkDrive.Checked = False
        TxtTotal.Text = ""
        ChkCredit.Checked = False
        'clears text boxes and total text
    End Sub

    Private Sub Label1_Click(sender As Object, e As EventArgs) Handles Label1.Click
    End Sub

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

        Try

            Dim oXL As Microsoft.Office.Interop.Excel.Application
            Dim oWB As Microsoft.Office.Interop.Excel.Workbook
            Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim oRng As Microsoft.Office.Interop.Excel.Range


            'On Error GoTo Err_Handler
            ' Start Excel and get Application object.
            oXL = CreateObject("Excel.Application")
            oXL.Visible = True

            ' Get a new workbook.
            oWB = oXL.Workbooks.Add
            oSheet = oWB.ActiveSheet

            ' Add cells by looping.
            Dim iRow As Integer = 1
            For iRow = 1 To 10
                oSheet.Cells(iRow, 1).Value = "Test" & iRow
            Next iRow

            '//Create an Array and then add these entries to an excel spreadsheet
            Dim a(10) As String
            For iRow = 1 To 10
                a(iRow) = "Array Entry" & iRow.ToString
            Next

            '//Add to sheet
            For iRow = 1 To 10
                oSheet.Cells(iRow, 2).Value = a(iRow)
            Next iRow

            '//Add from a textbox Control
            oSheet.Cells(1, 2).Value = txtCustomer.Text

            ' Make sure Excel is visible and give the user control
            ' of Microsoft Excel's lifetime.
            oXL.Visible = True
            oXL.UserControl = True

            ' Make sure you release object references.
            oRng = Nothing
            oSheet = Nothing
            oWB = Nothing
            oXL = Nothing
        Catch ex As Exception
            MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)

        End Try
    End Sub
End Class```

标签： excelvb.netms-office

Answer 1

请开启 Option Strict。这是一个两部分的过程。首先对于当前项目 - 在解决方案资源管理器中双击我的项目。选择左侧的编译。在 Option Strict 下拉列表中选择 ON。未来项目的第二个 - 转到工具菜单 -> 选项 -> 项目和解决方案 -> VB 默认值。在 Option Strict 下拉列表中选择 ON。这将使您避免在运行时出现错误。

您的大多数变量名称都很好，但为什么要调用 house sizelocation呢？怎么样houseSize？

除了我的评论，使用 aSelect Case这样代码就不必评估每个 If 语句。Select Case找到匹配项时停止。

CheckBox.Checked是一个布尔值。如果方法的属性返回 a Boolean，则无需使用= True。它不会给出错误，但会额外输入。

tax不能等于 1.07。1.07 是双精度，不能直接分配给整数。我已将税收更改为Decimal. 我已将所有内容更改为Doubles，Decimal因为这在使用金钱时会更好。文字数字后面的 D 告诉编译器这是一个小数。

还记得代数中乘法的结合属性吗？它适用于 vb.net，因此您可以去掉一些括号。

Private Sub BtnCalc_click(sender As Object, e As EventArgs) Handles BtnCalc.Click
    Dim house, roof, paver, drive As Integer
    Dim dblhouse, dblroof, dblpaver, dbldrive As Decimal
    Dim dbllocale As Decimal
    Dim tax As Decimal
    Dim dbltotal As Decimal
    Dim houseSize As Integer = CInt(LstSize.SelectedItem) '**EDIT**
    Dim customer = txtCustomer.Text
    Dim stringDate = TxtDate.Text
    'house sizes
    Select Case houseSize
        Case 1800
            dbllocale = 1D
        Case 2400
            dbllocale = 1.2D
        Case 3000
            dbllocale = 1.65D
        Case 4000
            dbllocale = 2.2D
        Case Else
            MessageBox.Show($"No matching location. Location = {houseSize}")
            Return
    End Select
    ''checks for which parts of the house get power washed
    If ChkCredit.Checked Then
        tax = 1.07D
    Else
        tax = 1
    End If
    ''check for house
    If Chkhouse.Checked Then
        house = 1
    End If
    ''check for roof
    If ChkRoof.Checked Then
        roof = 1
    End If
    ''check for pavers/sidewalks
    If ChkPaver.Checked Then
        paver = 1
    End If
    ''check for driveways/large patios
    If ChkDrive.Checked Then
        drive = 1
    End If

    dblhouse = house * 350 * dbllocale
    dblroof = roof * 450 * dbllocale
    dblpaver = paver * 125
    dbldrive = drive * 165
    dbltotal = tax * (dblhouse + dblroof + dblpaver + dbldrive)
    TxtTotal.Text = dbltotal.ToString
End Sub

这Location = LstSize.SelectedItem不会编译，因为 Location 没有声明。如果您想从列表框中获取值，请直接在Btn_click事件中执行。

至于完全不相关的 Excel 代码......在文件的顶部Imports Excel = Microsoft.Office.Interop.Excel这可以节省您的打字时间，因为您可以使用 Exce 而不是Microsoft blah blahblah

Private Sub TestExcel()
    Dim oXL As New Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet

    oXL.Visible = True
    ' Get a new workbook.
    oWB = oXL.Workbooks.Add
    'Add a sheet
    oSheet = CType(oWB.Worksheets.Add(), Excel.Worksheet)
    ' Add data by looping.
    For iRow = 1 To 10
        oSheet.Range("A" & iRow).Value = "Test" & iRow
    Next
    'Create an Array and then add these entries to an excel spreadsheet
    Dim a(9) As String 'an array in vb.net is declared with the upper bound in the parenthesis
    For iRow = 1 To 10
        a(iRow - 1) = "Array Entry" & iRow.ToString
    Next

    For iRow = 0 To 9 'indexes in arrays start at zero
        oSheet.Range("B" & (iRow + 1)).Value = a(iRow)
    Next

    'Add from a textbox Control                                                                                                      left, top, width, height
    Dim ExcelTextBox As Excel.Shape = oSheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 200, 100, 200, 50)
    ExcelTextBox.TextFrame.Characters.Text = "Insert TextBox in Excel"
End Sub