AdvancedHMI Software
General Category => Open Discussion => Topic started by: John_R on August 04, 2018, 12:07:56 PM
-
Hey All,
complete newb here, bear with me.....
got the software set up and working.
built a quick little project as referenced
set a com driver = EthernetIPforSLCMicroCom1 - set to IP in PLC
added 3 DigitalPanelMeter - set them to read "N" values in PLC
that all seems straight forward
I can run the project and the values I want pop in the displays.
So far, so good...
I'm starting off with baby steps and made my first two, now I want to take bigger steps.
My Objective here is to make a project that will read values from the PLC and write them to an Excel file, adding a row for each read, each minute, and creating a new file each day.
I would like to accomplish this without modifying the PLC program if possible.
Not asking too much am I?
I've been looking at examples for writing Excel files on the forum (found what looks like a good thread, 1875, kinda close to what I want, although it looks like it's for compactlogix using tags). I'm not sure if tailoring the OPs code for Datasubscriber would work for me, or if should I start from scratch?
In his example, it looks like the OP is triggering his read/write call from a bit in the PLC, as I said, would rather not have to modify the PLC program, is there a clock function in AHMI that would allow me to trigger from the project?
Or is there another way to go about this?
Can anyone point me in the right direction?
Regards,
JohnR
-
Here is a possible approach by using the Timer component found in the Toolbox under "All Windows Forms":
Use instructions from the thread you mentioned to install the EPPLUS package.
Drop a Timer on the form, set its Interval property to 60000 (which is 1 minute) and also set its Enabled property to True.
Double-click the Timer component to get to its Tick event code page and use the code similar to the one below:
Private currentRow As Integer = 1
Private PreviousDate As String = DateTime.Now.ToString("dd-MMM-yyyy")
Private CurrentDate As String = ""
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
'* Check the current date for the new daily file creation
CurrentDate = DateTime.Now.ToString("dd-MMM-yyyy")
If PreviousDate <> CurrentDate Then
If IO.File.Exists(".\MeterData.xlsx") Then
IO.File.Move(".\MeterData.xlsx", ".\MeterData - " & PreviousDate & ".xlsx") '* Move the current file to a new file, preserve the name and include the Date in it
IO.File.Delete(".\MeterData.xlsx") '* Delete the current file so it can be re-created
PreviousDate = CurrentDate
currentRow = 1
End If
End If
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo(".\MeterData.xlsx"))
'* Check if worksheet exists
Dim sheetExists As Boolean
For Each sheet In ExcelPackage.Workbook.Worksheets
If sheet.Name = "test" Then
sheetExists = True
Exit For
End If
Next sheet
If Not sheetExists Then
ExcelPackage.Workbook.Worksheets.Add("test")
End If
If currentRow = 1 Then
'*Write the Headers in Row 1
ExcelPackage.Workbook.Worksheets(1).Cells(currentRow, 1).Value = "Date/Time"
ExcelPackage.Workbook.Worksheets(1).Cells(currentRow, 2).Value = "Meter 1"
ExcelPackage.Workbook.Worksheets(1).Cells(currentRow, 3).Value = "Meter 2"
ExcelPackage.Workbook.Worksheets(1).Cells(currentRow, 4).Value = "Meter 3"
End If
currentRow += 1
'* Insert a timestamp and transfer the current values of the meters to Excel
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
ws.Cells(currentRow, 1).Value = DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss")
ws.Cells(currentRow, 2).Value = Me.DigitalPanelMeter1.Value
ws.Cells(currentRow, 3).Value = Me.DigitalPanelMeter2.Value
ws.Cells(currentRow, 4).Value = Me.DigitalPanelMeter3.Value
ExcelPackage.Save()
End Using
End Sub
The output should look like the attached picture, where the alignment might need some adjustments.
The code seems to be working fine for creating a new daily file.
The Timer component is not perfect and might eventually show as inaccurate since it could get affected by other applications running on the same computer.
If you run the app for a while and then stop it and run it again, the values in Excel file should get overwritten.
-
Thank you Godra, that got me going in the right direction. JohnR