private void SetQueryToExcel(Query q,Worksheet ws)
{
if (q == null)
{
return;
}
DataTable dt = q.Result;
//在当前激活的工作表上存放数据
ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
ListObject ls;
if (!ws.Controls.Contains(queryName))
{
int count = ws.ListObjects.Count;
for (int i = 1; i < count + 1; i++)
{
if (ws.ListObjects[i].Name == queryName)
{
ws.ListObjects[i].Delete();
// ws.Controls.Remove(_tableName);
}
}
ls = ws.Controls.AddListObject(ws.Range["A2"], queryName);
}
else
{
ls = (ListObject)ws.Controls[queryName];
}
ls.SetDataBinding(q.Result);
for (int i = 0; i < q.Fields.Count; i++)
{
ls.ListColumns[i + 1].Name = q.Fields[i].Name;
ws.Cells.set_Item(1, i + 1, q.Fields[i].Description);
}
ws.Columns.AutoFit();
ws.Columns.ShrinkToFit = true;
ws.Name = queryName;
MessageBox.Show("加载完成");
}
private void GetQueryDefinition()
{
try
{
WorkSpace area = checkStandard.Checked == true ? WorkSpace.StandardArea : WorkSpace.GlobalArea;
SAPConnection con = new SAPConnection(systemName);
q = con.CreateQuery(area, userGroup, queryName, variant);
// parameterCollection = new QuerySelectionParameterCollection();
parameterCollection = q.SelectionParameters;
dgvParameters.Rows.Clear();
DataGridViewComboBoxColumn col = (DataGridViewComboBoxColumn)dgvParameters.Columns["CSELNAME"];
col.DataSource = null;
col.DataSource = parameterCollection;
col.DisplayMember = "DescriptionText";
q = null;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
public Query CreateQuery(WorkSpace WorkSpace, string UserGroup, string QueryName, string Variant)
{
Query query = new Query(m_sysName)
{
Name = QueryName,
WorkArea = WorkSpace,
UserGroup = UserGroup,
Variant = Variant
};
query.RefreshFieldsAndSelections();
return query;
}
private void Excute()
{
try
{
SAPConnection con = new SAPConnection(systemName);
q = con.CreateQuery(area, userGroup, queryName, variant);
q.MaxRows = maxRow;
foreach (DataGridViewRow row in dgvParameters.Rows)
{
if (row.Cells["CNAME"].Value != null && row.Cells["CSIGN"].Value != null)
{
Range r = new Range();
r.Sign = row.Cells["CSIGN"].Value.ToString() == "I" ? Sign.Include : Sign.Exclude;
if (row.Cells["CLOW"].Value != null)
{
r.LowValue = row.Cells["CLOW"].Value.ToString();
}
if (row.Cells["CHIGH"].Value != null)
{
r.HighValue = row.Cells["CHIGH"].Value.ToString();
}
if (row.Cells["CNAME"].Value != null)
{
q.SelectionParameters[row.Cells["CNAME"].Value.ToString()].Ranges.Add(r);
}
}
}
q.Excute();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}
private void Excute()
{
try
{
SAPConnection con = new SAPConnection(systemName);
q = con.CreateQuery(area, userGroup, queryName, variant);
q.MaxRows = maxRow;
foreach (DataGridViewRow row in dgvParameters.Rows)
{
if (row.Cells["CNAME"].Value != null && row.Cells["CSIGN"].Value != null)
{
Range r = new Range();
r.Sign = row.Cells["CSIGN"].Value.ToString() == "I" ? Sign.Include : Sign.Exclude;
if (row.Cells["CLOW"].Value != null)
{
r.LowValue = row.Cells["CLOW"].Value.ToString();
}
if (row.Cells["CHIGH"].Value != null)
{
r.HighValue = row.Cells["CHIGH"].Value.ToString();
}
if (row.Cells["CNAME"].Value != null)
{
q.SelectionParameters[row.Cells["CNAME"].Value.ToString()].Ranges.Add(r);
}
}
}
q.Excute();
// DataTable dt = q.Result;
eventQueryExuteDone(q.Result);
//Worksheet ws; //当前工作表。
////在当前激活的工作表上存放数据
//ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
//ListObject ls;
//if (!ws.Controls.Contains(queryName))
//{
// int count = ws.ListObjects.Count;
// for (int i = 1; i < count + 1; i++)
// {
// if (ws.ListObjects[i].Name == queryName)
// {
// ws.ListObjects[i].Delete();
// // ws.Controls.Remove(_tableName);
// }
// }
// ls = ws.Controls.AddListObject(ws.Range["A2"], queryName);
//}
//else
//{
// ls = (ListObject)ws.Controls[queryName];
//}
//ls.SetDataBinding(q.Result);
//for (int i = 0; i < q.Fields.Count; i++)
//{
// ls.ListColumns[i + 1].Name = q.Fields[i].Name;
// ws.Cells.set_Item(1,i+1,q.Fields[i].Description);
//}
//ws.Columns.AutoFit();
//ws.Columns.ShrinkToFit = true;
//ws.Name = queryName;
MessageBox.Show("加载完成");
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}