笔记记录,设计有一定缺陷,同行指正,另:转载请注明
WPF的DataGrid导出到Excel
工作上用到,将wpf界面datagrid导出未Excel表格,观摩前人代码,改进了一下:如果属性是decimal、int等数值类型,在导入到Excel中时,都转换成了相应的格式类型,如有大神看见,请帮忙指点优化思路。
第一次写博客记录之前的代码,很多地方都不是很仔细,欢迎讨论!
1.界面通过按钮控制导出事件
//前端导出按钮触发事件
private void BtnExcel_Click(object sender, RoutedEventArgs e)
{
if(Data == null)
{
MessageBoxBase.ShowMessage("导出的数据不能为空!");
return;
}
using (ExcelHelper excel = new ExcelHelper())
{
string sheetName = "datagrid数据导出";
string filename = string.Format("{0}_{1}.xls", sheetName, DateTime.Now.ToString("yyyyMMddHHmmss"));
if(excel.SaveToExcel<Model>(Data, filename, sheetName))
MessageBox.Show("导出成功!");
}
}
2.具体的导出方法
public class ExcelHelper : IDisposable
{
private string fileName = null; //文件名
private IWorkbook workbook = null;
private FileStream fs = null;
private bool disposed;
public ExcelHelper()//构造函数,读入文件名
{
disposed = false;
}
/// <summary>
/// 将泛型集合保存到EXCEL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <param name="filename">EXCEL文件名(包括路径)</param>
/// <param name="sheetName">标签名称</param>
/// <param name="isColumnWritten">是否写列名称</param>
public bool SaveToExcel<T>(IEnumerable<T> collection,string filename,string sheetName, bool isColumnWritten = true)
{
DataTable data = ToDataTable<T>(collection);
if (data != null)
{
data.TableName = sheetName;
if (SaveFileDialog(filename))
{
return DataTableListToExcel(new List<DataTable>(new DataTable[] { data }),isColumnWritten);
}
}
return false;
}
/// <summary>
/// 将集合转换成DataTable,通过特性标志获取需要转换的数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection">转换数据</param>
/// <param name="tableName">表名(用于生成Execl的Sheet名字)</param>
/// <returns></returns>
public DataTable ToDataTable<T>(IEnumerable<T> collection, string tableName = null)
{
var props = typeof(T).GetProperties();
var dt = new DataTable();
var propList = props.Where(p =>
p.GetCustomAttributes(typeof(ExportExcelObjectSignAttribute), true).Length > 0);
props = propList.OrderBy(p =>
((ExportExcelObjectSignAttribute)
(p.GetCustomAttributes(typeof(ExportExcelObjectSignAttribute), true)[0])).Index).ToArray();
if (props.Length <= 0)
{
throw new Exception(typeof(T).FullName +
"中的属性缺少特性ExportExcelObjectSignAttribute(注:只针对需要导出的属性添加ExportExcelObjectSignAttribute特性)");
}
foreach (var property in props)
{
object[] objs = property.GetCustomAttributes(typeof(ExportExcelObjectSignAttribute), true);
if (objs.Length > 0)
{
dt.Columns.Add(new DataColumn(((ExportExcelObjectSignAttribute) objs[0]).Display,
property.PropertyType));
}
}
if (collection == null || collection.Count() == 0)
{
return dt;
}
if (collection.Count() > 0)
{
for (int i = 0; i < collection.Count(); i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in props)
{
object obj = pi.GetValue(collection.ElementAt(i), null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
dt.LoadDataRow(array, true);
}
}
if (!string.IsNullOrEmpty(tableName))
{
dt.TableName = tableName;
}
return dt;
}
/// <summary>
/// 选择保存路径
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public bool SaveFileDialog(string fileName)
{
Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
dlg.FileName = fileName; // Default file name
dlg.DefaultExt = ".xls"; // Default file extension
dlg.Filter = "Excel2003及以下版本|*.xls|Excel2007及以上版本|*.xlsx";
var dir = System.IO.Directory.GetCurrentDirectory(); //何志凌 2018-10-27
dlg.InitialDirectory = dir;
// Show save file dialog box
Nullable<bool> result = dlg.ShowDialog();
// Process save file dialog box results
if (result == true)
{
// Save document
this.fileName = dlg.FileName;
}
return !string.IsNullOrWhiteSpace(this.fileName);
}
/// <summary>
/// 将DataTableList导入到Excel中,用于生成多个sheet
/// </summary>
/// <param name="dataList"></param>
/// <param name="isColumnWritten"></param>
/// <returns></returns>
public bool DataTableListToExcel(List<DataTable> dataList, bool isColumnWritten = true)
{
MemoryStream ms = new MemoryStream();
try
{
if (string.IsNullOrEmpty(fileName))
{
return false;
}
// 兼容2003版本
if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
{
fileName = fileName.Replace(".xlsx", ".xls");
}
HSSFWorkbook workbook = new HSSFWorkbook();
for (int i = 0; i < dataList.Count; i++)
{
int count;
//Sheet名
string sheetName = string.IsNullOrEmpty(dataList[i].TableName) ? string.Format("sheet{0}", i + 1) : dataList[i].TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
if (isColumnWritten) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (int j = 0; j < dataList[i].Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(dataList[i].Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (int r = 0; r < dataList[i].Rows.Count; ++r)
{
IRow row = sheet.CreateRow(count);
for (int j = 0; j < dataList[i].Columns.Count; ++j)
{
//找到对应列数据类型,通过反射获取数据类型,switch 判断表走不同分支,控制Excel表格导出数据类型
//SetCellValue:写入数据只支持bool、double、string、DateTime、IRichTextString类型
var type = dataList[i].Columns[j].DataType.Name;
switch (type)
{
case "Decimal":
row.CreateCell(j).SetCellValue(Convert.ToDouble(dataList[i].Rows[r][j]));
break;
case "Int":
row.CreateCell(j).SetCellValue(Convert.ToDouble(dataList[i].Rows[r][j]));
break;
case "Float":
row.CreateCell(j).SetCellValue(Convert.ToDouble(dataList[i].Rows[r][j]));
break;
case "Double":
row.CreateCell(j).SetCellValue(Convert.ToDouble(dataList[i].Rows[r][j]));
break;
case "DateTime":
row.CreateCell(j).SetCellValue(Convert.ToDateTime(dataList[i].Rows[r][j]));
break;
case "Bool":
row.CreateCell(j).SetCellValue(Convert.ToBoolean(dataList[i].Rows[r][j]));
break;
default:
row.CreateCell(j).SetCellValue(dataList[i].Rows[r][j].ToString());
break;
}
//row.CreateCell(j).SetCellValue(dataList[i].Rows[r][j].ToString());
}
++count;
}
}
// 写入
workbook.Write(ms); //写入到excel
using (FileStream fs2 = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data2 = ms.ToArray();
fs2.Write(data2, 0, data2.Length);
fs2.Flush();
}
ms.Close();
ms.Dispose();
return true;
}
catch (Exception ex)
{
ms.Close();
ms.Dispose();
return false;
}
}
}
3.使用特性标注实体属性,方便选择需要导出的数据列表
namespace Models.AttributeModel
{
/// <summary>
/// 作用:对导出excel的实体标记需要导出的属性,并指定在excel表格中的顺序
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
public class ExportExcelObjectSignAttribute : Attribute
{
/// <summary>
/// 列显示名称
/// </summary>
public string Display { get; set; }
/// <summary>
/// 显示顺序
/// </summary>
public int Index { get; set; }
public ExportExcelObjectSignAttribute(string display, int index)
{
this.Display = display;
this.Index = index;
}
}
}