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