Getting Top and Left properties from Range object in C# 2005

[Wit]

Hello,
I am writing a windows application using visual studio 2005 and embeded OWC
spreadsheet.
I am trying to display a control on top of a selected cell of
AxOWC11.AxSpreadsheet object.
My problem is the Range.Top, Rage.Left, Range.Width, and Range.Height
properties return values that are not quite right. I have to multiply these
values with 1.335 (made up value by try and error.)
However, the top and left returns the distant from the edge of the sheet;
these distants do not include the width and height of headings and toolbox.
I cannot get the combo box or any of my controls to sit perfectly on top of
the selected cell.
Here is segment of my code.

private void axSpreadsheet1_ClickEvent(object sender, EventArgs e)
{
this.label1.Text = ""; // ignor
this.comboBox1.Visible = false; // the combobox that should
display on top of selected cell
try
{
double rowHeaderWidth = 24;
double colHeaderHeight = 17;
double toolbarHeight = 22;
double magicNumber = 1.335;

int visibleCol =
this.axSpreadsheet1.ActiveWindow.VisibleRange.Column;
int visibleRow =
this.axSpreadsheet1.ActiveWindow.VisibleRange.Row;
OWC11.Range r = this.axSpreadsheet1.Selection;
this.label1.Text = s;
double left = variantToDouble(r.Left);
double top = variantToDouble(r.Top);
double width = variantToDouble(r.Width);
double height = variantToDouble(r.Height);

if (this.axSpreadsheet1.DisplayColumnHeadings == false)
colHeaderHeight = 0;

if (this.axSpreadsheet1.DisplayRowHeadings == false)
rowHeaderWidth = 0;

if (this.axSpreadsheet1.DisplayToolbar == false)
toolbarHeight = 0;

OWC11.Range reference =
(OWC11.Range)this.axSpreadsheet1.Cells[visibleRow, visibleCol]; // top left
of visibleRange
this.comboBox1.Left = (int)(((left -
variantToDouble(reference.Left)) * magicNumber) + rowHeaderWidth);
this.comboBox1.Top =
(int)((Math.Abs((variantToDouble(reference.Top) - top)) * magicNumber) +
colHeaderHeight + toolbarHeight);
this.comboBox1.Width = (int)(width * magicNumber);
this.comboBox1.Height = (int)(height * magicNumber);
int right = (int)(left + width);
int bottom = (int)(top + height);

}
catch (Exception ex)
{
this.label1.Text += " " + ex.ToString();
}
this.comboBox1.Visible = true;
}

private double variantToDouble(object o)
{
double d = 0;

double.TryParse(o.ToString(), out d);
return d;
}

Thank you,
Wit

 

[Alvin Bruney - ASP.NET MVP]

There's no *scientific way that I know of to get it to sit exactly. The
object will tend to float depending on screen resolution, text size etc.

--
Warm Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley 2006
Blog: http://msmvps.com/blogs/Alvin/
-------------------------------------------------------

Hello,
I am writing a windows application using visual studio 2005 and embeded OWC
spreadsheet.
I am trying to display a control on top of a selected cell of
AxOWC11.AxSpreadsheet object.
My problem is the Range.Top, Rage.Left, Range.Width, and Range.Height
properties return values that are not quite right. I have to multiply these
values with 1.335 (made up value by try and error.)
However, the top and left returns the distant from the edge of the sheet;
these distants do not include the width and height of headings and toolbox.
I cannot get the combo box or any of my controls to sit perfectly on top of
the selected cell.
Here is segment of my code.

private void axSpreadsheet1_ClickEvent(object sender, EventArgs e)
{
this.label1.Text = ""; // ignor
this.comboBox1.Visible = false; // the combobox that should
display on top of selected cell
try
{
double rowHeaderWidth = 24;
double colHeaderHeight = 17;
double toolbarHeight = 22;
double magicNumber = 1.335;

int visibleCol =
this.axSpreadsheet1.ActiveWindow.VisibleRange.Column;
int visibleRow =
this.axSpreadsheet1.ActiveWindow.VisibleRange.Row;
OWC11.Range r = this.axSpreadsheet1.Selection;
this.label1.Text = s;
double left = variantToDouble(r.Left);
double top = variantToDouble(r.Top);
double width = variantToDouble(r.Width);
double height = variantToDouble(r.Height);

if (this.axSpreadsheet1.DisplayColumnHeadings == false)
colHeaderHeight = 0;

if (this.axSpreadsheet1.DisplayRowHeadings == false)
rowHeaderWidth = 0;

if (this.axSpreadsheet1.DisplayToolbar == false)
toolbarHeight = 0;

OWC11.Range reference =
(OWC11.Range)this.axSpreadsheet1.Cells[visibleRow, visibleCol]; // top left
of visibleRange
this.comboBox1.Left = (int)(((left -
variantToDouble(reference.Left)) * magicNumber) + rowHeaderWidth);
this.comboBox1.Top =
(int)((Math.Abs((variantToDouble(reference.Top) - top)) * magicNumber) +
colHeaderHeight + toolbarHeight);
this.comboBox1.Width = (int)(width * magicNumber);
this.comboBox1.Height = (int)(height * magicNumber);
int right = (int)(left + width);
int bottom = (int)(top + height);

}
catch (Exception ex)
{
this.label1.Text += " " + ex.ToString();
}
this.comboBox1.Visible = true;
}

