你的位置:首页 > ASP.net教程

[ASP.net教程]wpf 窗口程序下将datagrid导出为excel


    今天用了几个小时也没有找到将datagrid导出为excel的方法,搜索msdn发现,老外也木有解决这个问题,因此把代码贴出来,和大家分享一下,提高工作效率。简要说一哈,本程序使用反射,因此代码量看起来,很少,但是执行效率,还在没有进行优化处理。

  #region wpf客户端 导出DataGrid数据到Excel    /// <summary>    /// CSV格式化    /// </summary>    /// <param name="data">数据</param>    /// <returns>格式化数据</returns>    private static string FormatCsvField(string data)    {      return String.Format("\"{0}\"", data.Replace("\"", "\"\"\"").Replace("\n", "").Replace("\r", ""));    }        /// <summary>    /// 导出DataGrid数据到Excel    /// </summary>    /// <param name="withHeaders">是否需要表头</param>    /// <param name="grid">DataGrid</param>    /// <param name="dataBind"></param>    /// <returns>Excel内容字符串</returns>    public static string ExportDataGrid(bool withHeaders, System.Windows.Controls.DataGrid grid, bool dataBind)    {      try      {        var strBuilder = new System.Text.StringBuilder();        var source = (grid.ItemsSource as System.Collections.IList);        if (source == null) return "";        var headers = new List<string>();        List<string> bt = new List<string>();        foreach (var hr in grid.Columns)        {          //  DataGridTextColumn textcol = hr. as DataGridTextColumn;          headers.Add(hr.Header.ToString());          if (hr is DataGridTextColumn)//列绑定数据          {            DataGridTextColumn textcol = hr as DataGridTextColumn;            if (textcol != null)              bt.Add((textcol.Binding as Binding).Path.Path.ToString());    //获取绑定源                         }else if(hr is DataGridTemplateColumn)          {            if(hr.Header.Equals("操作"))            bt.Add("Id");          }          else          {          }        }        strBuilder.Append(String.Join(",", headers.ToArray())).Append("\r\n");        foreach (var data in source)        {          var csvRow = new List<string>();          foreach(var ab in bt)          {            string s = ReflectionUtil.GetProperty(data, ab).ToString();            if(s!=null)            {              csvRow.Add(FormatCsvField(s));            }            else            {              csvRow.Add("\t");            }          }          strBuilder.Append(String.Join(",", csvRow.ToArray())).Append("\r\n");          // strBuilder.Append(String.Join(",", csvRow.ToArray())).Append("\t");        }        return strBuilder.ToString();      }catch(Exception ex)      {        LogHelper.Error(ex);        return "";      }    }    /// <summary>    /// 导出DataGrid数据到Excel为CVS文件    /// 使用utf8编码 中文是乱码 改用Unicode编码    ///     /// </summary>    /// <param name="withHeaders">是否带列头</param>    /// <param name="grid">DataGrid</param>    public static void ExportDataGridSaveAs(bool withHeaders, System.Windows.Controls.DataGrid grid)    {      try      {        string data = ExportDataGrid(true, grid, true);        var sfd = new Microsoft.Win32.SaveFileDialog        {          DefaultExt = "csv",          Filter = "CSV Files (*.csv)|*.csv|All files (*.*)|*.*",          FilterIndex = 1        };        if (sfd.ShowDialog() == true)        {          using (Stream stream = sfd.OpenFile())          {            using (var writer = new StreamWriter(stream, System.Text.Encoding.Unicode))            {              data = data.Replace(",", "\t");              writer.Write(data);              writer.Close();            }            stream.Close();          }        }        MessageBox.Show("导出成功!");      }catch(Exception ex)      {        LogHelper.Error(ex);      }    }    #endregion 导出DataGrid数据到Excel  }