Onyx Ind

Hello-

I've created a small VSTO C# 2005SE Application that uses the Action Pane as a Graphical Interface allowing Production Machine operators to load data from an MSExcel2003 Spreadsheet into a PLC (Dedicated device that controls machine functions) over a Synchronous Socket.

Socket Comm

As a machine controls guy, I am a bit out of my element... but so far, so ok. All data transfer seems reliable. The Spreadsheet on event sends out a string, like "R601=45\r" and then code waits for an acknowledgment of "\r" back from the external controller if successful or " \r" if unrecognized but connected. The bulk of the work is done with this:

do

{

bytesRecieved = handler.Receive(bytes);

response += Encoding.ASCII.GetString(bytes, 0, bytesRecieved);

} while (!response.Contains("\r") || response.Contains(" "));

For best practice, is that good enough or is there a way to add a time out to the data response Meaning if the External controller doesn't respond in x milliseconds then assume severed connection or whatever. If I pull the ethernet cable as part of the commissioning test, everything pretty much freezes at the last line of above code.

Also, is there a way to change formatting of the spreadsheet in the VS IDE I try to open it in the IDE and the spreadsheet shows up for a second then goes away in favor of the following message:

This is the workbook designer. To Add components to the workbook, drag them from the Server Explorer or Toolbox and use the Properties window to set their properties. To create methods & events for the workbook, click here to switch to code view.

I want to get at the standard formatting functions only. Not code them.

Thanks in advance...

Lee



Re: Visual Studio Tools for Office VSTO Excel Formatting & Socket Communications ?

Phil Hoff - MSFT

There are a couple of concerns with this code as-is:

  1. If this code is located in an event handler for, say, a button click then you will be blocking the main UI thread while waiting for the response from the PLC. This means that Excel will be unresponsive until that occurs.
  2. There is no timeout or cancellation logic, which means that if the PLC is unresponsive then the handler will never exit and, since this means that Excel will remain unresponsive, no way for the user to get themselves out of the situation.

You will likely want to perform the socket I/O asynchronously, either through the Socket class itself, or by performing the I/O synchronously but on another thread (either a dedicated thread or the thread pool). The BackgroundWorker class may be of particular use here (http://msdn2.microsoft.com/en-us/library/8xs8549b.aspx). It allows one to pass a unit of work off to another thread and then be notified on the original thread when the work is complete. It also allows for the cancellation of the operation.

As for your second question, are you selecting the workbook or a worksheet in the solution explorer An Excel workbook does not have a designer of its own (beyond exposing a simple component tray). If you want to edit and format cells, for example, you should select one of the worksheets in the solution (e.g. Sheet1.cs, Sheet2.cs, Sheet3.cs, etc.)

-Phil





Re: Visual Studio Tools for Office VSTO Excel Formatting & Socket Communications ?

Onyx Ind

Phil-

Thanks for the input. I appreciate it... I missed the affect of blocking on Excel in researching this. It wasn't part of the original plan, however, I will be adding a timer driven event that will poll the controller for a couple of data elements once every second. The timer will be disabled when the button event that downloads machine recipe setups is activated- I believe that will eleminate possible conflicts between write-outs and write-out-commands-to-read-back data. It appears that changing from Synchronous to Asynchronous could keep this relatively short program as a single thread, which is desirable from my standpoint. Anything you think I should be aware of related to that Thanks in advance...





Re: Visual Studio Tools for Office VSTO Excel Formatting & Socket Communications ?

Phil Hoff - MSFT

Just be aware that, if you're using a WinForms Timer, handlers for the Tick event will still be executed on the main UI thread; the potential for blocking Excel remains. Also, since they use the windows message pump, WinForms timers aren't as accurate as other timers available in the .NET; their advantage is precisely that their handlers are executed on the main UI thread.

Unless your timeouts are extremely short, I would avoid doing any synchronous I/O on the main UI thread. I use to work in factory automation for a major semiconductor manufacturer. Our equipment communications protocols had timeouts anywhere from 30s to 120s, which meant that synchronous I/O on the main UI thread was completely inpractical.

-Phil





Re: Visual Studio Tools for Office VSTO Excel Formatting & Socket Communications ?

Onyx Ind

Got it. The watchdog on these Ctc Controllers is fixed at 85ms and lots of testing shows the comm strategy is stable with it's watchdog. I am going to change comms connection to Asynchronous and look at options with Timer triggered polling- maybe they will have to poll status as a manually initiated event or a system ms timer trigger loop in using the background function you referenced. Thanks very much for your input!