Find the cell coordinates in the upper left? Work with EPPlus

Question

I am trying to display an image in an excel extension sheet and I need to find what are the coordinates of the current cell?

My code currently loops, although each cell until it finds the corresponding tag in excel, where it knows that it is a cell for the image.

I have a cell, I just have no idea how to get the top and left properties of the cells?

foreach (ExcelRangeBase cell in range1) { } 

Please, help!

Thanks in advance.

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 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); 

Answer 2

Cell.Top and Cell.Left would be nice, but they do not exist in EPPlus. I was inspired by Pomner and made this function:

 /// <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; } 

Answer 3

Try cell.top and cell.left , they exist in the excel object model.

Answer 4

How to get top left corner of excel range:

 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]; 

where wsheet is a Worksheet object.