public class EpplusExcel { /// <summary> /// 导出Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataSource"></param> /// <param name="strFilePath"></param> /// <param name="MappingColumns"></param> /// <param name="keepTime">是否保留时分秒</param> public static void ToExcel<T>(List<T> dataSource, string strFilePath, List<ExcelColumns> MappingColumns = null,bool keepTime=false) { if (dataSource!=null&& dataSource.Count>0) { DataTable dataTable = ListToDataTable(dataSource, MappingColumns, keepTime); ToExcel(dataTable, strFilePath, MappingColumns); } else { if (!File.Exists(strFilePath)) { File.Create(strFilePath); } } } public static void ToExcel(DataTable dt, string strFilePath, List<ExcelColumns> MappingColumns = null) { try { if (dt.Rows.Count==0) { if (!File.Exists(strFilePath)) { File.Create(strFilePath); } return; } //目录不存在则创建 var dicpath= System.IO.Path.GetDirectoryName(strFilePath); if (!Directory.Exists(dicpath)) { Directory.CreateDirectory(dicpath); } if (File.Exists(strFilePath)) { File.Delete(strFilePath); } FileInfo file = new FileInfo(strFilePath); using (ExcelPackage ep = new ExcelPackage(file)) { ExcelWorksheet ws = ep.Workbook.Worksheets.Add("Sheet1"); #region 处理不存在的Mapping中的Name DataColumn[] arr = new DataColumn[dt.Columns.Count]; dt.Columns.CopyTo(arr,0) ; if (MappingColumns!=null) { foreach (var col in arr) { if (MappingColumns.All(m=>m.field!=col.ColumnName)) { dt.Columns.Remove(col.ColumnName); } } } #endregion ws.Cells["A1"].LoadFromDataTable(dt, true); ws.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid; //单元格背景颜色 ws.Cells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White); if (MappingColumns!=null) { for (int i = 0; i < dt.Columns.Count; i++) { var strColName= dt.Columns[i].ColumnName; var map= MappingColumns.FirstOrDefault(m=>m.field== strColName); if (map!=null) { ws.Cells[1, i+1].Value = map.title; } try { var lenth = ws.Cells[2, i + 1].Value.ToString().Length; ws.Column(i + 1).Width = lenth > 10 ? lenth + 6 : 10; } catch (Exception) { } ws.Cells[1, i+1].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells[1, i + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); ws.Cells[1, i+1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 151, 167));//设置单元格背景色 ws.Cells[1, i + 1].Style.Font.Color.SetColor(System.Drawing.Color.White); } } ws.Row(1).Height = 18; ws.Row(1).Style.Font.Bold = true; ws.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //设置字体，也可以是中文，比如：宋体 ws.Cells.Style.Font.Name = "宋体"; //字体加粗 //字体大小 ws.Cells.Style.Font.Size = 12; //字体颜色 // ws.Cells.Style.Font.Color.SetColor(System.Drawing.Color.Black); //单元格背景样式，要设置背景颜色必须先设置背景样式 //ws.Cells.Style.ShrinkToFit = true;//单元格自动适应大小 ws.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; ws.Cells.Style.Border.Top.Color.SetColor(System.Drawing.Color.Gray); ws.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; ws.Cells.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Gray); ws.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; ws.Cells.Style.Border.Left.Color.SetColor(System.Drawing.Color.Gray); ws.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; ws.Cells.Style.Border.Right.Color.SetColor(System.Drawing.Color.Gray); //设置单元格所有边框样式和颜色 //ws.Cells.Style.Border.BorderAround(ExcelBorderStyle.Thin, System.Drawing.ColorTranslator.FromHtml("#0097DD")); ep.Save(); //for (int j = 0; j < dt.Columns.Count; j++) //{ // string dtcolumntype = dt.Columns[j].DataType.Name.ToLower(); // if (dtcolumntype == "datetime") // { // for (int i = 0; i < dt.Rows.Count; i++) // { // ws.Cells[i+1, j+1].Style.Numberformat.Format = "yyyy/m/d h:mm"; // } // } //} } } catch (Exception ex) { throw ex; } } public static void ToExcel(DataTable dt, string strTemplete, string strExcelFile, int intSheet) { FileInfo strTemp = new FileInfo(strTemplete); FileInfo strNewTemp = new FileInfo(strExcelFile); try { ExcelPackage package = new ExcelPackage(strTemp); int vSheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页 ExcelWorksheet worksheet = package.Workbook.Worksheets[intSheet];//选定 指定页 //int maxColumnNum = ws.Dimension.End.Column;//最大列 //int minColumnNum = ws.Dimension.Start.Column;//最小列 //int maxRowNum = ws.Dimension.End.Row;//最小行 //int minRowNum = ws.Dimension.Start.Row;//最大行 worksheet.Cells["A1"].LoadFromDataTable(dt, true); package.SaveAs(strNewTemp); } catch (Exception ex) { throw ex; } } public static void ToExcel2(DataTable dt1, DataTable dt2, string strTemplete, string strExcelFile) { FileInfo strTemp = new FileInfo(strTemplete); FileInfo strNewTemp = new FileInfo(strExcelFile); try { ExcelPackage package = new ExcelPackage(strTemp); int vSheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页 ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet2"];//选定 表 worksheet.Cells["A1"].LoadFromDataTable(dt1, true); worksheet = package.Workbook.Worksheets["Sheet3"];//选定 指定页 worksheet.Cells["A1"].LoadFromDataTable(dt2, true); package.SaveAs(strNewTemp); } catch (Exception ex) { throw ex; } } /// <summary> /// 获取Datatable /// </summary> /// <param name="strFilePath"></param> /// <param name="showFirstRow"></param> /// <returns></returns> public static DataTable GetDataTableFromExcelHasNoHeader(string strFilePath, bool showFirstRow = false) { try { FileInfo file = new FileInfo(strFilePath); if (file.Extension.Replace(".","").ToLower()=="xls") { return Wise.Core.Common.Excel.ExcelHelper.GetDataTableFromExcelHasNoHeader(strFilePath, showFirstRow); } using (ExcelPackage ep = new ExcelPackage(file)) { ExcelWorksheet ws = ep.Workbook.Worksheets.FirstOrDefault(); int maxColumnNum = ws.Dimension.End.Column;//最大列 int minColumnNum = ws.Dimension.Start.Column;//最小列 int maxRowNum = ws.Dimension.End.Row;//最小行 int minRowNum = ws.Dimension.Start.Row;//最大行 DataTable vTable = new DataTable(); DataColumn vC; for (int j = 1; j <= maxColumnNum; j++) { vC = new DataColumn("F" + j, typeof(string)); vTable.Columns.Add(vC); } int startNum = showFirstRow ? 1 : 2; for (int n = startNum; n <= maxRowNum; n++) { DataRow vRow = vTable.NewRow(); for (int m = 1; m <= maxColumnNum; m++) { vRow[m - 1] = ws.Cells[n, m].Value; } vTable.Rows.Add(vRow); } return vTable; } } catch (Exception ex) { throw ex; } } public static DataTable GetExcel(string strFilePath, string tbName="dt1") { try { FileInfo file = new FileInfo(strFilePath); using (ExcelPackage ep = new ExcelPackage(file)) { ExcelWorksheet ws = ep.Workbook.Worksheets.FirstOrDefault(); int maxColumnNum = ws.Dimension.End.Column;//最大列 int minColumnNum = ws.Dimension.Start.Column;//最小列 int maxRowNum = ws.Dimension.End.Row;//最小行 int minRowNum = ws.Dimension.Start.Row;//最大行 DataTable vTable = new DataTable(); DataColumn vC; for (int j = 1; j <= maxColumnNum; j++) { vC = new DataColumn("A_" + j, typeof(string)); vTable.Columns.Add(vC); } for (int n = 2; n <= maxRowNum; n++) { DataRow vRow = vTable.NewRow(); for (int m = 1; m <= maxColumnNum; m++) { vRow[m - 1] = ws.Cells[n, m].Value; } vTable.Rows.Add(vRow); } return vTable; } } catch (Exception ex) { throw ex; } } public static DataTable GetExcel(string strFilePath, int tbIndex) { try { FileInfo file = new FileInfo(strFilePath); using (ExcelPackage ep = new ExcelPackage(file)) { ExcelWorksheet ws = ep.Workbook.Worksheets[tbIndex]; int maxColumnNum = ws.Dimension.End.Column;//最大列 int minColumnNum = ws.Dimension.Start.Column;//最小列 int maxRowNum = ws.Dimension.End.Row;//最小行 int minRowNum = ws.Dimension.Start.Row;//最大行 DataTable vTable = new DataTable(); DataColumn vC; for (int j = 1; j <= maxColumnNum; j++) { vC = new DataColumn("A_" + j, typeof(string)); vTable.Columns.Add(vC); } for (int n = 2; n <= maxRowNum; n++) { DataRow vRow = vTable.NewRow(); for (int m = 1; m <= maxColumnNum; m++) { vRow[m - 1] = ws.Cells[n, m].Value; } vTable.Rows.Add(vRow); } return vTable; } } catch (Exception ex) { throw ex; } } /// <summary> /// 类型转换 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entitys"></param> /// <returns></returns> public static DataTable ListToDataTable<T>(List<T> entitys, List<ExcelColumns> MappingColumns = null,bool keepTime=false) { //检查实体集合不能为空 if (entitys == null || entitys.Count < 1) { throw new Exception("The list is empty"); } //取出第一个实体的所有Propertie Type entityType = entitys[0].GetType(); var entityProperties = entityType.GetProperties().Where(m => (!m.GetAccessors()[0].IsVirtual) && (!(m.PropertyType.Name.ToLower() != "string" && m.PropertyType.IsClass))).ToList(); DataTable dt = new DataTable(); if (MappingColumns!=null) { foreach (var item in MappingColumns) { var prop = entityProperties.FirstOrDefault(m => m.Name == item.field); if (prop!=null) { var tp = prop.PropertyType; if (tp.IsGenericType && //判断是否为nullable泛型类 tp.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) { //如果tp为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换 if (tp.Name.ToLower() == "datetime" || (tp.GenericTypeArguments != null && tp.GenericTypeArguments.Length > 0 && tp.GenericTypeArguments[0].Name.ToLower() == "datetime")) { dt.Columns.Add(prop.Name, typeof(string)); } else { dt.Columns.Add(prop.Name,tp.GenericTypeArguments[0]); } } else { if (tp.Name.ToLower()== "datetime") { dt.Columns.Add(prop.Name, typeof(string)); } else { dt.Columns.Add(prop.Name, tp); } } } } //将所有entity添加到DataTable中 foreach (object entity in entitys) { //检查所有的的实体都为同一类型 object[] entityValues = new object[MappingColumns.Count]; var i = 0; DateTime dateTime = DateTime.Now; foreach (var item in MappingColumns) { var prop = entityProperties.FirstOrDefault(m => m.Name == item.field); if (prop != null) { var objval = prop.GetValue(entity, null); var tp = prop.PropertyType; if (tp.IsGenericType && //判断是否为nullable泛型类 tp.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) { //如果tp为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换 if (tp.Name.ToLower() == "datetime" || (tp.GenericTypeArguments != null && tp.GenericTypeArguments.Length > 0 && tp.GenericTypeArguments[0].Name.ToLower() == "datetime")) { if (objval!=null&&DateTime.TryParse(objval.ToString(), out dateTime)) { if (keepTime) { entityValues[i++] = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); } else { entityValues[i++] = dateTime.ToString("yyyy-MM-dd"); } } } else { NullableConverter nullableConverter = new NullableConverter(tp); if (objval!=null) { entityValues[i++] = nullableConverter.ConvertFromString(objval.ToString()); } else { entityValues[i++] = null; } } } else { if (tp.Name.ToLower() == "datetime") { if (objval != null && DateTime.TryParse(objval.ToString(), out dateTime)) { if (keepTime) { entityValues[i++] = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); } else { entityValues[i++] = dateTime.ToString("yyyy-MM-dd"); } } else { entityValues[i++] = ""; } } else { entityValues[i++] = objval; } } } } dt.Rows.Add(entityValues); } } else { for (int i = 0; i < entityProperties.Count; i++) { dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType); } //将所有entity添加到DataTable中 foreach (object entity in entitys) { //检查所有的的实体都为同一类型 object[] entityValues = new object[entityProperties.Count]; for (int i = 0; i < entityProperties.Count; i++) { entityValues[i] = entityProperties[i].GetValue(entity, null); } dt.Rows.Add(entityValues); } } return dt; } }