AdvancedHMI Software
General Category => Open Discussion => Topic started by: MrPike on June 03, 2015, 01:03:06 PM
-
Hi all. I know this topic has been discussed before. I am starting a new project that I need to populate an excel form with data read by AHMI. Can anyone explain how and provide some code samples how to do this, thx!
-
Archie posted this (http://www.plctalk.net/qanda/showthread.php?t=95614) at PLC talk. I think it's more towards writing from excel to the PLC, but it should get you started.
-
The post on PLCTalk also gives a link to a sample that you can use as a reference. That sample uses the EPPLus library. It shows how to read Excel, but the same principle would apply for writing. If you Google EPPlus, there are some good examples of using the libary.
-
Archie, I am looking to poll several registers of 5 different controllers and write this data to one excel sheet. If I'm correct, the BasicDataLogger2 will poll these registers but will save them to one file that I choose. I'm looking to put all 5 controllers in the same excel sheet. How do I go about doing this? I can add a Basicdatalogger2 for each controller but don't they all write to the same cells of the excel? Is there a way to manipulate the BasicDataLogger2 so that it writes to specific cells in the excel so that another basic Datalogger2 does not over write them. Let me know if I'm way off base here, thanks.
-
The BasicDataLoggers can only log from a single driver. To write from multiple drivers to a single file would have to be done in code. This evening I will see if I can put together a quick example
-
I was thinking I could use a BasicDataLogger2 for each device. That would give me all the registers I want. I just need to write them to the same excel file. Will the second data logger overwrite the first if I set them to the same file path or does it look for an empty cell? Ideally I would want device 1 use the first 10 columns then device 2 use the next 10 etc.
-
I did not think about it that way, but it will work since the BasicDataLogger always appends to a file. What I am not sure about is the data logger keeps the file open, so if 2 loggers try to open the same file I am unsure what will happen.
If that does work, you can use the Prefix property to put data in certain columns. For example is Prefix is set to ",,,,", then when the file is opened in Excel, the data will go in column 5. Just use the number of commas that you want for the column position.
-
Sorry Archie, I'm a little confused where to enter my registers that I want to poll with the BasicdataLogger2. This is the window I get when I open the PLCAddressValueItems collection. I try the ADD button but it doesn't allow me to add anything. What am I missing? Thx
-
Just to add I'm using 3.99a. I just opened 3.98r and I was able to add registers to the collection as expected. Thanks
-
Those erros usually come up when something get fouled up in Visual Studio. Every time I have seen that error, I would simply exit Visual Studio and re-open, then the errors go away.
-
Thanks that fixed the errors. I found the basicdatasubscriber2 writes to notepad. How can I get this to write to excel? Also it doesn't look like two data subscribers writing to the same file works since the data I see is the same for one or two DataSubscribers running. I looked at your sample for writing modbus to excel, I will try to code this if there is no way to do it with the datasubscribers. Thanks
-
The files created by the DataSubscrober are CSV (Comma Separated) files, which can be opened directly with Excel. One you open, it may ask a few question such as what character is the spearator.
-
Ok, I was able to change the file extension to .csv instead of .xls or .log and that part works. The prefix will only work if the file names are different in the dataLoggers file path. If I set the file the same, the prefix does not shift the data in the spreadsheet. Also, for some reason the time does not include seconds in the .csv format. I have checked the setting is correct in the DataLogger and it does write seconds if the file path is .log.
-
Mr. Pike.
Most likely Excel is cutting off the seconds because of its formatting.
Select the column and right-click and select format.
Now in the box add ":ss" and then see if this is OK.
-
Thanks, thinking more about it, I would like to omit the date from the cell data and keep just the time like it is. However I would like to add the date and time to the file name so that it will write a new file each time a log is created. See the code snippet attached. I run this but I doesn't work, debugging shows the file name does match the "GEN1_DATALOG_timestamp.csv"like I want it but it doesn't show up in the folder. What may I be missing? Thanks.
-
It appears the IO stream writer won't handle the DateTime.Now function string. I get the attached error. Is there way I can create or save the file with the date & time in the filename?
-
Maybe if you try to get that converted to a string before?
-
I'm still trying to work this out. The project I created is used on many computers owned by others. That creates an issue because if the PC date has the slash in it such as 11\2\15, then excel does not accept it since you cannot have a slash in the file name. I cant get everyone to change their date format so I need to create a different solution. I would like to create a file that is "save as" but not sure how. Also I would like to overwrite the old data rather than append it.
Archie, is there a code change that I can apply that would overwrite the data in the log each time the log is started? How would I make this "save as" so that the user could name the file when they stop the datalogger on a button click?, thank you.
-
You can format the Now() function like this:
Now.ToString("ddMMyyyy")
That will eliminate the slash problem
-
Ok, I almost have this working. Just a couple issues.
1) What is the proper way to disable the BasicdataLogger ? I want to enable/disable based on a checkbox. This is the code I have and it kind of works.
Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged
BasicDataLogger21.LogInterval = TextBox6.Text
If CheckBox1.Checked = True Then
BasicDataLogger21.CommComponent = ModbusTCPCom1
BasicDataLogger21.FileName = String.Concat(Date1, time1, file1)
Else
BasicDataLogger21.CommComponent = Nothing
End If
End Sub
2) when I enable the Basicdatalogger using the checkbow, the filename created is a concat of Date, time, and Device name. However I cannot get the Now() function to change time unless I close the project and reopen it. How can I get the Now() function to update when I uncheck and recheck the checkbox?
Sorry for any confusion. My goal is to create a unique name for the log each time the Checkbox1 is checked. Thanks
-
If you use the BasicDataLogger2, it has a PauseLogging property. Otherwise the best way would be to create a new driver instance to be used solely for the BasicDataLogger. After adding the new driver to the form, set the ComComponent property of the data logger to that driver. Then you can use DisableSubscriptions property of the driver instance.
-
Ok I have the files created being created the way I need. Now I need to populate the first row to create the units for the data below. I thought about a header but thought it would be just as easy to write to the first cell of each column with the "units'. Such as column 1 is KW, column 2 as KVAR etc... I added this code but get the error attached. What am I doing wrong? Thx
Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
Try
Dim sr As New System.IO.FileInfo("C:\DSLC2View\Logs\05Nov2015_08.34.20_GEN1 DATALOG")
EP = New OfficeOpenXml.ExcelPackage(sr)
EP.Workbook.Worksheets(1).Cells(1, 1).Value = "KW"
EP.Workbook.Worksheets(1).Cells(1, 2).Value = "KVAR"
EP.Save()
Catch ex As Exception
MsgBox("Failed to write. " & ex.Message)
End Try
End Sub
-
I have found that this works if I write to an .xlsx file not a .csv file. How do I modify this code to do the same thing to a .csv file?
Private EP As OfficeOpenXml.ExcelPackage
Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
Try
Dim sr As New System.IO.FileInfo("C:\DSLC2View\Logs\GEN1_DATALOG.xlsx")
EP = New OfficeOpenXml.ExcelPackage(sr)
EP.Workbook.Worksheets(1).Cells(1, 1).Value = "KW"
EP.Workbook.Worksheets(1).Cells(1, 2).Value = "KVAR"
EP.Save()
Catch ex As Exception
MsgBox("Failed to write. " & ex.Message)
End Try
-
Archie, seems I'm having big trouble with a little problem. I finally was able to create a new datalogger file as I want it except for the following issue:
I want to populate the first row of the columns with the units for the data below it, like seen in "correctpic"
The trouble I have is the first line of data is shifted as seen in "wrongpic"
I see why it is by the code seen in "code" but don't know how to correct it and make the first row of data start at column 1.
??? Is there a easier or better way to put these "units" in the first row?
??? Am I missing a field of the DataLogger that does this? ....Prefix?
I feel like I'm making it harder than necessary. Please help, thanks.
-
Mr. Pike,
Try changing your w.Write statement to this:
w.Write("TIME, KW, KVAR, VAB, VBC, VCA, IA, IB, IC, HZ" & Chr(13) & Chr(10))
This adds a Carriage Return and Line Feed to your write to get down to the next line.
Doug Lyons
-
Thank you, I was able to correct this by replacing w.write with w.writeline(). This command issues a line break and the datalogger will begin on the next line. Thank you for the reply. I'm learning slowly LOL
-
Does anyone know if writing text to the .csv as column headers can be set to the property bindings of the Datasubscriber so that the code would not be necessary? Thank you