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