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);
}