General VB

[ENA]

I'm developing my applications based on AdvancedHMI, which includes a great amount of coding. Since my first touch with Visual Basic was trough AdvancedHMI, I'm still battling difficulties of writing a good code in this language. So, I would like to open this topic, which could cover some more general issues, when upgrading your own AdvancedHMI code.

As I stated in my other posts, my application logs data to excel file on a time basis. On one of my forms I have a chart, where this data should be plotted. Data is plotted by pressing a button, next to the chart. Code for the Plot button, where I read data from excel file and fill the chart series, is:

Code: [Select]

    Private Sub plot_Click(sender As Object, e As EventArgs) Handles iscrtaj.Click
            For i As Integer = 900 To 910
            Chart1.Series("Nivo VS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("C" + CStr(i)).Value / 100)
            Chart1.Series("Nivo CS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("D" + CStr(i)).Value / 100)
            Chart1.Series("Tlak CS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("E" + CStr(i)).Value)
            Chart1.Series("Protok CS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("F" + CStr(i)).Value)
            Chart1.Series("Struja CS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("G" + CStr(i)).Value)
            Chart1.Series("Želj. protok").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("H" + CStr(i)).Value)
            Chart1.Series("Snaga UV").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("I" + CStr(i)).Value)
            Chart1.Series("N. uklj. T1").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("J" + CStr(i)).Value / 100)
            Chart1.Series("N. isklj. T1").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("K" + CStr(i)).Value / 100)
            Chart1.Series("N. uklj. T2").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("L" + CStr(i)).Value / 100)
            Chart1.Series("N. isklj. T2").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("M" + CStr(i)).Value / 100)
        Next
    End Sub
B column contains data and time, other columns are data. Counter "i" is now set to fixed values, later I plan to implement to plot data according to date picker.

My problem is, plotting this way is SLOW (can't stress this hard enough). When pressing the button, plotting is performed with a speed of 1 - 2 data point per second. I log data every minute, and should be able to chart data for several days, which comes to >10000 data points per series and I have 11 series in chart. What can I do to speed up the plotting?

[Archie]

What is the "excel" object you are using? Is it OLE automation?

[ENA]

Sorry, missed a line when copying:

Code: [Select]

Private Sub plot_Click(sender As Object, e As EventArgs) Handles iscrtaj.Click
        Dim excel As Object = GlobalVariables.oSheet_data
        For i As Integer = 900 To 910
            Chart1.Series("Nivo VS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("C" + CStr(i)).Value / 100)
            Chart1.Series("Nivo CS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("D" + CStr(i)).Value / 100)
            Chart1.Series("Tlak CS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("E" + CStr(i)).Value)
            Chart1.Series("Protok CS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("F" + CStr(i)).Value)
            Chart1.Series("Struja CS").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("G" + CStr(i)).Value)
            Chart1.Series("Želj. protok").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("H" + CStr(i)).Value)
            Chart1.Series("Snaga UV").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("I" + CStr(i)).Value)
            Chart1.Series("N. uklj. T1").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("J" + CStr(i)).Value / 100)
            Chart1.Series("N. isklj. T1").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("K" + CStr(i)).Value / 100)
            Chart1.Series("N. uklj. T2").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("L" + CStr(i)).Value / 100)
            Chart1.Series("N. isklj. T2").Points.AddXY(excel.Range("B" + CStr(i)).Value, excel.Range("M" + CStr(i)).Value / 100)
        Next
    End Sub
Separate class to hold the variables I use throughout application:

Code: [Select]

Public Class GlobalVariables
    '**************************************************************************************************
    'declaration of variables for Excel file
    'data log
    Public Shared CellIndexRow_data As Long = 2 'prvi red je zaglavlje
    Public Shared oExcel_data As Object
    Public Shared oBook_data As Object
    Public Shared oSheet_data As Object
    '**************************************************************************************************
'** rest of variables omitted to shorten the code
End Class

On application startup, this code is performed in MainForm_Load:

