Edit records in a LightSwitch Grid directly from Excel

Alright, welcome back - couple things before I start with this first "code sample" blog:

1. All code samples are going to be in C#
2. If you have ways to improve the code, and I'm guessing many of you do, by all means - enter a comment with a better sample.
3. I always use the three-tier desktop client (which is easily the best option if it is available to be used), so my Silverlight client examples will almost always be geared towards that. 

With that out of the way, one of the things I'd been trying to do for some time was allow users to update LightSwitch records in Excel.  One way was to do something like what Michael Washington blogged about here about a year ago using a custom control from ComponentOne.  This was a great article and idea, but more powerful than really I needed for the users I support.  They kept asking for an easy way to transfer notes into records from their local Excel files or to do updates to multiple records at the same time doing some sort of copy and paste.

Anyway, I found out there are some cool things you can do with Silverlight (which is surprising because Microsoft seems so anxious to kill it off - Zing!) that would allow me to do this using Excel around COM automation.  Beth Massi's post here shows some simple examples of this in Lightswitch, but the vast majority of articles I ended up using to help me figure this out were a couple years old and used Silverlight code that needed to be adjusted somewhat to make it work in LightSwitch.

So, to demo this functionality and how I used it, I'm going to make a simple contact table in Lightswitch (I know, you shouldn't use Double for an age column.  Just humor me)  -

Then a quick Editable Grid screen where I put some dummy records

At this point, I need to do a couple of things -

1. Add a button to the grid to open Excel
2. Bring over the records from the grid
3. Make sure any changes I make in Excel get reflected back in LightSwitch.

I'm going to assume you know how to do item 1.  For item 2, the articles I linked to above show how to do that piece very nicely so I can make some subtle changes but use much of the code they demonstrated.  Please note if paging is turned on, it will only bring over the first page (but frankly, I wouldn't recommend doing this with more than 100 records maximum) -

using System;
using System.Linq;
using System.IO;
using System.IO.IsolatedStorage;
using System.Collections.Generic;
using System.Windows;
using System.Threading;
using System.Runtime.InteropServices.Automation;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Framework.Client;
using Microsoft.LightSwitch.Presentation;
using Microsoft.LightSwitch.Presentation.Extensions;
namespace LightSwitchApplication
{
    public partial class Contact_Grid
    {
        partial void EditInExcel_Execute()
        {           

Microsoft.LightSwitch.Threading.Dispatchers.Main.BeginInvoke(() =>
                {
dynamic excel = 
System.Runtime.InteropServices.Automation.AutomationFactory.
CreateObject("Excel.Application");
                    excel.Visible = true;  // make it visible to the user.
 
                    dynamic workbook = excel.workbooks;
                    workbook.Add();
 
                    dynamic sheet = excel.ActiveSheet; 
// get the active sheet
                    dynamic cell = null;
                    int i = 1;
 
                    // iterate through our data source and populate the excel spreadsheet
     
                    foreach (Contact item in Contacts)
                    {
                        cell = sheet.Cells[i, 1]; // row, column
                        cell.Value = item.Id;
                        cell.ColumnWidth = 10;
 
                        cell = sheet.Cells[i, 2]; // row, column
                        cell.Value = item.First_Name;
                        cell.ColumnWidth = 25;
 
                        cell = sheet.Cells[i, 3];
                        cell.Value = item.Last_Name;
                        cell.ColumnWidth = 25;
 
                        cell = sheet.Cells[i, 4]; // row, column
                        cell.Value = item.Age;
                        cell.ColumnWidth = 10;
 
                        i++;
                    }

 
                    bool firstTime = true;
                    if (firstTime)
                    {
                        excel.SheetChange += new 
SheetChangedDelegate(SheetChangedEventHandler);
                        string sheetName = sheet.Name;
                        firstTime = false;
                    }
                });
        }

Notice I export the record ID, even though I'm not showing it on the grid - 
that's because I basically had to piece together concepts from the two articles 
I linked to earlier to get it working.  Binding to the right records is key - 
if your ID's are synched up with with the row numbers like mine are in this little example,
great, but that's not going to be the case the vast majority of the time.  
So you need to make sure you are binding back to something (or multiple things) that keep the link 
between the records.

        delegate void SheetChangedDelegate(dynamic excelSheet, 
dynamic rangeArgs);
        private void SheetChangedEventHandler(dynamic excelSheet, 
dynamic rangeArgs)
        {
            dynamic sheet = excelSheet;
            string sheetName = sheet.Name;
            dynamic range = rangeArgs;
            dynamic rowValue = range.Row;
 
IEnumerable<Contact> con = Contacts as IEnumerable<Contact>;
            int len = con.Count();
            dynamic col1range = sheet.Range("A1:A" + len.ToString());
            dynamic col2range = sheet.Range("B1:B" + len.ToString());
            dynamic col3range = sheet.Range("C1:C" + len.ToString());
            dynamic col4range = sheet.Range("D1:D" + len.ToString());
 
            for (int i = 0; i < len; i++)
            {
                dynamic item1 = col1range.Item(i + 1);
                dynamic item2 = col2range.Item(i + 1);
                dynamic item3 = col3range.Item(i + 1);
                dynamic item4 = col4range.Item(i + 1);
 
                foreach (Contact item in Contacts)
                {
                    if (item.Id == item1.Value)
                    {
                        item.First_Name = item2.Value.ToString();
                        item.Last_Name = item3.Value.ToString();
                        item.Age = item4.Value;
                    }
                }
            }
        }


So even though I exported the record ID, I skip it when I am allowing updates back,
since the system won't allow that anyway.

Now because of the way the dynamic calls work, you won't get certain Intellisense errors
if you screw something up.  You only find out at runtime - just keep that in mind.
So if I go ahead and run my project now, I see an "Edit in Excel" button on my grid now -




Once I click that, Excel opens up, the code cycles through the records and I get the following - 

Now I can make changes and see if worked.  Let's copy just the name Buddy Lee to all three records - 

I go back and check my project - and it did!

I can hit save and I'm good to go.  And if I want to, as long as I keep that Excel open, 
I can keep changing the grid.
Now like I said earlier, this was fine for what I needed, but I don't have code in there to handle new
records if people want to add line items, or if they want to use a file in their My Documents folder 
instead that they re-use and open up instead of launching a brand new Excel sheet each time. 
Hope this helped someone out and please feel free to leave comments or questions as feedback 
if you think of ways this could be done more effectively.