public T ExecuteScalar <T>(string sql, bool startTransaction = false)
{
try
{
return(Uility.ChangeType <T>(ExecuteScalar(sql, startTransaction)));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将Excel表格中的数据导入到实体类中
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="fileName">Excel文件路径</param>
/// <param name="sheetName">Excel表</param>
/// <param name="header">表头行</param>
/// <returns></returns>
public static List <T> Excel2Entity <T>(string fileName, string
sheetName, int header = 1)
{
List <T> entis = new List <T>();
FileInfo fi = new FileInfo(fileName);
if (!fi.Exists)
{
throw new Exception("文件不存在！");
}
using (ExcelPackage excel = new ExcelPackage(fi, true))
{
ExcelWorksheet ws = excel.Workbook.Worksheets[sheetName];
#region 创建表头
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
Dictionary <string, PropertyHandler> propertyHandlers = new Dictionary <string, PropertyHandler>();
#endregion
Dictionary <string, int> proMapColIndex = new Dictionary <string, int>();
for (int i = 1; i <= ws.Dimension.Columns; i++)
{
string colName = Uility.ChangeType <string>(ws.GetValue(header, i)).ToUpper();
proMapColIndex.Add(colName, i);
}
foreach (PropertyInfo pi in propertyInfos)
{
propertyHandlers.Add(pi.Name, new PropertyHandler(pi));
}
#region 向表中填充数据
Type type = typeof(T);
for (int i = header + 1; i <= ws.Dimension.Rows; i++)
{
T t = Activator.CreateInstance <T>();
foreach (PropertyInfo property in propertyInfos)
{
Type propertyType = property.PropertyType;
if (propertyType.IsGenericType)
{
continue;
}
object obj = ws.GetValue(i, proMapColIndex[property.Name.ToUpper()]);
object val = Uility.ChangeType(obj, propertyType);
//property.SetValue(t,val,null);
propertyHandlers[property.Name].Set(t, val);
}
entis.Add(t);
}
#endregion
}
return(entis);
}
/// <summary>
/// 将Excel数据库导入DataTable
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="header"></param>
/// <returns></returns>
public static DataTable Excel2DataTable(string fileName,
string sheetName, int header = 1)
{
DataTable dt = new DataTable();
FileInfo fi = new FileInfo(fileName);
if (!fi.Exists)
{
throw new Exception("文件不存在！");
}
using (ExcelPackage excel = new ExcelPackage(fi, true))
{
ExcelWorksheet ws = excel.Workbook.Worksheets[sheetName];
#region 创建表头
for (int i = 1; i <= ws.Dimension.Columns; i++)
{
string colname = Uility.ChangeType <string>(ws.GetValue(header, i));
if (string.IsNullOrEmpty(colname))
{
colname = "Col" + i.ToString();
}
dt.Columns.Add(colname, typeof(string));
}
#endregion
#region 向表中填充数据
for (int i = header + 1; i <= ws.Dimension.Rows; i++)
{
for (int j = 1; j <= ws.Dimension.Columns; j++)
{
DataRow dr = dt.NewRow();
dr[j - 1] = ws.GetValue(i, j);
dt.Rows.Add(dr);
}
}
#endregion
}
return(dt);
}
/// <summary>
/// 从XML中读取相应的节点到类中
/// </summary>
/// <param name="element"></param>
/// <param name="obj"></param>
public void ReadXML(XElement element, dynamic obj)
{
Type tType = obj.GetType();
PropertyInfo[] pList = null;
if (_PropertyInfoDic.ContainsKey(tType))
{
pList = _PropertyInfoDic[tType];
}
else
{
pList = tType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
_PropertyInfoDic.Add(tType, pList);
}
var attrs = element.Attributes();
foreach (var p in pList)
{
if (p.PropertyType.IsGenericType) //是否泛型
{
#region 泛型处理
dynamic list = null;
var genericParmTypes = p.PropertyType.GetGenericArguments();
if (p.PropertyType.IsInterface)
{
string typeStr = "System.Collections.Generic.List`{0}[{1}]";
string types = "";
Assembly ass = null;
foreach (var genericParmType in genericParmTypes)
{
types = "[" + genericParmType.ToString() + "," + genericParmType.Assembly.FullName + "],";
ass = genericParmType.Assembly;
}
typeStr = string.Format(typeStr, genericParmTypes.Length, types.TrimEnd(','));
try
{
list = Activator.CreateInstance(Type.GetType(typeStr));
}
catch (Exception ex)
{
throw new Exception("没有找到对应的类库");
}
}
else
{
list = Activator.CreateInstance(p.PropertyType);
}
foreach (var genericParmType in genericParmTypes)
{
var subElements = element.Elements(genericParmType.Name).ToList();
foreach (var subElement in subElements)
{
dynamic genericParm = Activator.CreateInstance(genericParmType);
ReadXML(subElement, genericParm);
list.Add(genericParm);
}
p.SetValue(obj, list, null);
}
#endregion
}
else if (p.PropertyType.IsArray) //是否数组
{
}
else
{
var attr = attrs.FirstOrDefault(n => n.Name == p.Name); //其他类型
if (attr != null)
{
p.SetValue(obj, Uility.ChangeType(attr.Value, p.PropertyType), null);
}
}
}
}
