Excel加载项：单元格的绝对位置 [英] Excel addin: Cell absolute position

问题描述

我如何找到某个单元格的绝对坐标（以像素为单位）？

How can I find the absolute coordinates in pixels of a certain cell?

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

I am developing an Office 2010 addon (the Ribbon UI) and I add a new button to a new menu in the Ribbon and when the button is pressed, I want to get the screen position of that cell. The problem is that

Globals.ThisWorkbook.Application.ActiveCell . Top / Left

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

only give the position relative to the A1 corner of the spreadsheet, while I want the position relative to 0,0 of the screen.

我发现这个：如何获取Excel 2003的屏幕X和Y C＃中的单元格，但它是Office 2003，而我并不完全理解答案。

I found this: How to get screen X and Y of an Excel 2003 cell in C# but it's for Office 2003 and I don't fully understand the answer.

我正在使用C＃作为devel，但是VB也会

I'm using C# for devel, but VB will also do.

谢谢！

推荐答案

我发现这篇文章，其中包含下面使用的API调用。我也提醒你可以通过Application.Commandbars（Ribbon）获得色带的高度。所以，在VBA中你可以做：

I found this post, which contains the API calls used below. I also was reminded that you can get the height of the ribbon with Application.Commandbars("Ribbon").Height. So, in VBA you'd do:

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

In response the Formula Bar and Headings height issue I added a function that hides them, gets the ActiveWindow.Height, then shows them and gets the new ActiveWindow.Height and figures the difference. That function now gets called in the line below that adds together the heights before converting. I think it works but I didn't do a lot of testing.

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以上是每英寸的点数。

The 72 above is the points per inch.

调用如下：

Sub test()
CellTopLeftPixels ActiveCell
End Sub

这篇关于Excel加载项：单元格的绝对位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！