private double variantToDouble(object o)
{
double d = 0;

double.TryParse(o.ToString(), out d);
return d;
}

Thank you,
Wit

 

[Corey Alix]

Your magic number might be a little off. Try 1.3333

This code works for me. With it I am able to position forms containing a
ChartSpace control on my spreadsheet, which gives the appearence of
integrated charting, like you would see in Excel. It works with the toolbar
on or off. I believe the trick might be to begin with the first visible
cell and determine your offset from there.

Note that this code does not work if you have increased your font size (the
ratio might become 5/4 instead of 4/3, for example). I haven't worked out
how to use GetTextMetrics to determine the proper scaling factor.

Public Shared ReadOnly OWC_HACK_FACTOR As Double = 4 / 3 ' TODO:
need to get GetTextMetrics working
' returns the screen coordinates of a cell
Public Shared Function CellPosition(ByVal piCell As OWC10.Range) As
System.Drawing.Point
Dim lvActiveWindow As OWC10.Window
lvActiveWindow = piCell.Application.ActiveWindow
' necessary to handle scrolling
Dim lvTopLeft As OWC10.Range =
lvActiveWindow.VisibleRange.Columns.Item(1).Rows.Item(1)
Dim Hack As Double = OWC_HACK_FACTOR
Dim X As Int32 = CInt((CDbl(piCell.Left) - CDbl(lvTopLeft.Left))
* Hack)
Dim Y As Int32 = CInt((CDbl(piCell.Top) - CDbl(lvTopLeft.Top)) *
Hack)
Dim lvPoint As System.Drawing.Point = New
System.Drawing.Point(lvActiveWindow.PointsToScreenPixelsX(X),
lvActiveWindow.PointsToScreenPixelsY(Y))
Return lvPoint
End Function

Hello,
I am writing a windows application using visual studio 2005 and embeded
OWC
spreadsheet.
I am trying to display a control on top of a selected cell of
AxOWC11.AxSpreadsheet object.
My problem is the Range.Top, Rage.Left, Range.Width, and Range.Height
properties return values that are not quite right. I have to multiply
these
values with 1.335 (made up value by try and error.)
However, the top and left returns the distant from the edge of the sheet;
these distants do not include the width and height of headings and
toolbox.
I cannot get the combo box or any of my controls to sit perfectly on top
of
the selected cell.
Here is segment of my code.

private void axSpreadsheet1_ClickEvent(object sender, EventArgs e)
{
this.label1.Text = ""; // ignor
this.comboBox1.Visible = false; // the combobox that should
display on top of selected cell
try
{
double rowHeaderWidth = 24;
double colHeaderHeight = 17;
double toolbarHeight = 22;
double magicNumber = 1.335;

int visibleCol =
this.axSpreadsheet1.ActiveWindow.VisibleRange.Column;
int visibleRow =
this.axSpreadsheet1.ActiveWindow.VisibleRange.Row;
OWC11.Range r = this.axSpreadsheet1.Selection;
this.label1.Text = s;
double left = variantToDouble(r.Left);
double top = variantToDouble(r.Top);
double width = variantToDouble(r.Width);
double height = variantToDouble(r.Height);

if (this.axSpreadsheet1.DisplayColumnHeadings == false)
colHeaderHeight = 0;

if (this.axSpreadsheet1.DisplayRowHeadings == false)
rowHeaderWidth = 0;

if (this.axSpreadsheet1.DisplayToolbar == false)
toolbarHeight = 0;

OWC11.Range reference =
(OWC11.Range)this.axSpreadsheet1.Cells[visibleRow, visibleCol]; // top
left
of visibleRange
this.comboBox1.Left = (int)(((left -
variantToDouble(reference.Left)) * magicNumber) + rowHeaderWidth);
this.comboBox1.Top =
(int)((Math.Abs((variantToDouble(reference.Top) - top)) * magicNumber) +
colHeaderHeight + toolbarHeight);
this.comboBox1.Width = (int)(width * magicNumber);
this.comboBox1.Height = (int)(height * magicNumber);
int right = (int)(left + width);
int bottom = (int)(top + height);

}
catch (Exception ex)
{
this.label1.Text += " " + ex.ToString();
}
this.comboBox1.Visible = true;
}

private double variantToDouble(object o)
{
double d = 0;

double.TryParse(o.ToString(), out d);
return d;
}

Thank you,
Wit