用C#开发EXCEL,因用Application.WorkBook.Open方式打开后的文档,不是宿主类,无法动态添加下拉框等信息,不得已,
借用了 ToolBar中的DropDown对象,目前效果已经生成,可以通过下拉框(从数据库中提取信息)给Cell赋值,但有个难题一直困扰我,ToolBar悬浮在workbook中,难以定位到当前选中的CELL格子里面,无法提取到格子对应的屏幕坐标,请各位高手不吝赐教,谢谢。代码在附件中,可以直接运行。
OFFICE 2003,SP3补丁。CSDN不能添加附件,代码如下晕
借用了 ToolBar中的DropDown对象,目前效果已经生成,可以通过下拉框(从数据库中提取信息)给Cell赋值,但有个难题一直困扰我,ToolBar悬浮在workbook中,难以定位到当前选中的CELL格子里面,无法提取到格子对应的屏幕坐标,请各位高手不吝赐教,谢谢。代码在附件中,可以直接运行。
OFFICE 2003,SP3补丁。CSDN不能添加附件,代码如下晕
private Office.CommandBar oCommandBar;
private Office.CommandBarComboBox oDrop;
private Excel.Range oComboBoxRange; //下拉框选中的格子
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
oCurSheet = (Worksheet)this.Application.ActiveSheet;
oCurSheet .SelectionChange+= new DocEvents_SelectionChangeEventHandler(oSheet_SelectionChange); try
{
Application.CommandBars["DropDownListSample"].Delete(); //如果DropDownListSample 存在就先删除
}
catch { } // Create a new command bar.
oCommandBar = this.Application.CommandBars.Add("DropDownListSample", missing, missing, missing); // Add a drop-down list box to the command bar.
oDrop = (Office.CommandBarComboBox)oCommandBar.Controls.Add(Office.MsoControlType.msoControlDropdown, missing, missing, missing, missing);
//将工程信息,放入下拉框中
oDrop.Clear();
oDrop.AddItem("aa", missing);
oDrop.AddItem("bb", missing); // Set the value to the first in the list.
oDrop.ListIndex = 1;
// Set the caption and style.
oDrop.Style = Office.MsoComboStyle.msoComboLabel;
oDrop.Change += new Microsoft.Office.Core._CommandBarComboBoxEvents_ChangeEventHandler(oDrop_Change);
} private void oDrop_Change(Office.CommandBarComboBox Ctrl)
{
this.oComboBoxRange.Value2 = Ctrl.Text;
}
private void oSheet_SelectionChange(Excel.Range oRange)
{
int iRow = oRange.Row;
int iCol = oRange.Column; //如果选中了C4列,则激活下拉框
if (iRow != 4 || iCol != 3)
{
// if (oCommandBar.Visible != false) oCommandBar.Visible = false; //如果不是该显示下拉框的格子,则隐藏下拉框
// return;
} try
{ string sTmp = oRange.get_Offset(iRow, iCol).Top.ToString();
oCommandBar.Top = (int)Convert.ToDecimal(sTmp);
sTmp = oRange.get_Offset(iRow, iCol).Left.ToString();
oCommandBar.Left = (int)Convert.ToDecimal(sTmp);
oCommandBar.Width = (int)Convert.ToDecimal(oRange.get_Offset(iRow, iCol).Width.ToString());
// oCommandBar.Left =(int)( (double)oRange.Left + (double)oRange.Width);
// oCommandBar.Top = (int)( (double)oRange.Top + (double)oRange.Height); double v_fFactor = 1;
oCommandBar.Left = (int)((double)oRange.Left + ((double)oRange.Width - (oCommandBar.Width * v_fFactor)) / 2 + 1);
oCommandBar.Top = (int)((double)oRange.Top + ((double)oRange.Height - (oCommandBar.Height * v_fFactor)) / 2 + 1);
oCommandBar.Width = (int)(oCommandBar.Width * v_fFactor - 0.5d);
oCommandBar.Height = (int)(oCommandBar.Height * v_fFactor - 0.5d); oCommandBar.Visible = true; this.oComboBoxRange = oRange;
}
catch (System.Exception E)
{
MessageBox.Show("异常现象e = " + E.Message.ToString());
} }