sl3中的DataGrid中的数据导出到Excel中 使用csv格式 解决中文是乱码的问题
首先建立一个简单的类作为数据源
<UserControl xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" x:Class="SlExportExcelCsv.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
>
<Grid x:Name="LayoutRoot" Loaded="LayoutRoot_Loaded">
<Grid>
<Grid.RowDefinitions>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
</Grid.RowDefinitions>
<data:DataGrid x:Name="dg"></data:DataGrid>
<Button x:Name = "btnExport" Content="导出到Excel" Grid.Row="1" Height="32" Width="100" FontSize="13" Margin="100,15,0,0" HorizontalAlignment="Left" VerticalAlignment="Bottom" Visibility="Visible" />
</Grid>
</Grid>
</UserControl>
private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)
{
List<Book> list = new List<Book>();
for (int i = 0; i < 4; i++)
{
Book book = new Book();
book.ID = i;
book.BookName = "程序员杂志" + i.ToString();
book.BookAuthor = "主编 黄长著" + i.ToString();
list.Add(book);
}
this.dg.ItemsSource = list;
void btnExport_Click(object sender, RoutedEventArgs e)
{
ExportExcel.ExportDataGridSaveAs(true,this.dg);
//throw new NotImplementedException();
//string data = ExportExcel.ExportDataGrid(true, this.dg);
//SaveFileDialog sfd = new SaveFileDialog();
//sfd.DefaultExt = "csv";
//sfd.Filter = "CSV Files (*.csv)|*.csv|All files (*.*)|*.*";
//sfd.FilterIndex = 1;
//if (sfd.ShowDialog() == true)
//{
// using (Stream stream = sfd.OpenFile())
// {
// using (StreamWriter sw = new StreamWriter(stream, System.Text.UnicodeEncoding.Unicode))
// {
// data = data.Replace(",", "\t");
// sw.Write(data);
// sw.Close();
// }
// stream.Close();
// }
//}
public static class ExportExcel
{
#region 导出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)
{
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();
}
/// <summary>
/// 导出DataGrid数据到Excel
/// </summary>
/// <param name="withHeaders">是否需要表头</param>
/// <param name="grid">DataGrid</param>
/// <returns>Excel内容字符串</returns>
public static string ExportDataGrid(bool withHeaders, DataGrid grid, bool dataBind)
{
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 DataGridTemplateColumn)
{
if (col.Header != null)
{
headers.Add(FormatCSVField(col.Header.ToString()));
}
else
{
headers.Add(string.Empty);
}
}
});
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 DataGridTemplateColumn)
{
FrameworkElement cellContent = col.GetCellContent(data);
TextBlock block = null;
if (cellContent.GetType() == typeof(Grid))
{
block = cellContent.FindName("TempTextblock") 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);
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, System.Text.UnicodeEncoding.Unicode))
{
data = data.Replace(",", "\t");
writer.Write(data);
writer.Close();
}
stream.Close();
}
}
}
#endregion 导出DataGrid数据到Excel
public class Book
{
public int ID { get; set; }
public string BookName { get; set; }
public string BookAuthor { get; set; }
}
{
public int ID { get; set; }
public string BookName { get; set; }
public string BookAuthor { get; set; }
}
Page.xaml界面设计，Page.xaml代码如下:
<UserControl xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" x:Class="SlExportExcelCsv.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
>
<Grid x:Name="LayoutRoot" Loaded="LayoutRoot_Loaded">
<Grid>
<Grid.RowDefinitions>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
</Grid.RowDefinitions>
<data:DataGrid x:Name="dg"></data:DataGrid>
<Button x:Name = "btnExport" Content="导出到Excel" Grid.Row="1" Height="32" Width="100" FontSize="13" Margin="100,15,0,0" HorizontalAlignment="Left" VerticalAlignment="Bottom" Visibility="Visible" />
</Grid>
</Grid>
</UserControl>
LayoutRoot_Loaded事件代码如下:
private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)
{
List<Book> list = new List<Book>();
for (int i = 0; i < 4; i++)
{
Book book = new Book();
book.ID = i;
book.BookName = "程序员杂志" + i.ToString();
book.BookAuthor = "主编 黄长著" + i.ToString();
list.Add(book);
}
this.dg.ItemsSource = list;
导出事件代码如下 :
void btnExport_Click(object sender, RoutedEventArgs e)
{
ExportExcel.ExportDataGridSaveAs(true,this.dg);
//throw new NotImplementedException();
//string data = ExportExcel.ExportDataGrid(true, this.dg);
//SaveFileDialog sfd = new SaveFileDialog();
//sfd.DefaultExt = "csv";
//sfd.Filter = "CSV Files (*.csv)|*.csv|All files (*.*)|*.*";
//sfd.FilterIndex = 1;
//if (sfd.ShowDialog() == true)
//{
// using (Stream stream = sfd.OpenFile())
// {
// using (StreamWriter sw = new StreamWriter(stream, System.Text.UnicodeEncoding.Unicode))
// {
// data = data.Replace(",", "\t");
// sw.Write(data);
// sw.Close();
// }
// stream.Close();
// }
//}
导出类文件如下:
public static class ExportExcel
{
#region 导出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)
{
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();
}
/// <summary>
/// 导出DataGrid数据到Excel
/// </summary>
/// <param name="withHeaders">是否需要表头</param>
/// <param name="grid">DataGrid</param>
/// <returns>Excel内容字符串</returns>
public static string ExportDataGrid(bool withHeaders, DataGrid grid, bool dataBind)
{
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 DataGridTemplateColumn)
{
if (col.Header != null)
{
headers.Add(FormatCSVField(col.Header.ToString()));
}
else
{
headers.Add(string.Empty);
}
}
});
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 DataGridTemplateColumn)
{
FrameworkElement cellContent = col.GetCellContent(data);
TextBlock block = null;
if (cellContent.GetType() == typeof(Grid))
{
block = cellContent.FindName("TempTextblock") 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);
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, System.Text.UnicodeEncoding.Unicode))
{
data = data.Replace(",", "\t");
writer.Write(data);
writer.Close();
}
stream.Close();
}
}
}
#endregion 导出DataGrid数据到Excel
源程序下载地址:https://files.cnblogs.com/z_lb/SlExportExcelCsv.zip
