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