Exporting data from Silverlight to Excel

Exporting data from Silverlight to Excel

Ill say it and I think you, dear reader, will agree with me, business people like to use Excel. It provides them the tools they need to manipulate data like no other desktop application can. I recently worked on a Silverlight project that required exporting data that was displayed in a Silverlight grid to Excel. This post will describe how it was done.

 

The Data

For the purposes of this post, I’ve changed the data source to the Northwind DB. I think I can say with confidence that my client wouldn’t want me posting their internal data on the web so I’m going with Northwind.

For this post, the data being displayed and exported will show Customer data and how long it took in days to ship each of their orders. We’ll also include the company name, contact name and phone.

 

The Project

To get started, I created a SIlverlight project and selected to have an ASP.NET web site created to host the site.

 

createProject createSite

 

        

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The Service

Considering that the Silverlight BCL doesn’t have the ability to do direct database access, we’ll be using LINQ to SQL and a WCF service to return the data that we want to display and export. In order to get access to Northwind via LINQ to SQL, I added LINQ to SQL classes.

 

 

CreateLinqToSql

 

and Dragged the two tables, Customers and Orders, onto the LINQ to SQL designer.

 

add_L2S_Classes

 

Next I added a Silverlight enabled WCF Service to my web app and created the following class and method.

 

 

namespace Export.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class NorthwindService
    {
        [OperationContract]
        public List<ShipRecord> GetShippingReport()
        {
            using (NorthwindDataContext ndc = new NorthwindDataContext())
            {
                var query = from c in ndc.Customers
                            join o in ndc.Orders on c.CustomerID equals o.CustomerID
                            where o.ShippedDate != null
                            select new ShipRecord {   CompanyName = c.CompanyName, 
                                                                ContactName = c.ContactName, 
                                                                Phone = c.Phone, 
                                                                OrderID = o.OrderID, 
                                                                DaysToShip = o.ShippedDate.Value.Subtract(o.OrderDate.Value).Days };

                return query.ToList();
            }
            
        }

        
    }

    public class ShipRecord
    {
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string Phone { get; set; }
        public int OrderID { get; set; }
        public int DaysToShip { get; set; }
    }
}

 

 

I created a custom class here to hold the data that I’m going to pass back to the SIlverlight client.  I also created one method that will get the data, GetShippingReport().

Within the GetShippingReport() method body, I use a Linq query here to populate a List<> and return it to the caller.

The last setup piece that’s required is to create a service reference within the Silverlight Project so we can access the WCF service. In order to do that I right clicked on References in the SL project and selected Add Service Reference. When presented with a dialog, clicked discover and chose the Northwind service that was discovered.

 

serviceReference

 

 

The UI

Let have a quick look at the user interface that we’ll provide for our users.

 

UIShot

 

 

Now that the necessary services are in place, lets look at the actual export. I suspect that most of you reading this blog probably have the latest and greatest version of office on your machine but this isn’t the case regarding to my client. They’re still working with Excel 2003 as I suspect many businesses are. Luckily, Excel 2003 does have XML support. I’m going to demonstrate 2 ways to do this export.

First, Ill create an XML template from within Excel that contains the columns that Ill be exporting. Then, Ill save that template and modify it so I can programmatically insert the rows.

Second, Ill create just a plain old XML document.

The reason I’m showing the two options is this: if I just created a plain old XML document, when the user double-clicks on it after export, it would more then likely open in a browser. I suspect this would confuse many users who would be expecting Excel. By creating an Excel template and exporting to that, when the user double click, it opens in Excel.

Lets look at using the Excel template first.

I opened Excel 2007 and put the columns in that I wanted to export as shown here.

 

templateSetup

 

I selected “Save as” being sure to save it as a XML Spreadsheet 2003 file.

 

exportSaveAs

 

With the file saved, I opened it up in NotePad and modified the 2 highlighted sections as shown here.

 

 

templateOrig 

original

 

templateMod

modified

 

 

As you can see above, I replaced values in 2 places: ss:ExpandedRowCount as well as the <Row> element where I had entered the sample data. I replaced the data with 2 tokens, [ROWCOUNT] and [ROWDATA],  that Ill swap out later when I have the real data.

I saved the file as ShipDelayTemplate.xml and added it to my Silverlight project being sure to set its Build Action in the Properties panel to Content.

WIth that complete, we are ready to look at the code.

To get the data into the DataGrid, I added a Loaded event handler that makes a call to the WCF Service and sets up the callback method. When the service returns and the callback is executed, I check to see if an error has occurred. If not, we set the ItemsSource for the DataGrid to the value that’s contained in e.Result.

 

