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