views:

14744

answers:

9

Hi everyone,

Is there a way I can export my Silverlight DataGrid data to excel or csv?

I searched the web but can't find any examples!

Thanks a lot

+2  A: 

I don't think Silverlight offers a way to download files. You can add a button to your app that calls a URL- i.e. http://www.mysite.com/generateexcelfile.aspx. Include as Querystring values the parameters used to generate the data being displayed in your Silverlight app, run your query and use your favorite Excel file generation component to generate the file on the fly. Redirect to it and it will download to the users' system.

Dave Swersky
Be careful with "create the file and then redirect to it" -- you have to delete these files at some point. Better thing to do would be to just save the Excel into the output stream of the .aspx page with the correct mime type, so that the browser can see that it's an Excel file, not html page, and give user an option to save it.
A: 

hmmmm, i was hoping that I could extend the DataGrid control adding an export to excel button and write some generic code that could do it for me for any DataGrid.

Can you think of a generic way that this might be achieved without having to write a custom export function for each grid?

+4  A: 

I have found this using the clipboard.

To make the code generic you can modify the first example to read the column bindings and apply they to the data using reflection:

public String ExportDataGrid(DataGrid grid)
{
    string colPath;
    System.Reflection.PropertyInfo propInfo;
    System.Windows.Data.Binding binding;
    System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
    System.Collections.IList source = (grid.DataContext as System.Collections.IList);
    if (source == null)
        return "";

    foreach (Object data in source)
    {
        foreach (DataGridColumn col in datagrid.Columns)
        {
            if (col is DataGridBoundColumn)
            {
                binding = (col as DataGridBoundColumn).Binding;
                colPath = binding.Path.Path;
                propInfo = data.GetType().GetProperty(colPath);
                if (propInfo != null)
                {
                    strBuilder.Append(propInfo.GetValue(data, null).ToString());
                    strBuilder.Append(",");
                }                        
            }

        }
        strBuilder.Append("\r\n");
    }


    return strBuilder.ToString();
}

of course, it only works when the path of the binding is the property name. For more advanced paths you have to apply the binding to the data (I suppose that would be a better solution, but I'm not sure now how to do this).

DaniCE
+1  A: 

Off the top of my head I would say you can add the export button using a ControlTemplate and then iterate over each item in the DataSource, and then use each column in Columns to obtain the content of each cell using the GetCellContent method, or use the DataGridColumn's binding information to get the appropriate cell value. You can then get the displayed value of this content and write that to your report.

Something like...

foreach (YourType item in grid.DataSource)
{
   foreach (DataGridColumn column in grid.Columns)
   {
      FrameworkElement cellContent = column.GetCellContent(item);

      // Now, determine the type of cell content and act accordingly.
      TextBlock block = cellContent as TextBlock;
      if (block != null)
      {
         // Report text value...
      }

      // ...etc...

   }
}

Or using the binding information as described by DaniCE.

Jeff Yates
+8  A: 

Silverlight 3 changes the answer to this question because it gives the ability of the user to create a file on the user's desktop in a location that they specify. I adapted the code submitted by DaniCE, split things into a few methods for readability and am using a loosely defined CSV format that Excel should recognize.

private void exportHistoryButton_Click(object sender, RoutedEventArgs e) 
{
    string data = ExportDataGrid(true, historyDataGrid);
    SaveFileDialog sfd = new SaveFileDialog()
    {
    DefaultExt = "csv",
    Filter = "CSV Files (*.csv)|*.csv|All files (*.*)|*.*",
    FilterIndex = 1
    };
    if (sfd.ShowDialog() == true)
    {
    using (Stream stream = sfd.OpenFile())
    {
        using (StreamWriter writer = new StreamWriter(stream)) {
     writer.Write(data);
     writer.Close();
        }
        stream.Close();
    }
    }
}

private string FormatCSVField(string data) {
    return String.Format("\"{0}\"",
     data.Replace("\"", "\"\"\"")
     .Replace("\n", "")
     .Replace("\r", "")
        );
}

public string ExportDataGrid(bool withHeaders, DataGrid grid)
{
    string colPath;
    System.Reflection.PropertyInfo propInfo;
    System.Windows.Data.Binding binding;
    System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
    System.Collections.IList source = (grid.ItemsSource as System.Collections.IList);
    if (source == null)
    return "";

    List<string> headers = new List<string>();
    grid.Columns.ToList().ForEach(col => {
    if (col is DataGridBoundColumn){
        headers.Add(FormatCSVField(col.Header.ToString()));
    }
    });
    strBuilder
    .Append(String.Join(",", headers.ToArray()))
    .Append("\r\n");

    foreach (Object data in source)
    {
    List<string> csvRow = new List<string>();
    foreach (DataGridColumn col in grid.Columns)
    {
        if (col is DataGridBoundColumn)
        {
     binding = (col as DataGridBoundColumn).Binding;
     colPath = binding.Path.Path;
     propInfo = data.GetType().GetProperty(colPath);
     if (propInfo != null)
     {
         csvRow.Add(FormatCSVField(propInfo.GetValue(data, null).ToString()));
     }
        }
    }
    strBuilder
        .Append(String.Join(",", csvRow.ToArray()))
        .Append("\r\n");
    }


    return strBuilder.ToString();
}
David in Dakota
Does this work?!
Goober
It does with some sleight of hand; it creates a CSV file, not XLS. If a person has Excel installed it opens it by virtue of the extension being mapped to Excel.
David in Dakota
If you get an error on the binding line, try casting like this:binding = (System.Windows.Data.Binding)((Microsoft.Windows.Controls.DataGridBoundColumn)col).Binding;
proudgeekdad
A: 

i think there is a way more easy than this you can write your CVS or open excel file with your code behind by get the schema of open file excel and write the Datagrid header to excel header and rows this is very simple to do if you want any sample pelase reply me

Ma3oude
+1  A: 

Check Ryan's solution out. Looks good, can't vouch for it though because I just found it. Ryan does what DLL above requested.

http://www.rshelby.com/post/exporting-data-from-silverilght-datagrid-to-excel.aspx

David in Dakota's solution above looks a little easier to implement, and there is always the redirect to a classic asp.net page with a data grid on it and the content type set to excel, but then you have more code to support and that way may not work for out of browser solutions that are offline (actually won't work).

Anyway, this is a common task. I hope some folks here or at Microsoft come up with a Mort plug and play solution :)

I found an extension method for CVS export from a Silverlight datagrid-

http://www.codeproject.com/KB/silverlight/SilverlightDataGridExport.aspx

It has the potential to be plug and play, but I had to tweek it just a little bit to get it to work with datagrids with item datasources (see comments in the post). Someone brighter and more experienced than myself should be able to tweak to perfection. Check it out, it should get you close to what you need.

infocyde
A: 

Here's a nice approach that worked for me http://forums.silverlight.net/forums/p/179321/404357.aspx

Fedor
A: 

I suggest you to give a look at http://www.viblend.com/products/net/silverlight/controls/datagrid.aspx

It supports Export to Excel, XML, CSV and HTML

Jane