Control Logix array to Excel in 2024

[Peter D]

Hi all.  I'm a newbie and stumbled onto AdvancedHMI looking for a way outside of using RS-Linx to transfer the contents of a floating point array to an excel spreadsheet for further analysis.  I have seen some discussion in this forum in 2017 that's quite apropos though the details of the coding go a bit over my head.  I have downloaded the basic sample project and got live data to show up in the Digital Panel Meter.

What I'm wondering is if there's a sample project more recent than "KeepingRecordsFromCLXToExcelV399x" that comes closer to what I'm trying to do.

Ideally I would push a button or check a box in my excel workbook and AdvancedHMI would either write a fresh copy of the array values into my workbook or alternatively would write the array values as a spreadsheet or a comma limited text file which the workbook could refer to.  Another option would be for AdvancedHMI to write the array values to and SQL database which the spreadsheet could draw from.

I'm not utterly lost coding in Visual Basic but when I look at the back-and-forth Archie had with folks who were trying to accomplish what I was trying to accomplish it looked like I would have to do a great deal of side reading to understand it.  If there's a post-2017 solution that's closer to what I'm trying to do it would be great.

[Archie]

There will be a need for some basic coding to transfer from your PLC to a spreadsheet. AdvancedHMI is the tool that makes it easy to get the data from the PLC. To get the data into a spreadsheet, you will need a tool for that. In the past, I would always recommend EPPlus as the tool. For new projects, I now suggest ClosedXML. That library is free and can be added to your project using Nuget.

Here is a simple demo of using ClosedXML:

1) After opening AdvancedHMI is Visual Studio, in Solution Explorer, click once on the AdvancedHMI project to make it the current selection
2) Go to Project -> Manage Nuget packages
3) At the top of the NUget windows, click Browse (Next to installed)
4) In the search bar, enter "ClosedXML"
5) ClosedXML should be the first item in the list, click once to highlight it.
6) To the right, click the install button
7) Accept the project additions and licenses

This will now have ClosedXML added to your project

Now let's put it to use:

1) In Solution Explorer, double click MainForm to open the designer
2) In a blank area on the MainForm, double click to get back to the code
3) Enter this code:

Code: [Select]

        Dim WB As New ClosedXML.Excel.XLWorkbook()
        WB.AddWorksheet()

        WB.Worksheets(0).Cell("A2").Value = "New Value"
        WB.SaveAs(".\ExcelFile2.xlsx")
4) Run the application, after it starts up then close it

At this point you now have created a Worksheet and added a value to cell A2.

1) In Solution Explorer, right click the AdvancedHMI project and select Open Folder in File Explorer
2) Browse to \bin\Debug
3) You should see the newly created file of ExcelFile2.xlsx

I will follow up later with another post on how to get data from your PLC

[bachphi]

Here is another example of how to add data to a new sheet each time in an existing Excel file.

Code: [Select]

' Open the existing workbook
 Using workbook As New XLWorkbook(filePath)
     ' Get the count of existing sheets
     Dim sheetCount As Integer = workbook.Worksheets.Count

     ' Add a new worksheet with a unique name
     Dim newSheetName As String = "SheetNum" & (sheetCount + 1).ToString()
     Dim newSheet As IXLWorksheet = workbook.Worksheets.Add(newSheetName)

     ' Populate the new worksheet with data contains TagName, TagValue
     For i As Integer = 0 To data.GetLength(0) - 1
         For j As Integer = 0 To data.GetLength(1) - 1
             newSheet.Cell(i + 1, j + 1).Value = data(i, j)
         Next
     Next

     ' Optional: Add some styling to the new sheet
     newSheet.Range("A1:B1").Style.Font.Bold = True
     newSheet.Columns().AdjustToContents()

     ' Save the workbook back to the file
     workbook.Save()
 End Using


[Peter D]

Thanks so much to both Archie and bachpi for the super prompt replies!  This looks tractable!!  I will try it out this weekend and report back.

[Peter D]

Archie,

Your instructions were delightfully detailed and easy to follow, but I must have screwed up somewhere.  When I build the project I get errors.  I know that the code inserted into MainForm is exactly as you suggested because I copied and pasted it in, so I'm not sending the code with this post.  The error I get is this:

Severity   Code   Description   Project   File   Line   Suppression State
Error (active)   BC32016   'Public Overloads ReadOnly Property Worksheets As IXLWorksheets' has no parameters and its return type cannot be indexed.   AdvancedHMI   C:\Users\phdel\source\repos\ArrayToExcel\AdvancedHMI\MainForm.vb   39

and then several warnings about vulnerabilities like this one:

