private void button1_Click(object sender, RibbonControlEventArgs e)
{
MySqlConnection myconn = null;
MySqlCommand mycom = null;
MySqlDataReader sdr;
Excel.Worksheet activeWorksheet = (Globals.ThisAddIn.Application.ActiveSheet);
try {
//MySqlDataAdapter myrec = null;
myconn = new MySqlConnection("Host =192.168.56.2;Database=MEPurchase;Username=root;Password=123456;Charset=utf8;");
myconn.Open();
mycom = myconn.CreateCommand();
Excel.Range selectedrange = Globals.ThisAddIn.Application.Selection;
foreach (Excel.Range cel in selectedrange)
{
if (cel.Value2 != null)
{
string keyword = cel.Value2.ToString();
keyword = new Regex("[\\s]+").Replace(keyword, "%");
string sql = string.Format(@"select
ordernumber, name, type, currency, price, provider
from vprice where type like {0} limit 1"
, "'%" + keyword + "%'");
mycom.CommandText = sql;
sdr = mycom.ExecuteReader();
sdr.Read();
cel.ClearComments();
SqlReturn sqlrt = new SqlReturn();
try { sqlrt.ordernumber = sdr.GetString(0); }
catch { };
try { sqlrt.name = sdr.GetString(1); }
catch { };
try { sqlrt.type = sdr.GetString(2); }
catch { };
try { sqlrt.currency = sdr.GetString(3); }
catch { };
try { sqlrt.price = sdr.GetFloat(4); }
catch { };
try { sqlrt.provider = sdr.GetString(5); }
catch { };
sdr.Close();
Excel.Comment c = cel.AddComment(string.Format("订货号：{0}\n名称 ：{1}\n型号 ：{2}\n币种 ：{3}\n单价 ：{4}\n供应商：{5}\n",
sqlrt.ordernumber, sqlrt.name, sqlrt.type, sqlrt.currency, sqlrt.price, sqlrt.provider));
c.Shape.TextFrame.AutoSize = true;;
}
}
}
catch (Exception ee) {
MessageBox.Show(ee.ToString());
}
}
private void FullPriceBtn_Click(object sender, RibbonControlEventArgs e)
{
MySqlConnection myconn = null;
MySqlCommand mycom = null;
MySqlDataReader sdr;
Excel.Worksheet activeWorksheet = (Globals.ThisAddIn.Application.ActiveSheet);
try {
//MySqlDataAdapter myrec = null;
myconn = new MySqlConnection("Host =192.168.56.2;Database=MEPurchase;Username=root;Password=123456;Charset=utf8;");
myconn.Open();
mycom = myconn.CreateCommand();
Excel.Range selectedrange = Globals.ThisAddIn.Application.Selection;
foreach (Excel.Range cel in selectedrange)
{
if (cel.Value2 != null)
{
string keyword = cel.Value2.ToString();
keyword = new Regex("[\\s]+").Replace(keyword, "%");
string sql = string.Format(@"select
ordernumber, name, type, currency, price, provider
from vprice where
type like {0} or
name like {0} or
ordernumber like {0} or
provider like {0} limit 99"
, "'%" + keyword + "%'");
mycom.CommandText = sql;
sdr = mycom.ExecuteReader();
int irow = 0;
SqlReturn[] sqlrt = new SqlReturn[100];
for (int i = 0; i < sqlrt.Length; i++)
{
sqlrt[i] = new SqlReturn();
}
MessageForm fm2 = new MessageForm("Name: " + cel.Value2);
fm2.Show();
DataTable dt = new DataTable();
initialTable(ref dt);
while (sdr.Read())
{
if (sdr.HasRows)
{
try { sqlrt[irow].ordernumber = sdr.GetString(0); }
catch { };
try { sqlrt[irow].name = sdr.GetString(1); }
catch { };
try { sqlrt[irow].type = sdr.GetString(2); }
catch { };
try { sqlrt[irow].currency = sdr.GetString(3); }
catch { };
try { sqlrt[irow].price = sdr.GetFloat(4); }
catch { };
try { sqlrt[irow].provider = sdr.GetString(5); }
catch { };
//res = res+string.Format("{0} {1} {2} {3} {4} {5}\n", sqlrt[irow].ordernumber, sqlrt[irow].name, sqlrt[irow].type, sqlrt[irow].currency, sqlrt[irow].price, sqlrt[irow].provider);
DataRow dr = dt.NewRow();
dr["订货号"] = sqlrt[irow].ordernumber;
dr["名称"] = sqlrt[irow].name;
dr["型号"] = sqlrt[irow].type;
dr["币种"] = sqlrt[irow].currency;
dr["单价"] = sqlrt[irow].price;
dr["供应商"] = sqlrt[irow].provider;
dt.Rows.Add(dr);
irow++;
}
else
{
break;
}
}
fm2.ShowMessage(dt);
sdr.Close();
}
}
}
catch (Exception ee) {
MessageBox.Show(ee.ToString());
}
}
