Epplus导出excel

 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;
        }


    }