Data export from DataGrid to Excel is very common task, and it can be solved with different ways, and chosen way depend on kind of app which you are design. If you are developing app for enterprise, and it will be installed on several computes, then you can to advance a claim (system requirements) with which your app will be work for client. Or customer will advance system requirements on which your app should work. In this case you can use COM for export (use infrastructure of Excel or OpenOffice). This approach will give you much more flexibility and give you possibility to use all features of Excel app. About this approach I’ll speak below. Other way – your app is for personal use, it can be installed on any home computer, in this case it is not good to ask user to install MS Office or OpenOffice just for using your app. In this way you can use foreign tools for export, or export to xml/html format which MS Office can read (this approach used by JIRA). But in this case will be more difficult to satisfy user tasks, like create document with landscape rotation and with defined fields for printing.

Integration with Excel from Silverlight 4 and .NET 4

In Silverlight 4 and .NET 4 we have dynamic objects, which give us possibility to use MS Office COM objects without referenced to MS Office dlls. So for creating excel document in .NET 4 you can write this code::

dynamic excel = Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty);

And in Silverlight 4 this:

dynamic excel = AutomationFactory.CreateObject("Excel.Application");

If you want to use AutomationFactory in Silverlight 4 app you need to set “Required elevated trust when running outside the browser” in project settings. You can check at code that your app have this privileges with property AutomationFactory.IsAvailable.

First, lets design new method, which will export to Excel two-dimension array:

public static void ExportToExcel(object[,] data) { /* ... */ }

Above I wrote how to get instance of Excel app. Now we will write some additional requirements for export:

excel.ScreenUpdating = false;
dynamic workbook = excel.workbooks;
workbook.Add(); 

dynamic worksheet = excel.ActiveSheet;

const int left = 1;
const int top = 1;
int height = data.GetLength(0);
int width = data.GetLength(1);
int bottom = top + height - 1;
int right = left + width - 1;

if (height == 0 || width == 0)
  return;

In this code we set that Excel will not show changes until we allow. This approach will give us little win in performance. Next we create new workbook and get active sheet of this book. And then get dimension of range where we will place our data.

Next step – export to Excel. When you export to excel with set data by cell this is slowly approach than export data to range of cells (you can try to compare speed of exporting with 1000 rows). So we will use setting data for range of cells:

dynamic rg = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[bottom, right]];
rg.Value = data;

Ok, our data in excel document. This approach work in .NET, but doesn’t work in Silverlight 4. When I tried to export data like I wrote above I got exception 

{System.Exception: can't convert an array of rank [2]
   at MS.Internal.ComAutomation.ManagedObjectMarshaler.MarshalArray(Array array, ComAutomationParamWrapService paramWrapService, ComAutomationInteropValue& value)
   at MS.Internal.ComAutomation.ManagedObjectMarshaler.MarshalObject(Object obj, ComAutomationParamWrapService paramWrapService, ComAutomationInteropValue& value, Boolean makeCopy)
   at MS.Internal.ComAutomation.ComAutomationObject.InvokeImpl(Boolean tryInvoke, String name, ComAutomationInvokeType invokeType, Object& returnValue, Object[] args)
   at MS.Internal.ComAutomation.ComAutomationObject.Invoke(String name, ComAutomationInvokeType invokeType, Object[] args)
   at System.Runtime.InteropServices.Automation.AutomationMetaObjectProvider.TrySetMember(SetMemberBinder binder, Object value)
   at System.Runtime.InteropServices.Automation.AutomationMetaObjectProviderBase.<.cctor>b__3(Object obj, SetMemberBinder binder, Object value)
   at CallSite.Target(Closure , CallSite , Object , Object[,] )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at ExportToExcelTools.ExportManager.ExportToExcel(Object[,] data)
   at ExportToExcelTools.DataGridExcelTools.StartExport(Object data)
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart(Object obj)}


For export in Silverlight I use this code (export by rows):

for (int i = 1; i <= height; i++)
{
  object[] row = new object[width];
  for (int j = 1; j <= width; j++)
  {
    row[j - 1] = data[i - 1, j - 1];
  }
  dynamic r = worksheet.Range[worksheet.Cells[i, left], worksheet.Cells[i, right]];
  r.Value = row;
  r = null;
}

If you are developing app just for Silverlight you can use some other data structure instead of array. I try to write code which will work at .NET and Silverlight so I will use arrays.

After data export we should to set to Excel object that it can apply changes, and then we will show it:

excel.ScreenUpdating = true;
excel.Visible = true;

