siverlight datagrid 导出xml或csv格式,保持颜色一致性
|
用拼xml方式导出excel xml对应的excel格式可以自己在建立一个Excel，另存为xml格式，可以看到表格合并，类型等等的拼xml方法 变颜色拼法 sw.WriteLine(" <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">"); //需要什么字体去引用id就行了例如上边的ss:ID='s28',拼样式的时候貌似都要加个ss:ID="Default"，让没有直接指定id的使用默认的样式
需要导出的excel与数据源的颜色一致只需把需要变颜色的坐标记录下来就行了 例如此时diccor 是key是行列的坐标,value就是需要变成的颜色的id,导出时需要得到每个格子的值，当然也能得到坐标,就可以变成相应的颜色了 Dictionary<string,string> diccor = new Dictionary<string,string>();
using System; using System.Windows;using System.Windows.Data; using System.Windows.Media; using System.Windows.Controls; using System.Collections; using System.Collections.Generic; using System.Collections.ObjectModel; using System.Linq; using System.Text; using System.IO; using System.Reflection; public static class DataGridExtensions { public static int length { get; set; } public static void Export(this DataGrid dg) { ExportDataGrid(dg);//dg is DataGrid name } public static void ExportDataGrid(DataGrid dGrid) { SaveFileDialog objSFD = new SaveFileDialog() { DefaultExt = "csv",Filter = "CSV Files (*.csv)|*.csv|Excel XML (*.xml)|*.xml|All files (*.*)|*.*",FilterIndex = 2 }; if (objSFD.ShowDialog() == true) { string strFormat = objSFD.SafeFileName.Substring(objSFD.SafeFileName.IndexOf('.') + 1).ToUpper(); StringBuilder strBuilder = new StringBuilder(); //if (dGrid.ItemsSource == null) return; List<string> lstFields = new List<string>(); //表头,这里不要表头了,因为表头是另外一个文件 //if (dGrid.HeadersVisibility == DataGridHeadersVisibility.Column || dGrid.HeadersVisibility == DataGridHeadersVisibility.All) //{ // foreach (DataGridColumn dgcol in dGrid.Columns) // lstFields.Add(FormatField(dgcol.Header.ToString(),strFormat)); // BuildStringOfRow(strBuilder,lstFields,strFormat); //} strBuilder.AppendLine(HeaderStr(length)); foreach (object data in dGrid.ItemsSource) { lstFields.Clear(); foreach (DataGridColumn col in dGrid.Columns) { string strValue = ""; Binding objBinding = null; if (col is DataGridBoundColumn) objBinding = (col as DataGridBoundColumn).Binding; if (col is DataGridTemplateColumn) { //This is a template column... let us see the underlying dependency object DependencyObject objDO = (col as DataGridTemplateColumn).CellTemplate.LoadContent(); FrameworkElement oFE = (FrameworkElement)objDO; FieldInfo oFI = oFE.GetType().GetField("TextProperty"); if (oFI != null) { if (oFI.GetValue(null) != null) { if (oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)) != null) objBinding = oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)).ParentBinding; } } } if (objBinding != null) { if (objBinding.Path.Path != "") { PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path); if (pi != null) { if (pi.GetValue(data,null) != null) { strValue = pi.GetValue(data,null).ToString(); } else { strValue = ""; } } } if (objBinding.Converter != null) { if (strValue != "") strValue = objBinding.Converter.Convert(strValue,typeof(string),objBinding.ConverterParameter,objBinding.ConverterCulture).ToString(); else strValue = ""; //strValue = objBinding.Converter.Convert(data,objBinding.ConverterCulture).ToString(); } } lstFields.Add(FormatField(strValue,strFormat)); } BuildStringOfRow(strBuilder,strFormat); } StreamWriter sw = new StreamWriter(objSFD.OpenFile(),System.Text.Encoding.UTF8);//中文乱码处理哦 if (strFormat == "XML") { //Let us write the headers for the Excel XML sw.WriteLine("<?xml version='1.0' encoding='utf-8'?>"); sw.WriteLine("<?mso-application progid='Excel.Sheet'?>"); sw.WriteLine("<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'>"); sw.WriteLine("<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>"); sw.WriteLine("<Author>Arasu Elango</Author>"); sw.WriteLine("<Created>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</Created>"); sw.WriteLine("<LastSaved>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</LastSaved>"); sw.WriteLine("<Company>Atom8 IT Solutions (P) Ltd.,</Company>"); sw.WriteLine("<Version>12.00</Version>"); sw.WriteLine("</DocumentProperties>"); sw.WriteLine("<Worksheet ss:Name='Silverlight Export' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>"); sw.WriteLine("<Table>"); } sw.Write(strBuilder.ToString()); if (strFormat == "XML") { sw.WriteLine("</Table>"); sw.WriteLine("</Worksheet>"); sw.WriteLine("</Workbook>"); } sw.Close(); } } private static void BuildStringOfRow(StringBuilder strBuilder,List<string> lstFields,string strFormat) { switch (strFormat) { case "XML": strBuilder.AppendLine("<Row>"); strBuilder.AppendLine(String.Join("rn",lstFields.ToArray())); strBuilder.AppendLine("</Row>"); break; case "CSV": strBuilder.AppendLine(String.Join(",",lstFields.ToArray())); break; } } private static string FormatField(string data,string format) { switch (format) { case "XML": return String.Format("<Cell><Data ss:Type='String'>{0}</Data></Cell>",data); case "CSV": return String.Format("'0}'",data.Replace("'","''").Replace("n","").Replace("r","")); } return data; } private static string HeaderStr(int length) //单独拼接表头 { string teableheader = "<Row><Cell ss:MergeDown='1'><Data ss:Type='String'>车辆自编号</Data></Cell>"; for (int i = 0; i < length; i++) { teableheader += "<Cell ss:MergeAcross='3'><Data ss:Type='Number'>" + (i + 1) + "</Data></Cell>"; } teableheader += "</Row><Row><Cell ss:Index='2'><Data ss:Type='String'>到站</Data></Cell>"; for (int i = 0; i < length; i++) { if (i == 0)//第二列的第一行才要合并ss:Index='2'所以单独提出来 { teableheader += @"<Cell><Data ss:Type='String'>计划</Data></Cell>" + "<Cell><Data ss:Type='String'>实际</Data></Cell>" + "<Cell><Data ss:Type='String'>备注</Data></Cell>"; } else { teableheader += @"<Cell><Data ss:Type='String'>到站</Data></Cell> <Cell><Data ss:Type='String'>计划</Data></Cell>" + "<Cell><Data ss:Type='String'>实际</Data></Cell>" + "<Cell><Data ss:Type='String'>备注</Data></Cell>"; } } teableheader += "</Row>"; return teableheader; } }
导出csv using System; using System.Net; using System.Windows; using System.Windows.Controls; using System.Windows.Documents; using System.Windows.Ink; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Animation; using System.Windows.Shapes; using System.IO; using System.Collections.Generic; using System.Linq; namespace UH.Service { public class ExportExcel { #region 导出Siverlight 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","")); } /// <summary> /// 导出DataGrid数据到Excel /// </summary> /// <param name="withHeaders">是否需要表头</param> /// <param name="grid">DataGrid</param> /// <returns>Excel内容字符串</returns> public static string ExportDataGrid(bool withHeaders,DataGrid grid) { System.Reflection.PropertyInfo propInfo; System.Windows.Data.Binding binding; var strBuilder = new System.Text.StringBuilder(); var source = (grid.ItemsSource as System.Collections.IList); if (source == null) return ""; var 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("rn"); foreach (Object data in source) { var csvRow = new List<string>(); foreach (DataGridColumn col in grid.Columns) { if (col is DataGridBoundColumn) { binding = (col as DataGridBoundColumn).Binding; string colPath = binding.Path.Path; propInfo = data.GetType().GetProperty(colPath); if (propInfo != null) { if (propInfo.GetValue(data,null) == null) { csvRow.Add(FormatCsvField("")); } else { csvRow.Add(FormatCsvField(propInfo.GetValue(data,null).ToString())); } } } } strBuilder.Append(String.Join(",csvRow.ToArray())).Append("rn"); } return strBuilder.ToString(); } /// <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,DataGrid grid,bool dataBind) { var strBuilder = new System.Text.StringBuilder(); var source = (grid.ItemsSource as System.Collections.IList); if (source == null) return ""; var headers = new List<string>(); grid.Columns.ToList().ForEach(col => { if (col is DataGridTemplateColumn) { headers.Add(col.Header != null ? FormatCsvField(col.Header.ToString()) : string.Empty); } }); strBuilder.Append(String.Join(",headers.ToArray())).Append("rn"); foreach (Object data in source) { var csvRow = new List<string>(); foreach (DataGridColumn col in grid.Columns) { if (col is DataGridTemplateColumn) { FrameworkElement cellContent = col.GetCellContent(data); TextBlock block; if (cellContent.GetType() == typeof(Grid)) { block = cellContent.FindName("TempTextblock") as TextBlock; //block = cellContent.FindName("Textblock") as TextBlock; } else { block = cellContent as TextBlock; } if (block != null) { csvRow.Add(FormatCsvField(block.Text)); } } } strBuilder.Append(String.Join(",csvRow.ToArray())).Append("rn"); //strBuilder.Append(String.Join(",csvRow.ToArray())).Append("t"); } return strBuilder.ToString(); } /// <summary> /// 导出DataGrid数据到Excel为CVS文件 /// 使用utf8编码 中文是乱码 改用Unicode编码 /// /// </summary> /// <param name="withHeaders">是否带列头</param> /// <param name="grid">DataGrid</param> public static void ExportDataGridSaveAs(bool withHeaders,DataGrid grid) { string data = ExportDataGrid(true,grid); var sfd = new 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("导出成功"); } } } #endregion 导出DataGrid数据到Excel } } （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |