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;
}
}
浙公网安备 33010602011771号