Excel 2003-当工作表中存在图表时，图片对象无法计算正确的列和行. [英] Excel 2003 - Picture Object is not calculating the correct Columns and Rows when a Diagram is present in the Sheet.

问题描述

在Excel中，转到插入->图…(现在将打开图图库"窗口)，双击图以在Excel上显示它.
我正在尝试使用以下代码计算图表的位置

In Excel, go to Insert -> Diagram… (Now Diagram Gallery window will open), Double click on a diagram to display it on Excel.
I am trying to calculate the position of the Diagram using the below code

Pictures pictures = ws.Pictures(missing) as Pictures;
if (pictures != null)
{
foreach (object pic in pictures)
{
 Picture picObj = pic as Picture;
if (picObj != null && picObj.Visible)
{
if (firstCol > picObj.TopLeftCell.Column)
firstCol = picObj.TopLeftCell.Column;
if (firstRow > picObj.TopLeftCell.Row)
firstRow = picObj.TopLeftCell.Row;
if (lastCol < picObj.BottomRightCell.Column)
lastCol = picObj.BottomRightCell.Column;
if (lastRow < picObj.BottomRightCell.Row)
lastRow = picObj.BottomRightCell.Row;
 }
}
}

但这为行和列返回错误的值
我正在使用另一个"forloop"来计算Shapes和Diagram对象的位置是否正确计算.但是随着图片对象设置错误的值，我在下面的行中得到了错误.
PrintRange = ws.get_Range(ws.Cells [firstRow，firstCol]，ws.Cells [lastRow，lastCol]);

有人可以建议使用图片对象时如何避免使用图，或者如何正确计算单元格位置.

But this is returning wrong value for Rows and Columns
I am using another "forloop" for calculating Shapes and Diagram objects position is calculated correctly. But as the picture Object setting the wrong value i am getting error for the below line.
PrintRange = ws.get_Range(ws.Cells[firstRow, firstCol], ws.Cells[lastRow, lastCol]);

Could some one suggest how we can avoid the Diagrams when using the Picture Object or how to calculate the cell position correctly

推荐答案

图存储在Shapes中；)

您需要遍历工作表中的形状集合，并按名称或索引查找图.

VBA代码:

The diagrams are stored in Shapes ;)

You need to go through the collection of shapes in worksheet and find a diagram by the name or by the index.

VBA code:

'gets the address of diagram
Function GetDiagramPosition(wsh As Worksheet, sName As String) As String
Dim shDgm As Shape, i As Integer

For i = 1 To wsh.Shapes.Count
    Set shDgm = wsh.Shapes(i)
    If shDgm.Name = sName Then Exit For
Next

GetDiagramPosition = shDgm.TopLeftCell.Address & ":" & shDgm.BottomRightCell.Address

End Function

C#代码的翻译版本: http://www.carlosag.net/tools/codetranslator/ [^ ]

C# code translated on: http://www.carlosag.net/tools/codetranslator/[^]

string GetDiagramPosition(Worksheet wsh, string sName) {
       Shape shDgm;
       for (int i = 1; (i <= wsh.Shapes.Count); i++) {
           shDgm = wsh.Shapes(i);
           if ((shDgm.Name == sName)) {
               break;
           }
       }
       return (shDgm.TopLeftCell.Address + (":" + shDgm.BottomRightCell.Address));
   }

结果:"

Result: "

A

3:

这篇关于Excel 2003-当工作表中存在图表时，图片对象无法计算正确的列和行.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！