In Russian

Denis Gladkikh

outcoldman

Export data to Excel from Silverlight/WPF DataGrid

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)}

But I could export one-dimension arrays (one row), so I think this is problem of Silverlight, I posted bug in section .net 4 on http://connect.microsoft.com.

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).

Conclusion

My approach very easy allows you to set how to export data from DataGrid with attached properties. If you want to use this approach I recommend you design new features: show busy indicator when data is exporting, and use OpenOffice when Excel is not installed on computer. Thanks.

Download sample: ExportToExcelTools.zip

Shout it kick it on DotNetKicks.com

Updated

  • Add opportunity to export DataGrid with DataSet data as DataSource.
  • Put SetTextFormat parameter which will set 'Text Format' for all cells in Excel file.

You can download last source code from my assembla source code repository ExportToExcelTools

Silverlight, C#, Excel, Dynamic, WPF, XAML, WPF 4, Silverlight 4, DataGrid, COM

You might also like

Comments (76)

Anonymous ( ) #
gravatar
Very bad English.
Why don't you write in your nativelanguage?
Denis Gladkikh ( ) #
gravatar
Thanks, Anonymous :)
I write in my native language too. This is translation and I try to perfect my knowledge of English language.
Baber ( ) #
gravatar
Great work denis
Denis Gladkikh ( ) #
gravatar
Thanks, Baber.
Mike ( ) #
gravatar
Did you just go to the effort of brilliantly explaining a tricky subject, and the only thanks you go was to have your English criticized ?!

Blimey.. there's no pleasing some people !

Excellent article.. well explained, but I wasn't able to load the solution in VS2008. It said it was created using a more up-to-date version (VS2010 ?)
Denis Gladkikh ( ) #
gravatar
Mike, thanks.

SL4 working only on VS2010, and COM working only on SL4. This is why I used VS2010.

If you are using WPF and VS2008 I can create project for you. But if you are using SL3 - you need to upgrate to vs2010/sl4.
Rui Marinho ( ) #
gravatar
Hi Denis, great work, im using this lib in my project, i have found a issue when using converters in the datagrid.. i have to add your oproject o my soultion instead of onl the dll to try figure out what's going on. But very nice .. i just need to write the start exporting eveent like u refer, and the completed one.

Thanks a lot for sharing.

ps Chart.ExportToExcel() would be awsone :D
Denis Gladkikh ( ) #
gravatar
Rui, Welcome.

I will think about Chart.ExportToExcel(), but it is looks like a very difficult.
chenkai ( ) #
gravatar
i can't read this.
that's too bad.
Denis Gladkikh ( ) #
gravatar
chenkai, sorry, this is all that I can. You can try to ask me if you want.
chenkai ( ) #
gravatar
denis nice work!
i'm very sorry to hear that. i just make mistake in wrong pleace to comment.

so why did you add the app file on the page?
Denis Gladkikh ( ) #
gravatar
chenkai, sorry, you mean "why didn't"?
You think I should? I was thinking about it... But not saw this necessity.

Maybe will do it in next blogposts. :)
Rui Marinho ( ) #
gravatar
Hi again denis. i am struggling with large amounts of data... do u think that could be a way where i can export let's say 5000 rows to excel? my app almost crashes :(
Denis Gladkikh ( ) #
gravatar
Hi Rui Marinho, this is strange that your app crashes when you are trying to export 5000 rows. I used this method with huge data collections and all was fine. Can you send me a test project? I will try to find a best way for you. Just contact me. Cheers!
Brian Wells ( ) #
gravatar
Nice job.

Is there a way to format a column as text?

e.g. I have a datagrid value of "4196256761", but on export the value displays as 4.19626E+12.

This is in Excel 2010.

In the xaml I see:

ExportToExcelTools:DataGridExcelTools.FormatForExport="MM.dd.yyyy"

So entering "text" or "string" don't format the column...

ExportToExcelTools:DataGridExcelTools.FormatForExport="text"

Also, is there a way to format date fields as "MM/dd/yyyy"?

doesn't work so well w/ slashes. escaping w/ double slash kind of works, but the values display w/ the double slash in the workbook.

Thanks!
Denis Gladkikh ( ) #
gravatar
Hi Brian , please download last source code from my assembla repository ExportToExcelTools.

You will find a checkbox with "Set 'Text Format'" label, try to check it and you will see solution of your problem. I added opportunity to set parameter setTextFormat, when you put value true to this format my library will set Text Format for all cells.