public MainPage()
        {
            InitializeComponent();
            Loaded += MainPage_Loaded;
        }

        void MainPage_Loaded(object sender, RoutedEventArgs e)
        {
            Northwind.NorthwindServiceClient client = new Export.Northwind.NorthwindServiceClient();
            client.GetShippingReportCompleted += client_GetShippingReportCompleted;
            client.GetShippingReportAsync();
        }

        void client_GetShippingReportCompleted(object sender, Export.Northwind.GetShippingReportCompletedEventArgs e)
        {
            if (e.Error != null)
            {
                MessageBox.Show(e.Error.ToString());
            }
            else
            {
                dgShipReport.ItemsSource = e.Result;               
            }
        }
 
 

The Export….Finally!

The data is now being happily displayed on the grid in the page. Suddenly, from out of no where, the user wants to put this data into Excel!! Good thing we saw that coming!

You probably noticed the 2 buttons on the form (not what we would do in a real app but come one….work with me). Both buttons fire the same event handler. Lets look at that code.

 

private void btnExport_Click(object sender, System.Windows.RoutedEventArgs e)
{
    string data = String.Empty;
    SaveFileDialog sfd = new SaveFileDialog();
    sfd.DefaultExt = "xml";
    sfd.Filter = "XML Files | *.xml";

    bool? resp = sfd.ShowDialog();

    if (resp == true)
    {
        using (Stream fs = (Stream)sfd.OpenFile())
        {
            //get the data from the grid so we can format it into xml
            IEnumerable<ShipRecord> recs = dgShipReport.ItemsSource as IEnumerable<ShipRecord>;

            //determine what type of xml to create based on the tag property of the button that fired the event
            Button btn = sender as Button;

            if (btn.Tag.Equals("OfficeXML"))
                data = CreateOfficeXML(recs);
            else
                data = CreateXML(recs);

            //write it out
            byte[] info = new System.Text.UTF8Encoding(true).GetBytes(data);
            fs.Write(info, 0, info.Length);
            fs.Close();
        }
    }
}

In the handler above, we use the SaveFile Dialog (new to SL3) and present the user with the dialog. If the user chooses to save the file, we grab the data from the datagrid as an IEnumerable of our custom type, ShipRecord. Then we determine which button was pushed by checking the Tag property on the sender object (which we cast to a Button) and call the appropriate method to produce the XML.

In the CreateXML method, we use Linq to Sql to dynamically create an XML document on the fly. You will need to add a reference to System.Xml.Linq to gain access to XElement. I encourage you to check out the docs for the System.Xml.Linq namespace to see what’s possible.

private string CreateXML(IEnumerable<ShipRecord> records)
{
    var shipDelay =
    new XElement("ShipRecords",
        from record in records
        select
                    new XElement("ShipRecord",
                        new XElement("CompanyName", record.CompanyName),
                        new XElement("ContactName", record.ContactName),
                        new XElement("Phone", record.Phone),
                        new XElement("OrderID", record.OrderID),
                        new XElement("DaysToShip", record.DaysToShip)));
    
    return shipDelay.ToString();
}

And finally, in the CreateOfficeXML method, I use a StringBuilder to create the rows that I want to insert into my previously created XML template. Once I have the rows created, I use Application.GetResourceStream to open the saved template and simply use the String.Replace method to insert the new values where I had put the tokens earlier.

private string CreateOfficeXML(IEnumerable<ShipRecord> records)
{
    int count = records.Count() + 1;
    StringBuilder sb = new StringBuilder();

    foreach (var rec in records)
    {
        sb.AppendLine("<Row>");
        sb.AppendFormat("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", rec.CompanyName);
        sb.AppendFormat("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", rec.ContactName);
        sb.AppendFormat("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", rec.Phone);
        sb.AppendFormat("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", rec.OrderID);
        sb.AppendFormat("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", rec.DaysToShip);
        sb.AppendLine("</Row>");
    }

    StreamResourceInfo sri = Application.GetResourceStream(new Uri("ShipDelayTemplate.xml", UriKind.Relative));
    var sr = new StreamReader(sri.Stream);

    string data = sr.ReadToEnd();
    data = data.Replace("[ROWCOUNT]", count.ToString());
    data = data.Replace("[ROWDATA]", sb.ToString());
    sr.Close();

    return data;
}

 

Wrapping up

I hope this has been a helpful look at how to export data to Excel from a SIlverlight application. Please post any comments or questions.

You can download the source code here (1.27mb)

Thanks for visiting.

12 Replies to “Exporting data from Silverlight to Excel”

  1. Great article, I really appreciate how you show everything thats needed rather then skipping steps which is a common occurance with these kinds of articles. Great job and thanks again

  2. Error in download the source code.Please allow to download the source code with out error 404.

  3. What is the change to be done if there exists a Hyperlink Column on the Grid. How can i retrieve the Text of the Hyperlink Column?

  4. Hi Pedja,
    Thanks for the comment but I fail to see how the data transfer mechanism is an issue when the end result for the user is an Excel document.

Leave a Reply

Your email address will not be published. Required fields are marked *

Get Adobe Flash player