VB writing excel & graph problem

Dear all

I am using arduino and ACS712 sensor . I am taking reading serially from Sensor and dispaly on VB and trying to plot graph and excel sheet for record. CUrrently i am facing below 2 problems.

1) averaging sample for every1s
2) writing into excel sheet every 1 min including current and watthour.

I am pasting my code here. I am facing problem in writing into Excel sheet
I also can you check my this part of code. I see my curve get deflection high low instead of cumulative curve.

Code:

---

If Avg_count = 1 Then
                Power = (distance * voltage) / 3600
                watt_hour = watt_hour + Power
                'Twatt.Text = watt_hour
                Display.Text = watt_hour
                voltage_text.Text = 24

                System.Threading.Thread.Sleep(250)
                If watt_hour > 1000 Then
                    watt_hour = 1.0 + watt_hour
                End If

                Avg_count = Avg_count + 1

            End If
            If Avg_count > 1 Then
                Avg_count = 0
            End If

---

if i use this line i get excel file written improperly

Code:

---

If Not IO.File.Exists((csvFile)) Then
                   
                    headerText = "Date,TIME ,current,Watt "
                End If

                Using outFile = My.Computer.FileSystem.OpenTextFileWriter(csvFile, True)
                    If headerText.Length > 0 Then
                        outFile.WriteLine(headerText)
                    End If
                   
                    Dim date1 As String = "24-10-2014"
                    Dim time1 As String = TimeOfDay()

                    Dim watt1 As String = CStr(watt_hour)
                    Dim x As String = date1 + "," + time1 + "," + distance + "," + watt1
                    outFile.Write(x)
                End Using
            End If

---

Attachment 119049

If i replace with below. In excel sheet it write date,time,current properly .

Code:

---

If Not IO.File.Exists((csvFile)) Then
                    'headerText = "Date,TIME ,current, "
                              End If

                Using outFile = My.Computer.FileSystem.OpenTextFileWriter(csvFile, True)
                    If headerText.Length > 0 Then
                        outFile.WriteLine(headerText)
                    End If
                    Dim date1 As String = "24-10-2014"
                    Dim time1 As String = TimeOfDay()

                    Dim watt1 As String = CStr(watt_hour)
                    'Dim x As String = date1 + "," + time1 + "," + distance
                    'outFile.Write(x)

                End Using
            End If

---

Attachment 119051
My complete code

Code:

---


Imports System
Imports System.IO.Ports
Imports System.ComponentModel
Imports System.Threading

Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Windows.Forms
Imports System.Windows.Forms.DataVisualization.Charting


