writing to excel

[MrPike]

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!

[dmroeder]

Archie posted this at PLC talk.  I think it's more towards writing from excel to the PLC, but it should get you started.

[Archie]

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.

[MrPike]

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.

[Archie]

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

[MrPike]

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.

[Archie]

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.

[MrPike]

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

[MrPike]

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 

[Archie]

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.

[MrPike]

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

[Archie]

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.

[MrPike]

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. 

[DougLyons]

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.

[MrPike]

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.