/// <summary>
/// 获取Sheet
/// </summary>
/// <param name="stream"></param>
/// <param name="excelVersionEnum"></param>
/// <returns></returns>
public List <ExcelSheetModel <TEntity> > GetSheets(Stream stream, ExcelVersionEnum excelVersionEnum)
{
//返回结果
List <ExcelSheetModel <TEntity> > sheets = new List <ExcelSheetModel <TEntity> >();
IWorkbook workbook = ExcelHelper.GetWorkbook(stream, excelVersionEnum);
//获取所有Sheet
int sheetCount = workbook.NumberOfSheets;
//遍历Sheet
for (int i = 0; i < sheetCount; i++)
{
//不如不启用当前Sheet则跳过
if (ExcelGlobalDTO.DisableSheetIndexs != null && ExcelGlobalDTO.DisableSheetIndexs.Contains(i))
{
continue;
}
ISheet sheet = workbook.GetSheetAt(i);
//获取sheet
SheetAttribute sheetAttr = typeof(TEntity).GetCustomAttribute <SheetAttribute>() as SheetAttribute;
if (sheetAttr != null && sheetAttr.SheetName != sheet.SheetName)
{
continue;
}
//获取头部行
IRow row = sheet.GetRow(ExcelGlobalDTO.GlobalStartRowIndex);
if (row == null)
{
continue;
}
//获取表头信息
List <string> cellValues = row.Cells.Select(s => ExcelHelper.GetCellValue(s)).ToList();
if (cellValues == null)
{
continue;
}
//构建默认一个
ExcelSheetModel <TEntity> sheetModel = new ExcelSheetModel <TEntity>();
sheetModel.SheetIndex = i;
sheetModel.SheetName = sheet.SheetName;
//设置一个默认
sheets.Add(sheetModel);
}
return(sheets);
}
/// <summary>
/// 获取下级的错误信息
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private List <ColumnErrorMessage> GetMasterSlaveErrorMessages(TEntity entity)
{
//获取当前实体的错误信息
List <ColumnErrorMessage> errors = new List <ColumnErrorMessage>();
errors.AddRange(entity.ColumnErrorMessage);
//遍历实体属性，寻找级联实体，找到异常信息
foreach (var prop in entity.GetType().GetProperties())
{
//获取Sheet特性标记
SheetAttribute sheetAttribute = prop.GetCustomAttribute <SheetAttribute>();
if (sheetAttribute == null)
{
continue;
}
//当为级联对象时处理
var values = prop.GetValue(entity, null) as IEnumerable;
if (values == null)
{
continue;
}
//程序集
string assemblyString = "Warship";
string importClassName = "Warship.Excel.Model.ExcelGlobalDTO`1";
//类型
Type[] types = prop.PropertyType.GetGenericArguments();
Type type = prop.PropertyType.GetGenericArguments()[0];
//值不为空的时遍历实体
foreach (var value in values)
{
Type importType = Assembly.Load(assemblyString).GetType(importClassName).MakeGenericType(type);
object importModel = System.Activator.CreateInstance(importType);
BindingFlags flag = BindingFlags.Instance | BindingFlags.NonPublic;
List <ColumnErrorMessage> errorsResult = importType.GetMethod("GetMasterSlaveErrorMessages", flag).Invoke(importModel, new object[] { value }) as List <ColumnErrorMessage>;
errors.AddRange(errorsResult);
}
;
}
return(errors);
}
/// <summary>
/// 设置实体属性的值
/// </summary>
/// <param name="sheetAttribute"></param>
/// <param name="entity"></param>
/// <param name="prop"></param>
/// <param name="childSheetEntityList"></param>
public void SetEntityPropertyValues(SheetAttribute sheetAttribute, MasterT entity, PropertyInfo prop, List <SlaveT> childSheetEntityList)
{
List <SlaveT> results = new List <SlaveT>();
//获取主实体的属性值
string masterValue = entity.GetType().GetProperty(sheetAttribute.MasterEntityProperty).GetValue(entity, null)?.ToString();
foreach (var item in childSheetEntityList)
{
//获取从属性的属性值
string slaveValue = item.GetType().GetProperty(sheetAttribute.SlaveEntityProperty).GetValue(item, null).ToString();
//如果两者相等说明一致，则向当前属性上赋值
if (masterValue == slaveValue)
{
results.Add(item);
}
}
prop.SetValue(entity, results);
}
/// <summary>
/// 实体属性级联设置
/// </summary>
/// <param name="entity"></param>
/// <param name="prop"></param>
/// <param name="sheetAttribute"></param>
private void EntityPropertyCascadeSet(TEntity entity, PropertyInfo prop, SheetAttribute sheetAttribute)
{
//获取Sheet信息
ISheet propSheet = ExcelGlobalDTO.Workbook.GetSheet(sheetAttribute.SheetName);
Type[] types = prop.PropertyType.GetGenericArguments();
Type type = types[0];//获取泛型类型
string key = entity.GetType().FullName + "." + prop.Name;
#region 读取下级Sheet的内容，由于属性是遍历的因此如果一次已经获取下级Sheet，则直接从CascadeCache取
if (CascadeCache.ChildSheetEntityList.Keys.Contains(key) == false)
{
lock (lockObj)
{
if (CascadeCache.ChildSheetEntityList.Keys.Contains(key) == false)
{
string assemblyString = "Warship";
string importClassName = "Warship.Excel.Import.Import`1";
Type importType = Assembly.Load(assemblyString).GetType(importClassName).MakeGenericType(type);
//构造参数，执行构造函数
object importModel = System.Activator.CreateInstance(importType);
Type[] pt = new Type[2];
pt[0] = typeof(int);
pt[1] = typeof(int);
ConstructorInfo ci = importType.GetConstructor(pt);
ci.Invoke(new object[] { sheetAttribute.StartRowIndex, sheetAttribute.StartColumnIndex });
//获取非当前Sheet，全部设置为禁用，保证之取到当前的
int thisSheetIndex = ExcelGlobalDTO.Workbook.GetSheetIndex(sheetAttribute.SheetName);
List <int> disableSheetIndexs = new List <int>();
for (int i = 0; i < ExcelGlobalDTO.Workbook.NumberOfSheets; i++)
{
if (i != thisSheetIndex)
{
disableSheetIndexs.Add(i);
}
}
object excelGlobalDTO = importModel.GetType().GetProperty("ExcelGlobalDTO").GetValue(importModel);
excelGlobalDTO.GetType().GetProperty("DisableSheetIndexs").SetValue(excelGlobalDTO, disableSheetIndexs);
//执行
importType.GetMethod("ExecuteByBuffer").Invoke(importModel, new object[] { ExcelGlobalDTO.FileBytes, ExcelGlobalDTO.ExcelVersionEnum });
ExcelGlobalDTO.SlaveExcelGlobalDTO = excelGlobalDTO;
var globalSheets = excelGlobalDTO.GetType().GetProperty("Sheets").GetValue(excelGlobalDTO) as IEnumerable;
foreach (var item in globalSheets)
{
var sheetEntitys = item.GetType().GetProperty("SheetEntityList").GetValue(item) as IEnumerable;
CascadeCache.ChildSheetEntityList.Add(key, sheetEntitys);
}
}
}
}
#endregion
#region 级联属性赋值
string cascadeTypeName = "Warship.Excel.Import.Helper.Cascade`2";
Type[] cascadeTypeArguments = new Type[2];
cascadeTypeArguments[0] = entity.GetType();
cascadeTypeArguments[1] = type;
Type cascadeType = Type.GetType(cascadeTypeName).MakeGenericType(cascadeTypeArguments);
object cascadeModel = System.Activator.CreateInstance(cascadeType);
cascadeType.GetMethod("SetEntityPropertyValues").Invoke(cascadeModel, new object[] { sheetAttribute, entity, prop, CascadeCache.ChildSheetEntityList[key] });
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="rowNum"></param>
/// <param name="sheetModel"></param>
/// <param name="sheet"></param>
/// <param name="files"></param>
/// <returns></returns>
private TEntity GetEntity(int rowNum, ExcelSheetModel <TEntity> sheetModel, ISheet sheet, List <ColumnFile> files)
{
//实体
TEntity entity = new TEntity
{
RowNumber = rowNum
};
#region 行转换实体并赋值
//获取行
IRow row = sheet.GetRow(rowNum);
#region 行判断及列是否有值判断
//当前行没有任何内容
if (row == null || row.Cells == null)
{
return(null);
}
//判断是否有值，没有值则跳出
List <string> cellValues = row.Cells.Select(s => ExcelHelper.GetCellValue(s)).ToList();
if (cellValues.Exists(w => string.IsNullOrEmpty(w) == false) == false)
{
return(null);
}
#endregion
//实体类型
Type entityType = entity.GetType();
//遍历头部，设置属性值和其他列
foreach (ExcelHeadDTO headDto in sheetModel.SheetHeadList)
{
ICell cell = row.GetCell(headDto.ColumnIndex);
string value = ExcelHelper.GetCellValue(cell);//获取单元格的值，设置属性值
#region 属性为空,添加其他列
//属性为空,添加其他列
if (string.IsNullOrEmpty(headDto.PropertyName) == true)
{
EntityOtherColumnsSet(entity, sheetModel, headDto, value);
continue;
}
#endregion
#region 属性不为空，设置属性值
//属性不为空，设置属性值
PropertyInfo prop = PropertyHelper.GetPropertyInfo <TEntity>(headDto.PropertyName);
//级联Sheet
SheetAttribute sheetAttribute = prop.GetCustomAttribute <SheetAttribute>();
if (sheetAttribute != null)
{
EntityPropertyCascadeSet(entity, prop, sheetAttribute);
continue;
}
try
{
#region 非泛型列赋值
//列文件
if (prop.PropertyType == typeof(List <ColumnFile>))
{
List <ColumnFile> columnFiles = files.Where(n => n.MinRow == row.RowNum && n.MinCol == headDto.ColumnIndex).ToList();
if (columnFiles != null && columnFiles.Count > 0)
{
prop.SetValue(entity, columnFiles, null);
}
continue;
}
//判断值
if (string.IsNullOrEmpty(value))
{
continue;
}
//entity.SetPropertyValue(headDto.PropertyName, value);
//属性是否设置
bool propertyIsSetValue = this.EntityPropertySetValue(entity, prop, headDto, cell);
if (propertyIsSetValue == true)
{
continue;
}
//默认设置
prop.SetValue(entity, Convert.ChangeType(value, prop.PropertyType), null);
#endregion
}
catch (Exception ex)
{
EntityPropertyErrorSet(entity, prop, headDto, ex);
}
#endregion
}
#endregion
return(entity);
}
//string url = System.Web.HttpUtility.UrlDecode("http%3A%2F%2F10.5.11.101%3A10120%2F%2FMyWorkflow%2FWF_ProcessInitiate_Form_Transfer.aspx%3Fmode%3D1%26opentype%3Dbizsys%26processguid%3D%26businessGUID%3Db333c498-71d6-e811-80c3-00155d0a444e%26businessType%3D%25u6750%25u6599%25u7533%25u8BF7%25u5BA1%25u6279");
//string encodeUrl = System.Web.HttpUtility.UrlEncode("http://10.5.11.101:10120//MyWorkflow/WF_ProcessInitiate_Form_Transfer.aspx?mode=1&opentype=bizsys&processguid=&businessGUID=b333c498-71d6-e811-80c3-00155d0a444e&businessType=材料申请审批");
/// <summary>
/// 级联
/// </summary>
/// <param name="args"></param>
void Test(string[] args)
{
ContractImportDTO contract = new ContractImportDTO();
PropertyInfo prop = contract.GetType().GetProperty("Products");
bool isGenericType = prop.PropertyType.IsGenericType;
Type[] types = prop.PropertyType.GetGenericArguments();
Type type = types[0];
string assemblyFile = "Warship.dll";
string className = "Warship.Excel.Model.ExcelSheetModel`1";// + "<" + type.FullName + ">";
string productAssemblyName = type.Assembly.GetName().Name;
//string className = "Warship.Excel.Model.ExcelRowModel";
//string className = type.FullName;
//Assembly.Load(type.FullName);
//Type testType = Assembly.Load("Warship").GetType(className).MakeGenericType(type); ;
//加载泛型类
Type genericClass = Assembly.LoadFrom(assemblyFile).GetType(className);
//制作泛型类型
genericClass = genericClass.MakeGenericType(type);
//ExcelSheetModel < typeof(ExcelRowModel) > propSheetModel = new ExcelSheetModel<TEntity>();
//ExcelSheetModel<ContractProductImportDTO> propSheetModel = System.Activator.CreateInstance(Type.GetType(className)) as ExcelSheetModel<ContractProductImportDTO>;
var propSheetModel = System.Activator.CreateInstance(genericClass);
SheetAttribute sheetAttribute = prop.GetCustomAttribute <SheetAttribute>();
if (sheetAttribute != null)
{
////获取Sheet信息
//ISheet propSheet = ExcelGlobalDTO.Workbook.GetSheet(sheetAttribute.SheetName);
//if (prop.PropertyType.IsGenericType)
//{
// Type[] types = prop.PropertyType.GetGenericArguments();
// Type type = prop.PropertyType.GetGenericArguments()[0];
//构建SheetModel
//ExcelSheetModel <typeof(ExcelRowModel)> propSheetModel = new ExcelSheetModel<TEntity>();
//propSheetModel.StartRowIndex = sheetAttribute.StartRowIndex;
//propSheetModel.StartColumnIndex = sheetAttribute.StartColumnIndex;
//propSheetModel.SheetIndex = ExcelGlobalDTO.Workbook.GetSheetIndex(sheetAttribute.SheetName);
//propSheetModel.SheetName = sheetAttribute.SheetName;
//}
////TODO
}
//变量声明
DateTime dt = new DateTime();
DateTime.TryParse("12-9月-2018", out dt);
DateTime dt1 = Convert.ToDateTime("12-九月-2018");
string excelPath = Directory.GetCurrentDirectory() + "\\..\\Template\\合同导入模板.xls";
Import <ContractImportDTO> import = new Import <ContractImportDTO>(1);
import.ExcelGlobalDTO.SetDefaultSheet();
import.Execute(excelPath);
Export <ContractImportDTO> export = new Export <ContractImportDTO>();
export.Execute(import.ExcelGlobalDTO);
#region 基于实体的导入导出
////导入
//Import<ContractProductImportDTO> import = new Import<ContractProductImportDTO>(1);
//import.ExcelGlobalDTO.DisableSheetIndexs = new List<int>();
//import.ExcelGlobalDTO.DisableSheetIndexs.Add(1);
//import.Execute(excelPath);
//import.ExcelGlobalDTO.Sheets.First().ColumnOptions = new Dictionary<string, List<string>>();
//import.ExcelGlobalDTO.Sheets.First().ColumnOptions.Add("类型",new List<string>() {
// "工程类","采购类"
//});
//Dictionary<string, List<string>> dic = new Dictionary<string, List<string>>();
//dic.Add("类型", new List<string>() {
// "工程类A","采购类B"
//});
//import.ExcelGlobalDTO.Sheets.First().SheetEntityList[1].ColumnOptions = dic;
////循环设置区块内容
//foreach (var item in import.ExcelGlobalDTO.Sheets)
//{
// //设置区块
// item.AreaBlock = new Component.Office.Excel.Model.AreaBlock();
// item.AreaBlock.StartRowIndex = 0;
// item.AreaBlock.EndRowIndex = 0;
// //设置区块
// item.AreaBlock.StartColumnIndex = 0;
// item.AreaBlock.EndColumnIndex = 6;
// item.AreaBlock.Height = 256 * 3;
// //设置区块内容
// StringBuilder noteString = new StringBuilder("相关数据字典：（★★请严格按照相关格式填写，以免导入错误★★）\n");
// noteString.Append("1.列名带有' * '是必填列;\n");
// noteString.Append("2.会员卡号：会员卡号长度为3~20位,且只能数字或者英文字母;\n");
// //设置区块内容
// noteString.Append("3.性别：填写“男”或者“女”;\n");
// //设置区块内容
// noteString.Append("4.手机号码：只能是11位数字的标准手机号码;\n");
// noteString.Append("5.固定电话：最好填写为“区号+电话号码”，例：075529755361;\n");
// //设置区块内容
// noteString.Append("6.会员生日：填写格式“年-月-日”，例：1990-12-27，没有则不填;\n");
// //设置区块
// item.AreaBlock.Content = noteString.ToString();
//}
////设置导出错误信息
//Export<ContractProductImportDTO> export = new Export<ContractProductImportDTO>();
//export.Execute(import.ExcelGlobalDTO);
#endregion
}