public static MDataTable Check(string objName, string objCode, MDataTable dt) { #if DEBUG MDataTable newDt = dt.GetSchema(false); FillTable(objName, objCode, newDt);//重新获取。 MDataTable addTable = dt.GetSchema(false); bool needUpdate = false; foreach (MDataRow row in newDt.Rows) { MDataRow mr = dt.FindRow(string.Format("Field='{0}'", row.Get <string>("Field"))); if (mr == null)//找不到,则添加行 { row.Set("Hidden", true); addTable.Rows.Add(row); } else if (mr["Field"].ToString() == mr["Title"].ToString()) // 找到,则试图设置中文名称。 { mr.Set("Title", row.Get <string>("Title")); needUpdate = true; } } if (addTable.Rows.Count > 0) { addTable.AcceptChanges(AcceptOp.Auto, null, "ObjName", "Field"); } if (needUpdate) { dt.AcceptChanges(AcceptOp.Update); } #endif return(dt); }
/// <summary> /// 在进行新增加前处理的事件(action参数不需要关闭)。 /// 数据行直接拿action.Data /// 返回true则继续执行更新,返回false则停止更新。 /// </summary> private void SetKeyValue(MDataRow row) { if (keyValue.Count > 0) { foreach (KeyValuePair <string, string> item in keyValue) { row.Set(item.Key, item.Value); } } }
void CreateRow() { MDataColumn mdc = new MDataColumn(); mdc.Add("ID", SqlDbType.Int, true); mdc.Add("Name"); mdc.Add("CreateTime", SqlDbType.DateTime); row = new MDataRow(mdc); row.Set(0, 1).Set(1, "hello").Set(2, DateTime.Now); row.SetToAll(this); }
//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); }
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; } }
private static void FillTable(string objName, string objCode, MDataTable dt) { Dictionary <string, string> fieldTitleDic = GridConfig.FieldTitle; string errInfo; string tableName = Convert.ToString(CrossDb.GetEnum(objCode)); MDataColumn mdc = DBTool.GetColumns(tableName, null, out errInfo); if (mdc == null || mdc.Count == 0) { if (!string.IsNullOrEmpty(errInfo)) { dt.DynamicData = errInfo; } return; } MCellStruct cell = null; int jointPrimaryCount = mdc.JointPrimary.Count; for (int i = 0; i < mdc.Count; i++) { cell = mdc[i]; MDataRow row = dt.NewRow(); row.Set(Config_Grid.ObjName, objName); row.Set(Config_Grid.Field, cell.ColumnName); row.Set(Config_Grid.Title, fieldTitleDic.ContainsKey(cell.ColumnName) ? fieldTitleDic[cell.ColumnName] : cell.ColumnName); row.Set(Config_Grid.Hidden, (i == 0 && jointPrimaryCount < 2) || i > 25);//超过25个字段,后面的都先隐藏。 row.Set(Config_Grid.OrderNum, (i + 1) * 10); row.Set(Config_Grid.Width, 100); row.Set(Config_Grid.Sortable, i > 0); row.Set(Config_Grid.Import, i > 0); row.Set(Config_Grid.Export, i > 0); row.Set(Config_Grid.Colspan, 1); row.Set(Config_Grid.Rowspan, 1); row.Set(Config_Grid.Edit, i > 0 || jointPrimaryCount > 1); row.Set(Config_Grid.Frozen, i < 4); row.Set(Config_Grid.Align, "center"); string value = DataType.GetType(cell.SqlType).Name.ToLower() + "," + cell.MaxSize + "," + cell.Scale + (cell.IsCanNull ? ",0" : ",1") + (cell.IsPrimaryKey ? ",1" : ",0"); row.Set(Config_Grid.DataType, value); if (i == 0) { if (jointPrimaryCount < 2) { row.Set(Config_Grid.Formatter, "#"); } } else { switch (DataType.GetGroup(cell.SqlType)) { case 2: row.Set(Config_Grid.Formatter, "dateFormatter"); break; case 3: row.Set(Config_Grid.Formatter, "boolFormatter"); break; default: if (cell.MaxSize > 50) { row.Set(Config_Grid.Formatter, "stringFormatter"); } break; } } dt.Rows.Add(row); } }
private static void FillTable(string objName, string objCode, MDataTable dt) { Dictionary <string, string> fieldTitleDic = GridConfig.FieldTitle; MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(objCode)); MCellStruct cell = null; for (int i = 0; i < mdc.Count; i++) { cell = mdc[i]; MDataRow row = dt.NewRow(); row.Set(Config_Grid.ObjName, objName); row.Set(Config_Grid.Field, cell.ColumnName); row.Set(Config_Grid.Title, fieldTitleDic.ContainsKey(cell.ColumnName) ? fieldTitleDic[cell.ColumnName] : cell.ColumnName); row.Set(Config_Grid.Hidden, i == 0); row.Set(Config_Grid.OrderNum, (i + 1) * 10); row.Set(Config_Grid.Width, 100); row.Set(Config_Grid.Sortable, i > 0); row.Set(Config_Grid.Import, i > 0); row.Set(Config_Grid.Export, i > 0); row.Set(Config_Grid.Colspan, 1); row.Set(Config_Grid.Rowspan, 1); row.Set(Config_Grid.Edit, i > 0); row.Set(Config_Grid.Frozen, i < 4); row.Set(Config_Grid.Align, "center"); row.Set(Config_Grid.DataType, DataType.GetType(cell.SqlType).Name.ToLower() + "," + cell.MaxSize + "," + cell.Scale + "," + (cell.IsCanNull ? 0 : 1)); if (i == 0) { row.Set(Config_Grid.Formatter, "#"); } else { switch (DataType.GetGroup(cell.SqlType)) { case 2: row.Set(Config_Grid.Formatter, "dateFormatter"); break; case 3: row.Set(Config_Grid.Formatter, "boolFormatter"); break; default: if (cell.MaxSize > 50) { row.Set(Config_Grid.Formatter, "stringFormatter"); } break; } } dt.Rows.Add(row); } }
/// <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); }
/// <summary> /// 读取上传文件中的Excel(返回表格,如果需要导入,还需要格式化表头) /// </summary> /// <param name="workbook">Excel工作本</param> /// <param name="sheetIndex">第N个Sheet表格</param> /// <param name="isMergedCellName">是否把合并列头的名称连接起来</param> /// <param name="startIndex">开始索引</param> /// <param name="headCrossRowNum">头部跨行数(为0时自动识别)</param> /// <returns></returns> private static MDataTable ReadExcel(IWorkbook workbook, Stream stream = null, int sheetIndex = 0, int startIndex = 0, int headCrossRowNum = 0, bool isMergedCellName = false) { MDataTable dt = new MDataTable(); try { if (workbook != null) { ISheet sheet = workbook.GetSheetAt(sheetIndex); dt.TableName = sheet.SheetName; dt.DynamicData = sheet; IRow excelRow = sheet.GetRow(startIndex); int dataRowStart = startIndex; if (headCrossRowNum <= 0) { dataRowStart += 1; #region 遍历、找出(头部跨行数)最大行。用最大行进行遍历列(如果为空,往上一级找) int mIndex = 0; for (int i = 0; i < excelRow.Cells.Count; i++) { ICell cell = excelRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);// .Cells[i]; if (cell.IsMergedCell) { NPOI.SS.Util.CellRangeAddress range = sheet.GetCellRange(cell);//获取范围块。 if (range != null) { dataRowStart = Math.Max(dataRowStart, range.LastRow + 1);//设置数据的读取行数。 mIndex++; i += range.LastColumn - range.FirstColumn; } } } #endregion } else { dataRowStart += headCrossRowNum; } //读取列头。 if (dataRowStart > 1) { excelRow = sheet.GetRow(dataRowStart - 1); } dt.RecordsAffected = dataRowStart; dt.Columns.CheckDuplicate = false; #region 读取列头 //if (excelRow.FirstCellNum > 0) //{ // for (int i = 0; i < excelRow.FirstCellNum; i++) // { // string columnName = "该列头为空_" + i; // dt.Columns.Add(columnName); // } //} int emptyCellCount = 0;//兼容处理错误的Excel格式(读了256个空格列) for (int i = 0; i < excelRow.Cells.Count; i++) { string columnName = string.Empty; for (int j = dataRowStart; j > startIndex; j--) { #region MyRegion IRow row = sheet.GetRow(j - 1); ICell cell; try { cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);//不能用GetCell(i),会多出一行导致下面错误位。 } catch (Exception) { continue; } string name = cell.ToString().Trim(); // .StringCellValue.Trim(); if (!string.IsNullOrEmpty(name) && !columnName.Contains(name)) // { columnName += name + "_"; } else if (j != dataRowStart && cell.IsMergedCell) { cell = sheet.GetMergedRegion(cell);//获取范围块。 if (cell != null) { name = cell.ToString().Trim(); if (!string.IsNullOrEmpty(name) && !columnName.Contains(name)) { columnName += name + "_"; } } } if (!isMergedCellName && !string.IsNullOrEmpty(columnName)) { break; } #endregion } columnName = columnName.TrimEnd('_').Trim(); if (string.IsNullOrEmpty(columnName)) { if (emptyCellCount > 30)//连续30次空格列 { break; } emptyCellCount++; columnName = "该列头为空_" + i; } else { emptyCellCount = 0;//只要一个正常,即回归索引 } if (dt.Columns.Contains(columnName)) { columnName += "_" + i; } dt.Columns.Add(columnName); } //移除空格列 if (emptyCellCount > 0) { dt.Columns.RemoveRange(dt.Columns.Count - emptyCellCount, emptyCellCount); } if (dt.Columns.Count > 0) { dt.Conn = dt.Columns.Count.ToString();//找个变量存储实际的列的长度,在SetError中使用。(dt可能在SetError前列被变更) } #endregion ICell sheetCell; int emptyCount = 0; for (int i = dataRowStart; i <= sheet.LastRowNum; i++) { excelRow = sheet.GetRow(i); if (excelRow == null) { break; } MDataRow tbRow = dt.NewRow(); bool isOk = false; for (int j = 0; j < dt.Columns.Count; j++) { #region 读一行 sheetCell = excelRow.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL); if (sheetCell != null) { string value = string.Empty; if (sheetCell.CellType == CellType.Numeric) { try { if (sheetCell.ToString().Split('/', '-').Length > 1) { value = sheetCell.DateCellValue.ToString(); } else { value = sheetCell.NumericCellValue.ToString(); } } catch { value = sheetCell.ToString(); } } else if (sheetCell.CellType == CellType.Formula) { try { /*公式不一定是Numeric的取值,也有可能是=G4 这种,然而单元格G4不是数字; * 公式单元格也有可能读取错误 #VALUE! #REF! 等*/ CellType resultType = sheetCell.CachedFormulaResultType; switch (resultType) { case CellType.Boolean: value = sheetCell.BooleanCellValue.ToString(); break; case CellType.Numeric: value = sheetCell.NumericCellValue.ToString(); break; case CellType.Blank: case CellType.Error: case CellType.Unknown: value = string.Empty; break; default: value = sheetCell.StringCellValue; break; } //value = sheetCell.NumericCellValue.ToString(); //由公式取值 } catch { } } else { value = sheetCell.ToString(); } value = value.Trim(); if (!isOk && !string.IsNullOrEmpty(value)) { isOk = true; } if (!string.IsNullOrEmpty(value))//空值当Null值处理,避免字段有Check对空值的约束 { tbRow.Set(sheetCell.ColumnIndex, value); } } #endregion } if (isOk)//忽略空行数据。 { dt.Rows.Add(tbRow); } else { if (dt.Rows.Count == 0) { dt.RecordsAffected++; } emptyCount++; if (emptyCount > 1)//超过2次空格行,跳出。 { break; } } } workbook.Close();//关闭了,dt.DynamicData带出了Sheet,还是可以后续使用(估计NPOI的没处理) } } catch (Exception err) { Log.WriteLogToTxt(err); } return(dt); }