用于Excel扩展表的串行数据

I need help, I accepted a project with the thought that it should be straight forward, but now I am stuck. It’s a data logging program that needs to receive a serial string from a com port, process the string, then put that information into an Excel spread sheet. So far I have the serial port working using which is working.

我需要帮助，我接受了一个项目，认为它应该是直截了当的，但现在我被卡住了。它是一个数据日志程序，需要从com端口接收串行字符串，处理字符串，然后将这些信息放入Excel扩展表中。到目前为止，我有一个正在工作的串行端口。

Private Sub SerialPort_DataReceived(ByVal sender As Object, ByVal e As System.IO.Ports.SerialDataReceivedEventArgs) Handles serPort.DataReceived
        Dim str As String = serPort.ReadExisting()
        Invoke(myDelegate, str)
    End Sub

    Sub procString(input As String)
        bufString = input
        If InStr(bufString, "|") Then
            tempString.Add(bufString.Split(New Char() {ChrW(2)}))

        End If
    End Sub

Which brings me to my first question, how to best handle the incoming string. Currently I am splitting the string into a List (Of String), I am assuming that is the best method to handle the strings prior to sending to excel. Please correct me if I am wrong. Second question is how to best process this string, then put that information into Excel. I have tried using a timer to get the data into Excel, but it is not working

这就引出了我的第一个问题，如何最好地处理传入的字符串。目前我将字符串分割成一个(字符串)列表，我假设这是在发送到excel之前处理字符串的最佳方法。如果我说错了，请纠正我。第二个问题是如何最好地处理这个字符串，然后将这些信息放到Excel中。我尝试过使用计时器将数据导入Excel，但它不起作用

Private Sub Timer1_Tick(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Timer1.Tick


        UsedRange = xlWorkSheet.UsedRange
        RowRange = UsedRange.Rows

        Dim CurrentRow As Int32 = RowRange.Count + 1
        xlCells = xlWorkSheet.Cells


        xlCells(CurrentRow, 1) = tempString(0).ToString()  ' Start of text
        xlCells(CurrentRow, 2) = tempString(1).ToString()  ' Unit Address
        xlCells(CurrentRow, 3) = tempString(2).ToString()  ' Product SKU

I am sure there is an easier way of getting this data into Excel, I am just not sure how best to go about it. Any insight on how best to get this done would be greatly appreciated. The incoming string is a pipe delimited and has a STX and ETX.

我确信有一种更简单的方法可以将这些数据输入到Excel中，我只是不确定如何最好地处理它。对于如何最好地完成这一工作，我们将非常感激。传入的字符串是带分隔符的管道，具有STX和ETX。

1 个解决方案

#1

To answer the first question, what you are doing works. I assume you are trying to not lock up the serial port while you add the information to the excel file? If not, you could do it all on the same thread without doing all this. But it is good you are doing it this way.

要回答第一个问题，你在做什么是有效的。我猜您在将信息添加到excel文件时，正在尝试不锁住串行端口?如果不是，您可以在相同的线程上完成所有这些操作。但是你这样做是很好的。

I would like to suggest a couple things

我想提出几点建议

1: When you are changing an object in multiple threads, you have to worry about thread safety. To avoid issues with that, you should use a SyncLock. They are pretty easy to work with.

1:当您在多个线程中修改对象时，您必须考虑线程的安全性。为了避免这个问题，您应该使用一个SyncLock。它们很容易处理。

2: You seem to be splitting the string and putting it in an array, but when another comes in, are you overwriting or adding to the list? I cannot see from the code posted. I would suggest, since it might be possible that DataReceived could fire 2 or more times for only one Tick of your timer, you put the full string into the collection (ADD) and then in the timer, you parse it and remove it from the collection.

2:你似乎在分割字符串并将其放入数组中，但是当另一个字符串进来时，你是在重写还是添加到列表中呢?我看不出张贴的代码。我建议，由于DataReceived可能只会在你的计时器中触发2次或更多次，你将完整的字符串放入集合(添加)中，然后在计时器中，你将它解析并从集合中删除。

For example, the DataRecieved might do this:

例如，数据透视可以这样做:

SyncLock MyLock
    tempString.add(bufString)
End SyncLock

And your timer might do this:

你的计时器可以这样做:

SyncLock MyLock
    For Each s As String In tempString
        Dim sAry As String() = bufString.Split(New Char() {ChrW(2)})
        For i As Int16 = 1 To sAry.Length
            xlCells(CurrentRow, i).Value = sAry(i - 1).ToString()
        Next
    Next
    tempString.Clear()
End SyncLock

To answer the second question, your issue is likely because you are not setting the proper property in excel. While you are doing this:

要回答第二个问题，您的问题可能是因为您没有在excel中设置适当的属性。当你这样做的时候:

xlCells(CurrentRow, 1) = ?

You should be doing this:

你应该这样做:

xlCells(CurrentRow, 1).Value = ?

#1