本文介绍了如何在WPF应用中利用EPPlus库将DataGrid数据导出到Excel文件，以及如何将DataGrid内容导入CSV。通过打开文件保存对话框选择路径，新建文档，遍历列和行，结合反射获取并转换数据，实现了数据的高效迁移。
WPF实现DataGrid 导出到Excel|CSV
WPF 利用EPPlus实现DataGrid 导出到Excel
基本思路
- 找开文件保存对话框，选择文件保存路径.
- 新建文档
- 遍历表格列，建立List 集合，并写入表头。
- 遍历各行。判断是绑定列或者是自定义模板列，根据列的Binding 对象，得到objBinding.Path.Path绑定属性; item为行数据的实例对象，反射属性值。
- 判断是否存在有转换器。如有，进行数据转换。
- 写入数据。
private void SaveExcelWithEpplus(DataGrid dGrid, string sheetName)
{
//选择保存名称
string saveFilePath="";
Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog()
{
DefaultExt = "xlsx",
Filter = "XLS Files (*.xlsx)|*.xlsx",
FilterIndex = 1
};
if (sfd.ShowDialog() == true)
saveFilePath=sfd.FullName
else
return;
if (dGrid != null)
{
using (var package = new ExcelPackage())
{
// 新增工作表
ExcelWorksheet ws = package.Workbook.Worksheets.Add(sheetName);
//获得显示的表头
List<DataGridColumn> displayCols = new List<DataGridColumn>();
if (dGrid.HeadersVisibility == DataGridHeadersVisibility.Column
|| dGrid.HeadersVisibility == DataGridHeadersVisibility.All)
{
foreach (DataGridColumn col in dGrid.Columns)
{
if (col.Visibility == Visibility.Visible)
{
displayCols.Add(col);
}
}
}
//写表头
int colIndex = 0;
foreach (var c in displayCols)
{
colIndex++;
ws.Cells[1, colIndex].Value = c.Header;
ws.Cells[1, colIndex].Style.Border.BorderAround(ExcelBorderStyle.Thin);
}
//设置表头格式
using (var range = ws.Cells[1, 1, 1, displayCols.Count])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
range.Style.Border.BorderAround(ExcelBorderStyle.Double);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
//写行数据
int rowIndex = 1;
object objValue = null;
foreach (var item in dGrid.Items)
{
rowIndex++;
colIndex = 0;
foreach (var col in displayCols)
{
colIndex++;
Binding objBinding = null;
if (col is DataGridBoundColumn) //如果是绑定列
{
objBinding = (col as DataGridBoundColumn).Binding as Binding;
}
else if (col is DataGridTemplateColumn)//如果是模板列
{
DependencyObject o = (col as DataGridTemplateColumn).CellTemplate.LoadContent();
FrameworkElement element = (FrameworkElement)o;
FieldInfo fieldInfo = element.GetType().GetField("TextProperty");
if (fieldInfo != null)
{
if (fieldInfo.GetValue(null) != null)
{
if (element.GetBindingExpression((DependencyProperty)fieldInfo.GetValue(null)) != null)
objBinding = element.GetBindingExpression((DependencyProperty)fieldInfo.GetValue(null)).ParentBi
}
}
}
//绑定对象的属性名
string Field = null;
if (objBinding != null)
{
Field = objBinding.Path.Path;//LeagueName
//如果绑定属性不为空，则通过反射得到相应的值
if (!string.IsNullOrEmpty(Field))
{
PropertyInfo pi = item.GetType().GetProperty(Field);
if (pi != null) objValue = pi.GetValue(item, null);
//如果存在转换器，则进行转换
if (objBinding.Converter != null)
{
if (objValue != null)
objValue = objBinding.Converter.Convert(objValue, typeof(string),
objBinding.ConverterParameter,
objBinding.ConverterCulture).ToString();
else
objValue = objBinding.Converter.Convert(item, typeof(string),
objBinding.ConverterParameter,
objBinding.ConverterCulture).ToString();
}
using (var range = ws.Cells[rowIndex, colIndex])
{
//数值或日期可能存在StringFormat
if (objBinding.StringFormat != null && pi.PropertyType.Name != "String")
{
range.Value = Convert.ToDouble(objValue);
range.Style.Numberformat.Format = "0.00"; //保存小数2位
}
else
{
range.Value = objValue.ToString();
}
range.Style.Border.BorderAround(ExcelBorderStyle.Thin);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
}
}
}
}
package.SaveAs(new FileInfo(saveFilePath));
}
}
}
WPF中将DataGrid导入CSV方法
/// <summary>
/// WPF中将DataGrid导入CSV方法
/// </summary>
/// <param name="sfileName"></param>
/// <param name="dataGrid"></param>
public void WPF_DataGridToCSV(string sfileName, DataGrid dataGrid)
{
//根据arg传递的Grid对象，写入 csv
dataGrid.SelectAllCells();
dataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
ApplicationCommands.Copy.Execute(null, dataGrid);
dataGrid.UnselectAllCells();
string Clipboardresult = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
using (FileStream fs = new FileStream(sfileName, FileMode.CreateNew))
{
using (var sw = new StreamWriter(fs, System.Text.Encoding.UTF8))
{
sw.WriteLine(Clipboardresult);
}
}
}
private void SaveExcelWithEpplus(DataGrid dGrid)
{
//选择保存名称
string saveFilePath = "";
Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog()
{
DefaultExt = "xlsx",
Filter = "XLS Files (*.xlsx)|*.xlsx",
FilterIndex = 1
};
if (sfd.ShowDialog() == true)
saveFilePath = sfd.FileName;
else
return;
if (dGrid != null)
{
using (var package = new ExcelPackage())
{
// 新增工作表
ExcelWorksheet ws = package.Workbook.Worksheets.Add("Data");
//获得显示的表头
List<DataGridColumn> displayCols = new List<DataGridColumn>();
if (dGrid.HeadersVisibility == DataGridHeadersVisibility.Column
|| dGrid.HeadersVisibility == DataGridHeadersVisibility.All)
{
foreach (DataGridColumn col in dGrid.Columns)
{
if (col.Visibility == Visibility.Visible)
{
displayCols.Add(col);
}
}
}
//写表头
int colIndex = 0;
foreach (var c in displayCols)
{
colIndex++;
ws.Cells[1, colIndex].Value = c.Header;
ws.Cells[1, colIndex].Style.Border.BorderAround(ExcelBorderStyle.Thin);
}
//设置表头格式
using (var range = ws.Cells[1, 1, 1, displayCols.Count])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
range.Style.Border.BorderAround(ExcelBorderStyle.Double);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
//写行数据
int rowIndex = 1;
object objValue = null;
bool isMultiBinding = false;
foreach (var item in dGrid.Items)
{
rowIndex++;
colIndex = 0;
foreach (var col in displayCols)
{
isMultiBinding = false;
colIndex++;
BindingBase objBinding = null;
if (col is DataGridBoundColumn) //如果是绑定列
{
DataGridBoundColumn colbing = col as DataGridBoundColumn;
var type = colbing.Binding.GetType().Name;//Binding MultiBinding
if (colbing.Binding.GetType().Name == "MultiBinding")
{
isMultiBinding = true;
}
objBinding = colbing.Binding;
}
else if (col is DataGridTemplateColumn)//如果是模板列
{
DependencyObject o = (col as DataGridTemplateColumn).CellTemplate.LoadContent();
FrameworkElement element = (FrameworkElement)o;
FieldInfo fieldInfo = element.GetType().GetField("TextProperty");
if (fieldInfo != null)
{
if (fieldInfo.GetValue(null) != null)
{
if (element.GetBindingExpression((DependencyProperty)fieldInfo.GetValue(null)) != null)
objBinding = element.GetBindingExpression((DependencyProperty)fieldInfo.GetValue(null)).ParentBinding;
}
}
}
//绑定对象的属性名
string Field = null;
if (objBinding != null)
{
if (isMultiBinding)
{
var bds = objBinding as MultiBinding;
List<object> objList = new List<object>();
foreach (Binding bd in bds.Bindings)
{
Field = bd.Path.Path;//LeagueName
PropertyInfo pi = item.GetType().GetProperty(Field);
if (pi != null) objValue = pi.GetValue(item, null);
objList.Add(objValue);
}
//如果存在转换器，则进行转换
if (bds.Converter != null)
{
if (objValue != null)
objValue = bds.Converter.Convert(objList.ToArray(), typeof(string),
bds.ConverterParameter,
bds.ConverterCulture).ToString();
}
}
else
{
var bd = objBinding as Binding;
Field = bd.Path.Path;//LeagueName
PropertyInfo pi = item.GetType().GetProperty(Field);
if (pi != null) objValue = pi.GetValue(item, null);
//如果存在转换器，则进行转换
if (bd.Converter != null)
{
if (objValue != null)
objValue = bd.Converter.Convert(objValue, typeof(string),
bd.ConverterParameter,
bd.ConverterCulture).ToString();
else
objValue = bd.Converter.Convert(item, typeof(string),
bd.ConverterParameter,
bd.ConverterCulture).ToString();
}
}
}
using (var range = ws.Cells[rowIndex, colIndex])
{
//数值或日期可能存在StringFormat
if (objBinding.StringFormat != null)
{
range.Value = Convert.ToDouble(objValue);
range.Style.Numberformat.Format = "0.00"; //保存小数2位
}
else
{
range.Value = objValue.ToString();
}
range.Style.Border.BorderAround(ExcelBorderStyle.Thin);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
}
}
package.SaveAs(new FileInfo(saveFilePath));
}
}
}
扫一扫
2356
到【灌水乐园】发言
