C# Silverlight DataGrid：导出到 excel 或 csv

Silverlight DataGrid: Export to excel or csv

提问by

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

有没有办法将我的 Silverlight DataGrid 数据导出到 excel 或 csv？

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

我在网上搜索，但找不到任何示例！

Thanks a lot

非常感谢

回答by Dave Swersky

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.

我不认为 Silverlight 提供下载文件的方法。您可以向您的应用程序添加一个调用 URL 的按钮 - 即http://www.mysite.com/generateexcelfile.aspx。将用于生成在 Silverlight 应用程序中显示的数据的参数作为查询字符串值包括在内，运行您的查询并使用您最喜欢的 Excel 文件生成组件来即时生成文件。重定向到它，它将下载到用户的系统。

回答by DaniCE

I have found thisusing 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).

当然，只有当绑定的路径是属性名时才有效。对于更高级的路径，您必须将绑定应用于数据（我认为这将是一个更好的解决方案，但我现在不确定如何执行此操作）。

回答by Jeff Yates

Off the top of my head I would say you can add the export button using a ControlTemplateand then iterate over each item in the DataSource, and then use each column in Columnsto obtain the content of each cell using the GetCellContentmethod, 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.

在我的头顶上，我会说您可以使用 a 添加导出按钮ControlTemplate，然后遍历 中的每个项目DataSource，然后使用Columns该GetCellContent方法中的每一列获取每个单元格的内容，或者使用 DataGridColumn 的绑定信息获取适当的单元格值。然后，您可以获取此内容的显示值并将其写入您的报告。

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.

或者使用DaniCE描述的绑定信息。

回答by t3rse

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.

Silverlight 3 更改了此问题的答案，因为它使用户能够在用户桌面上指定的位置创建文件。我改编了 DaniCE 提交的代码，将事情分成几种方法以提高可读性，并使用 Excel 应该识别的松散定义的 CSV 格式。

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

回答by infocyde

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

查看 Ryan 的解决方案。看起来不错，但不能保证，因为我刚刚找到它。Ryan 执行了上面要求的 DLL。

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

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

上面 Dakota 解决方案中的 David 看起来更容易实现，并且总是重定向到经典的 asp.net 页面，上面有数据网格，内容类型设置为 excel，但是你有更多的代码需要支持，这样可能不适用于离线的浏览器解决方案（实际上不起作用）。

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

无论如何，这是一项常见的任务。我希望这里或微软的一些人想出一个 Mort 即插即用的解决方案:)

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

我找到了一种从 Silverlight 数据网格导出 CVS 的扩展方法-

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

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.

它具有即插即用的潜力，但我不得不稍微调整一下才能让它与具有项目数据源的数据网格一起工作（请参阅帖子中的评论）。比我更聪明、更有经验的人应该能够调整到完美。检查一下，它应该让你接近你所需要的。

回答by Fedor

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

这是一个对我有用的好方法 http://forums.silverlight.net/forums/p/179321/404357.aspx

回答by Andrew

I know this is an old post but it did help me. I have done some edits to make it work with silverlight 4, converts and excel. I wanted the fast export so I first use CSV then open it with excel. This code works silverlight web and oob elevated trust. In Web will not open in excel.

