public static void BindTable(IDialogVisualizerService windowService, MDataTable dt, string title)
{
if (dt == null)
{
return;
}
if (string.IsNullOrEmpty(title))
{
title = string.Format("TableName : {0} Rows： {1} Columns： {2}", dt.TableName, dt.Rows.Count, dt.Columns.Count);
}
Form form = FormCreate.CreateForm(title);
DataGridView dg = FormCreate.CreateGrid(form);
try
{
if (dt.Rows.Count > 200)
{
dt = dt.Select(200, null);
}
//插入行号
dt.Columns.Insert(0, new MCellStruct("[No.]", System.Data.SqlDbType.Int));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0].Value = i + 1;
}
dt.Bind(dg);
AutoSizeColumn(dg);
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
windowService.ShowDialog(form);
}
/// <summary>
/// 获取发起工单列表
/// </summary>
public void GetStartAppList()
{
string where = "AllowRoleIDs is null or AllowRoleIDs=''";
string likeOr = GetWhereLikeOr("AllowRoleIDs", WF.User.Current.RoleIDs);
if (!string.IsNullOrEmpty(likeOr))
{
where += " or " + likeOr;
}
MDataTable dt = WF.Table.App.Select <MDataRow>(where);
jsonResult = dt.Select("IsEnable=1 order by AppID asc").ToJson(false, false, RowOp.None, true);
}
public MDataTable GetUserList(GridConfig.SelectType st)
{
MDataTable dt = null;
using (MAction action = new MAction(TableNames.Sys_User))
{
dt = action.Select();
}
dt.JoinOnName = Sys_User.UserID.ToString();
MDataTable joinDt = dt.Join(TableNames.Sys_UserInfo, Sys_UserInfo.UserInfoID.ToString());
return(joinDt.Select(PageIndex, PageSize, GetWhere() + GetOrderBy(Sys_User.UserID.ToString()), GridConfig.GetSelectColumns(ObjName, st)));
}
/// <summary>
/// 导出Excel和数据
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static MemoryStream CreateExcel(MDataTable dt)
{
MemoryStream ms = new MemoryStream();
try
{
MDataTable header = dt.DynamicData as MDataTable;
HSSFWorkbook export = new HSSFWorkbook();
ICellStyle style = GetStyle(export, HSSFColor.LightOrange.Index);
ISheet sheet = export.CreateSheet("Sheet1"); //创建内存Excel
IRow row = sheet.CreateRow(0); //index代表第N行
row.Height = 500;
string title = string.Empty;
int columnCount = dt.Columns.Count;
ICell cell;
int ColTitleRowCount = 1;
if (ExportMulHeader(header, false))
{
header = header.Select("Export=1 ORDER BY MergeIndexed DESC");
CreateMulHeadExcel(export, header, out ColTitleRowCount, columnCount);
}
else
{
for (int i = 0; i < columnCount; i++)
{
title = string.IsNullOrEmpty(dt.Columns[i].Description) ? dt.Columns[i].ColumnName : dt.Columns[i].Description;
cell = row.CreateCell(i);
cell.SetCellValue(title);//设置列头
sheet.SetColumnWidth(i, 3000);
cell.CellStyle = style;
}
}
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet.CreateRow(ColTitleRowCount + i);//index代表第N行0,1
for (int j = 0; j < columnCount; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
export.Write(ms);
ms.Flush();
ms.Close();
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
return(ms);
}
protected override MDataTable Select(GridConfig.SelectType st)
{
switch (ObjName)
{
case "V_Test": //处理Demo中文本数据库
MDataTable dt = null;
using (MAction action = new MAction("Demo_TestA"))
{
dt = action.Select();
}
dt.JoinOnName = "id";
MDataTable joinDt = dt.Join("Demo_TestB", "id");
return(joinDt.Select(PageIndex, PageSize, GetWhere() + GetOrderBy("id"), GridConfig.GetSelectColumns(ObjName, st)));
}
return(base.Select(st));
}
protected override MDataTable Select(GridConfig.SelectType st)
{
switch (ObjName)
{
case "V_Test": //处理Demo中文本数据库
if (AppConfig.DB.DefaultDalType == DalType.Txt)
{
MDataTable dt = null;
using (MAction action = new MAction(CrossDb.GetEnum("Demo_TestA")))
{
dt = action.Select();
}
dt.JoinOnName = "ID";
MDataTable joinDt = dt.Join("Demo_TestB", "ID");
return(joinDt.Select(PageIndex, PageSize, GetWhere() + GetOrderBy("ID"), GridConfig.GetSelectColumns(ObjName, st)));
}
break;
}
return(base.Select(st));
}
/// <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);
}