Purpose

The purpose of this how-to is to familiarize you with the process of automating Excel using the MC.

Prerequisities

Visual C# Express (free) or Visual Studio http://www.microsoft.com/express/download

MS Office/Excel Preferably 2000+

makingthings C# library http://sourceforge.net/project/downloading.php?groupname=makingthings&filename=dotnet.zip&use_mirror=internap

available ethernet port on PC (or router/switch)

Set Everything Up

First make sure you have your environment set up correctly.

1. You should be able to communicate via ethernet with the make controller, establish this connection using mcHelper. If you don't have a switch or router, you may need to connect the MC directly to your PC. See this tutorial: http://www.makingthings.com/documentation/tutorial/direct-ethernet-communication/tutorial-all-pages

2. The next major step is to verify that your MS Office install supports .Net programmability. Doing this will ensure that you can automate excel from Visual Studio. To verify this, pop in your office disk and hit the “add/remove features” and then fill in the advanced customization check. It should show you some collapsed boxes, open the Excel one and make sure the “.Net Programmability Support” is on “Run from My Computer”. If you think you may ever want to automate Word or Outlook, this would be the time to update those as well. Then finish the installation Excel will talk to C#..  If you only did a partial install, the PIAs will not be installed.

3. Make sure you have downloaded the .Net library from makingthings as shown above and know its location on your PC.

4. Create an excel file called "KillerApp.xls" and put it directly under the C:\

Time to program:

1. Open Visual C# and start up a new Windows Forms Application. Be sure to give it an appropriate name for the project you're working on. i.e. "KillerApplication"

2. Now that your program is open you'll see a blank form that you can draw cute buttons and controls on. This will be what your user sees when they are running your program.

