/// <summary> /// 是否拥有菜单的权限 /// </summary> /// <param name="url">菜单的相对路径</param> public bool HasMenu(Uri uri) { string mid = HttpContext.Current.Request["sys_mid"]; MDataRow menu = GetMenu(uri); if (menu == null && !string.IsNullOrEmpty(mid)) { bool isContain = parentIDList.ContainsKey(uri.LocalPath); if (isContain) { mid = parentIDList[uri.LocalPath];//如果已经存在,则取存在过的。 } menu = UserMenu.FindRow("MenuID='" + mid + "'"); if (menu != null && !isContain) { parentIDList.Add(uri.LocalPath, mid); } } if (menu != null) { _menuID = menu.Get <string>("MenuID"); MenuName = menu.Get <string>("MenuName"); } return(menu != null); }
private static string GetJson(MDataTable configTable) { JsonHelper json = new JsonHelper(false, false); string configKey = string.Empty; bool isKeyChanged = false; List <MDataRow> groupList = new List <MDataRow>(); int count = configTable.Rows.Count; for (int i = 0; i < count; i++) { MDataRow row = configTable.Rows[i]; if (configKey != string.Empty && configKey != row.Get <string>("ConfigKey", string.Empty).Trim()) { isKeyChanged = true; } if (isKeyChanged) { if (groupList.Count > 0) { json.Add(configKey, GetInnerJson(groupList), true); groupList.Clear(); } isKeyChanged = false; } groupList.Add(row); configKey = row.Get <string>("configKey", string.Empty).Trim(); if (i == count - 1 && groupList.Count > 0)//最的一条(最后一组处理) { json.Add(configKey, GetInnerJson(groupList), true); } } json.AddBr(); return(json.ToString()); }
//internal static Dictionary<string, string> tableDic = DBTool.GetTables(AppConfig.DB.DefaultConn); /// <summary> /// 获取表的描述。 /// </summary> /// <param name="tableName"></param> /// <returns></returns> public static string GetTableDescription(string objName, string tableName) { string description = GetVallue(LangConst.TableDescription, objName); if (string.IsNullOrEmpty(description)) { MDataRow row = ExcelConfig.GetExcelRow(objName); if (row != null) { description = row.Get <string>(Config_Excel.Description); } if (string.IsNullOrEmpty(description)) { description = GetVallue(LangConst.TableDescription, tableName); if (string.IsNullOrEmpty(description)) { description = CrossDb.GetDescription(tableName); } } } if (string.IsNullOrEmpty(description)) { return(objName); } return(description); }
/// <summary> /// 是否拥有菜单的权限 /// </summary> /// <param name="url">菜单的相对路径</param> public bool HasMenu(Uri uri) { string key = UserAuth.UserID + ":" + uri.PathAndQuery; MDataRow menu = CacheManage.LocalInstance.Get(key) as MDataRow; if (menu == null) { menu = GetMenu(uri); string mid = HttpContext.Current.Request["sys_mid"]; if (menu == null && HttpContext.Current.Request.UrlReferrer != null) { menu = GetMenu(HttpContext.Current.Request.UrlReferrer); } if (menu == null && !string.IsNullOrEmpty(mid) && mid.IndexOfAny(new char[] { ' ', '%', ',' }) == -1) { bool isContain = parentidList.ContainsKey(uri.LocalPath); if (isContain) { mid = parentidList[uri.LocalPath];//如果已经存在,则取存在过的。 } menu = UserMenu.FindRow("MenuID='" + mid + "'"); if (menu != null && !isContain) { switch (Path.GetFileNameWithoutExtension(HttpContext.Current.Request.UrlReferrer.LocalPath).ToLower()) { case "configgrid": //如果是系统页面,则忽略 case "dialogview": break; default: if (!parentidList.ContainsKey(uri.LocalPath)) { parentidList.Add(uri.LocalPath, mid); } break; } } } } if (menu != null) { CacheManage.LocalInstance.Set(key, menu, 0.5);//存档30秒。 _menuid = menu.Get <string>("MenuID"); MenuName = menu.Get <string>("MenuName"); } return(menu != null); }
private static void CreateMergeHeader(ISheet sheet, ICellStyle style, Dictionary <int, List <MDataRow> > headerGroup, MDataTable header) { for (int i = 0; i < headerGroup.Count; i++) //事先创建好每一行数据,之后能才产生合并数据。 { IRow row = sheet.CreateRow(i); for (int d = 0; d < header.Rows.Count; d++) { ICell cel = row.CreateCell(d); cel.CellStyle = style; } } //设置标题和合并单元格。 foreach (KeyValuePair <int, List <MDataRow> > item in headerGroup) { IRow row = sheet.GetRow(item.Key); row.Height = 300; int cellIndex = 0; for (int i = 0; i < item.Value.Count; i++) { MDataRow data = item.Value[i]; int colspan = data.Get <int>(Config_Grid.Colspan, 1); int rowspan = data.Get <int>(Config_Grid.Rowspan, 1); string title = data.Get <string>(Config_Grid.Title); if (i == 0) { string field = data.Get <string>(Config_Grid.Field); cellIndex = header.GetIndex(Config_Grid.Field + "='" + field + "'"); if (cellIndex < 0) { cellIndex = 0; } } if (colspan > 1 || rowspan > 1) { sheet.AddMergedRegion(new CellRangeAddress(item.Key, item.Key + rowspan - 1, cellIndex, cellIndex + colspan - 1));//合并单元格 } ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); sheet.SetColumnWidth(cell.ColumnIndex, 4000); cell.SetCellValue(title); cellIndex += colspan; } } }
/// <summary> /// 根据配置Key和Name获取对应的名称。 /// </summary> public static string GetVallue(string configKey, string configName) { MDataRow row = KeyValueTable.FindRow("ConfigKey='" + configKey + "' and ConfigName='" + configName + "'"); if (row != null) { return(row.Get <string>("ConfigValue")); } return(configName); }
/// <summary> /// 获取指定菜单下的FuncKeys /// </summary> /// <returns></returns> public string GetFuncKeys(string menuID) { if (!string.IsNullOrEmpty(menuID)) { MDataRow row = UserMenu.FindRow("MenuID='" + menuID + "'"); if (row != null) { return(row.Get <string>("ActionRefNames", "").ToLower()); } } return(string.Empty); }
/// <summary> /// 获取指定菜单下的FuncKeys /// </summary> /// <returns></returns> public string GetFuncKeys(string menuid) { if (!string.IsNullOrEmpty(menuid) && menuid.IndexOfAny(new char[] { ' ', '%', ',' }) == -1) { MDataRow row = UserMenu.FindRow("MenuID='" + menuid + "'"); if (row != null) { return(row.Get <string>("ActionRefNames", "").ToLower()); } } return(string.Empty); }
public string GetExcelMapping() { MDataRow row = ExcelConfig.GetExcelRow(Query <string>("ID")); string objName = row.Get <string>(Config_Excel.ExcelName); string[] TableNames = row.Get <string>(Config_Excel.TableNames).Split(','); Dictionary <string, string> dic = new Dictionary <string, string>(); JsonHelper js = new JsonHelper(); foreach (string name in TableNames) { MDataColumn mdc = DBTool.GetColumns(name); js.Add(name, GetJson(mdc), true); dic.Add(name, name); } JsonHelper jh = new JsonHelper(); jh.Add("objName", objName); jh.Add("arrColumns", js.ToString(false)); jh.Add("arrTables", MDataTable.CreateFrom(dic).ToJson(false, false)); return(jh.ToString()); }
/// <summary> /// 是否拥有菜单的权限 /// </summary> public bool HasMenu(string menuID) { #if DEBUG if (string.IsNullOrEmpty(menuID) || UserAuth.IsAdmin) // 开发时临时开权限。 { return(true); } #endif MDataRow menu = UserMenu.FindRow("MenuID='" + menuID + "'"); //获取当前请求的Url if (menu != null) { this._menuID = menuID; MenuName = menu.Get <string>("MenuName"); return(true); } return(false); }
public override void Get() { switch (TableName) { case "Sys_User": ObjName = "V_SYS_UserList"; MDataRow row = GetOne(); if (row != null) { row.Set("Password", EncrpytHelper.Decrypt(row.Get <string>("Password"))); jsonResult = row.ToJson(); } break; default: base.Get(); break; } }
//public void GetUserInfo() //{ // jsonResult = UserAuth.User.ToJson(); //} /// <summary> /// 用户首页呈现的菜单数据 /// </summary> public void GetUserMenu() { MDataTable dt = p.UserMenu; if (IsUseUISite)//格式化菜单数据。 { string ui = AppConfig.GetApp("UI").Trim('/'); for (int i = 0; i < dt.Rows.Count; i++) { MDataRow row = dt.Rows[i]; string url = row.Get <string>(Sys_Menu.MenuUrl).TrimStart('/'); if (url != "#" && !url.ToLower().StartsWith(ui.ToLower() + "/")) { row.Set(Sys_Menu.MenuUrl, "/" + ui + "/" + url); } } } jsonResult = dt.ToJson(false, false, true); }
/// <summary> /// 获取指定菜单下的FuncKeys /// </summary> /// <returns></returns> public string GetFuncKeys(string menuid) { if (!string.IsNullOrEmpty(menuid) && menuid.IndexOfAny(new char[] { ' ', '%', ',' }) == -1) { MDataRow row = UserMenu.FindRow("MenuID='" + menuid + "'"); if (row != null) { string keys = row.Get <string>("ActionRefNames", "").ToLower(); if (!string.IsNullOrEmpty(keys)) { if (Path.GetFileNameWithoutExtension(HttpContext.Current.Request.UrlReferrer.LocalPath).ToLower() == "configgrid") { keys = "export,config,view,add,del,viewsql,edit,savesql"; } } return(keys); } } return(string.Empty); }
/// <summary> /// 是否拥有菜单的权限 /// </summary> public bool HasMenu(string menuid) { //#if DEBUG // if (string.IsNullOrEmpty(menuid) || UserAuth.IsAdmin) // 开发时临时开权限。 // { // return true; // } //#endif if (!string.IsNullOrEmpty(menuid) && menuid.IndexOfAny(new char[] { ' ', '%', ',' }) == -1) { MDataRow menu = UserMenu.FindRow("MenuID='" + menuid + "'"); //获取当前请求的Url if (menu != null) { this._menuid = menuid; MenuName = menu.Get <string>("MenuName"); return(true); } } return(false); }
/// <summary> /// 生成Sheet的级联指定 /// </summary> private static void CreateCascadeSheet(ISheet sheet, MDataTable header, Dictionary <string, string[]> validateData, int maxLevel) { Dictionary <string, int> formatdic = new Dictionary <string, int>(); MDataTable[] dt2 = header.Split(Config_Grid.Field + " like 'mg_%'"); header = dt2[1];//去掉多级表头的数据。 for (int i = 0; i < header.Rows.Count; i++) { string formater = header.Rows[i].Get <string>(Config_Grid.Formatter); if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater)) { formatdic.Add(formater, i);//存储列索引 } } int maxRow = 1000;//限制最大行数(07之前版本的excel最大行数为65536,但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效) IDataValidationHelper dvHelper = sheet.GetDataValidationHelper(); for (int i = 0; i < header.Rows.Count; i++) { MDataRow dtRow = header.Rows[i]; string formatter = dtRow.Get <string>(Config_Grid.Formatter); if (formatter == "boolFormatter") { formatter = "#是否"; //对bool型特殊处理。 } if (!string.IsNullOrEmpty(formatter) && formatter.StartsWith("#") && validateData != null && formatter.Length > 1) //&& validateData.ContainsKey(formatter) { //处理数据的有效性 CellRangeAddressList regions = null; IDataValidationConstraint constraint = null; IDataValidation dataValidate = null; if (validateData.ContainsKey(formatter)) { regions = new CellRangeAddressList(maxLevel, maxRow, i, i); string key = formatter.Split('=')[0].Replace("#", "").Replace(" ", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V"); constraint = dvHelper.CreateFormulaListConstraint(key); dataValidate = dvHelper.CreateValidation(constraint, regions); sheet.AddValidationData(dataValidate); } if (formatter.StartsWith("#C"))//级联要接着父级后加数据有效性才行 { string parentFormatter = formatter; while (formatdic.ContainsKey(parentFormatter)) { int point = 0; int parentindex = formatdic[parentFormatter]; formatdic.Remove(parentFormatter); foreach (var item in formatdic) { if (item.Key.IndexOf('=') > -1) { string parent = item.Key.Split('=')[1]; parent = parent.Replace(">", "#"); if (parent.Equals(parentFormatter.Split('=')[0])) { int selfindex = item.Value; string t = ConvertIndexToChar(parentindex); for (int im = maxLevel; im < maxRow; im++) { string func = string.Format("INDIRECT(${0}${1})", t, im + 1); regions = new CellRangeAddressList(im, im, selfindex, selfindex); constraint = dvHelper.CreateFormulaListConstraint(func); dataValidate = dvHelper.CreateValidation(constraint, regions); sheet.AddValidationData(dataValidate); } parentFormatter = item.Key; break; } } point += 1; } if (point.Equals(formatdic.Count)) { parentFormatter = string.Empty; } } } } } }
public void Import() { //根据视图名读取ExcelConfig信息。 MDataRow excelInfo = null; int index = 0, headCrossRowNum = 0; string sheetName = null; try { excelInfo = ExcelConfig.GetExcelRow(ObjName); if (excelInfo != null) { index = excelInfo.Get <int>("StartIndex", 0); headCrossRowNum = excelInfo.Get <int>("HeadCrossRowNum", 0); sheetName = excelInfo.Get <string>("CnName"); } } catch (Exception err) { Log.WriteLogToTxt(err);//避免其它地方没有升级数据库表脚本。 } MDataTable dt = ExcelHelper.ReadExcel(excelInfo != null, null, index, headCrossRowNum, sheetName); if (!dt.Columns.Contains("错误信息")) { dt.Columns.Add("错误信息", System.Data.SqlDbType.NVarChar); } dt.TableName = excelInfo != null ? ObjName : TableName; bool result = false; string msg = string.Empty; ImportResult iResult = BeforeImport(dt, excelInfo, out msg); if (iResult == ImportResult.Continue) { result = FormatExcel(dt, excelInfo); if (result) { result = ExcelConfig.AcceptChanges(dt, excelInfo, ObjName);// dt.AcceptChanges(AcceptOp.Auto); } } else { result = iResult == ImportResult.True; } if (!result) { if (dt.DynamicData != null && dt.DynamicData is Exception) { msg = ((Exception)dt.DynamicData).Message; msg += "(PS:可能模板不匹配)"; } if (excelStream == null) { excelStream = ExcelHelper.SetError(dt); } if (string.IsNullOrEmpty(msg)) { msg = "导入失败" + (excelStream != null ? "(请查看输出的Excel错误信息)" : "(请检测是否模板错误)"); } } else if (string.IsNullOrEmpty(msg)) { msg = "导入成功"; } dt.DynamicData = null; dt = null; GC.Collect(); jsonResult = JsonHelper.OutResult(result, msg); }
/// <summary> /// 导入时,把中文列头翻译成英文列头。 /// </summary> public static Dictionary <string, string> FormatterTitle(MDataTable dt, MDataRow info, string objName) { if (info == null) { return(GridConfig.SetHeaderField(dt, objName)); } else { Dictionary <string, string> formatDic = new Dictionary <string, string>(); MDataTable config = GetExcelInfo(info.Get <string>(0)); if (config != null) { //附加自定义列。 foreach (var configRow in config.Rows) { string formatter = configRow.Get <string>(Config_ExcelInfo.Formatter); if (!string.IsNullOrEmpty(formatter) && formatter[0] != '#')//增加默认值的列。 { string excelName = configRow.Get <string>(Config_ExcelInfo.ExcelName); if (!dt.Columns.Contains(excelName)) { MCellStruct ms = new MCellStruct(excelName, System.Data.SqlDbType.NVarChar); ms.TableName = configRow.Get <string>("TableName"); dt.Columns.Insert(dt.Columns.Count - 1, ms); } } } MDataRow row; foreach (MCellStruct item in dt.Columns) { row = config.FindRow("ExcelName='" + item.ColumnName + "'"); if (row == null && item.ColumnName.IndexOf('_') > 0) // 兼容只找一级的映射列。 { string columnName = item.ColumnName.Split('_')[0]; row = config.FindRow("ExcelName='" + columnName + "'"); } if (row != null) { string field = row.Get <string>("Field"); if (string.IsNullOrEmpty(field)) { continue; } if (string.Compare(item.ColumnName, field, StringComparison.OrdinalIgnoreCase) != 0) { item.Description = item.ColumnName;//把中文列名放到描述里。 item.TableName = row.Get <string>("TableName"); int index = dt.Columns.GetIndex(field); if (index < 0) { item.ColumnName = field;// } else // 字段同名 { item.ColumnName = item.TableName + "." + field; //修改上一个,也增加表名。 dt.Columns[index].ColumnName = dt.Columns[index].TableName + "." + dt.Columns[index].ColumnName; } } string formatter = row.Get <string>("Formatter"); if (!string.IsNullOrEmpty(formatter)) // 需要格式化的项 { if (formatter.Length > 2 && formatter[0] == '#') { //item.SqlType = System.Data.SqlDbType.NVarChar;//重置数据类型(int数据将格式成文本) formatDic.Add(item.ColumnName, formatter.Substring(1).Split(new string[] { "=>" }, StringSplitOptions.None)[0]); } else { item.DefaultValue = SQLCode.FormatPara(formatter);//如果不是#开头的,设置为默认值。 } } } } } return(formatDic); } }
/// <summary> /// 批量更新或插入。 /// </summary> /// <param name="dt"></param> /// <param name="excelRow"></param> /// <returns></returns> public static bool AcceptChanges(MDataTable dt, MDataRow excelRow, string objName = null) { if (excelRow == null) { MDataTable dtImportUnique = GridConfig.GetList(objName, GridConfig.SelectType.ImportUnique); string[] names = null; if (dtImportUnique != null && dtImportUnique.Rows.Count > 0) { names = new String[dtImportUnique.Rows.Count]; for (int i = 0; i < dtImportUnique.Rows.Count; i++) { names[i] = dtImportUnique.Rows[i].Get <string>("Field"); } } return(dt.AcceptChanges(AcceptOp.Auto, null, names)); } bool result = true; //获取相关配置 string[] tables = excelRow.Get <string>(Config_Excel.TableNames).Split(','); MDataTable configTable = GetExcelInfo(excelRow.Get <string>(Config_Excel.ExcelID)); Dictionary <string, string> rowPrimaryValue = new Dictionary <string, string>(); //存档每个表每行的主键值。 Dictionary <string, string> wherePrimaryValue = new Dictionary <string, string>(); //存档where条件对应的主键值。 using (MAction action = new MAction(tables[0])) { action.SetAopOff(); action.BeginTransation(); AppConfig.Debug.OpenDebugInfo = false; IExcelConfig excelConfigExtend = ExcelConfigFactory.GetExcelConfigExtend(); foreach (var table in tables) { GC.Collect();//后面的Fill查询代码循环上万次会增长太多内存,提前调用,能降低内存。 action.ResetTable(table); for (int i = 0; i < dt.Rows.Count; i++) { action.Data.Clear(); var row = dt.Rows[i]; foreach (var cell in row) //遍历所有数据行 { if (cell.Struct.TableName == table) //过滤出属于本表的字段。 { string[] items = cell.ColumnName.Split('.'); string columnName = items[items.Length - 1]; action.Set(columnName, cell.Value); } } #region 检测是否需要插入外键。 MDataTable foreignTable = configTable.FindAll("TableName='" + table + "' and IsForeignkey=1"); if (foreignTable != null) { foreach (var foreignRow in foreignTable.Rows) { string formatter = foreignRow.Get <string>("Formatter"); string fTableName = foreignRow.Get <string>("ForeignTable"); if (string.IsNullOrEmpty(formatter)) { //获取主键外值键 string key = fTableName + i; if (rowPrimaryValue.ContainsKey(key)) { string value = rowPrimaryValue[key]; action.Set(foreignRow.Get <string>("Field"), value); } } else // 从其它自定义列取值。 { MDataCell cell = row[formatter]; cell = cell ?? row[fTableName + "." + formatter]; if (cell != null) { action.Set(foreignRow.Get <string>("Field"), cell.Value); } } } foreignTable = null; } #endregion #region //获取唯一联合主键,检测是否重复 string where = string.Empty; List <MDataRow> rowList = configTable.FindAll("TableName='" + table + "' and IsUnique=1"); if (rowList != null && rowList.Count > 0) { bool IsUniqueOr = excelRow.Get <bool>("IsUniqueOr"); List <MDataCell> cells = new List <MDataCell>(); string errText = string.Empty; int errorCount = 0; foreach (var item in rowList) { var cell = action.Data[item.Get <string>(Config_ExcelInfo.Field)]; if (cell != null) { if (cell.IsNullOrEmpty) // 唯一主键是必填写字段 { errorCount++; errText += "[第" + (i + 1) + "行数据]:" + cell.Struct.ColumnName + "[" + cell.Struct.Description + "]不允许为空!\r\n"; } else { cells.Add(cell); } } } if (errorCount > 0) { if (!IsUniqueOr || errorCount == rowList.Count) { result = false; dt.DynamicData = new Exception(errText); goto err; } } MDataCell[] item2s = cells.ToArray(); where = action.GetWhere(!IsUniqueOr, item2s); item2s = null; rowList = null; } if (!string.IsNullOrEmpty(where)) { action.SetSelectColumns(action.Data.PrimaryCell.ColumnName); if (action.Fill(where))//根据条件查出主键ID { string key = table + where; if (wherePrimaryValue.ContainsKey(key)) { rowPrimaryValue.Add(table + i, wherePrimaryValue[key]);//记录上一个主键值。 } else { rowPrimaryValue.Add(table + i, action.Get <string>(action.Data.PrimaryCell.ColumnName));//记录上一个主键值。 } if (action.Data.GetState() == 2) { ExcelResult eResult = excelConfigExtend.BeforeUpdate(action.Data, row); if (eResult == ExcelResult.Ignore || (eResult == ExcelResult.Default && action.Update(where))) { continue;//已经存在了,更新,准备下一条。 } else { result = false; dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]:" + action.DebugInfo); goto err; } } else { continue;//已经存在了,同时没有可更新字段 } } else if (!string.IsNullOrEmpty(action.DebugInfo))//产生错误信息,发生异常 { result = false; dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]:" + action.DebugInfo); goto err; } } #endregion if (action.Data.GetState() == 0) { continue;//没有可映射插入的列。 } //插入前,调用函数(插入特殊主键等值) string errMsg; ExcelResult excelResult = excelConfigExtend.BeforeInsert(action.Data, row, out errMsg); if (excelResult == ExcelResult.Ignore) { continue; } if (excelResult == ExcelResult.Error || !action.Insert(InsertOp.ID)) { result = false; action.RollBack(); if (string.IsNullOrEmpty(errMsg)) { errMsg = "[第" + (i + 1) + "行数据]:" + action.DebugInfo; } dt.DynamicData = new Exception(errMsg); excelConfigExtend.OnInsertError(errMsg, dt); goto err; } //插入后事件(可以触发其它事件) excelConfigExtend.AfterInsert(action.Data, row, i == dt.Rows.Count - 1); string primaryKey = action.Get <string>(action.Data.PrimaryCell.ColumnName); rowPrimaryValue.Add(table + i, primaryKey);//记录上一个主键值。 if (!wherePrimaryValue.ContainsKey(table + where)) { wherePrimaryValue.Add(table + where, primaryKey); } } } err: action.EndTransation(); excelConfigExtend.Dispose(); } return(result); }
/// <summary> /// 导入时:把中文列头翻译成英文列头(同时处理列头结构)。 /// 并返回字典:key:列头,value:格式化名 /// </summary> public static Dictionary<string, string> FormatterTitle(MDataTable dt, MDataRow info, string objName) { if (info == null) { return GridConfig.SetHeaderField(dt, objName); } else { Dictionary<string, string> formatDic = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase); MDataTable infoConfig = GetExcelInfo(info.Get<string>(0)); if (infoConfig != null) { //附加自定义列。 foreach (var configRow in infoConfig.Rows) { string formatter = configRow.Get<string>(Config_ExcelInfo.Formatter); if (!string.IsNullOrEmpty(formatter) && formatter[0] != '#')//增加默认值的列。 { string excelName = configRow.Get<string>(Config_ExcelInfo.ExcelName); if (!dt.Columns.Contains(excelName)) { MCellStruct ms = new MCellStruct(excelName, System.Data.SqlDbType.NVarChar); ms.TableName = configRow.Get<string>(Config_ExcelInfo.TableName); dt.Columns.Insert(dt.Columns.Count - 1, ms); } } } MDataRow infoRow; foreach (MCellStruct item in dt.Columns) { infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + item.ColumnName + "'"); if (infoRow == null && item.ColumnName.IndexOf('_') > 0) // 兼容只找一级的映射列。 { string columnName = item.ColumnName.Split('_')[0]; infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + columnName + "'"); } if (infoRow != null) { string field = infoRow.Get<string>(Config_ExcelInfo.Field); if (string.IsNullOrEmpty(field)) { continue; } item.Description = item.ColumnName;//把中文列名放到描述里。 item.TableName = infoRow.Get<string>(Config_ExcelInfo.TableName); if (string.Compare(item.ColumnName, field, StringComparison.OrdinalIgnoreCase) != 0) { int index = dt.Columns.GetIndex(field); if (index < 0) { item.ColumnName = field;// } else // 字段同名 { item.ColumnName = item.TableName + "." + field; //修改上一个,也增加表名。 dt.Columns[index].ColumnName = dt.Columns[index].TableName + "." + dt.Columns[index].ColumnName; } } string formatter = infoRow.Get<string>(Config_ExcelInfo.Formatter); if (!string.IsNullOrEmpty(formatter)) // 需要格式化的项 { if (formatter.Length > 2 && formatter[0] == '#') { //item.SqlType = System.Data.SqlDbType.NVarChar;//重置数据类型(int数据将格式成文本) formatDic.Add(item.ColumnName, formatter.Substring(1).Split(new string[] { "=>" }, StringSplitOptions.None)[0]); } else { item.DefaultValue = SqlCode.FormatPara(formatter);//如果不是#开头的,设置为默认值(同时处理@参数)。 } } } } } return formatDic; } }
/// <summary> /// 批量更新或插入。 /// </summary> /// <param name="dt"></param> /// <param name="excelRow"></param> /// <returns></returns> public static bool AcceptChanges(MDataTable dt, MDataRow excelRow, string objName = null) { if (excelRow == null) { MDataTable dtImportUnique = GridConfig.GetList(objName, GridConfig.SelectType.ImportUnique); string[] names = null; if (dtImportUnique != null && dtImportUnique.Rows.Count > 0) { names = new String[dtImportUnique.Rows.Count]; for (int i = 0; i < dtImportUnique.Rows.Count; i++) { names[i] = dtImportUnique.Rows[i].Get<string>(Config_Grid.Field); } } return dt.AcceptChanges(AcceptOp.Auto, null, names); } bool result = true; //获取相关配置 string[] tables = excelRow.Get<string>(Config_Excel.TableNames).Split(','); MDataTable configTable = GetExcelInfo(excelRow.Get<string>(Config_Excel.ExcelID)); Dictionary<string, string> rowPrimaryValue = new Dictionary<string, string>();//存档每个表每行的主键值。 Dictionary<string, string> wherePrimaryValue = new Dictionary<string, string>();//存档where条件对应的主键值。 int acceptType = excelRow.Get<int>(Config_Excel.AcceptType); using (MAction action = new MAction(tables[0])) { action.SetAopState(AopOp.CloseAll); action.BeginTransation(); AppConfig.Debug.OpenDebugInfo = false; IExcelConfig excelConfigExtend = ExcelConfigFactory.GetExcelConfigExtend(); foreach (var table in tables) { GC.Collect();//后面的Fill查询代码循环上万次会增长太多内存,提前调用,能降低内存。 action.ResetTable(table); for (int i = 0; i < dt.Rows.Count; i++) { action.Data.Clear(); var row = dt.Rows[i]; foreach (var cell in row)//遍历所有数据行 { if (cell.Struct.TableName != null && cell.Struct.TableName.ToLower() == table.ToLower())//过滤出属于本表的字段。 { string[] items = cell.ColumnName.Split('.'); string columnName = items[items.Length - 1]; action.Set(columnName, cell.Value); } } #region 检测是否需要插入外键。 MDataTable foreignTable = configTable.FindAll("TableName='" + table + "' and IsForeignkey=1"); if (foreignTable != null) { foreach (var foreignRow in foreignTable.Rows) { string formatter = foreignRow.Get<string>("Formatter"); string fTableName = foreignRow.Get<string>("ForeignTable"); if (string.IsNullOrEmpty(formatter)) { //获取主键外值键 string key = fTableName + i; if (rowPrimaryValue.ContainsKey(key)) { string value = rowPrimaryValue[key]; action.Set(foreignRow.Get<string>("Field"), value); } } else // 从其它自定义列取值。 { MDataCell cell = row[formatter]; cell = cell ?? row[fTableName + "." + formatter]; if (cell != null) { action.Set(foreignRow.Get<string>("Field"), cell.Value); } } } foreignTable = null; } #endregion #region //获取唯一联合主键,检测是否重复 string where = string.Empty; List<MDataRow> rowList = configTable.FindAll("TableName='" + table + "' and IsUnique=1"); if (rowList != null && rowList.Count > 0) { bool isUniqueOr = excelRow.Get<bool>(Config_Excel.WhereType); List<MDataCell> cells = new List<MDataCell>(); string errText = string.Empty; int errorCount = 0; foreach (var item in rowList) { var cell = action.Data[item.Get<string>(Config_ExcelInfo.Field)]; if (cell != null) { if (cell.IsNullOrEmpty) // 唯一主键是必填写字段 { errorCount++; errText += "[第" + (i + 1) + "行数据]:" + cell.Struct.ColumnName + "[" + cell.Struct.Description + "]不允许为空!\r\n"; } else { cells.Add(cell); } } } if (errorCount > 0) { if (!isUniqueOr || errorCount == rowList.Count) { result = false; dt.DynamicData = new Exception(errText); goto err; } } MDataCell[] item2s = cells.ToArray(); where = action.GetWhere(!isUniqueOr, item2s); item2s = null; rowList = null; } if (!string.IsNullOrEmpty(where)) { MDataRow data = action.Data.Clone(); action.SetSelectColumns(action.Data.PrimaryCell.ColumnName); if (action.Fill(where))//根据条件查出主键ID (数据被清空) { string key = table + where; if (wherePrimaryValue.ContainsKey(key)) { rowPrimaryValue.Add(table + i, wherePrimaryValue[key]);//记录上一个主键值。 } else { rowPrimaryValue.Add(table + i, action.Get<string>(action.Data.PrimaryCell.ColumnName));//记录上一个主键值。 } action.Data.LoadFrom(data, RowOp.IgnoreNull, false);//还原数据。 if (action.Data.GetState() == 2 && acceptType != 1)//排除掉仅插入选项 { ExcelResult eResult = excelConfigExtend.BeforeUpdate(action.Data, row); if (eResult == ExcelResult.Ignore || (eResult == ExcelResult.Default && action.Update(where))) { continue;//已经存在了,更新,准备下一条。 } else { result = false; dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]:" + action.DebugInfo); goto err; } } else { continue;//已经存在了,同时没有可更新字段 } } else if (action.RecordsAffected == -2)//产生错误信息,发生异常 { result = false; dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]:" + action.DebugInfo); goto err; } } #endregion if (action.Data.GetState() == 0 || acceptType == 2)//仅更新则跳过插入 { continue;//没有可映射插入的列。 } //插入前,调用函数(插入特殊主键等值) string errMsg; ExcelResult excelResult = excelConfigExtend.BeforeInsert(action.Data, row, out errMsg); if (excelResult == ExcelResult.Ignore) { continue; } if (excelResult == ExcelResult.Error || !action.Insert(InsertOp.ID)) { result = false; action.RollBack(); if (string.IsNullOrEmpty(errMsg)) { errMsg = "[第" + (i + 1) + "行数据]:" + action.DebugInfo; } dt.DynamicData = new Exception(errMsg); excelConfigExtend.OnInsertError(errMsg, dt); goto err; } //插入后事件(可以触发其它事件) excelConfigExtend.AfterInsert(action.Data, row, i == dt.Rows.Count - 1); string primaryKey = action.Get<string>(action.Data.PrimaryCell.ColumnName); rowPrimaryValue.Add(table + i, primaryKey);//记录上一个主键值。 if (!wherePrimaryValue.ContainsKey(table + where)) { wherePrimaryValue.Add(table + where, primaryKey); } } } err: action.EndTransation(); excelConfigExtend.Dispose(); } return result; }
/// <summary> /// 验证基础数据(数据类型、长度、是否为Null) /// </summary> /// <returns></returns> public static bool ValidateData(MDataTable dt, MDataRow info) { bool result = true; string[] tables = null; List<string> requiredList = new List<string>();//必填项表。 if (info != null) { tables = info.Get<string>(Config_Excel.TableNames, string.Empty).Split(','); MDataTable dtRequired = GetExcelInfo(info.Get<string>(0));//必填项表。 if (dtRequired != null && dtRequired.Rows.Count > 0) { dtRequired = dtRequired.Select(Config_ExcelInfo.IsRequired + "=1"); if (dtRequired != null && dtRequired.Rows.Count > 0) { foreach (var row in dtRequired.Rows) { requiredList.Add(row.Get<string>(Config_ExcelInfo.TableName) + row.Get<string>(Config_ExcelInfo.Field)); } } } } else { tables = dt.TableName.Split(','); } bool isOK = false; foreach (var table in tables)//重置列头。 { MDataColumn mdc = DBTool.GetColumns(table); foreach (var cs in dt.Columns) { string[] items = cs.ColumnName.Split('.'); if (cs.TableName == table) { int index = mdc.GetIndex(items[items.Length - 1]); if (index > -1) { isOK = true;//至少需要一个列对应上,若没有,则模板错误 cs.SqlType = mdc[index].SqlType; cs.IsCanNull = mdc[index].IsCanNull; if (requiredList.Contains(table + mdc[index].ColumnName))//要求必填 { cs.IsCanNull = false; } cs.MaxSize = mdc[index].MaxSize; } } } } if (!isOK) { return false; } foreach (var row in dt.Rows) { StringBuilder sb = new StringBuilder(); foreach (var cell in row) { if (!string.IsNullOrEmpty(cell.Struct.TableName)) { string columnName = string.IsNullOrEmpty(cell.Struct.Description) ? cell.Struct.ColumnName : cell.Struct.Description; if (!cell.Struct.IsCanNull && cell.IsNullOrEmpty) { sb.AppendFormat("[{0}]不允许为空。", columnName); cell.State = -1; } else if (cell.Struct.MaxSize != -1 && cell.ToString().Length > cell.Struct.MaxSize && cell.Struct.SqlType != System.Data.SqlDbType.Bit) { sb.AppendFormat("[{0}]长度超过{1}。", columnName, cell.Struct.MaxSize); cell.State = -1; } else if (!cell.FixValue()) { sb.AppendFormat("[{0}]数据类型错误。", columnName); cell.State = -1; } } } if (sb.Length > 0) { result = false; row.Set("错误信息", row.Get<string>("错误信息") + sb.ToString()); } } return result; }
public static MemoryStream CreateExcelHeader(MDataTable header, Dictionary <string, string[]> validateData) { MemoryStream ms = new MemoryStream(); if (header != null && header.Rows.Count > 0) { MDataTable importHeader = header.FindAll("Import=1"); try { XSSFWorkbook export = new XSSFWorkbook(); ICellStyle style = GetStyle(export, HSSFColor.LightOrange.Index); ISheet sheet = export.CreateSheet("Sheet1");//创建内存Excel #region 创建引用 int rowStartIndex = 1; CreateValidationSheet(export, validateData, rowStartIndex); #endregion importHeader.Rows.Sort("ORDER BY MergeIndexed DESC");//Hidden=0 AND (Export=1 OR Field LIKE 'mg_%') MDataTable headTable = importHeader.Clone(); int ColTitleRowCount = 0; Dictionary <string, int> formatdic = new Dictionary <string, int>(); for (int i = importHeader.Rows.Count - 1; i >= 0; i--)//MDataTable 不支持 NOT LIKE { if (importHeader.Rows[i]["Field"].Value.ToString().IndexOf("mg") > -1) { importHeader.Rows.RemoveAt(i);//非字段列移除 } } int colSum = importHeader.Rows.Count;//实际列数 importHeader.Rows.Sort("ORDER BY OrderNum ASC"); if (!ExportMulHeader(header, true)) { IRow row = sheet.CreateRow(0); ICell cell; for (int i = 0; i < colSum; i++) { string title = importHeader.Rows[i]["Title"].Value.ToString(); cell = row.CreateCell(i); cell.SetCellValue(title);//设置列头 sheet.SetColumnWidth(i, 3000); cell.CellStyle = style; } } else { CreateMulHeadExcel(export, headTable, out ColTitleRowCount, colSum); ColTitleRowCount -= 1; } for (int i = 0; i < importHeader.Rows.Count; i++) { string formater = importHeader.Rows[i].Get <string>("Formatter"); if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater)) { formatdic.Add(formater, i);//存储列索引 } } int maxRow = 50000;//限制最大行数(07之前版本的excel最大行数为65536,但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效) XSSFSheet xssfSheet = (XSSFSheet)sheet; XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet); for (int i = 0; i < importHeader.Rows.Count; i++) { MDataRow dtRow = importHeader.Rows[i]; string formatter = dtRow.Get <string>("Formatter"); if (formatter == "boolFormatter") { formatter = "#是否"; //对bool型特殊处理。 } if (!string.IsNullOrEmpty(formatter) && formatter.StartsWith("#") && validateData != null && formatter.Length > 1) //&& validateData.ContainsKey(formatter) { //处理数据的有效性 CellRangeAddressList regions = null; IDataValidationConstraint constraint = null; IDataValidation dataValidate = null; //int maxRow = 65535; if (validateData.ContainsKey(formatter)) { regions = new CellRangeAddressList(ColTitleRowCount + 1, maxRow, i, i); string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V"); /*03版本api * constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter] * dataValidate = new HSSFDataValidation(regions, constraint); */ constraint = dvHelper.CreateFormulaListConstraint(key); dataValidate = dvHelper.CreateValidation(constraint, regions); sheet.AddValidationData(dataValidate); //regions = new CellRangeAddressList(ColTitleRowCount, maxRow, i, i); //string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V"); //constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter] //dataValidate = new HSSFDataValidation(regions, constraint); //sheet.AddValidationData(dataValidate); } // if (formatter.StartsWith("#C"))//级联要接着父级后加数据有效性才行 { string Parentformatter = formatter; while (formatdic.ContainsKey(Parentformatter)) { int point = 0; int parentindex = formatdic[Parentformatter]; formatdic.Remove(Parentformatter); foreach (var item in formatdic) { if (item.Key.IndexOf('=') > -1) { string parent = item.Key.Split('=')[1]; parent = parent.Replace(">", "#"); if (parent.Equals(Parentformatter.Split('=')[0])) { int selfindex = item.Value; //int parentindex = formatdic[hereformatter]; string t = IntToMoreChar(parentindex); for (int im = ColTitleRowCount; im < maxRow; im++) { string func = string.Format("@INDIRECT({0}{1})", t, im + 1); regions = new CellRangeAddressList(im, im, selfindex, selfindex); constraint = dvHelper.CreateFormulaListConstraint(func); dataValidate = dvHelper.CreateValidation(constraint, regions); sheet.AddValidationData(dataValidate); } //for (int im = ColTitleRowCount; im < maxRow; im++)//1000应为maxRow //{ // string func = "INDIRECT(" + t + (im + 1) + ")";//excel2013不能级联,03可以,其他没测过 // regions = new CellRangeAddressList(ColTitleRowCount, im, selfindex, selfindex); // constraint = DVConstraint.CreateFormulaListConstraint(func); // dataValidate = new HSSFDataValidation(regions, constraint); // sheet.AddValidationData(dataValidate); //} Parentformatter = item.Key; break; } } point += 1; } if (point.Equals(formatdic.Count)) { Parentformatter = string.Empty; } } } } } export.Write(ms); ms.Flush(); ms.Close(); } catch (Exception err) { Log.WriteLogToTxt(err); } } return(ms); }
/// <summary> /// 验证基础数据(数据类型、长度、是否为Null) /// </summary> /// <returns></returns> public static bool ValidateData(MDataTable dt, MDataRow info) { bool result = true; string[] tables = null; List <string> requiredList = new List <string>();//必填项表。 if (info != null) { tables = info.Get <string>(Config_Excel.TableNames, string.Empty).Split(','); MDataTable dtRequired = GetExcelInfo(info.Get <string>(0));//必填项表。 if (dtRequired != null && dtRequired.Rows.Count > 0) { dtRequired = dtRequired.Select("IsRequired=1"); if (dtRequired != null && dtRequired.Rows.Count > 0) { foreach (var row in dtRequired.Rows) { requiredList.Add(row.Get <string>("TableName") + row.Get <string>("Field")); } } } } else { tables = dt.TableName.Split(','); } bool isOK = false; foreach (var table in tables)//重置列头。 { MDataColumn mdc = CYQ.Data.Tool.DBTool.GetColumns(table); foreach (var cs in dt.Columns) { string[] items = cs.ColumnName.Split('.'); if (cs.TableName == table) { int index = mdc.GetIndex(items[items.Length - 1]); if (index > -1) { isOK = true;//至少需要一个列对应上,若没有,则模板错误 cs.SqlType = mdc[index].SqlType; cs.IsCanNull = mdc[index].IsCanNull; if (requiredList.Contains(table + mdc[index].ColumnName))//要求必填 { cs.IsCanNull = false; } cs.MaxSize = mdc[index].MaxSize; } } } } if (!isOK) { return(false); } foreach (var row in dt.Rows) { StringBuilder sb = new StringBuilder(); foreach (var cell in row) { if (!string.IsNullOrEmpty(cell.Struct.TableName)) { string columnName = string.IsNullOrEmpty(cell.Struct.Description) ? cell.Struct.ColumnName : cell.Struct.Description; if (!cell.Struct.IsCanNull && cell.IsNullOrEmpty) { sb.AppendFormat("[{0}]不允许为空。", columnName); cell.State = -1; } else if (cell.Struct.MaxSize != -1 && cell.ToString().Length > cell.Struct.MaxSize && cell.Struct.SqlType != System.Data.SqlDbType.Bit) { sb.AppendFormat("[{0}]长度超过{1}。", columnName, cell.Struct.MaxSize); cell.State = -1; } else if (!cell.FixValue()) { sb.AppendFormat("[{0}]数据类型错误。", columnName); cell.State = -1; } } } if (sb.Length > 0) { result = false; row.Set("错误信息", row.Get <string>("错误信息") + sb.ToString()); } } return(result); }
public void Import() { //根据视图名读取ExcelConfig信息。 MDataRow excelInfo = null; int index = 0, headCrossRowNum = 0; string sheetName = null; try { excelInfo = ExcelConfig.GetExcelRow(ObjName); if (excelInfo != null) { index = excelInfo.Get <int>(Config_Excel.StartIndex, 0); headCrossRowNum = excelInfo.Get <int>(Config_Excel.HeadCrossRowNum, 0); sheetName = excelInfo.Get <string>(Config_Excel.Description); } } catch (Exception err) { Log.WriteLogToTxt(err);//避免其它地方没有升级数据库表脚本。 } MDataTable dt = ExcelHelper.ReadExcel(null, sheetName, index, headCrossRowNum, excelInfo != null); if (!dt.Columns.Contains(LangConst.ErrorInfo)) { dt.Columns.Add(LangConst.ErrorInfo, System.Data.SqlDbType.NVarChar); } dt.TableName = excelInfo != null ? ObjName : TableName; bool result = false; string msg = string.Empty; ImportResult iResult = BeforeImport(dt, excelInfo, out msg); if (iResult == ImportResult.Continue) { result = FormatExcel(dt, excelInfo); if (result) { result = ExcelConfig.AcceptChanges(dt, excelInfo, ObjName);// dt.AcceptChanges(AcceptOp.Auto); } } else { result = iResult == ImportResult.True; } if (!result) { if (dt.DynamicData != null && dt.DynamicData is Exception) { msg = ((Exception)dt.DynamicData).Message; msg += LangConst.ImportTemplateNotMatch; } if (excelStream == null) { excelStream = ExcelHelper.SetError(dt); } if (string.IsNullOrEmpty(msg)) { msg = LangConst.ImportError + (excelStream != null ? LangConst.ImportCheckErrorInfo : LangConst.ImportCheckTemplateIsRight); } } else if (string.IsNullOrEmpty(msg)) { msg = LangConst.ImportSuccess; } dt.DynamicData = null; dt = null; GC.Collect(); jsonResult = JsonHelper.OutResult(result, msg); }
/// <summary> /// 获得多表头层级,并分组(算法和前端Aries.Common.Js里的getColumnGroup一致) /// </summary> private static Dictionary <int, List <MDataRow> > GetColumnGroup(MDataTable header) { Dictionary <int, List <MDataRow> > result = new Dictionary <int, List <MDataRow> >(); int index = 0; int[] num = new int[6]; for (int i = 0; i < header.Rows.Count; i++) { MDataRow row = header.Rows[i]; if (row.Get <string>(Config_Grid.Field).IndexOf("mg_") != -1) { int colspan = row.Get <int>(Config_Grid.Colspan, 1); if (num[index] > 0)//内部嵌套 { index++; num[index] = colspan; num[index - 1] = num[index - 1] - num[index];//父级数字要减掉子级的数量 } else { num[index] = colspan; } if (!result.ContainsKey(index)) { result.Add(index, new List <MDataRow>()); } result[index].Add(row); } else { var level = (num[index] > 0) ? index + 1 : index; if (num[index] > 0) { num[index]--; if (num[index] == 0) //列已经够了 { if (index > 0) //如果是子级 { index--; } } } if (!result.ContainsKey(level)) { result.Add(level, new List <MDataRow>()); } result[level].Add(row); } } //设置RowSpan属性 int maxLen = result.Count; for (int i = 0; i < result.Count; i++) { for (int k = 0; k < result[i].Count; k++) { MDataRow row = result[i][k]; if (row.Get <int>(Config_Grid.Rowspan, 1) == 1 && !row.Get <string>(Config_Grid.Field).StartsWith("mg_")) { row.Set(Config_Grid.Rowspan, maxLen - i); } } } return(result); }