Public Class Form1
    Dim myPort As Array
    Dim Distance As Integer

    Dim dT As DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        myPort = IO.Ports.SerialPort.GetPortNames()
        PortComboBox.Items.AddRange(myPort)
        BaudComboBox.Items.Add(9600)
        BaudComboBox.Items.Add(19200)
        BaudComboBox.Items.Add(38400)
        BaudComboBox.Items.Add(57600)
        BaudComboBox.Items.Add(115200)
        ConnectButton.Enabled = True
        DisconnectButton.Enabled = False
        Call initializeDataTable()
        Call initializeChart()
        Chart1.DataSource = dT
        Timer1.Interval = 1000
        Timer1.Start()
    End Sub


    Private Sub initializeDataTable()

        dT = New DataTable
        dT.Columns.Add(New DataColumn("time", GetType(DateTime)))
        dT.Columns.Add(New DataColumn("current", GetType(Double)))
    End Sub

    Private Sub initializeChart()

        Chart1.Series.Clear()
        'create the new charting series....
        Dim newSeries As New DataVisualization.Charting.Series
        With newSeries
            'assign the series to a chart area....
            .ChartArea = Chart1.ChartAreas(0).Name

            'define the xvalue member. It must match the datatable's column name
            .XValueMember = "time"
            .XValueType = DataVisualization.Charting.ChartValueType.DateTime
            'define the yValue meber. It too must match the datatable column name
            .YValueMembers = "current"
            .XValueType = DataVisualization.Charting.ChartValueType.Double

            'select the chart type
            .ChartType = DataVisualization.Charting.SeriesChartType.Line
        End With

        'turn off the xAxis labels
        Chart1.ChartAreas(0).AxisX.LabelStyle.Enabled = False

        'add the series to the chart
        Chart1.Series.Add(newSeries)

    End Sub

    Private Sub ConnectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ConnectButton.Click
        SerialPort1.PortName = PortComboBox.Text
        SerialPort1.BaudRate = BaudComboBox.Text
        SerialPort1.Open()
        Timer1.Start()

        'lblMessage.Text = PortComboBox.Text & " Connected."
        ConnectButton.Enabled = False
        DisconnectButton.Enabled = True
    End Sub

    Private Sub DisconnectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DisconnectButton.Click
        SerialPort1.Close()

        DisconnectButton.Enabled = False
        ConnectButton.Enabled = True
    End Sub
    Dim rnd As New Random

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Static counter As Integer = 0
        Static average_sum As Double = 0
        Static Avg_count As Integer = 0
        Dim voltage As Double = 24
        Dim Power As Double
        Static Cc_count As Long = 0
        Static watt_hour As Double


        Try

            SerialPort1.Write("c")
            System.Threading.Thread.Sleep(250)
            Dim k As Double
            Dim distance As String = SerialPort1.ReadLine()
            k = CDbl(distance)
            ListBoxSensor.Text = k
            Dim s As New Series
            counter = counter + 1
            Count_val.Text = counter

            Avg_count = Avg_count + 1





            ' drawing graph

            If Avg_count = 1 Then
                Power = (distance * voltage) / 3600
                watt_hour = watt_hour + Power
                'Twatt.Text = watt_hour
                Display.Text = watt_hour
                voltage_text.Text = 24

                System.Threading.Thread.Sleep(250)
                If watt_hour > 1000 Then
                    watt_hour = 1.0 + watt_hour
                End If

                Avg_count = Avg_count + 1

            End If
            If Avg_count > 1 Then
                Avg_count = 0
            End If

            Dim current As Double = watt_hour
            'Dim current As Double = k
            Dim r As DataRow = dT.NewRow
            r("time") = Now
            'r("current") = k
            r("current") = watt_hour
            'add the row
            dT.Rows.Add(r)

            'remove any row older than 1 minute
            Dim oldestTime As DateTime = Now.AddMinutes(-1)
            Do While DirectCast(dT.Rows(0)("time"), DateTime) < oldestTime

                dT.Rows.RemoveAt(0)

            Loop
            'finally bind the chart....
            Chart1.DataBind()

            'write into excel sheet for every 1 minute ..................
            If Cc_count < 10 Then
                Cc_count = Cc_count + 1
                Dim array(10) As Double
                For value As Double = 0 To Cc_count
                    array(Cc_count) = distance
                    average_sum = average_sum + array(Cc_count)
                    value = value + 1
                    V_count.Text = value

                    AVG_count_text.Text = Cc_count
                    If value > 10 Then
                        average_sum = average_sum / value
                        System.Threading.Thread.Sleep(250)
                        AVG_CR1.Text = average_sum
                        Cc_count = 0
                    End If

                Next

                'If value = 10 Then
                '    average_sum = average_sum / Avg_count
                '    System.Threading.Thread.Sleep(250)
                '    AVG_CR1.Text = average_sum
                '    Cc_count = 0
                'End If

            End If

            If counter = 60 Then
                counter = 0
                Dim headerText = ""
                Dim csvFile As String = IO.Path.Combine(My.Application.Info.DirectoryPath, "Current.csv")

                'If Avg_count = 1 Then
                '    Power = (distance * voltage) / 3600
                '    watt_hour = watt_hour + Power

                '    If watt_hour > 1000 Then
                '        KW_hour = 1 + watt_hour
                '    End If
                'End If

                If Not IO.File.Exists((csvFile)) Then
                    'headerText = "Date,TIME ,current, "
                    headerText = "Date,TIME ,current,Watt "
                End If

                Using outFile = My.Computer.FileSystem.OpenTextFileWriter(csvFile, True)
                    If headerText.Length > 0 Then
                        outFile.WriteLine(headerText)
                    End If
                    ' Dim y As String = DateAndTime.Now()

                    'Dim date1 As String = DateAndTime.Year(2014)
                    ' Integer Year1= DateAndTime.Year()
                    Dim date1 As String = "24-10-2014"
                    Dim time1 As String = TimeOfDay()

                    Dim watt1 As String = CStr(watt_hour)
                    Dim x As String = date1 + "," + time1 + "," + distance + "," + watt1
                    outFile.Write(x)


                    'Dim x As String = date1 + "," + time1 + "," + distance
                    'outFile.Write(x)

                End Using
            End If



        Catch ex As Exception

            ' MsgBox("Error in Timer1_Tick: " & ex.Message)
        End Try



    End Sub

    Private Sub Relay_ON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Relay_ON.Click
        SerialPort1.Write("1")
    End Sub

    Private Sub Relay_Off_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Relay_Off.Click
        SerialPort1.Write("0")
    End Sub


    Private Sub Browse_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Browse_btn.Click
      OpenFileDialog1.InitialDirectory = "C:\"
        OpenFileDialog1.FileName = "Open A File..."
        OpenFileDialog1.Multiselect = False
        OpenFileDialog1.Filter = "Executable Files|*.csv"
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim sName As String = OpenFileDialog1.SafeFileName
            TextBox1.Text = OpenFileDialog1.FileName
        End If

    End Sub


    Private Sub OK_Pressed_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Pressed.Click

    End Sub


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

    End Sub
End Class

---

First up, turn on Option Strict/Explicit. Change + for &, you are appending strings, not adding numbers.

How to turn on Option Strict/Explicit.??? As you said i changed to

Code:

---

Dim x As String = date1 & "," & time1 & "," & distance & "," & watt1

---