Good luck, and tell me if you will have any other questions.
Brian Wells ( ) #
gravatar
I found resolution for the values displaying w/ exponential notation:

in ExportManager.cs, add this line:
rg.NumberFormat = "0";


Still curious to know if there's a way to get this to work:
"MM/dd/yyyy HH:mm:ss"

this works ok though:
"MM.dd.yyyy HH:mm:ss"

Thanks
Denis Gladkikh ( ) #
gravatar
Brian, try to get latest source code from url which I gave you.
Or you can fix it manually in ExportManager.cs file. Place this line
rg.NumberFormat = "@";
instead of
rg.NumberFormat = "0";
Denis Gladkikh ( ) #
gravatar
Brian, about "MM/dd/yyyy HH:mm:ss" format, please look at MSDN documentation about symbol '/' for custom formats http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx#dateSeparator
technette ( ) #
gravatar
outcoldman,

Thank you. I really appreciate the time and effort it took to build this tool. I'm going to try to implement this on my

current project. I am currently using an export utility but since I have a datapager, it will not export all data to

Excel. Only the data that is on the current page. I hope that I can overcome this problem with your tool. I need to

be able to export up to 10,000 rows.
Denis Gladkikh ( ) #
gravatar
Hi technette,

In your case, I would export the data just with ExportManager class. I mean with DataGridExcelTools you will find which columns you should show in excel table, and with ExportManager you should export the data without showing it in DataGrid. If you are using WPF - you can load all 10 000 rows and then publish them to Excel. If you are using Silverlight - better will be load the data page-by-page without showing it in DataGrid. You can show busy indicator with progress bar, you should use two threads: one for data loading and one for data exporting.

Tell me if you have any question.
subecho ( ) #
gravatar
К сожалению это только цветочки (это я про задачу). Настоящая проблема с экспортом встает, когда у тебя куча сложных (составных) template-колонок со всякими байндингами для visibility отдельных полей, тултипами, тригеррами. Там attached пропертями не обойтись - там приходится прибегать к парсингу темплейтов (по крайней мере это то как я это делаю). Все еще в поисках более элегантного и эффективного решения, которое позволяло бы экспортировать DataGrid в Excel в виде наиболее приближенном к отображению...
technette ( ) #
gravatar
Denis, Thank you for responding. I am using Silverlight 4 and I do have questions. I'm using a navigation application and the grids I want to export are on pages. After demonstrating my application to users, I will have to try your ExportManager Class. Would I first add your dll and the class? I converted the export manager to vb and added the class to my project but the IDE still doesn't display the method. Do you have a sample of using the progress bar with this?
Denis Gladkikh ( ) #
gravatar
technette, I haven't sample with progress bar, sorry. IDE doens't display the method maybe because you didn't import namespace (reference dll)?
technette ( ) #
gravatar
Having a problem casing the domainDatasource data or the DataGrid's Items Source.

Public Shared Sub ExportToExcel(ByVal data As Object(,))
Denis Gladkikh ( ) #
gravatar
technette this code works only with collection which can be converted to List by Linq (IEnumerable) or DataTable (WPF case). Sorry, it is not support DomainDatasource.
technette ( ) #
gravatar
Please dis regard my last comment.. I am trying the ExportDataGridTools. I get an error

" Dim rg = worksheet.Range(worksheet.Cells(top, left), worksheet.Cells(bottom, right))"

Error while invoking Cells - member not found.
Denis Gladkikh ( ) #
gravatar
technette, I don't know VB syntax. In my code worksheet.Cells[i, left] - Cells is array, in your code it looks like you want to call method Cells and pass two parameters top and left. Maybe you should write something different?
technette ( ) #
gravatar
When I try to use the DataGridExcelTools, it is referencing the ExportManager.

Private Shared Sub StartExport(ByVal data As Object)

ExportManager.ExportToExcel(TryCast(data, Object(,)))

End Sub

This is where the error is:

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]];
technette ( ) #
gravatar
Denis,

The DataGridExcelTools is referencing the ExportManager:

This comes from the Export Manager:

excel.ScreenUpdating = False

Dim workbook = excel.workbooks

workbook.Add()

Dim worksheet = excel.ActiveSheet

Const left As Integer = 1

Const top As Integer = 1

Dim height As Integer = data.GetLength(0)

Dim width As Integer = data.GetLength(1)

Dim bottom As Integer = top + height - 1