3. Add the references to the components you'll be using in your project. Make sure you can see the "Project Explorer" it is like an outline of your project that may appear on the right side. Right click on the "references" tab so you can add a reference to:

      a. Excel: Go to the COM tab, this will show you all the preloaded components you can use. Scroll down to Microsoft Excel Object Library(whatever version you have (Excel 2003 is 11.0). If this option doesn't show up. That means you don't have .Net programmability in your install of Excel, so get your MS Office cds out and go back to the Setting Everything Up Step 2.

      b. MakingThings: Go to the "browse" tab and browse to the "\dotnet\MakeControllerOsc\bin\release\MakeControllerOsc.dll". Click OK and this will be added to your project.

4. USING: If your form is still visible, you'll want to see the code inside your program and tell it that we're going to actually use the things we just added. Double click on your form, which will take you into the code of your project. Scroll to the top of the screen and you'll see the "using" block. Add the following lines to the code and make sure you get it absolutely correct. It may help to let IntelliSense help you fill this in, as that will help the program recognize it better. I use IntelliSense whenever possible.

using Microsoft.Office.Interop.Excel;
using MakingThings;

5. Now your application will realize that you actually want to use Excel and the MC. So you need to call up the application when you start up the program.

Create public variables that can be accessed by your entire application by placing them just under where it says  Form1 : Form{

   public static Microsoft.Office.Interop.Excel.Application xlAPP = new Microsoft.Office.Interop.Excel.ApplicationClass();//creates an application variable to fill
public static Microsoft.Office.Interop.Excel.Workbook xlWB;//creates an empty workbook variable to fill
public static Worksheet xlWS;//creates an empty worksheet variable to fill

6. This may also be a good place to put in your variables for Udp Controller Communication

    public static UdpPacket udpPacket = new UdpPacket();  
    public Osc oscUdp = new Osc(udpPacket);

7. Lets also make a public variable that is an array that we can drop all of our xbee values into. We're breaking encapsulation by making it public,  but this is meant to be a basic tutorial.

Int32[] xBeeArray = new Int32[11];//an array with 11 slots in it to hold each xbee value i.e. address - DI8

8. Inside the routine "public Form1()" it will be time to set up what happens when your application starts. You'll want to tell the program where to find the microcontroller, where to find the excel spreadsheet and any other things you want to happen when you start up. Here is an example of what you might find in this routine:

InitializeComponent();//this should be at the beginning, it loads up your form so that you can see it      
//INITIALIZE MICROCONTROLLER
//tell program what IP to look for the controller
            udpPacket.RemoteHostName = "192.168.0.200";
            udpPacket.RemotePort = 10000;
            udpPacket.LocalPort = 10000;
            udpPacket.Open();
            
//Open the Harness Machine Excel worksheet that should be on the C drive

                xlAPP.Visible = true;
                xlWB = xlAPP.Workbooks.Open("C:\\KillerApp.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, false, true, true);
                xlWS = (Worksheet)KillerApplication.Form1.xlAPP.Sheets[1];

//Tell the program what to do with the incoming debug messages
//we'll be listening for Xbee Messages in this program, so send them to "XbeeMessage"
               oscUdp.SetAddressHandler("/xbee/io16", XbeeMessage);

9. Now you'll need to create the actual routine that will manage the incoming message from the xbee. Start by creating a callback delegate, this will be used later in order to manage threading. Place the following just below the routine we were working in a moment ago.

delegate void IndicatorCallbackInt(int value);

Now it's time to separate the values from the xbee out into an array that we can use in our program, here we're creating the routine that will be called whenever we get a new message from xbee. You'll notice that it accepts an osc message:

        private void XbeeMessage(OscMessage oscM)
        {//here we separate out the values, change them from values to integers & load them into an array
            for(int i = 0; i <= 10 ; i++)//cycle through the array from 0 to 10
            {
            xBeeArray[i] = (int)oscM.Values[i];
            }
            Xbee16Indicator(1);
        }

10. Now we'll make the routine that actually uses the values we just separated out. As you may have noticed, it's called Xbee16Indicator() and it accepts an integer value, here's what it will look like, the first half of the routine handles threading, which we won't get into, but it's necessary. Your routine should look something like this:

public void Xbee16Indicator(int value)
        {

                try//try makes it so your program won't crash if it hears a value from the controller while closing
                {
                    if (this.InvokeRequired)//this is to call up a thread that will allow us to use our message to manage this program
                    {
                        IndicatorCallbackInt d = new IndicatorCallbackInt(Xbee16Indicator);
                        this.Invoke(d, new object[] { value });
                    }
                    else
                    {

                        Form1.xlWS.Cells[2, 6] = Convert.ToString(xBeeArray[0]);//2 is the row number, 6 is the column number
                       
                        barXBEEstrength.Value = xBeeArray[1];//update a progress bar with the xbee strength value
                        Form1.xlWS.Cells[3, 6] = Convert.ToString(xBeeArray[1]);//put the strength into excel
                       
                        barXBEE0.Value = xBeeArray[2];//update a progress bar
                        Form1.xlWS.Cells[4, 6] = Convert.ToString(xBeeArray[2]);//pass to excel

                        barXBEE1.Value = xBeeArray[3];
                        Form1.xlWS.Cells[5, 6] = Convert.ToString(xBeeArray[3]);

                        barXBEE2.Value = Convert.ToInt32(xBeeArray[4]);
                        Form1.xlWS.Cells[6, 6] = Convert.ToString(xBeeArray[4]);
 
                        barXBEE3.Value = Convert.ToInt32(xBeeArray[5]);
                        Form1.xlWS.Cells[7, 6] = Convert.ToString(xBeeArray[5]);

                        barXBEE4.Value = Convert.ToInt32(xBeeArray[6]);
                        Form1.xlWS.Cells[8, 6] = Convert.ToString(xBeeArray[6]);

                        barXBEE5.Value = Convert.ToInt32(xBeeArray[7]);
                        Form1.xlWS.Cells[9, 6] = Convert.ToString(xBeeArray[7]);

                        barXBEE6.Value = Convert.ToInt32(xBeeArray[8]);
                        Form1.xlWS.Cells[10, 6] = Convert.ToString(xBeeArray[8]);

                        barXBEE7.Value = Convert.ToInt32(xBeeArray[9]);
                        Form1.xlWS.Cells[11, 6] = Convert.ToString(xBeeArray[9]);

                        barXBEE8.Value = Convert.ToInt32(xBeeArray[10]);           
                        Form1.xlWS.Cells[12, 6] = Convert.ToString(xBeeArray[11]);

                    }
                }
                catch
                { }
        }

Notice that near the end of the routine I'm actually calling up KillerApplication.Main.xlWS which is my Excel sheet, and then I'm putting the values into the cells indicated. You can also pull information in from Excel, To do so you would use something like:

AddressNumber = Convert.ToInt32((string)((Range)MAIN.xlWS.Cells[12, COLUMN]).Text);//pull in a value and assign it to the variable AddressNumber

Refine Your Program

At this point you may decide you'd like to put a timestamp on each entry and have that show up in excel, all you'll need to do is use the time function, and then pass that time into excel using the same methods.

Keep in mind that you are working with Excel at all times in this program, You may want to consider putting in a command to close excel when the program closes so that you won't have excel running all the time.

You can also send OSC messages to the controller through Visual C#, here is an example of how you would do so:

//This is the routine to which you will send all of your servo commands, send an index to say which servo to move, then a value to move it to
        public void ServoSet(int index, int value)
        {
            OscMessage oscMSV = new OscMessage();
            oscMSV.Address = "/servo/" + index.ToString() + "/position";
            oscMSV.Values.Add(value);
            oscUdp.Send(oscMSV);
        }

//Call the routine using this line, with a servo index and a value
        ServoSet(3, ServoPos);

Considerations

If your program crashes before you get to the xlAPP.visible = true command, your excel application will be invisible, and you'll have to go into the task manager and close out the EXCEL process manually.

You may want to consider the maximum sheet size in excel as well as your autosend time, you may run out of space quick if you don't have the proper code implemented. Such code might create new sheets when the page fills up, or alert the user etc. It could also send a message to your MC telling it to slow down the autosend speed

By default, the 2008 versions of Visual Studio target the .Net framework 3.5, which can be a pain if you want to install your program on somebody else's computer who doesn't have the update, you can change it to the .Net 2.0, which is more common by going into Properties, the Application tab and changing the target framework.

Further information

http://www.makingthings.com/documentation/tutorial/dot-net/tutorial-all-pages

I wrote an application that tracks xbee modules into Excel. The application is source only. http://makingthings.com/Members/tunell/sensortracker/

Microsoft MSDN

Visual Studio Help is an excellent tool for learning how to automate Excel