Code: [Select]

        GlobalVariables.oExcel_data = CreateObject("Excel.Application")
        GlobalVariables.oExcel_data = New Excel.Application
        GlobalVariables.oExcel_data.Workbooks.Open("C:\data_Medven.xlsx")
        GlobalVariables.oBook_data = GlobalVariables.oExcel_data.Workbooks(1)
        GlobalVariables.oSheet_data = GlobalVariables.oBook_data.Worksheets(1)
        While (GlobalVariables.oSheet_data.Range("A" + CStr(GlobalVariables.CellIndexRow_data)).Value <> 0)
            GlobalVariables.CellIndexRow_data = GlobalVariables.CellIndexRow_data + 1
        End While

[Archie]

The Excel automation is a slow process since it starts up Excel and passes everything through it. I would recommend using EPPlus because it directly loads the spreadsheet in memory and is much faster:

http://epplus.codeplex.com/

[ENA]

Thank you Archie. I modified my code, so it doesn't use Excel any more, but EPPlus, and charting is definitely faster. I'm still testing other functions, to see how they do after conversion.

[ENA]

Well, I found a problem that appeared with using EPPlus instead of Excel. When exporting events in xlsx file, current date and time are logged:

Code: [Select]

GlobalVariables.worksheet_event.Cells("B" + CStr(GlobalVariables.index_event)).Value = Now()
I display event log in a separate form, using DataGridView. DataGridView is filled with this code:

Code: [Select]

                With Me.DataGridView1.Rows(Me.DataGridView1.Rows.Count - 1)
                    .Cells("id").Value = GlobalVariables.worksheet_event.Cells("A" + CStr(i)).Value
                    .Cells("vrijeme").Value = GlobalVariables.worksheet_event.Cells("B" + CStr(i)).Value 'this is the time cell
                    .Cells("dogadaj").Value = GlobalVariables.worksheet_event.Cells("C" + CStr(i)).Value
                    .Cells("objekt").Value = GlobalVariables.worksheet_event.Cells("D" + CStr(i)).Value
                End With

In DataGridiew, column B format is DateTime. When I was using excel, the values in column B were shown as e.g. "10. siječnja 2014. 13:55:14" (10th January 2014 13:55:14). When retrieving values from excel event log using EPPlus, DataGridView shows them as a number, presumably because retrieved values are in double format. I'm stuck on this, because I can't convert this value into a date.

[ENA]

Problem was solved by using FromOADate on data that was read from excel file.

[ENA]

I'm going to continue this thread, since I have a problem considering EPPlus. This application is still using AdvancedHMI 3.60.
I have a timer for 5 s, which reads data from PLC and writes it in excel file using EPPlus. After each write, I call Save(). Writing to file is successful and the data is saved, but after some various time after I start the application, I get the error (please see screenshot). Sometimes it happens after a minute or two, sometimes after cca 10 minutes. Date in the meantime is saved. Any ides what to look for?

[Archie]

Writing to file is successful and the data is saved, but after some various time after I start the application, I get the error (please see screenshot). Sometimes it happens after a minute or two, sometimes after cca 10 minutes. Date in the meantime is saved. Any ides what to look for?

By any chance are you opening the file in Excel at the time the error occurs? The reason I ask is because I believe if you open the file in Excel, it locks the file then EPPlus can no longer make changes to it until you close Excel.

[ENA]

So, for the sake of community I will share the follow up, although is a shame of mine. I hope someone, sometime will make use of this. 

Quick intro, I developed an application based on AdvancedHMI roughly a year ago and now it required some upgrades regarding functionality. To do so, I had to write new piece of code which handled data differently than the original application. Modifications were not major, but big enough to make some room for mistakes.

As stated in post above, the problem was with saving the excel file, for which I blamed my new code and spent several days trying to fix this bug.

My "bug" has roots some six months ago, when we purchased WD Cloud and set up live auto backup feature (Cloud saves any changes to any file made on any of computers). I write to excel file every five seconds and backup software has its own agenda, collisions were bound to happen. Of course, when the file is being backed up, EPPlus can't access it.

Case closed.

[Archie]

Thanks for following up with what you found.