时间戳

首页 > 解决方案 > 为什么我必须在这里转换“单元格”？

excel - 为什么我必须在这里转换“单元格”？

问题描述

简短的关注：我注意到我必须在CType此处转换 using，即使Cells已经返回Range. 我究竟做错了什么？Cells是只读的，不带任何参数。好吧，我曾经被这样展示过。

如果我这样做

xlRange = myWorksheet.Cells(Line + 1US, 2US)

错误是

Option Strict On 禁止从“对象”到“范围”的隐式转换

我想提一下——如果是因为这个原因——我将 Option Infer Off 关闭，这样我就强迫自己总是写下类型。

源代码应在 Excel 表中写入一段时间内的测量值。

例子：

    A          B        C
1  07.09.2021  08:00    25,0
2              12:00    30,0
3              16:00    35,0
4  08.09.2021  08:00    26,0
5              12:00    28,0
6              16:00    26,0

类范围的变量：

Private Time_of_the_program_start As Date
Private Duration_of_the_runtime As TimeSpan
Private xlApp As Excel.Application
Private myWorkbook As Excel.Workbook
Private running As Boolean = False
Private Line As UInt16 = 0US
Private ReadOnly Deu As New System.Globalization.CultureInfo("de-DE")

running在按钮过程中设置为 True。

Private Function read_and_write_data(ByVal file_path As String) As Boolean
        xlApp = New Excel.Application With {
            .Visible = True
        }
        myWorkbook = xlApp.Workbooks.Add()
        Dim myWorksheet As Excel.Worksheet = CType(myWorkbook.Sheets("Tabelle1"), Excel.Worksheet)
        Dim xlRange As Excel.Range
        Dim Temperatur As Single

        While running
            Duration_of_the_runtime = Date.Now - Time_of_the_program_start
            If Duration_of_the_runtime.Days = 365 Then Exit While

            Me.Invoke(Sub() Label_Duration.Text =
                          Duration_of_the_runtime.Days.ToString(Deu).PadLeft(3, "0"c) & ":" &
                          Duration_of_the_runtime.Hours.ToString(Deu).PadLeft(2, "0"c) & ":" &
                          Duration_of_the_runtime.Minutes.ToString(Deu).PadLeft(2, "0"c))

            'System.Threading.Thread.Sleep(10000)
            'myWorksheet.Cells(Line, column)
            '–––––––––––––––––––––––––––––––––––––––
            'Spalte A: Datum
            '–––––––––––––––––––––––––––––––––––––––
            xlRange = CType(myWorksheet.Cells(Line + 1US, 1US), Excel.Range)
            xlRange.Value = Date.Now.ToString("d", Deu)
            '–––––––––––––––––––––––––––––––––––––––
            'Spalte B: Uhrzeiten
            'Spalte C: Messwerte
            '–––––––––––––––––––––––––––––––––––––––
            Temperatur = Get_Temperature_from_Pt100()

            xlRange = CType(myWorksheet.Cells(Line + 1US, 2US), Excel.Range)
            xlRange.Value = Date.Now.ToString("t", Deu)

            xlRange = CType(myWorksheet.Cells(Line + 1US, 3US), Excel.Range)
            xlRange.Value = Temperatur
            '‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
            Temperatur = Get_Temperature_from_Pt100()
            xlRange = CType(myWorksheet.Cells(Line + 2US, 2US), Excel.Range)
            xlRange.Value = Date.Now.ToString("t", Deu)

            xlRange = CType(myWorksheet.Cells(Line + 2US, 3US), Excel.Range)
            xlRange.Value = Temperatur
            '‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
            Temperatur = Get_Temperature_from_Pt100()
            xlRange = CType(myWorksheet.Cells(Line + 3US, 2US), Excel.Range)
            xlRange.Value = Date.Now.ToString("t", Deu)

            xlRange = CType(myWorksheet.Cells(Line + 3US, 3US), Excel.Range)
            xlRange.Value = Temperatur
            '–––––––––––––––––––––––––––––––––––––––
            'inkrementieren
            '–––––––––––––––––––––––––––––––––––––––
            Line += 3US
        End While

        myWorksheet.SaveAs(file_path, Excel.XlFileFormat.xlWorkbookDefault)
        myWorkbook.Close()
        xlApp.Quit()
        If myWorksheet IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(myWorksheet)
        If myWorkbook IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(myWorkbook)
        If xlApp IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
        xlApp = Nothing
        myWorkbook = Nothing
        myWorksheet = Nothing

        Return True
    End Function

顺便说一句：有没有更优雅的解决方案？

Me.Invoke(Sub() Label_Duration.Text =
                          Duration_of_the_runtime.Days.ToString(Deu).PadLeft(3, "0"c) & ":" &
                          Duration_of_the_runtime.Hours.ToString(Deu).PadLeft(2, "0"c) & ":" &
                          Duration_of_the_runtime.Minutes.ToString(Deu).PadLeft(2, "0"c))

标签： excelvb.net

解决方案

对于您的主要问题，不，您没有做错任何事情，这就是 Excel 自动化界面的行为方式。具体来说，集合类型都是经典的 VBA Collection（或功能等价物），这意味着索引访问返回的是一个Object而不是强类型的东西。

正如您所指出的，如果您尝试在Option Strict On上下文中使用它，您将不得不转换结果。我自己的具有大量集合项访问权限的互操作代码中充斥着诸如DirectCast(.Rows(2), Excel.Range)...

推荐阅读