Dim right As Integer = left + width - 1

If height = 0 OrElse width = 0 Then

Return

End If

Dim rg = worksheet.Range(worksheet.Cells(top, left), worksheet.Cells(bottom, right))

This is from the VB from the DataGridExcelTools:

Public Shared Sub ExportToExcel(ByVal grid As DataGrid)

Dim thread As New Thread(AddressOf StartExport)

thread.Start(PrepareData(grid))

End Sub

Private Shared Sub StartExport(ByVal data As Object)

ExportManager.ExportToExcel(TryCast(data, Object(,)))

End Sub
Denis Gladkikh ( ) #
gravatar
technette, as I said before I don't know VB.NET. If you want you can try to send me an email with sample project, which you are trying to build. My email you can find there.
Shehzad ( ) #
gravatar
Thumbs up buddy, for a nice article.

I have problem in exporting a DatagridComboBoxColumn, this extension method only exports the SelectValue of ComboBox column, I need to export DisplayMember of ComboBox. Can you please help me to sort it out.
Denis Gladkikh ( ) #
gravatar
Shehzad, try to use ExportToExcelTools:DataGridExcelTools.PathForExport="...", where instead of ... put path which you want to see in export.
fazal ( ) #
gravatar
Is the current ExporttoExceltools.zip the latest file or should we go to the repository to download the file?

It would be great if you had zipped the solution and place a link. thats much easier. But I should commend you that this is exactly what I was looking for and I will give it a try.
Denis Gladkikh ( ) #
gravatar
fazal, really I don't know if zip archive has latest version or not. Better will be download source code from assembla. It latest 100% :)
alex.e ( ) #
gravatar
Excellent work!

The only change I've made is the default font color for the header

here's the code

rgHeader.Font.colorindex = 2; //white

// Calucated as 189*(int) Math.Pow(16, 4) + 129*(int) Math.Pow(16, 2) + 78

// where Red = 78(4E) Green = 129 (81) Blue = 189 (BD)

// Hex color #4E81BD

rgHeader.Interior.Color = 12419406;

Так держать Денис

Уважуха!!!
viyoma ( ) #
gravatar
Thanks a Lot!!!!!!
Danny ( ) #
gravatar
Denis;

Greate work.