我知道这是一个旧帖子，但它确实帮助了我。我进行了一些编辑，使其适用于 Silverlight 4、转换和 excel。我想要快速导出，所以我首先使用 CSV，然后用 excel 打开它。此代码适用于 Silverlight Web 和 oob 提升信任度。在 Web 中不会在 excel 中打开。

     private static void OpenExcelFile(string Path)
    {
        dynamic excelApp;
        excelApp = AutomationFactory.CreateObject("Excel.Application");
        dynamic workbook = excelApp.workbooks;
        object oMissing = Missing.Value;

        workbook = excelApp.Workbooks.Open(Path,

           oMissing, oMissing, oMissing, oMissing, oMissing,

           oMissing, oMissing, oMissing, oMissing, oMissing,

           oMissing, oMissing, oMissing, oMissing);



        dynamic sheet = excelApp.ActiveSheet;


        // open the existing sheet


        sheet.Cells.EntireColumn.AutoFit();
        excelApp.Visible = true;
    }
    private static string FormatCSVField(string data)
    {
        return String.Format("\"{0}\"",
            data.Replace("\"", "\"\"\"")
            .Replace("\n", "")
            .Replace("\r", "")
            );
    }
   public  static string ExportDataGrid(DataGrid grid,string SaveFileName,bool AutoOpen)
    {
        string colPath;
        System.Reflection.PropertyInfo propInfo;
        System.Windows.Data.Binding binding;
        System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
       var source = grid.ItemsSource;

        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 (var 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)
                        {
                            string valueConverted = "";
                            if (binding.Converter.GetType().ToString() != "System.Windows.Controls.DataGridValueConverter")
                                valueConverted = binding.Converter.Convert(propInfo.GetValue(data, null), typeof(System.String), binding.ConverterParameter, System.Globalization.CultureInfo.CurrentCulture).ToString();
                            else
                                valueConverted = FormatCSVField(propInfo.GetValue(data, null) == null ? "" : propInfo.GetValue(data, null).ToString());

                            csvRow.Add(valueConverted.ToString());
                        }
                    }
                }
                strBuilder
                    .Append(String.Join(",", csvRow.ToArray()))
                    .Append("\r\n");
            }

        if (AutomationFactory.IsAvailable)
        {
            var sampleFile = "\" + SaveFileName;
            var path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            path += "\Pement";


            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }
            else
            {
                var files = System.IO.Directory.EnumerateFiles(path);
                foreach (var item in files)
                {
                    try
                    {
                        System.IO.File.Delete(item);
                    }
                    catch { }
                }
            }

            StreamWriter sw = File.CreateText(path + sampleFile);
            sw.WriteLine(strBuilder.ToString());
            sw.Close();

            if (AutoOpen)
                OpenExcelFile(path + sampleFile, true, true);
        }
        else
        {
            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(strBuilder.ToString());
                        writer.Close();
                    }
                    stream.Close();
                }
            } 
        }
        return strBuilder.ToString();
    }

回答by VenerableAgents

Those solutions didn't work for me, so I modified them to one that worked. (My solution doesn't require quotes around fields, so I left out the FormatCSVField function)

这些解决方案对我不起作用，所以我将它们修改为有效的解决方案。（我的解决方案不需要在字段周围加上引号，所以我省略了 FormatCSVField 函数）

    public void SaveAs(string csvPath)
    {
        string data = ExportDataGrid(true, _flexGrid);
        StreamWriter sw = new StreamWriter(csvPath, false, Encoding.UTF8);
        sw.Write(data);
        sw.Close();
    }

    public string ExportDataGrid(bool withHeaders, Microsoft.Windows.Controls.DataGrid grid) 
    {
        System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
        System.Collections.IEnumerable source = (grid.ItemsSource as System.Collections.IEnumerable);

        if (source == null) return "";

        List<string> headers = new List<string>();

        grid.Columns.ToList().ForEach(col =>
        {
            if (col is Microsoft.Windows.Controls.DataGridBoundColumn)
            {
                headers.Add(col.Header.ToString());
            }
        });

        strBuilder.Append(String.Join(",", headers.ToArray())).Append("\r\n");
        foreach (Object data in source)
        {
            System.Data.DataRowView d = (System.Data.DataRowView)data;
            strBuilder.Append(String.Join(",", d.Row.ItemArray)).Append("\r\n");
        }

        return strBuilder.ToString();
    }

回答by John Gilmer

I needed to do the same thing. I used the implementation by t3rse, but had to make a few changes. I don't have enough reputation to do a comment on his answer, so I will list them here:

我需要做同样的事情。我使用了 t3rse 的实现，但必须进行一些更改。我没有足够的声誉对他的回答发表评论，所以我将在此处列出：

• For the line that says propInfo.GetValue(data, null).ToString(), I checked to see if the value returned by GetValue is Null before calling ToString() on it.

• In the method FormatCSVField(), it replaced a double quote with three double quotes. It should only replace it with two double quotes.

• The implementation only uses columns of type DataGridBoundColumn and ignores others. I have columns that are not DataGridBoundColumn that I wanted included, so I got the property name of the data source for those columns with col.SortMemberPath.

• 对于表示 propInfo.GetValue(data, null).ToString() 的行，我在调用 ToString() 之前检查了 GetValue 返回的值是否为 Null。

• 在 FormatCSVField() 方法中，它用三个双引号替换了一个双引号。它应该只用两个双引号替换它。

• 该实现仅使用 DataGridBoundColumn 类型的列并忽略其他列。我有一些不是我想要包含的 DataGridBoundColumn 的列，所以我使用 col.SortMemberPath 获得了这些列的数据源的属性名称。