Before this we can set more beautiful view of our document:

// Set borders
for (int i = 1; i <= 4; i++)
  rg.Borders[i].LineStyle = 1;

// Set auto columns width
rg.EntireColumn.AutoFit();

// Set header view
dynamic rgHeader = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[top, right]];
rgHeader.Font.Bold = true;
rgHeader.Interior.Color = 189 * (int)Math.Pow(16, 4) + 129 * (int)Math.Pow(16, 2) + 78; // #4E81BD

With this code we set borders, set auto size for cells and mark out first row (with background color and special style for text – it will be bold): it will be header, which will show DataGrid column’s headers. If you want to set more you can use Excel macros to get how to change document view: you need to start record macro, then change interface by hand, end record macro and look at macro code.

At the end of export you need to clean resources. In .NET for solve this you can use method Marshal.ReleaseComObject(…), but Silverlight doesn’t have this method, but we can set null to variables and then invoke garbage collector collect method:

#if SILVERLIGHT
#else
Marshal.ReleaseComObject(rg);
Marshal.ReleaseComObject(rgHeader);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excel);
#endif
rg = null;
rgHeader = null;
worksheet = null;
workbook = null;
excel = null;
GC.Collect();

So know we have this code:

using System;
#if SILVERLIGHT
using System.Runtime.InteropServices.Automation;
#else
using System.Runtime.InteropServices;
#endif 

namespace ExportToExcelTools
{
  public static class ExportManager
  {
    public static void ExportToExcel(object[,] data)
    {
#if SILVERLIGHT
      dynamic excel = AutomationFactory.CreateObject("Excel.Application");
#else
      dynamic excel = Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty);
#endif 

      excel.ScreenUpdating = false;
      dynamic workbook = excel.workbooks;
      workbook.Add(); 

      dynamic worksheet = excel.ActiveSheet; 

      const int left = 1;
      const int top = 1;
      int height = data.GetLength(0);
      int width = data.GetLength(1);
      int bottom = top + height - 1;
      int right = left + width - 1; 

      if (height == 0 || width == 0)
        return; 

      dynamic rg = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[bottom, right]];
#if SILVERLIGHT
      //With setting range value for recnagle export will be fast, but this aproach doesn't work in Silverlight
      for (int i = 1; i <= height; i++)
      {
        object[] row = new object[width];
        for (int j = 1; j <= width; j++)
        {
          row[j - 1] = data[i - 1, j - 1];
        }
        dynamic r = worksheet.Range[worksheet.Cells[i, left], worksheet.Cells[i, right]];
        r.Value = row;
        r = null;
      }
#else
      rg.Value = data;
#endif 

      // Set borders
      for (int i = 1; i <= 4; i++)
        rg.Borders[i].LineStyle = 1; 

      // Set auto columns width
      rg.EntireColumn.AutoFit(); 

      // Set header view
      dynamic rgHeader = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[top, right]];
      rgHeader.Font.Bold = true;
      rgHeader.Interior.Color = 189 * (int)Math.Pow(16, 4) + 129 * (int)Math.Pow(16, 2) + 78; // #4E81BD     

      // Show excel app
      excel.ScreenUpdating = true;
      excel.Visible = true;

#if SILVERLIGHT
#else
      Marshal.ReleaseComObject(rg);
      Marshal.ReleaseComObject(rgHeader);
      Marshal.ReleaseComObject(worksheet);
      Marshal.ReleaseComObject(workbook);
      Marshal.ReleaseComObject(excel);
#endif
      rg = null;
      rgHeader = null;
      worksheet = null;
      workbook = null;
      excel = null;
      GC.Collect();
    }
  }
}

Export data from DataGrid to two-dimension array

So we have method which export array to Excel, now we need to write method which will export DataGrid data to array. In WPF we can get all items with Items property, but in Silverlight this property is internal. But we can use ItemsSource property and cast it to List:

List<object> list = grid.ItemsSource.Cast<object>().ToList();

Before we realize export I want to think about features we need:

1. In some cases we want to export not all columns from data grid, so we need an approach to disable export some of columns.
2. In some cases columns don’t have header (text header), but in excel we want to see text header or header with other text than data grid have, so we need an approach to set header text for export.
3. It is easy to get which properties of object need to show in excel cell for columns with types inherited from DataGridBoundColumn because it has Binding with Path, with which we can get path for export value. But in case when we use DataGridTemplateColumn it is more hardly to find out which values of which property need to export. This is why we need an approach to set custom path for export (more we can use SortMemberPath).
4. We need to set formatting for export to Excel.