I have a WPF application that has three tabs that each have a datagrid. How can I have them all export to the same workbook but a different sheet?
Denis Gladkikh ( ) #
gravatar
Hi Danny, sorry for so long reply. I just have made for you example how to export data to Excel more than from one DataGrid. It is very fast fix, please get latest version from my assembla repository http://www.assembla.com/code/outcoldman_p/subversion/nodes/BlogProjects/ExportToExcelTools
Sebastian K ( ) #
gravatar
Thank you a lot for this article, it was very useful for my project, I just had to rewrite a few lines (I code in C#) and it worked perfectly.
Sancio ( ) #
gravatar
Great article ! I am using it in a WPF project, but i can not make it work with datatable as datasource. The excel opens but it takes only the headers. I think because i am using 
dataGrid.DataContext=dataTable;
instead of an ObservableCollection like in this example, something must be chaged in DataGridExcelTools.cs . Please help me! Thank you for your time
Denis Gladkikh ( ) #
gravatar
Sancio, did you try to use latest source code from assembla ? I hope it should supports DataTable.
Carl-Johan Larsson ( ) #
gravatar
OK, its a bit late but i have to ask.

To: Anonymous ( 23.04.2010 22:42 )

Why did you write that?
thenndral ( ) #
gravatar
Hello,

First I thank you for such an amazing Code.

I have a question, In the above comments I saw in the latest version you wrote the code for multi-grid export to excel in workbook.

I didn't see the sample for that. Could you help me to find out.

Thanks,

thenndral
Vikas Dangwal ( ) #
gravatar
You can use OpenXML instead of Excel library. Microsoft provide these libraries and best thing is any plateform can open these type of Xml files. Performance wise it is really good.
Sunil ( ) #
gravatar
Thanks a ton. was very useful. Nice work. Was a bit difficult to understand on first glace but after a while, was able to appreciate it.
Dexter ( ) #
gravatar
Best Implementation Of WPF Extended DataGrid Can be found here WPF Extended DataGrid Features

Column Choosers.

AutoFilter Control.

Export To Excel Feature.

Copy Paste To Excel and Copy Paste From Excel To DataGrid.

Three State Sorting.

Displaying Sort Order If Multiple Sort is done.

Export To Excel with formatting.

Export To PDF with formatting.
Dmitry ( ) #
gravatar
Needed this feature today..Discovered another way of exporting to CSV. Think its more simple: 
dg.SelectAllCells();
            dg.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
            ApplicationCommands.Copy.Execute(null, dg);
            dg.UnselectAllCells();
            var stream = (System.IO.Stream)Clipboard.GetData(DataFormats.CommaSeparatedValue);
            var encoding = System.Text.Encoding.GetEncoding("UTF-8");
            var reader = new System.IO.StreamReader(stream, encoding);
            string result = reader.ReadToEnd();
            Clipboard.Clear();
where dg is our DataGrid, result- csv. So that here we select all the cells in grid, copy them to clipboard and then "paste" to file in CSV format.
Sam ( ) #
gravatar
Hi Denis, great work and thanks a lot for sharing it helped me lot.

But I have a question you answered Rui that you used this with huge data collections.

When I test your SL solution with 1000 Person. It take over a minute before excel opens.

What is the best way in SL if you have huge data collection?

Thanks in advance.
Chibby ( ) #
gravatar
This thing is Awesome! Thanks. Was completely plug and play (WPF) and got it working in literally minutes.

Thank you for an outstanding effort and contribution.
Anonymous ( ) #
gravatar
Hi, this is a nice article. Exactly what I'm looking for..

Unfortunately, when I use this in my application, the only thing that gets imported to Excel are the field names and everything is empty.

Am I missing something?
Sam ( ) #
gravatar
My test with huge data collection was with eleveted trust in-browser with your SL solution, when it took long time to load excel.

This code works just fine in OOB, then it loads in matter of seconds.

Pity that there is such a big difference.
Denis Gladkikh ( ) #
gravatar
Sam, unfortunately I do not know right now how to solve performance problems with Silverlight, I will try to look on this. Maybe this problem is unsolved - and the only way to solve it - show Progress Bar when do export.

Anonymous, could you send me example how you use it to my email: links?
Denis Gladkikh ( ) #
gravatar
Sam, I found one easy solution which can increase performance - just change how I export data to excel: by rows or by columns. The most expensive operation is a operation of creating a new COM object, and if you have 1000 rows - then 1000 COM objects will be created. In most cases tables have more rows than columns - so just need to change the order how I export data from by rows to by columns. I have already did it and have already committed it to assembla repository. This is the main change which I did - http://www.assembla.com/code/outcoldman_p/subversion/changesets/31#ch1
Sam ( ) #
gravatar
Thanks Denis,

I have tired your change, there was a problem all rows just contains the header info. It seems that it only takes the first value in the column object.
Nick ( ) #
gravatar
Thank you very much Denis for sharing your source code! You are the best!
Clint ( ) #
gravatar
I have a DataGrid that uses a stack panel from a different list. Basically there is 1 ID, and multiple rows for each ID. When that is exported to Excel only the ID is passed. The column headers are also passed, but the only value passed is the ID row.
Denis Gladkikh ( ) #
gravatar
Clint, could you create some sample and share it somewhere? I will take a look on it.
ali ( ) #
gravatar
very very thank you!!!
SAMAN ( ) #
gravatar
hi Denis. thanks for this examle

I have a problem :

I use IvalueConverter for convert a column data such as follow: 
<;DataGridTextColumn >
                        <;DataGridTextColumn.Binding>
                            <;Binding Path="Name" >
                                <;Binding.Converter>
                                    <;ExportToExcelSample:SampleConverter />
                                <;/Binding.Converter>
                            <;/Binding>
                        <;/DataGridTextColumn.Binding>
                    </DataGridTextColumn>
I want show my data COnverted in excel but this sample dont show data converted instead of data into itemsource that bind to grid.

this is very important for me

thanks

can you help me?
Denis Gladkikh ( ) #
gravatar
Hi SAMAN,

There are no way to get the value from the binding object at runtime. You can try to use the tricks like BindingEvaluator, but I don't think that this will work fast enough.

Also you can just get the Converter object from the binding at method GetPath and use it when you get the value by this path in GetValue. This looks like something very easy to implement.
sami ( ) #
gravatar
hi Denis, again

at first thanks for help

but I cant implement your above suggest

can you write a sample for binding object into Export data to Excel Tools Project

thanks a lot
Denis Gladkikh ( ) #
gravatar
sami, as workaround you also can just add new property to your view model which you can use for binding / export.
David ( ) #
gravatar
Awesome implementation of this!
Kumar ( ) #
gravatar
Very nice code and it helped me a lot Denis. Thank you so much. While looping in GetValue() of DatagridExcelTools page, in the obj value if i get multiple values i want to show them all the values seperated by , in that field.How should i do this?I have tried this way but no luck.Can you help me?IEnumerable collection = (IEnumerable)property.GetValue(obj, null); foreach (object val in collection) { obj = val + ";"; }Thanks & Regards,Kumar
Denis Gladkikh ( ) #
gravatar

Kumar, probably you put you code in wrong place. Try this code before "return obj;".

if (obj is IEnumerable)
{
    StringBuilder result = new StringBuilder();
    IEnumerable collection = (IEnumerable)property.GetValue(obj, null); 
    foreach (object val in collection) 
    { 
        if (val != null)
        {
            result.AppendFormat("{0};", val.ToString()); 
        }
    }
 
    obj = result.ToString();
}
Kumar ( ) #
gravatar
Thanks for the Quick reply Denis.I have used this as you said but i am getting an error like this

"Object does not match target type"

at line

IEnumerable collection = (IEnumerable)property.GetValue(obj, null);

How can i convert that to IEnumerable collection based on it's parent observable collection type.

The other trail i am doing is as shown below.Is there any possibility that i can use like this

filter:DataGridExcelTools.PathForExport="{Binding Capabilities, Converter={StaticResource seperator}}"

Waiting for ur reply

Thanks & Regards,

Kumar
Denis Gladkikh ( ) #
gravatar
Kumar, my fault, the code should be 
if (obj is IEnumerable)
{
    StringBuilder result = new StringBuilder();
    foreach (object val in (IEnumerable)obj) 
    { 
        if (val != null)
        {
            result.AppendFormat("{0};", val.ToString()); 
        }
    }
 
    obj = result.ToString();
}
Binding for PathForExport is not supported.
Rob ( ) #
gravatar
I'm have problems with exporting by columns. The header data is displaying in every cell. Do you know of any solution to this problem?

-thanks rob
Denis Gladkikh ( ) #
gravatar
Hi Rob,

It worked fine for me when I wrote this. Perhaps one of the latest updates of SL or Office broke this. I don't have solution for this.
Ricardo ( ) #
gravatar
Hi Denis.

Thanks for your work on this problem. I have one small problem. While trying to use your solution i get back a problem at this line : Listlist = grid.ItemsSource.Cast().ToList(); The problem that the debugger shows is this "The query results can not be enumerated more than once" any suggestions??
Denis Gladkikh ( ) #
gravatar
Ricardo what you are using as a ItemsSource for your Data Grid? Looks like this collection does not support multiple enumeration.
Ricardo ( ) #
gravatar
Hi Denis,

Thanks for the reply! I eventually fixed my enumeration problem by manually adding each row to the list of objects using this code: 
List list = new List();
foreach (var items in dgProjects.Items)
{
var row = dgProjects.ItemContainerGenerator.ContainerFromItem(items) as DataGridRow;
list.Add(row);
}
I get the headers and a border around the correct number of rows but no information in the rows.

Does this seem correct? Any suggestions?
Denis Gladkikh ( ) #
gravatar
Ricardo, it does not look for me right, my code expects the real data source objects, not the DataGridRow. What you are using as a collection for data grid? You need to pass this collection to the ExportToExcel function.
Ricardo ( ) #
gravatar
Denis,

My Datagrid is populated through a stored proc using linq. Everytime I try to enumerate the actual ItemSource it throws an exception saying that it can not be enumerated more than once.
Flippie ( ) #
gravatar
" Dim rg = worksheet.Range(worksheet.Cells(top, left), worksheet.Cells(bottom, right))"

Error while invoking Cells - member not found.

I had the same error and did this:

Dim rg1 = worksheet.Cells(top, left)

Dim rg2 = worksheet.Cells(bottom, right)

Dim rg = worksheet.Range(rg1,rg2)

I do not know why it worked but it did.

Denis Gladkikh : Thanks.
Submit Comment
If you want to get notifications about new comments at this topic, please fill email text box and check proper item. If you want to place source code in comment body place it in tags [code]...[/code], you can set language like this [code cs]...[/code], where cs can be cs, html, xml, java, js, php, sql, cpp, css.

 

busy