Excel加载项:单元格绝对位置

本文关键字：位置 单元格 加载项 Excel | 更新日期: 2023-09-27 18:24:05

如何找到某个单元格的像素绝对坐标？

我正在开发一个Office 2010插件（功能区UI），并在功能区的新菜单中添加一个新按钮，当按下该按钮时，我想获得该单元格的屏幕位置。问题是

Globals.ThisWorkbook.Application.ActiveCell . Top / Left

只给出相对于电子表格A1角的位置，而我想要相对于屏幕0,0的位置。

我发现了这个：如何在C#中获得Excel 2003单元格的屏幕X和Y，但这是针对Office 2003的，我不完全理解答案。

我使用C#进行开发，但VB也可以。

谢谢！

Excel加载项:单元格绝对位置

我找到了这篇文章，其中包含下面使用的API调用。我还被提醒，你可以用Application.Commandbars（"ribbon"）.height来获得ribbon的高度。所以，在VBA中，你可以做：

编辑：作为对公式栏和标题高度问题的回应，我添加了一个函数来隐藏它们，获取ActiveWindow.height，然后显示它们，获取新的ActiveWindow.HHeight并计算差异。该函数现在在下面的一行中被调用，该行在转换之前将高度相加。我认为它有效，但我没有做很多测试。

Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Const LOGPIXELSX As Long = 88
Private Const LOGPIXELSY As Long = 90
Sub CellTopLeftPixels(rng As Excel.Range)
Dim RibbonHeight As Long
Dim TotalTop As Long
Dim TotalLeft As Long
RibbonHeight = Application.CommandBars("Ribbon").Height
TotalTop = (RibbonHeight + GetFormulaBarAndHeadingsHeight + rng.Top) * PixelsPerPointY
TotalLeft = rng.Left * PixelsPerPointX
Debug.Print "Top: "; TotalTop; " Left: "; TotalLeft
End Sub
Function GetFormulaBarAndHeadingsHeight()
Dim ActiveWindowHeightWhenHidden As Long
Dim ActiveWindowHeightWhenShown As Long
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
ActiveWindowHeightWhenHidden = ActiveWindow.Height
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
ActiveWindowHeightWhenShown = ActiveWindow.Height
GetFormulaBarAndHeadingsHeight = ActiveWindowHeightWhenHidden - ActiveWindowHeightWhenShown
End Function
Function PixelsPerPointX() As Double
Dim hdc As Long
Dim PixPerInchX As Long
hdc = GetDC(0)
PixPerInchX = GetDeviceCaps(hdc, LOGPIXELSX)
PixelsPerPointX = PixPerInchX / 72
ReleaseDC 0, hdc
End Function
Function PixelsPerPointY() As Double
Dim hdc As Long
Dim PixPerInchY As Long
hdc = GetDC(0)
PixPerInchY = GetDeviceCaps(hdc, LOGPIXELSY)
PixelsPerPointY = PixPerInchY / 72
ReleaseDC 0, hdc
End Function

上面的72是每英寸的点数。

可以这样称呼：

Sub test()
CellTopLeftPixels ActiveCell
End Sub

我发现这可以在没有任何技巧的情况下工作：

    Point GetScreenPositionFromCell(Excel.Range cell, Excel.Application excel)
    {
        var wnd = excel.ActiveWindow;
        if (wnd != null)
        {
            var result = new Point
            {
                X = wnd.PointsToScreenPixelsX((int)cell.Left),
                Y = wnd.PointsToScreenPixelsY((int)cell.Top)
            };
            //cleanup
            Marshal.ReleaseComObject(wnd);
            wnd = null;
            return result;
        }
        throw new Exception("Error retrieving active Excel-window.");
    }
相关文章：