Severity   Code   Description   Project   File   Line   Suppression State
Warning   NU1903   Package 'System.IO.Packaging' 8.0.0 has a known high severity vulnerability, https://github.com/advisories/GHSA-qj66-m88j-hmgj   AdvancedHMI   AdvancedHMI      


   

[bachphi]

Remove the 's'

WB.Worksheet(0).Cell("A2").Value = "New Value"

[Peter D]

Ooooh.  Thanks for the sharp eye, Bachphi.

[Peter D]

Well, spelling "Worksheet" without an "s" did get me to the point where I can build it.  When I run it is still gives an error as if my workbook doesn't contain a zeroeth worksheet even after I purportedly added one.  The debugger breaks on the line that tries to assign a "New value" in cell A2 saying:

System.ArgumentException: 'There isn't a worksheet associated with that position.'

I assume that the "position" referred to is element zero of Worksheet array but I'm not seeing what I did wrong.  Sorry for my inexperience.  It's probably something obvious.  I have:

    Dim WB As New ClosedXML.Excel.XLWorkbook()

    WB.AddWorksheet()

    WB.Worksheet(0).Cell("A2").Value = "New Value"
    WB.SaveAs(".\ExcelFile2.xlsx")
End Sub

[bachphi]

change the code to:

WB.Worksheet("Sheet1").Cell("A2").Value = "New Value"

[Archie]

Here is the project I tested with:

https://www.advancedhmi.com/downloads/ClosedXMLTest2.zip

[bachphi]

Your code works correctly with ClosedXMLTest2.zip. But it will not work with the downloaded AdvancedHMIv399yBeta40.zip. Not sure why?

[bachphi]

As Archie mentioned, EPPlus now requires a commercial license for business use.
However, for non-commercial purposes, you can still add it to your project via NuGet.
There is also a free version called EPPlusFree, a continuation of the original 4.5.3.3 version.
I prefer EPPlus over alternatives like ClosedXML because it only adds a single DLL file, while ClosedXML includes multiple files.

Code: [Select]

   Using package As New ExcelPackage(New FileInfo(filePath))
       ' Get the count of existing sheets
       Dim sheetCount As Integer = package.Workbook.Worksheets.Count
       ' Add a new worksheet with a unique name
       Dim newSheetName As String = "SheetNum" & (sheetCount + 1).ToString()
       Dim newSheet As ExcelWorksheet = package.Workbook.Worksheets.Add(newSheetName)

       ' Populate the new worksheet with data contains TagName, TagValue
       For i As Integer = 0 To Data.GetLength(0) - 1
           For j As Integer = 0 To Data.GetLength(1) - 1
               newSheet.Cells(i + 1, j + 1).Value = Data(i, j)
           Next
       Next

       ' Optional: Add some styling to the new sheet
       Using range As ExcelRange = newSheet.Cells("A1:B1")
           range.Style.Font.Bold = True
       End Using
       newSheet.Cells.AutoFitColumns()

       ' Save the workbook back to the file
       package.Save()
   End Using

[Peter D]

Thanks to both of you "on it" guys.  I really appreciate the nitty gritty help.  I think I just found the problem I stumbled over.  I think that error message was actually very direct.  There is no WB.Worksheet(0).  The first worksheet is WB.Worksheet(1).   I will see where I get now with Archie's original suggestion.  Failing that I'll look at the example Archie sent and that doesn't straighten me out I'll have a look at the EPPlus possibility.  Thanks so much.

[Peter D]

Ok guys, yes, Worksheet(1) instead of Worksheet(0) fixes it and it works as Archie describes.  Adding in  the code to write an array to the worksheet, I can get it to write a one-dimensional array into my worksheet with the code shown below.  I'm almost all the way there!!  The final step for me is to have my data source be a ControlLogix tag.  In the quick start Youtube tutorial I made the link to a PLC tag name by setting a property of the panel meter I had placed on my main panel.  Here, I have no visual control I'm working with so how do I reference the 400 element float array with tag name "Histogram1" that exists within my ControlLogix program in my Visual Basic code?
----------------------------------------------------------------------------------------------------------------------
Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim WB As New ClosedXML.Excel.XLWorkbook()
    Dim FloatData As Double() = {1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9}

    WB.AddWorksheet()

    For i As Integer = 0 To FloatData.GetLength(0) - 1
        WB.Worksheet(1).Cell(i + 2, 2).Value = FloatData(i)
    Next
    WB.SaveAs(".\HistogramExample.xlsx")
End Sub

[bachphi]

Drag a 'button' from the Toolbox and drop it on the form, then double click to get to the default click event, then insert the code :

Code: [Select]

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim FloatData() As String = EthernetIPforCLXCom1.Read("Historygram1[0]", 400)
End Sub