/// <summary> /// 获取表的列头数据(输出EasyUI的列头格式) /// </summary> public virtual void GetHeader() { MDataTable dt = GridConfig.GetList(ObjName, GridConfig.SelectType.All); if (dt == null || dt.Rows.Count == 0) { dt = GridConfig.Create(ObjName, ObjCode, dt.GetSchema(false)); if (p.UrlMenuID != string.Empty && dt.Rows.Count > 0)//仅处理配置了菜单的数据。 { //顺带处理视图语句与菜单名称的绑定 KeyValueConfig.SetTableDescription(ObjName, p.MenuName); } } if (Query <string>("reflesh") == "1")//刷新表结构 { string msg; bool result = GridConfig.Flesh(ObjName, ObjCode, dt, out msg); jsonResult = JsonHelper.OutResult(result, msg); } else if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i][Config_Grid.Field].Value = dt.Rows[i].Get <string>(Config_Grid.Field).ToLower(); } jsonResult = dt.ToJson(false, false, RowOp.None, true); } else { string err = dt.DynamicData != null?dt.DynamicData.ToString() : "Init Column Header Fail!"; jsonResult = JsonHelper.OutResult(false, err); } }
/// <summary> /// 获取表的列头数据(输出EasyUI的列头格式) /// </summary> public void GetHeader() { MDataTable dt = GridConfig.GetList(ObjName, GridConfig.SelectType.All); if (dt == null || dt.Rows.Count == 0) { dt = GridConfig.Create(ObjName, ObjCode, dt.GetSchema(false)); if (p.UrlMenuID != string.Empty)//仅处理配置了菜单的数据。 { //顺带处理视图语句与菜单名称的绑定 KeyValueConfig.SetTableDescription(ObjName, p.MenuName); } } else { //dt = GridConfig.Check(ObjName,ObjCode, dt); } if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i][Config_Grid.Field].Value = dt.Rows[i].Get <string>(Config_Grid.Field).ToLower(); } jsonResult = dt.ToJson(false, false, true); } else { jsonResult = JsonHelper.OutResult(false, "Init Column Header Fail!"); } }
public void Export() { MDataTable dt = Select(GridConfig.SelectType.Export); dt.TableName = ObjName; dt.DynamicData = GridConfig.GetList(ObjName, GridConfig.SelectType.All); Dictionary <string, string> formatParas = GridConfig.SetHeaderDescription(dt); //格式化列头(描述)(需要修改列头的数据格式) KeyValueConfig.FormatNameValue(dt, formatParas, true); //格式化配置项 WebHelper.SendFile(KeyValueConfig.GetTableDescription(ObjName, TableName) + "_" + DateTime.Now.ToString("yyyyMMdd") + ".xls", ExcelHelper.CreateExcel(dt)); //ExcelHelper.CreateExcel(dt) }
/// <summary> /// 允许重写导出的数据源(适用于存储过程的自定义数据源) /// </summary> protected virtual MDataTable Select(GridConfig.SelectType st) { MDataTable dt; using (MAction action = new MAction(CrossObjName)) { action.SetSelectColumns(GridConfig.GetSelectColumns(ObjName, st));//只查询要显示的列数据。 dt = action.Select(PageIndex, PageSize, GetWhere() + GetOrderBy(action.Data.PrimaryCell.ColumnName)); } return(dt); }
public virtual void Export() { MDataTable dt = Select(GridConfig.SelectType.Export); dt.TableName = ObjName; dt.DynamicData = GridConfig.GetList(ObjName, GridConfig.SelectType.Export); Dictionary <string, string> formatParas = GridConfig.GetFormatter(dt); //格式化列头(描述)(需要修改列头的数据格式) KeyValueConfig.FormatNameValue(dt, formatParas, true); //格式化配置项 string fix = ExcelHelper.BookType == ExcelHelper.WorkBookType.High ? ".xlsx" : ".xls"; WebHelper.SendFile(KeyValueConfig.GetTableDescription(ObjName, TableName) + "_" + DateTime.Now.ToString("yyyyMMdd") + fix, ExcelHelper.CreateExcel(dt));//ExcelHelper.CreateExcel(dt) }
/// <summary> /// 获取表的列头数据(输出EasyUI的列头格式) /// </summary> public void GetHeader() { MDataTable dt = GridConfig.GetList(ObjName, GridConfig.SelectType.All); if (dt == null || dt.Rows.Count == 0) { dt = GridConfig.Create(ObjCode, dt.GetSchema(false)); } else { //dt = GridConfig.Check(ObjCode, dt); } //顺带处理视图语句与菜单名称的绑定 KeyValueConfig.SetTableDescription(ObjName, p.MenuName); jsonResult = dt.ToJson(false, false, true); }
/// <summary> /// 获取某对应项对应的数据库脚本。 /// </summary> /// <param name="objName">对象名称</param> /// <returns></returns> internal static string GetGridConfigScript(string objName) { StringBuilder sb = new StringBuilder(); MDataTable dt = GridConfig.GetList(objName, GridConfig.SelectType.All); if (dt != null) { dt.Columns.RemoveAt(0);//移除主键 sb.Append("Delete from Config_Grid where ObjName='" + objName + "'\r\nGo\r\n"); //创建SQL脚本。 foreach (var row in dt.Rows) { row.TableName = "Config_Grid"; sb.AppendLine(GetSQLScript(row, "ObjName", "Field")); } } return(sb.ToString()); }
/// <summary> /// 获取导入数据的模板(支持2007以上) /// </summary> public void ExcelTemplate() { string path = HttpContext.Current.Server.MapPath("~/Resource/Excel/" + ObjName + ".xls"); if (!File.Exists(path)) { path = path + "x"; if (!File.Exists(path)) { path = HttpContext.Current.Server.MapPath("~/Resource/Excel/" + TableName + ".xls"); if (!File.Exists(path)) { path = path + "x"; } } } MemoryStream ms = null; if (File.Exists(path)) { byte[] data = File.ReadAllBytes(path); ms = new MemoryStream(data, 0, data.Length, false, true); } string fix = "_模板"; if (ms == null) { fix = "-模板"; string objName = ObjName; MDataTable dt = GridConfig.GetList(objName, GridConfig.SelectType.All);//获取所有列的字段名。 if (dt.Rows.Count > 0) { ms = ExcelHelper.CreateExcelHeader(dt, KeyValueConfig.GetValidationData(dt)); } } WebHelper.SendFile(KeyValueConfig.GetTableDescription(ObjName, TableName) + fix + Path.GetExtension(path), ms); }
/// <summary> /// 获取导入数据的模板(支持2007以上) /// </summary> public void ExcelTemplate() { string path = HttpContext.Current.Server.MapPath("~/Resource/Excel/" + ObjName + ".xls"); if (!File.Exists(path)) { path = path + "x"; if (!File.Exists(path) && ObjName != TableName) { path = HttpContext.Current.Server.MapPath("~/Resource/Excel/" + TableName + ".xls"); if (!File.Exists(path)) { path = path + "x"; } } } MemoryStream ms = null; string fix = ExcelHelper.BookType == ExcelHelper.WorkBookType.High ? ".xlsx" : ".xls"; if (File.Exists(path)) { byte[] data = File.ReadAllBytes(path); ms = new MemoryStream(data, 0, data.Length, false, true); fix = Path.GetExtension(path); } if (ms == null) { string objName = ObjName; MDataTable header = GridConfig.GetList(objName, GridConfig.SelectType.Import);//获取所有列的字段名。 if (header.Rows.Count > 0) { ms = ExcelHelper.CreateExcelHeader(header, KeyValueConfig.GetValidationData(header)); } } WebHelper.SendFile(KeyValueConfig.GetTableDescription(ObjName, TableName) + fix, ms); }
/// <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); }