VSTO EXCEL篇学习笔记五【原】

    

VSTO实现的一个动态生成的业务数据操作界面:

// 初始化菜单和Pane
        private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
            //TextBox tb1 = new TextBox();
            //tb1.Left = 100;
            //tb1.Top = 100;
            ExcelTaskTrackPanel editControl = new ExcelTaskTrackPanel();
            //editControl.Controls.Add(tb1);bo
            this.ActionsPane.Controls.Add(editControl);
            //this.Application.Workbooks["ExcelTaskTrack"].CommandBars.ActiveMenuBar.Controls["T1"].Delete(false);   
            ExcelBulidMainMenu exBulidMainMen = new ExcelBulidMainMenu();
            exBulidMainMen.BulidMainMenu(this.Application.Workbooks["ExcelTaskTrack1"]);

        }

//动态创建业务数据界面 
private UserTable CreateUserTable(int ruleId)
        {
            DataTable dtFields = new DataTable();
            //dtFields = DBHelper.GetTable("SELECT [ESM_SYSUIROLE].*,[ESM_SYSFIELDSROLE].* FROM [ESM_RULEFLD],[ESM_SYSFIELDSROLE],[ESM_SYSUIROLE],[ESM_RULE]" +
            //            "WHERE [ESM_RULEFLD].RU_OBJECT_ID=" + ruleId + " AND [ESM_RULEFLD].RU_OBJECT_ID=[ESM_RULE].RU_OBJECT_ID"+
            //            " AND [ESM_RULEFLD].UR_CLASS_ID=[ESM_SYSUIROLE].UR_CLASS_ID");
            dtFields = DBHelper.GetTable("SELECT  ESM_SYSUIROLE.UR_OBJECT_ID, ESM_SYSUIROLE.UR_CLASS_ID, ESM_SYSUIROLE.FR_CLASS_ID, ESM_SYSUIROLE.FR_OBJECT_ID,"+ 
                      "ESM_SYSUIROLE.SR_OBJECT_ID, ESM_SYSUIROLE.UITYPE, ESM_SYSUIROLE.ALLOWEDIT, ESM_SYSUIROLE.VISIBLE," + 
                      "ESM_SYSUIROLE.HEADRANGEROW, ESM_SYSUIROLE.HEADRANGECOL, ESM_SYSFIELDSROLE.FR_OBJECT_ID AS Expr1," +
                      "ESM_SYSFIELDSROLE.FR_CLASS_ID AS Expr2, ESM_SYSFIELDSROLE.FIELD_NAME, ESM_SYSFIELDSROLE.FIELD_INDEX," +
                      "ESM_SYSFIELDSROLE.DATATYPE, ESM_SYSFIELDSROLE.REALNAME, ESM_SYSFIELDSROLE.TABLENAME," +
                      "ESM_SYSFIELDSROLE.DATABASENAME, ESM_SYSFIELDSROLE.FIELD_LENGTH, ESM_SYSFIELDSROLE.ISNULL, ESM_SYSFIELDSROLE.TABLEID," +
                      "ESM_SYSFIELDSROLE.REGEX, ESM_SYSFIELDSROLE.ISPK, ESM_SYSFIELDSROLE.ISFK, ESM_SYSFIELDSROLE.TOOlMESSAGE," +
                      "ESM_SYSFIELDSROLE.ERRORMESSAGE " +
                      "FROM ESM_RULEFLD INNER JOIN " +
                      " ESM_RULE ON ESM_RULEFLD.RU_OBJECT_ID = ESM_RULE.RU_OBJECT_ID INNER JOIN " +
                      " ESM_SYSUIROLE ON ESM_RULEFLD.UR_CLASS_ID = ESM_SYSUIROLE.UR_CLASS_ID INNER JOIN " +
                      " ESM_SYSFIELDSROLE ON ESM_SYSUIROLE.FR_OBJECT_ID = ESM_SYSFIELDSROLE.FR_OBJECT_ID"+
                      " WHERE(ESM_RULEFLD.RU_OBJECT_ID = " + ruleId + ") ORDER BY  ESM_SYSFIELDSROLE.FIELD_INDEX");


            UserTable table = new UserTable();
            table.TbName = "ESM_APPORDERFLOW";
            List<Column> list = new List<Column>();
            
            //return table;
            foreach(DataRow dr in dtFields.Rows)
            {
                Column col = new Column();
                col.ColId = (int)dr["FR_OBJECT_ID"];
                col.ColName = dr["REALNAME"].ToString ();
                col.ColDisplyName = dr["FIELD_NAME"].ToString ();
                col.ColDBType = dr["DATATYPE"].ToString ();
                col.ErrorMessage = dr["ERRORMESSAGE"].ToString();
                col.IsPK = (bool)dr["ISPK"];
                col.Length = int.Parse(dr["FIELD_LENGTH"].ToString ());
                col.Regex = dr["REGEX"].ToString();
                col.ToolMessage = dr["TOOLMESSAGE"].ToString();
                list.Add(col); 
            }
            table.Columns = list;
            return table;
            //col.ColId = 1;
            //col.ColName = "productID";
            //col.ColDisplyName = "产品代码";
            //col.ColDBType = "varchar";
            //col.ErrorMessage = "产品代码错误，只能是2-10个数字和字母！";
            //col.IsPK = true;
            //col.Length = 10;
            //col.Regex = "^[0-9A-Za-z]{2,10}$";
            //col.ToolMessage = "只能是数字和字母";
            //list.Add(col);

            
        }