siverlight datagrid 导出xml或csv格式,保持颜色一致性

最新推荐文章于 2015-05-23 15:55:28 发布

tnblog技术分享～

最新推荐文章于 2015-05-23 15:55:28 发布

阅读量1k

点赞数

分类专栏： siverlight

siverlight 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

用拼xml方式导出excel

xml对应的excel格式可以自己在建立一个Excel，另存为xml格式，可以看到表格合并，类型等等的拼xml方法

变颜色拼法

sw.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine(" <WindowHeight>8955</WindowHeight>");
sw.WriteLine(" <WindowWidth>11355</WindowWidth>");
sw.WriteLine(" <WindowTopX>480</WindowTopX>");
sw.WriteLine(" <WindowTopY>15</WindowTopY>");
sw.WriteLine(" <ProtectStructure>False</ProtectStructure>");
sw.WriteLine(" <ProtectWindows>False</ProtectWindows>");
sw.WriteLine(" </ExcelWorkbook>");
sw.WriteLine(" <Styles>");
sw.WriteLine(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
sw.WriteLine(" <Alignment ss:Vertical=\"Center\"/>");
sw.WriteLine(" <Borders/>");
sw.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>");
sw.WriteLine(" <Interior/>");
sw.WriteLine(" <NumberFormat/>");
sw.WriteLine(" <Protection/>");
sw.WriteLine(" </Style>");
sw.WriteLine("<Style ss:ID='s28'><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12' ss:Color='#FF0000'/></Style>");
sw.WriteLine(" </Styles>");

//需要什么字体去引用id就行了例如上边的ss:ID='s28',拼样式的时候貌似都要加个ss:ID=\"Default\"，让没有直接指定id的使用默认的样式

需要导出的excel与数据源的颜色一致只需把需要变颜色的坐标记录下来就行了

例如此时diccor 是key是行列的坐标,value就是需要变成的颜色的id,导出时需要得到每个格子的值，当然也能得到坐标,就可以变成相应的颜色了

Dictionary<string, string> diccor = new Dictionary<string, string>();
foreach (var item in dic)
{
diccor[item.Key + "" + item.Value] = "ss:StyleID='s28'";
}

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, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
}
}
lstFields.Add(FormatField(strValue, strFormat));
}
BuildStringOfRow(strBuilder, lstFields, 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("\r\n", 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", "").Replace("\r", ""));
}
/// <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("\r\n");
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("\r\n");
}
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("\r\n");
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("\r\n");
//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
}
}