导出数据EPPlus
前言
导出数据在管理系统中经常要用到，目前的Excel导出工具多种多样，如：NPOI、EPPlus等……本篇使用的是EPPlus，记录下在工作中用到的导入导出类，以便后面使用
代码
导出
public class Export2Excel { /// <summary> /// 生成excel /// </summary> /// <param name="dtSource">数据源</param> /// <param name="title">标题(Sheet名)</param> /// <param name="showTitle">是否显示</param> /// <returns></returns> public static MemoryStream Export(DataTable dtSource, string title, bool showTitle = true) { using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title); int maxColumnCount = dtSource.Columns.Count; int curRowIndex = 0; if (showTitle == true) { curRowIndex++; //主题 workSheet.Cells[curRowIndex, 1, 1, maxColumnCount].Merge = true; workSheet.Cells[curRowIndex, 1].Value = title; var headerStyle = workSheet.Workbook.Styles.CreateNamedStyle("headerStyle"); headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; headerStyle.Style.Font.Bold = true; headerStyle.Style.Font.Size = 20; workSheet.Cells[curRowIndex, 1].StyleName = "headerStyle"; curRowIndex++; //导出时间栏 workSheet.Cells[curRowIndex, 1, 2, maxColumnCount].Merge = true; workSheet.Cells[curRowIndex, 1].Value = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm"); workSheet.Cells[curRowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; } curRowIndex++; var titleStyle = workSheet.Workbook.Styles.CreateNamedStyle("titleStyle"); titleStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; titleStyle.Style.Font.Bold = true; //标题 for (var i = 0; i < maxColumnCount; i++) { DataColumn column = dtSource.Columns[i]; workSheet.Cells[curRowIndex, i + 1].Value = column.ColumnName; workSheet.Cells[curRowIndex, i + 1].StyleName = "titleStyle"; } workSheet.View.FreezePanes(curRowIndex, 1);//冻结标题行 //内容 for (var i = 0; i < dtSource.Rows.Count; i++) { curRowIndex++; for (var j = 0; j < maxColumnCount; j++) { DataColumn column = dtSource.Columns[j]; var row = dtSource.Rows[i]; object value = row[column]; var cell = workSheet.Cells[curRowIndex, j + 1]; var pType = column.DataType; pType = pType.Name == "Nullable`1" ? Nullable.GetUnderlyingType(pType) : pType; if (pType == typeof(DateTime)) { cell.Style.Numberformat.Format = "yyyy-MM-dd hh:mm"; cell.Value = Convert.ToDateTime(value); } else if (pType == typeof(int)) { cell.Value = Convert.ToInt32(value); } else if (pType == typeof(double) || pType == typeof(decimal)) { cell.Value = Convert.ToDouble(value); } else { cell.Value = value == null ? "" : value.ToString(); } workSheet.Cells[curRowIndex, j + 1].Value = row[column].ToString(); } } workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name = "宋体"; workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();//自动填充 for (var i = 1; i <= workSheet.Dimension.End.Column; i++) { workSheet.Column(i).Width = workSheet.Column(i).Width + 2; }//在填充的基础上再加2 MemoryStream ms = new MemoryStream(package.GetAsByteArray()); return ms; } } /// <summary> /// 生成excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dtSource">数据源</param> /// <param name="columns">导出字段表头合集</param> /// <param name="title">标题(Sheet名)</param> /// <param name="showTitle">是否显示标题</param> /// <returns></returns> public static byte[] Export<T>(IList<T> dtSource, ExportColumnCollective columns, string title, bool showTitle = true) { using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title); int maxColumnCount = columns.ExportColumnList.Count; int curRowIndex = 0; //Excel标题 if (showTitle == true) { curRowIndex++; workSheet.Cells[curRowIndex, 1, 1, maxColumnCount].Merge = true; workSheet.Cells[curRowIndex, 1].Value = title; var headerStyle = workSheet.Workbook.Styles.CreateNamedStyle("headerStyle"); headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; headerStyle.Style.Font.Bold = true; headerStyle.Style.Font.Size = 20; workSheet.Cells[curRowIndex, 1].StyleName = "headerStyle"; curRowIndex++; //导出时间 workSheet.Cells[curRowIndex, 1, 2, maxColumnCount].Merge = true; workSheet.Cells[curRowIndex, 1].Value = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm"); workSheet.Cells[curRowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; } //数据表格标题(列名) for (int i = 0, rowCount = columns.HeaderExportColumnList.Count; i < rowCount; i++) { curRowIndex++; workSheet.Cells[curRowIndex, 1, curRowIndex, maxColumnCount].Style.Font.Bold = true; var curColSpan = 1; for (int j = 0, colCount = columns.HeaderExportColumnList[i].Count; j < colCount; j++) { var colColumn = columns.HeaderExportColumnList[i][j]; var colSpan = FindSpaceCol(workSheet, curRowIndex, curColSpan); if (j == 0) curColSpan = colSpan; var toColSpan = colSpan + colColumn.ColSpan; var cell = workSheet.Cells[curRowIndex, colSpan, colColumn.RowSpan + curRowIndex, toColSpan]; cell.Merge = true; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Cells[curRowIndex, colSpan].Value = colColumn.Title; curColSpan += colColumn.ColSpan; } } workSheet.View.FreezePanes(curRowIndex + 1, 1);//冻结标题行 Type type = typeof(T); PropertyInfo[] propertyInfos = type.GetProperties(); if (propertyInfos.Count() == 0 && dtSource.Count > 0) propertyInfos = dtSource[0].GetType().GetProperties(); //数据行 for (int i = 0, sourceCount = dtSource.Count(); i < sourceCount; i++) { curRowIndex++; for (var j = 0; j < maxColumnCount; j++) { var column = columns.ExportColumnList[j]; var cell = workSheet.Cells[curRowIndex, j + 1]; foreach (var propertyInfo in propertyInfos) { if (column.Field == propertyInfo.Name) { object value = propertyInfo.GetValue(dtSource[i]); var pType = propertyInfo.PropertyType; pType = pType.Name == "Nullable`1" ? Nullable.GetUnderlyingType(pType) : pType; if (pType == typeof(DateTime)) { cell.Style.Numberformat.Format = "yyyy-MM-dd hh:mm"; cell.Value = Convert.ToDateTime(value); } else if (pType == typeof(int)) { cell.Style.Numberformat.Format = "#0"; cell.Value = Convert.ToInt32(value); } else if (pType == typeof(double) || pType == typeof(decimal)) { if (column.Precision != null) cell.Style.Numberformat.Format = "#,##0.00";//保留两位小数 cell.Value = Convert.ToDouble(value); } else { cell.Value = value == null ? "" : value.ToString(); } } } } } workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name = "宋体"; workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();//自动填充 for (var i = 1; i <= workSheet.Dimension.End.Column; i++) { workSheet.Column(i).Width = workSheet.Column(i).Width + 2; }//在填充的基础上再加2 return package.GetAsByteArray(); } } private static int FindSpaceCol(ExcelWorksheet workSheet, int row, int col) { if (workSheet.Cells[row, col].Merge) { return FindSpaceCol(workSheet, row, col + 1); } return col; } } public class ExportColumnCollective { /// <summary> /// 字段列集合 /// </summary> public List<ExportColumn> ExportColumnList { get; set; } /// <summary> /// 表头或多表头集合 /// </summary> public List<List<ExportColumn>> HeaderExportColumnList { get; set; } } public class ExportColumn { /// <summary> /// 标题 /// </summary> [JsonProperty("title")] public string Title { get; set; } /// <summary> /// 字段 /// </summary> [JsonProperty("field")] public string Field { get; set; } /// <summary> /// 精度(只对double、decimal有效) /// </summary> [JsonProperty("precision")] public int? Precision { get; set; } /// <summary> /// 跨列 /// </summary> [JsonProperty("colSpan")] public int ColSpan { get; set; } /// <summary> /// 跨行 /// </summary> [JsonProperty("rowSpan")] public int RowSpan { get; set; } }
导入
public class Import2Excel<T> where T : XlsRow, new() { private List<XlsEntity> xlsHeader = new List<XlsEntity>(); #region 初始化转换形式 public void ForMember(Expression<Func<T, object>> entityExpression, Func<string, object> func) { XlsEntity xlsEntity = new XlsEntity(); xlsEntity.EntityName = GetPropertyName(entityExpression); xlsEntity.ColumnName = xlsEntity.EntityName; xlsEntity.ConvertFunc = func; xlsHeader.Add(xlsEntity); } public void ForMember(string columnName, Expression<Func<T, object>> entityExpression) { XlsEntity xlsEntity = new XlsEntity(); xlsEntity.ColumnName = columnName; xlsEntity.EntityName = GetPropertyName(entityExpression); xlsHeader.Add(xlsEntity); } public void ForMember(string columnName, string entityName) { XlsEntity xlsEntity = new XlsEntity(); xlsEntity.ColumnName = columnName; xlsEntity.EntityName = entityName; xlsHeader.Add(xlsEntity); } public void ForMember(string columnName, string entityName, Func<string, object> func) { XlsEntity xlsEntity = new XlsEntity(); xlsEntity.ColumnName = columnName; xlsEntity.EntityName = entityName; xlsEntity.ConvertFunc = func; xlsHeader.Add(xlsEntity); } public void ForMember(string columnName, Expression<Func<T, object>> entityExpression, Func<string, object> func) { XlsEntity xlsEntity = new XlsEntity(); xlsEntity.ColumnName = columnName; xlsEntity.EntityName = GetPropertyName(entityExpression); xlsEntity.ConvertFunc = func; xlsHeader.Add(xlsEntity); } #endregion /// <summary> /// Excel文件流加载到内存 /// </summary> /// <param name="ExcelFileStream">文件流</param> /// <param name="SheetIndex">加载页码</param> /// <returns></returns> public List<T> LoadFromExcel(Stream ExcelFileStream, int SheetIndex = 0) { List<T> resultList = new List<T>(); using (ExcelPackage package = new ExcelPackage(ExcelFileStream)) { ExcelWorksheet worksheet = package.Workbook.Worksheets[SheetIndex];//选定 指定页 int colStart = worksheet.Dimension.Start.Column; int colEnd = worksheet.Dimension.End.Column; int rowStart = worksheet.Dimension.Start.Row; int rowEnd = worksheet.Dimension.End.Row; PropertyInfo[] propertyInfoList = typeof(T).GetProperties(); XlsEntity xlsEntity; #region 将实体和excel列标题进行对应绑定,添加到集合中 for (int i = colStart; i <= colEnd; i++) { string columnName = worksheet.Cells[rowStart, i].Value.ToString(); xlsEntity = xlsHeader.FirstOrDefault(e => e.ColumnName == columnName); for (int j = 0; j < propertyInfoList.Length; j++) { if (xlsEntity != null && xlsEntity.ColumnName == columnName) { xlsEntity.ColumnIndex = i; xlsHeader.Add(xlsEntity); } else if (propertyInfoList[j].Name == columnName) { xlsEntity = new XlsEntity(); xlsEntity.ColumnName = columnName; xlsEntity.EntityName = propertyInfoList[j].Name; xlsEntity.ColumnIndex = i; xlsHeader.Add(xlsEntity); break; } } } #endregion #region 根据对应的实体名列名的对应绑定就行值的绑定 for (int row = rowStart + 1; row <= rowEnd; row++) { T result = new T(); foreach (PropertyInfo p in propertyInfoList) { var xlsRow = xlsHeader.FirstOrDefault(e => e.EntityName == p.Name); if (xlsRow == null || xlsRow?.ColumnIndex == 0) continue; ExcelRange cell = worksheet.Cells[row, xlsRow.ColumnIndex]; if (cell.Value == null) continue; try { if (xlsRow.ConvertFunc != null) { object entityValue = xlsRow.ConvertFunc(cell.Value.ToString()); p.SetValue(result, entityValue); } else { cellBindValue(result, p, cell); } } catch (Exception ex) { if (result.ErrColumn == null) result.ErrColumn = new List<string>(); if (result.ErrMessage == null) result.ErrMessage = new List<string>(); if (result.ErrValue == null) result.ErrValue = new List<string>(); result.ErrColumn.Add(p.Name); result.ErrMessage.Add(ex.Message); result.ErrValue.Add(cell.Value.ToString()); result.IsErr = true; } } resultList.Add(result); } #endregion } return resultList; } private static void cellBindValue(T result, PropertyInfo p, ExcelRange cell) { switch (p.PropertyType.Name.ToLower()) { case "string": p.SetValue(result, cell.GetValue<String>()); break; case "int16": p.SetValue(result, cell.GetValue<Int16>()); break; case "int32": p.SetValue(result, cell.GetValue<Int32>()); break; case "int64": p.SetValue(result, cell.GetValue<Int64>()); break; case "decimal": p.SetValue(result, cell.GetValue<Decimal>()); break; case "double": p.SetValue(result, cell.GetValue<Double>()); break; case "datetime": p.SetValue(result, cell.GetValue<DateTime>()); break; case "boolean": p.SetValue(result, cell.GetValue<Boolean>()); break; case "byte": p.SetValue(result, cell.GetValue<Byte>()); break; case "char": p.SetValue(result, cell.GetValue<Char>()); break; case "single": p.SetValue(result, cell.GetValue<Single>()); break; default: p.SetValue(result, cell?.Value?.ToString()); break; } } private static string GetPropertyName(Expression<Func<T, object>> expression) { Expression expressionToCheck = expression; bool done = false; while (!done) { switch (expressionToCheck.NodeType) { case ExpressionType.Convert: expressionToCheck = ((UnaryExpression)expressionToCheck).Operand; break; case ExpressionType.Lambda: expressionToCheck = ((LambdaExpression)expressionToCheck).Body; break; case ExpressionType.MemberAccess: var memberExpression = ((MemberExpression)expressionToCheck); string propertyName = memberExpression.Member.Name; return propertyName; default: done = true; break; } } return ""; } } public class XlsEntity { /// <summary> /// 实体名称 /// </summary> public string EntityName { get; set; } /// <summary> /// 列名称 /// </summary> public string ColumnName { get; set; } /// <summary> /// 列下标 /// </summary> public int ColumnIndex { get; set; } /// <summary> /// 转换方法 /// </summary> public Func<string, object> ConvertFunc { get; set; } } public class XlsRow { /// <summary> /// 错误信息 /// </summary> public List<string> ErrMessage { get; set; } /// <summary> /// 错误列名 /// </summary> public List<string> ErrColumn { get; set; } /// <summary> /// 错误内容 /// </summary> public List<string> ErrValue { get; set; } /// <summary> /// 是否转换出错（false:未出错，true：出错） /// </summary> public bool IsErr { get; set; } }
使用
public async Task<byte[]> ExportExcel(Params param) { List<Entity> list = await GetDataSource(param).ToListAsync(); //导出表头和字段集合 ExportColumnCollective ecc = new ExportColumnCollective(); //导出字段集合 ecc.ExportColumnList = new List<ExportColumn> { new ExportColumn{ Field = "UserId" }, new ExportColumn{ Field = "UserName" }, }; //导出表头集合 ecc.HeaderExportColumnList = new List<List<ExportColumn>> { new List<ExportColumn> { new ExportColumn{ Title = "用户编号" }, new ExportColumn{ Title = "姓名" } } }; byte[] result = Export2Excel.Export<BillEntity>(list, ecc, "用户", false); return result; }
public async Task<FileResult> ExportExcel([FromQuery] BillParams pairs) { byte[] result = await _billService.ExportExcel(pairs); return File(result, "application/vnd.ms-excel", $"{DateTime.Now.ToString("yyMMddHHmmssfff")}.xlsx"); }
