public void GetQueryBuilderFromFomular(string formular) { if (formular.Contains("TVC_QUERY") && formular.Contains("USER TABLE")) { string tmp = formular.Replace("USER TABLE(", ""); formular = tmp.Substring(0, tmp.Length - 1); } if (formular.Contains("TT_XLB_EB") || formular.Contains("USER TABLE") || formular.Contains("TVC_QUERY")) { Excel._Worksheet sheet = null; string vParamsString = Regex.Match(formular, @"\" + /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ System.Convert.ToChar(34) + @"\,.+?\)").Value.ToString(); Excel._Workbook wbook = null; if (_xlsApp != null) { sheet = (Excel._Worksheet)_xlsApp.ActiveWorkbook.ActiveSheet; wbook = (Excel._Workbook)_xlsApp.ActiveWorkbook; } // fill to parameter Array int i = 0, n = 0; if (!(string.IsNullOrEmpty(vParamsString))) { vParamsString = /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vParamsString.Substring(1); vParamsString = vParamsString.Substring(1, vParamsString.Length - 2); // Strings.Mid(vParamsString, 1, /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vParamsString.Length - 1); vParamsString = vParamsString + ","; // them dau , cho de xu ly n = Regex.Matches(vParamsString, ".*?,").Count; // cac tham so if (n > 0) { string[] vParameter = new string[n]; // tham so dau tien la vi tri cua cong thuc foreach (System.Text.RegularExpressions.Match p in Regex.Matches(vParamsString, ".*?,")) { i = i + 1; if (i == 1) { _sqlBuilder.Pos = p.Value.ToString().Replace(",", string.Empty); } else { string address = p.Value.ToString().Replace(",", string.Empty); string value = ""; if (wbook != null) { foreach (Excel._Worksheet isheet in wbook.Sheets) { try { value = isheet.get_Range(address, Type.Missing).Value.ToString(); _sqlBuilder.ParaValueList[i - 1] = value; break; } catch { } } } //vParameter[i - 1] = p._Value.ToString().Replace(",", string.Empty); } } } if (!formular.Contains("TVC_QUERY")) { Parsing.Formular2SQLBuilder(formular, ref _sqlBuilder); } else { Parsing.TVCFormular2SQLBuilder(formular, ref _sqlBuilder); } } //SetDataToForm(); } }
public void ShowFields() { Excel._Worksheet sheet = (Excel._Worksheet)ExcelApp.ActiveSheet; _xlsCell = (Excel.Range)ExcelApp.ActiveCell; string _address = _xlsCell.get_AddressLocal(1, 1, Excel.XlReferenceStyle.xlA1, 0, 0).ToString(); _address = _address.Replace("$", ""); string formular = _xlsCell.Formula.ToString(); if (formular.Contains("TVC_QUERY") && formular.Contains("USER TABLE")) { string tablename = "data"; try { Excel.Range rangeTableName = ExcelApp.get_Range("A" + _xlsCell.Row); tablename = rangeTableName.Value.ToString(); } catch { } string tmp = formular.Replace("USER TABLE(", ""); formular = tmp.Substring(0, tmp.Length - 1); SQLBuilder _sqlBuilder = new SQLBuilder(processingMode.Details); if (!formular.Contains("TVC_QUERY")) { Parsing.Formular2SQLBuilder(formular, ref _sqlBuilder); } else { Parsing.TVCFormular2SQLBuilder(formular, ref _sqlBuilder); } DataTable dt_list = new DataTable(); if (_sqlBuilder.SelectedNodes.Count > 0) { //CommoControl commo = new CommoControl(); //string connnectString = commo.CreateConnectString(Properties.Settings.Default.Server // , Properties.Settings.Default.User // , Properties.Settings.Default.Pass // , Properties.Settings.Default.DBName); //a.THEME = this.THEME; dt_list.TableName = tablename; dt_list.Columns.Add("Name"); dt_list.Columns.Add("Code"); for (int i = 0; i < _sqlBuilder.SelectedNodes.Count; i++) { Node colum = _sqlBuilder.SelectedNodes[i]; string desc = colum.Description; int dem = 0; for (int j = i - 1; j >= 0; j--) { Node node = _sqlBuilder.SelectedNodes[j]; if (node.Description == colum.Description) { dem++; } } if (dem > 0) { desc = colum.Description + dem; } dt_list.Rows.Add(new string[] { colum.Description, desc }); } TVCDesigner.MainForm frm = new TVCDesigner.MainForm(dt_list, null, null); frm.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode)); } } }