Silverlight Export to Excel

The latest thing I’ve been working on is exporting to Excel from my Silverlight Indicator.

With Silverlight 4 there are two problem domains, In Browser and Out of Browser.

The Out of Browser mode is dead easy using Automation Factory. For it to function it does require the Silverlight App to be locally installed with elevated permissions. There are several examples out there, on the web, of this type of implementation now I leveraged the approach posted by Pradeep at c-sharecorner.com for my application, see his code below. This is a great solution that opens the exported data in an excel spreadsheet.


Solution using Out of Browser Mode
Add reference to Microsoft.CSharp.dll to the project.


using System.Runtime.InteropServices.Automation;
…

///

///This code has been leveraged from the following site:
///http://www.c-sharpcorner.com/UploadFile/pchandraker/1899/
///
///

private void btnExport_Click(object sender, RoutedEventArgs e)
{
dynamic excelApp;
excelApp = AutomationFactory.CreateObject("Excel.Application");
excelApp.Visible = true;
dynamic workbook = excelApp.workbooks;
workbook.Add();
dynamic sheet = excelApp.ActiveSheet;
dynamic cell = null;
int index = 1;
foreach (Employee emp in EmployeeGrid.ItemsSource)
{
cell = sheet.Cells[index, 1];
cell.Value = emp.EmployeeName;
cell = sheet.Cells[index, 2];
cell.Value = emp.EmployeeId;
cell = sheet.Cells[index, 3];
cell.Value = emp.Department;
index++;
}
}

Reference links –
Automation API - http://msdn.microsoft.com/en-us/library/ff457794(v=VS.95).aspx
More documentation - http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.automation.automationfactory(VS.95).aspx
A slightly different implementation - http://www.codeproject.com/KB/silverlight/SL4InteropWithExcel.aspx
A similar implementation - http://deepumi.wordpress.com/2010/06/01/export-datagrid-to-excel-in-silverlight-4-0/

Solution using In Browser Mode

With the Out of Browser Domain covered, I needed to tackle the In Browser Domain. To my surprise there wasn’t as many solutions as I thought there would be out there. Exporting a datagrid to excel would seem like a common enough task. Though I did find one gem in the rough Raelango on codeproject.com had posted a nice solution that works great if the columns are TextColumns or are TextBlocks in a template column. It defiantly got me pointed in the right direction. You can see his solution here: http://www.codeproject.com/KB/silverlight/SilverlightDataGridExport.aspx

I took a similar approach though I ended up customizing the code to fit my customized data types. It led to a nice solution which saves to an XML or CSV file based on the user choice through a standard save dialog.

Libraries for Client Object Model

Ok this falls in to information I’ve had to look-up a couple times now. So I’m posting this to my blog for quick reference in the future.

Libraries required for SharePoint development using Client Object Model

The two files for Silverlight are:
Microsoft.SharePoint.Client.Silverlight.dll
Microsoft.SharePoint.Client.Silverlight.Runtime.dll

They can be located on the server at %ProgramFiles%\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\ClientBin

For sandbox solutions you need:
Microsoft.SharePoint.Client.dll
Microsoft.SharePoint.Client.Runtime.dll

These are located on the server at %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\ISAPI

Getting the URL Parameter Using C#

As I’ve been working with the SharePoint COM API in Silverlight more things that were really hard to do or nearly impossible to do with SharePoint Designer (SPD) have become easier. One of the first things I need to do was link the Silverlight applications and my existing pages together which I felt was really easy to do by passing Query String Parameters.

This is really easy to do in C# with the Windows.Browser namespace.

Here is the method I use to accomplish this.

private string getURLParam(string key)
{
string value = "";
HtmlPage.Document.QueryString.TryGetValue(key, out value);
return value;
}

Now I can provide whatever key I would like to consume, in my case a row ID so I know which record to query in the SharePoint list.

Alien Earth Review

Alien Earth by Megan Lindholm

Overall Grade C

This book takes place in a dystopian future long after the earth had been abandoned due to humans killing the planet. Humans are assisted in leaving the planet by the Arthropiana who travel in Beastships which are capable of interstellar travel.

The story is centered on a human crew of the Beastships who are traveling back to earth and the interaction with the Arthropiana co-captain.

Unfortunately the story seemed fairly predictable from plot to the characters, though the character development was paced ok. The one thing I liked, even though I knew how it was going to end by about halfway through, was the ending I felt it really delivered though getting there took a very predictable path.

http://www.meganlindholm.com/novels/alien-earth/