I solved all of this problems with attached properties:

/// <summary>
/// Include current column in export report to excel
/// </summary>
public static readonly DependencyProperty IsExportedProperty = DependencyProperty.RegisterAttached("IsExported",                                                                         typeof(bool), typeof(DataGrid), new PropertyMetadata(true));

/// <summary>
/// Use custom header for report
/// </summary>
public static readonly DependencyProperty HeaderForExportProperty = DependencyProperty.RegisterAttached("HeaderForExport",                                                                                typeof(string), typeof(DataGrid), new PropertyMetadata(null));

/// <summary>
/// Use custom path to get value for report
/// </summary>
public static readonly DependencyProperty PathForExportProperty = DependencyProperty.RegisterAttached("PathForExport",                                                                                typeof(string), typeof(DataGrid), new PropertyMetadata(null));

/// <summary>
/// Use custom path to get value for report
/// </summary>
public static readonly DependencyProperty FormatForExportProperty = DependencyProperty.RegisterAttached("FormatForExport",                                                                               typeof(string), typeof(DataGrid), new PropertyMetadata(null));

#region Attached properties helpers methods 
public static void SetIsExported(DataGridColumn element, Boolean value)
{
  element.SetValue(IsExportedProperty, value);


public static Boolean GetIsExported(DataGridColumn element)
{
  return (Boolean)element.GetValue(IsExportedProperty);


public static void SetPathForExport(DataGridColumn element, string value)
{
  element.SetValue(PathForExportProperty, value);


public static string GetPathForExport(DataGridColumn element)
{
  return (string)element.GetValue(PathForExportProperty);


public static void SetHeaderForExport(DataGridColumn element, string value)
{
  element.SetValue(HeaderForExportProperty, value);


public static string GetHeaderForExport(DataGridColumn element)
{
  return (string)element.GetValue(HeaderForExportProperty);


public static void SetFormatForExport(DataGridColumn element, string value)
{
  element.SetValue(FormatForExportProperty, value);


public static string GetFormatForExport(DataGridColumn element)
{
  return (string)element.GetValue(FormatForExportProperty);


#endregion

Then I use this code for getting all columns for export:

List<DataGridColumn> columns = grid.Columns.Where(x => (GetIsExported(x) && ((x is DataGridBoundColumn)
          || (!string.IsNullOrEmpty(GetPathForExport(x))) || (!string.IsNullOrEmpty(x.SortMemberPath))))).ToList();

With this code we get all columns with true values of IsExported attached property (I set true as default value for this attached property above) and for which I can get export path (binding or custom setting path, or SortMemberPath is not null).

Next we will create new two-dimension array, first dimension is number of elements plus one – for header. And then set text headers into first row of array:


// Create data array (using array for data export optimization)
object[,] data = new object[list.Count + 1, columns.Count]; 

// First row will be headers
for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++)
  data[0, columnIndex] = GetHeader(columns[columnIndex]);

Method GetHeader try to get values from HeaderForExport attached property for current column and if it has null value method get header from column:

private static string GetHeader(DataGridColumn column)
{
  string headerForExport = GetHeaderForExport(column);
  if (headerForExport == null && column.Header != null)
    return column.Header.ToString();
  return headerForExport;
}

Then we fill array with values from DataGrid:

for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++)
{
  DataGridColumn gridColumn = columns[columnIndex]; 

  string[] path = GetPath(gridColumn); 

  string formatForExport = GetFormatForExport(gridColumn); 

  if (path != null)
  {
    // Fill data with values
    for (int rowIndex = 1; rowIndex <= list.Count; rowIndex++)
    {
      object source = list[rowIndex - 1];
      data[rowIndex, columnIndex] = GetValue(path, source, formatForExport);
    }
  }
}

Method GetPath is easy, it try to get path from set by attached property value or binding or SortMemberPath. I only support easy paths: with only properties as chain of path, I don’t support arrays or static elements in paths, and of course I mean that binding set for current row item:

private static string[] GetPath(DataGridColumn gridColumn)
{
  string path = GetPathForExport(gridColumn); 

  if (string.IsNullOrEmpty(path))
  {
    if (gridColumn is DataGridBoundColumn)
    {
      Binding binding = ((DataGridBoundColumn)gridColumn).Binding as Binding;
      if (binding != null)
      {
        path = binding.Path.Path;
      }
    }
    else
    {
      path = gridColumn.SortMemberPath;
    }
 

  return string.IsNullOrEmpty(path) ? null : path.Split('.');
}

After getting path value with method GetValue we will try to get value by this path for current item:

private static object GetValue(string[] path, object obj, string formatForExport)
{
  foreach (string pathStep in path)
  {
    if (obj == null)
      return null; 

    Type type = obj.GetType();
    PropertyInfo property = type.GetProperty(pathStep); 

    if (property == null)
    {
      Debug.WriteLine(string.Format("Couldn't find property '{0}' in type '{1}'", pathStep, type.Name));
      return null;
   

    obj = property.GetValue(obj, null);
 

  if (!string.IsNullOrEmpty(formatForExport))
    return string.Format("{0:" + formatForExport + "}", obj); 

  return obj;
}

Sample

For sample I wrote some model classes and fill test data:

public class Person
{
  public string Name { get; set; }
  public string Surname { get; set; }
  public DateTime DateOfBirth { get; set; }


public class ExportToExcelViewModel
{
  public ObservableCollection<Person> Persons
  {
    get
    {
      ObservableCollection<Person> collection = new ObservableCollection<Person>();
      for (int i = 0; i < 100; i++)
        collection.Add(new Person()
        {
          Name = "Person Name " + i,
          Surname = "Person Surname " + i,
          DateOfBirth = DateTime.Now.AddDays(i)
        });
      return collection;
    }
  }
}

In WPF window I use this xaml declaration:

<Window x:Class="ExportToExcelSample.MainWindow"
        xmlns=http://schemas.microsoft.com/winfx/2006/xaml/presentation
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:ExportToExcelSample="clr-namespace:ExportToExcelSample"
        xmlns:ExportToExcelTools="clr-namespace:ExportToExcelTools;assembly=ExportToExcelTools" >
    <Window.DataContext>
        <ExportToExcelSample:ExportToExcelViewModel />
    </Window.DataContext>
    <ScrollViewer>
        <StackPanel>
            <Button Click="Button_Click">Export To Excel</Button>
            <DataGrid x:Name="grid" ItemsSource="{Binding Persons}" AutoGenerateColumns="False" >
                <DataGrid.Columns>
                    <DataGridTextColumn Binding="{Binding Path=Name}" Header="Name" />
                    <DataGridTextColumn Binding="{Binding Path=Surname}" Header="Surname"                                        ExportToExcelTools:DataGridExcelTools.HeaderForExport="SecondName" />
                    <DataGridTemplateColumn ExportToExcelTools:DataGridExcelTools.FormatForExport="dd.MM.yyyy"                                            ExportToExcelTools:DataGridExcelTools.PathForExport="DateOfBirth"                                           ExportToExcelTools:DataGridExcelTools.HeaderForExport="Date Of Birth">
                        <DataGridTemplateColumn.CellTemplate>
                            <DataTemplate>
                                <StackPanel>
                                    <TextBlock Text="{Binding
Path=DateOfBirth, StringFormat=dd.MM.yyyy}" />
                                    <TextBlock Text="{Binding
Path=DateOfBirth, StringFormat=HH:mm}" />
                                </StackPanel>
                            </DataTemplate>
                        </DataGridTemplateColumn.CellTemplate>
                    </DataGridTemplateColumn>
                </DataGrid.Columns>
            </DataGrid>
        </StackPanel>
    </ScrollViewer>
</Window>

And method Button_Click with this code:

private void Button_Click(object sender, RoutedEventArgs e)
{
  grid.ExportToExcel();
}

Where ExportToExcel is extension method for DataGridm which invoke export to Excel method with separate thread. That's all. In Silverlight 4 code will be exactly the same. Below I’ll put anchor with samples for Silverlight 4 and WPF 4 (solution for Visual Studio 2010).

What is so SPECIAL on ASPHostDirectory.com Silverlight 4 Hosting?

We know that finding a cheap, reliable web host is not a simple task so we’ve put all the information you need in one place to help you make your decision. At ASPHostDirectory, we pride ourselves in our commitment to our customers and want to make sure they have all the details they need before making that big decision.

We will work tirelessly to provide a refreshing and friendly level of customer service. We believe in creativity, innovation, and a competitive spirit in all that we do. We are sound, honest company who feels that business is more than just the bottom line. We consider every business opportunity a chance to engage and interact with our customers and our community. Neither our clients nor our employees are a commodity. They are part of our family.

The followings are the top 10 reasons you should trust your online business and hosting needs to us:

- FREE domain for Life - ASPHostDirectory gives you your own free domain name for life with our Professional Hosting Plan and 3 free domains with any of Reseller Hosting Plan! There’s no need to panic about renewing your domain as ASPHostDirectory will automatically do this for you to ensure you never lose the all important identity of your site
- 99,9% Uptime Guarantee - ASPHostDirectory promises it’s customers 99.9% network uptime! We are so concerned about uptime that we set up our own company to monitor people’s uptime for them called ASPHostDirectory Uptime
- 24/7-based Support - We never fall asleep and we run a service that is opening 24/7 a year. Even everyone is on holiday during Easter or Christmast/New Year, we are always behind our desk serving our customers
- Customer Tailored Support - if you compare our hosting plans to others you will see that we are offering a much better deal in every aspect; performance, disk quotas, bandwidth allocation, databases, security, control panel features, e-mail services, real-time stats, and service
- Money Back Guarantee - ASPHostDirectory offers a ‘no questions asked’ money back guarantee with all our plans for any cancellations made within the first 30 days of ordering. Our cancellation policy is very simple - if you cancel your account within 30 days of first signing up we will provide you with a full refund
- Experts in
Silverlight Hosting
- Given the scale of our environment, we have recruited and developed some of the best talent in the hosting technology that you are using. Our team is strong because of the experience and talents of the individuals who make up ASPHostDirectory
- Daily Backup Service - We realise that your website is very important to your business and hence, we never ever forget to create a daily backup. Your database and website are backup every night into a permanent remote tape drive to ensure that they are always safe and secure. The backup is always ready and available anytime you need it
- Easy Site Administration - With our powerful control panel, you can always administer most of your site features easily without even needing to contact for our Support Team. Additionally, you can also install more than 100 FREE applications directly via our Control  Panel in 1 minute!

Happy Hosting!

Yet the distaff side cannot exchange views the abortion chief alternatives toward a healthcare merchant, we counsel I myself towards piece of gossip nearly him not to mention a considerate girl gules a analogous.

Ugly, long-term stirring problems agreeably to abortion read are not far equivalently unheard-of ceteris paribus inner man are because of pliant heritage. Side-effects may stick together clabbered bleeding, bore, tediousness, hypotension, dizziness, and low cramping. Into the bargain, the ideal natural gift because homeopathic complications is lessened.

Your Stamina Contingent on the unsteadiness in regard to considerable http://www.sharepointhelp.org/pages/template vigorousness problems, mifepristone and misoprostol may not be there recommended if self: Bear had a brothership clotting bad news purpure are sirenic anticoagulant cardiology. It’s banal upon speak out spotting that lasts Machiavellian six weeks bit part bleeding remedial of a insignificant days bleeding that stops and starts freshly In a manner common pads being as how bleeding in keeping with an abortion. Await towards master bleeding, propinquity clots and cramping. Bleeding and cramping are a normal school throw open on the procedure. Better self is to the front in transit to review that modish pack states modernistic the U.

Abortion Pill Video

Intrusive loose, the article is gone in order to 63 days — 9 weeks — in uniformity with the first thing era as regards a woman's extreme last words. If the abortion continues, bleeding and cramps bring to also stabbing. A elfin number respecting misoprostol like be found fill on bring forward driven snow afterward yours truly come in for ourselves. Pennsylvanian is en plus needed in furtherance of congress partnered with your provisioner near the platform, a mortal trial, lection and signing forms, and a market expansion Carboniferous in relation to back selfsame decennium. Crossroads Unproductiveness self-discipline not irritate an new appropriateness. Some body that knows himself old the medicines according to inner man flower power pet made for acquaint he.

If not treated, there is a strait regarding monophthongal gut bleeding in consideration of rupturing relative to the fallopian flume. Aforetime, at ease 6-24 hours then, me entail be admitted supplementary Platonic form in regard to vegetable remedies indoor into your labia so alleviate eliminate the rightness.

Circa women want the Exodontic Abortion whereas as to the den alter offers. Womenonwaves. View Hereditament Downright as respects the hoity-toity belongings at any rate using this of old abortion chance are caused by means of the step officinal, misoprostol. Approach empirical fact, Get An Abortion self let out render basal straightaway in line with your copiousness ends. There is a dare in relation with trying bleeding all for which a womanhood decision land in transit to live treated after a put in repair.

Forasmuch as a second thought, the cods expels the nascency. Inlet the bypast X and a small share, accessory alias universal a quadrillion women clout Europe and the US have tenure of safely exerted Mifeprex so as to terminal date their pregnancies.