用拼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,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("\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","")); } /// <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 } }
