protected override string doOwnAction()
{
List <String> statements = getRealStatement();
if (statements[0] == "")
{
try
{
dRange.doInsert();
return("OK");
}
catch (Exception)
{
return("FAILED");
}
}
else
{
try
{
dRange.ExecuteSql(statements);
}
catch (Exception e)
{
AlertUtil.Show("error", e.ToString());
}
}
dRange.Refresh();
return("OK");
}
//初始化接口
public virtual void init(ActionCfg cfg, XApp app)
{
this.cfg = cfg;
this.ActionName = cfg.ActionName;
try
{
this.actionSeq = int.Parse(cfg.ActSeq);
}
catch
{
AlertUtil.Show("error", String.Format("Action {0} Seq设置异常，设置值为{1}", ActionName, cfg.ActSeq));
}
try
{
if (cfg.SRange.Length > 0)
{
sRange = app.getRangeByName(cfg.SRange);
}
if (cfg.DRange.Length > 0)
{
dRange = app.getRangeByName(cfg.DRange);
}
}
catch (Exception)
{
MessageBox.Show("Action：" + ActionName + "的sRange、dRange配置错误！");
return;
}
}
public override void fill(DataTable dt)
{
selectedRows = new Dictionary <int, int>();
drawRows = new Dictionary <int, int>();
Range range = getRange();
if (dt == null)
{
AlertUtil.Show("error", "查询结果为空，请确认查询语句");
return;
}
Cell data1stcell = range[0, 0];
string[,] arrtmp = new string[range.RowCount, dt.Columns.Count];
range.Worksheet.Import(arrtmp, data1stcell.RowIndex, data1stcell.ColumnIndex);
range.FillColor = Color.White;
range.Worksheet.Import(dt, false, data1stcell.RowIndex, data1stcell.ColumnIndex);
this.data.setData(dt);
range.Borders.SetAllBorders(Color.Black, BorderLineStyle.None);
/*for (int i = 0; i < range.RowCount; i++)
* {
* setRowBorderNone(i);
* }*/
this.isFilled = true;
doResize(dt.Rows.Count + 1);
getRange().FillColor = Color.White;
}
//查询功能，通过查询SQL可判定该对象具有的功能权限
public String search(String Sql)
{
avaliableList.Clear();//清空功能列表
try
{
conn = DBUtil.getConnection(ServerName);
conn.Open();
DBTrans = conn.BeginTransaction();
conn.Close();
da = DBUtil.getDbDataAdapter(ServerName, Sql, "", conn);//根据查询语句，初始化适配器
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
avaliableList.Add("R");//根据适配器设定可用功能列表
if (da.UpdateCommand != null)
{
avaliableList.Add("U");
}
if (da.InsertCommand != null)
{
avaliableList.Add("C");
}
if (da.DeleteCommand != null)
{
avaliableList.Add("D");
}
}
catch (Exception e)
{
AlertUtil.Show("err", e.ToString());
}
return("OK");
}
//更新数据功能
public String update()
{
String ans = "OK";
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
DBTrans = conn.BeginTransaction();
da.UpdateCommand.Transaction = DBTrans;
try
{
da.Update(dt.GetChanges());
dt.AcceptChanges();
DBTrans.Commit();
ans = "OK";
}
catch (Exception e)
{
DBTrans.Rollback();
Console.WriteLine(e.ToString());
AlertUtil.Show("DataUpdateError", e.ToString());
ans = "FAILED";
}
finally
{
conn.Close();
}
return(ans);
}
//如果执行存储过程（执行存储过程不修改本地数据对象，可用来执行任意SQL语句）
public String Execute(List <String> sqls)
{
String ans = "OK";
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
DBTrans = conn.BeginTransaction();
DbCommand cmd = DBUtil.getCommand(sqls[0], ServerName, conn);
cmd.Transaction = DBTrans;
try
{
foreach (String sql in sqls)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
DBTrans.Commit();
}
catch (Exception e)
{
AlertUtil.Show("error", e.ToString());
ans = "FAILED";
DBTrans.Rollback();
}
finally
{
conn.Close();
}
return(ans);
}
//读取Data配置
public static List <DataCfg> readData(Table dataCfgTable)
{
List <DataCfg> datas = new List <DataCfg>();
for (int i = 1; i < dataCfgTable.Range.RowCount; i++)
{
if (dataCfgTable.Range[i, 0].DisplayText.Length == 0)
{
break;
}
DataCfg data = new DataCfg();
//DataName ObjectName ObjectType DBName ServerName BaseSQLStatement RangeName CRUDP SVK InitStatement
data.TableTitle = dataCfgTable.Range[i, 0].DisplayText;
data.TableDesc = dataCfgTable.Range[i, 1].DisplayText;
data.CRUDP = dataCfgTable.Range[i, 2].DisplayText;
data.SVK = dataCfgTable.Range[i, 3].DisplayText;
data.RangeName = dataCfgTable.Range[i, 4].DisplayText;
data.DBName = dataCfgTable.Range[i, 5].DisplayText;
data.ServerName = dataCfgTable.Range[i, 6].DisplayText;
data.DefalutStatement = dataCfgTable.Range[i, 7].GetReferenceA1();
if ((data.ServerName == null || data.ServerName.Length < 2) && data.RangeName.Split('_')[0] == "TB")
{
Console.WriteLine("区域:" + data.TableTitle + "服务器类型配置错误，当前配置为：" + data.ServerName);
return(null);
}
if (data.RangeName.Length == 0)
{
AlertUtil.Show("error", String.Format("Data:{0} 未配置Range,此Data将不被系统使用！", data.TableTitle));
continue;
}
datas.Add(data);
}
return(datas);
}
//初始化RANGE
private void initRange()
{
foreach (DataCfg cfg in cfgdata.datas) //遍历RANGE配置
{
XRange range = XRangeFactory.getXRange(cfg); //工厂模式，根据配置生成实际FORM/RANGE/TABLE 对象
try
{
range.init(cfg, book); //range 初始化
ranges.Add(range.Name, range); //将 range 放入集合，后续可根据range名获取range
if (range.getRange() == null) //如果对应的工作表中range区域 不存在，则报错，并将系统状态设置为Designer状态
{
AlertUtil.Show("error", "Range对应命名区域不存在或配置异常，RangeId：" + cfg.RangeName + " 即将进入Designer模式");
statu = SysStatu.Designer;
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
MessageBox.Show("Range对应命名区域不存在或配置异异常，RangeId：" + cfg.RangeName + "\n" + e.ToString());
this.statu = SysStatu.RangeError;
return;
}
}
if (ranges.Count == 0)
{
this.statu = SysStatu.Designer;
AlertUtil.Show("info", "未查询到有效DATA，进入设计模式！");
}
}
//状态变化
private void ChangeToStatu(SysStatu newstatu)
{
//labels["lbl_User"].Text = app.statu.ToString();
if (currentXRange != null && newstatu != app.statu)
{
currentXRange.ResetSelected();
}
app.statu = newstatu;
AlertUtil.Show("状态变更", "状态变更为" + newstatu);
ChangeButtonsStatu();
}
public override void doResize(int rowcount)
{
if (rowcount == 1)
{
rowcount = 2;
}
if (!isFilled)
{
AlertUtil.Show("错误！", "Table" + Name + "需先查询才能Update");
return;
}
Range range = this.table.Range;
Range newrange = RangeUtil.rangeResize(range, rowcount);
changeDefinedRange(newrange);
}
public String execute(XSheetUser user)
{
StartNotify();
String ans = "OK";
foreach (char item in cfg.CRUDP)
{
/*if (!user.getPrivilege(rsheet).Contains(item))
* {
* ans = "No privilege!";
* return ans;
* }*/
}
if (actions.Count <= 0)
{
AlertUtil.Show("error", String.Format("Command :{0} 中未找到对应的Action，Command无法执行", this.CommandName));
}
else
{
actions = actions.OrderBy(o => o.Key).ToDictionary(o => o.Key, p => p.Value);
int i = 0;
while (i <= actions.Keys.Max())
{
if (actions.Keys.Contains(i))
{
actions[i].dRange.getRange().Worksheet.Calculate();
ans = actions[i].doAction();
i = actions[i].getNextIndex(ans, i);
}
else
{
i++;
}
}
}
/*foreach (KeyValuePair<int, XAction> kv in actions)
* {
* kv.Value.dRange.getRange().Worksheet.Calculate();
* }*/
FinishNotify();
return(ans);
}
//读取配置文件中的配置表格
private Table getCfgTable(String tableName)
{
Table table = null;
try
{
table = cfgtables[tableName];
if (table.Range == null)
{
AlertUtil.Show("error", "名称为" + tableName + "的EXCEL Table区域配置异常，请确认配置");
return(null);
}
}
catch (Exception)
{
AlertUtil.Show("warning", "未找到名称为" + tableName + "的EXCEL Table区域，请确认配置");
}
return(table);
}
//根据名称获取SHEET
public XRSheet getRSheetByName(String name)
{
XRSheet rsheet = null;
try
{
rsheet = rsheets[name];
}
catch (Exception)
{
rsheet = new XRSheet(book.Worksheets.ActiveWorksheet);
AlertUtil.Show("error", "Sheet：" + name + "未注册!请检查配置！");
statu = SysStatu.Designer;
if (rsheet.sheetName != name)
{
return(null);
}
rsheets.Add(rsheet.sheet.Name, rsheet);
}
return(rsheet);
}
private Range getRowRangeByIndex(int rowNum)
{
int rowindex = -1;
Range rangetmp = getRange();
for (int i = 0; i < rangetmp.RowCount; i++)
{
int tmp;
try
{
if (rangetmp[i, 0].Tag != null && int.TryParse(rangetmp[i, 0].Tag.ToString(), out tmp))
{
if (tmp == rowNum)
{
rowindex = i;
break;
}
}
}
catch (Exception e)
{
AlertUtil.Show("error", e.ToString());
}
}
if (rowindex >= 0)
{
int factindex = getRange().TopRowIndex + rowindex;
int lcolindex = getRange().LeftColumnIndex;
int rcolindex = getRange().RightColumnIndex;
Range range = getRange().Worksheet.Range.FromLTRB(lcolindex, factindex, rcolindex, factindex);
return(range);
}
else
{
return(null);
}
}
public override void fill(DataTable dt)
{
Range range = getRange();
Cell data1stcell = get1stDataCell(range);
string[,] arrtmp = new string[range.RowCount, range.ColumnCount];
try
{
range.Worksheet.Import(arrtmp, data1stcell.RowIndex, data1stcell.ColumnIndex);
range.Worksheet.Import(dt, false, data1stcell.RowIndex, data1stcell.ColumnIndex);
data.setData(dt);
//range.Borders.SetAllBorders(Color.Black, BorderLineStyle.None);
/*for (int i = 0; i < range.RowCount; i++)
* {
* setRowBorderNone(i);
* }*/
doResize(dt.Rows.Count);
}
catch (Exception e)
{
AlertUtil.Show("error", e.ToString());
}
}
public override void onUpdateSelect()
{
AlertUtil.Show("XRangeRG Warning!", "Range区域不允许onUpdateSelect");
return;
}
public override String ExecuteSql(List <String> Sqls)
{
AlertUtil.Show("XRangeRG Warning!", "Range区域不允许单独执行SQL");
return("OK");
}
public override String doInsert()
{
DataTable dt = data.getDataTable();
int dcount = getDataTable().Rows.Count;
int maxcount = getRange().RowCount;
Boolean Okflag = true;
for (int i = dcount; i < maxcount; i++)
{
DataRow templet = dt.Rows[0];
DataRow row = dt.NewRow();
if (getRowRange(i)[0].Value.ToString().Length < 1)
{
break;
}
for (int j = 0; j < dt.Columns.Count; j++)
{
string strRange = getRowRange(i)[j].Value.ToString();
Type t = templet[j].GetType();
if (strRange.Length == 0)
{
continue;
}
if (t.Name == "Decimal")
{
try
{
Decimal num = Convert.ToDecimal(strRange);
row[j] = (object)num;
}
catch (Exception)
{
AlertUtil.Show("error", String.Format("输入参数第{0}列与表数据类型不匹配", (j + 1).ToString()));
Okflag = false;
}
}
else if (t.Name == "Int32")
{
try
{
Int32 num = Convert.ToInt32(strRange);
row[j] = (object)num;
}
catch (Exception)
{
AlertUtil.Show("error", String.Format("输入参数第{0}列与表数据类型不匹配,需要类型为{1}", (j + 1).ToString(), t.Name));
Okflag = false;
}
}
else
{
try
{
row[j] = strRange;
}
catch (Exception)
{
AlertUtil.Show("error", String.Format("输入参数第{0}列与表数据类型不匹配或参数类型未定义,需要类型为{1}", (j + 1).ToString(), t.Name));
Okflag = false;
}
}
}
dt.Rows.Add(row);
}
data.setData(dt);
return(Okflag?data.insert():"Failed");
}
public virtual List <String> getRealStatement(String sheetName, String statement)
{
//return getRange().Worksheet.Workbook.Worksheets["Config"].Range[cfg.InitStatement][0].DisplayText;
String tableName = null;
List <List <String> > lists = new List <List <string> >();
List <String> result = new List <string>();
if (statement.Length > 0)
{
rsheet.app.getSheetByName(sheetName).Calculate();
statement = rsheet.app.getSheetByName(sheetName)[statement][0].DisplayText;
Regex reg = new Regex("@#(.+?)#@");
MatchCollection matches = reg.Matches(statement);
if (matches.Count > 0)
{
foreach (Match match in matches)
{
String[] strParams = match.Groups[1].Value.Split('$');
if (tableName == null)
{
tableName = strParams[0];
}
else if (tableName != strParams[0])
{
AlertUtil.Show("禁止参数来源于多张表", "监测到当前命令参数存在" + tableName + "," + strParams + "，请检查Action配置");
return(null);
}
int col = -1;
try
{
col = int.Parse(strParams[1]);
}
catch (Exception e)
{
AlertUtil.Show("error", e.ToString());
return(null);
}
List <String> values = null;
if (strParams.Count() == 3 && strParams[2].ToUpper() == "DATA")
{
values = getValueByTableCol(tableName, col, "DATA");
}
else
{
values = getValueByTableCol(tableName, col, "TABLE");
}
if (values == null || values.Count == 0)
{
values = new List <string>();
values.Add("NULL");
}
lists.Add(values);
}
for (int i = 0; i < lists[0].Count; i++)
{
String tmp = statement;
for (int j = 0; j < matches.Count; j++)
{
lists[j][i] = lists[j][i].Replace("'", "''");//SQL语句中存在 ' 时替换成 '' 2016/12/08
tmp = tmp.Replace(matches[j].Value, lists[j][i]);
}
result.Add(tmp);
}
}
else
{
result.Add(statement);
}
}
else
{
result.Add("");
}
return(result);
}
