Show Posts
1
Open Discussion / Saving Array Tags data to excel csv file
« on: October 11, 2018, 11:52:54 PM »
I've seen this code in some topics similar to my target output (credits to bob1371)
Is this applicable also in my scenario? I have an array of tags which is Test_Tags[0] to Test_Tags[12] which need to save data in .csv file per date and append all data changes every row. In this example it has a limit of 500, so I just remove the 500 from every code of array on this syntax?
Code: [Select]
Private Sub DataSubscriber1_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataChanged
If e.ErrorId = 0 AndAlso e.Values.Count > 0 AndAlso e.Values(0) = "True" Then
Console.WriteLine("About to read the data")
Dim MyValues0() As String = EthernetIPforCLXCom1.Read("VCell1B_FES_Cycle_Average[0]", 500)
Dim MyValues1() As String = EthernetIPforCLXCom1.Read("VCell_1A_FES_Cycle_Average[0]", 500)
Dim MyValues2() As String = EthernetIPforCLXCom1.Read("VCell_2B_FES_Cycle_Average[0]", 500)
Dim MyValues3() As String = EthernetIPforCLXCom1.Read("VCell_2A_FES_Cycle_Average[0]", 500)
Dim MyValues4() As String = EthernetIPforCLXCom1.Read("VCell_3B_FES_Cycle_Average[0]", 500)
Dim MyValues5() As String = EthernetIPforCLXCom1.Read("VCell_3A_FES_Cycle_Average[0]", 500)
Dim MyValues6() As String = EthernetIPforCLXCom1.Read("Inspect_FES_Cycle_Average[0]", 500)
Dim MyValues7() As String = EthernetIPforCLXCom1.Read("Repair_FES_Cycle_Average[0]", 500)
Dim MyValues8 As String = EthernetIPforCLXCom1.Read("MDY5")
Console.WriteLine(MyValues0.Length & "elements read VCell_1B.")
Console.WriteLine(MyValues1.Length & "elements read VCell_1A.")
Console.WriteLine(MyValues2.Length & "elements read VCell_2B.")
Console.WriteLine(MyValues3.Length & "elements read VCell_2A.")
Console.WriteLine(MyValues4.Length & "elements read VCell_3B.")
Console.WriteLine(MyValues5.Length & "elements read VCell_3A.")
Console.WriteLine(MyValues6.Length & "elements read Inspect.")
Console.WriteLine(MyValues7.Length & "elements read Repair.")
Console.WriteLine("All Data Read")
'*Transfer the values to Excel
'*Creates Excel file
Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Data.xlsx"))
ExcelPackage.Workbook.Worksheets.Add(MyValues8)
Console.WriteLine("ws creation ok")
'*Writes the Headers in Row 1
ExcelPackage.Workbook.Worksheets(1).Cells("A1").Value = "VCell 1B"
ExcelPackage.Workbook.Worksheets(1).Cells("C1").Value = "VCell 1A"
ExcelPackage.Workbook.Worksheets(1).Cells("E1").Value = "VCell 2B"
ExcelPackage.Workbook.Worksheets(1).Cells("G1").Value = "VCell 2A"
ExcelPackage.Workbook.Worksheets(1).Cells("I1").Value = "VCell 3B"
ExcelPackage.Workbook.Worksheets(1).Cells("K1").Value = "VCell 3A"
ExcelPackage.Workbook.Worksheets(1).Cells("M1").Value = "Inspect"
ExcelPackage.Workbook.Worksheets(1).Cells("O1").Value = "Repair"
Console.WriteLine("header creation ok")
'*Loops to write data in correct columns
For Index = 0 To MyValues0.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues0(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "A" & (Index + 3)
ws.Cells(CellNum).Value = MyValues0(Index)
Next
Console.WriteLine("Worksheet OK 1B")
For Index = 0 To MyValues1.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues1(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "C" & (Index + 3)
ws.Cells(CellNum).Value = MyValues1(Index)
Next
Console.WriteLine("Worksheet OK 1A")
For Index = 0 To MyValues2.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues2(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "E" & (Index + 3)
ws.Cells(CellNum).Value = MyValues2(Index)
Next
Console.WriteLine("Worksheet OK 2B")
For Index = 0 To MyValues3.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues3(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "G" & (Index + 3)
ws.Cells(CellNum).Value = MyValues3(Index)
Next
Console.WriteLine("Worksheet OK 2A")
For Index = 0 To MyValues4.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues4(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "I" & (Index + 3)
ws.Cells(CellNum).Value = MyValues4(Index)
Next
Console.WriteLine("Worksheet OK 3B")
For Index = 0 To MyValues5.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues5(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "K" & (Index + 3)
ws.Cells(CellNum).Value = MyValues5(Index)
Next
Console.WriteLine("Worksheet OK 3A")
For Index = 0 To MyValues6.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues6(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "M" & (Index + 3)
ws.Cells(CellNum).Value = MyValues6(Index)
Next
Console.WriteLine("Worksheet OK Inspect")
For Index = 0 To MyValues7.Length - 1
Console.WriteLine("Element " & Index & "=" & MyValues7(Index))
Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(1)
Dim CellNum As String = "O" & (Index + 3)
ws.Cells(CellNum).Value = MyValues7(Index)
Next
Console.WriteLine("Worksheet OK Repair")
'*Saves.xlsl file
ExcelPackage.Save()
End Using
End If
End Sub Is this applicable also in my scenario? I have an array of tags which is Test_Tags[0] to Test_Tags[12] which need to save data in .csv file per date and append all data changes every row. In this example it has a limit of 500, so I just remove the 500 from every code of array on this syntax?
Code: [Select]
EthernetIPforCLXCom1.Read("VCell1B_FES_Cycle_Average[0]", 500) Also instructed to put PLCAddressValue to DataSubscriber the tagname, how to input tagname of arrays as PLCAddress Value? or I just need to run first the BeginReadMultiple to achieve that. Im sorry for the question, I'm new in the process of AdvancedHMI but have knowledge on VB. Thanks in advance