寻找单元格左上角的坐标？使用EPPlus进行操作。

Question

我正在尝试将一张图片映射到Excel电子表格中，我需要找到当前单元格的坐标是什么?

目前我的代码会循环遍历每个单元格，直到在Excel中找到一个匹配的标记，在这里它知道这是放置图片的单元格。

我已经有了单元格，但完全不知道如何获取单元格的上边距和左边距属性？

foreach (ExcelRangeBase cell in range1)
     {

     }

请帮忙！

提前感谢。

- Pomster

Answer 1

int row = cellAddress.Start.Row;
int column = cellAddress.Start.Column;


if (mCellsResult.Count() > 0)
{
var mCells = mCellsResult.First();

//if the cell and the merged cell do not share a common start address then skip this cell as it's already been covered by a previous item
if (mCells.Start.Address != cellAddress.Start.Address)
                                                        continue;

if (mCells.Start.Column != mCells.End.Column)
{
colSpan += mCells.End.Column - mCells.Start.Column;
}

if (mCells.Start.Row != mCells.End.Row)
{
   rowSpan += mCells.End.Row - mCells.Start.Row;
}
}
double height = 0, width = 0;
for (int h = 0; h < rowSpan; h++)
{
height += xlWorkSeet1[k].Row(row + h).Height;
}
for (int w = 0; w < colSpan; w++)
{
 width += xlWorkSeet1[k].Column(column + w).Width;
}

double pointToPixel = 0.75;

height /= pointToPixel;
width /= 0.1423;


picture = xlWorkSeet1[k].Drawings.AddPicture(System.Guid.NewGuid().ToString() + row.ToString() + column.ToString(), image);
picture.From.Column = column - 1;
picture.From.Row = row - 1;
picture.SetSize((int)width, (int)height);

- Pomster

Answer 2

Cell.Top和Cell.Left很好，但在EPPlus中它们不存在。我受到Pomster的启发，制作了这个函数：

/// <summary>Get top, left, width and height of a given cell.</summary>
    Rectangle GetCellTopLeftCoordinates(ExcelWorksheet worksheet, ExcelRangeBase cell)
    {
        double top = 0, left = 0, width = 0, height = 0;

        //Get top and left position:
        for (int i = 1; i < cell.Start.Row; i++)
            top += worksheet.Row(i).Height;

        for (int i = 1; i < cell.Start.Column; i++)
            left += worksheet.Column(i).Width;

        //Get width and height:
        if (cell.Merge)  //Then the cell are merged with others:
        {
            foreach (string address in worksheet.MergedCells)  //Loop through all merged cells:
                if (Intersect(worksheet.Cells[address], cell))  //Then we have found the particular, merged range:
                {
                    ExcelRange range = worksheet.SelectedRange[address];

                    for (int i = range.Start.Row; i <= range.End.Row; i++)
                        height += worksheet.Row(i).Height;

                    for (int i = range.Start.Column; i <= range.End.Column; i++)
                        width += worksheet.Column(i).Width;

                    break;
                }
        }
        else //No merges - just get dimensions:
        {
            width = worksheet.Column(cell.Start.Column).Width;
            height = worksheet.Row(cell.Start.Row).Height;
        }

        //Convert point to pixels:
        top /= 0.75;
        left /= 0.1423;
        height /= 0.75;
        width /= 0.1423;
        return new Rectangle((int)left, (int)top, (int)width, (int)height);
    }

    bool Intersect(ExcelRange range, ExcelRangeBase cell)
    {
        foreach (ExcelRangeBase item in range)
            if (item.Address == cell.Address)
                return true;

        return false;
    }

- Kenneth Bo Christensen

---

这个函数运行良好，但是请注意：GetCellTopLeftCoordinates中的Rectangle构造函数的最终返回值被错误地使用了："top"和"left"参数应该交换。 - Kaganar

Answer 3

尝试使用cell.top和cell.left，它们存在于Excel对象模型中。

- nutsch

Answer 4

如何获取 Excel 区域的左上角：

using Excel = Microsoft.Office.Interop.Excel;
...
// get upper left corner of range defined by a RangeStr like "B2:D4"
Excel.Range UpperLeftCell = (Excel.Range)wsheet.get_Range(RangeStr).Cells[1,1]; 

这里的 wsheet 是一个工作表对象。

- tuncalik

---

OP正在请求与EPPlus相关的信息，因此Interop是无用的。 - Donald Rich