CODEWITHSUNDEEP
c#excelmodel-view-controllerclosedxml
Jithin Shaji
Jithin Shaji

Reputation: 23

How to center Image inside a merged cell range in ClosedXML

I am using ClosedXML (https://github.com/ClosedXML/ClosedXML) for creating an excel file in my C# MVC Controller. As per the documentation in https://github.com/closedxml/closedxml/wiki/How-can-I-insert-an-image, I have inserted an image in a cell and merged that cell with to cells on the right side.My code is as follows:

For adding image 

var imagePath = @"c:\myFolder\image.jpg";
var image = MyWorkSheet.AddPicture(imagePath )                                               .MoveTo((MyWorkSheet.Cell(3,1).Address))                                               .Scale(0.2);
image.Width = 50;
image.Height = 50;

For merging cell

 MyWorkSheet.Range(MyWorkSheet.Cell(3,1).Address, MyWorkSheet.Cell(3, 3).Address).Merge();

But the image lies on the upper left corner of the cell. I cant find any web source explaining how to center the image in the cell range. Anyone please help me.

Upvotes: 2

Views: 5808

Answers (2)

Matt Roy
Matt Roy

Reputation: 1525

You have to move your image with an offset from the cell. To do that, you have to calculate that offset. Column width is returned in point (not in pixel). You have to convert it to pixel to compare with image pixel width.

So you can do : 

int iColumnWidth = (MyWorkSheet.Column(1).Width - 1) * 7 + 12; // To convert column width in pixel unit.
int xOffset = (iColumnWidth - image.Width) / 2;
int yOffset = 0;
image.MoveTo(MyWorkSheet.Cell(3,1), New Point(xOffset, yOffset));

Upvotes: 3

Tabish Matin
Tabish Matin

Reputation: 85

You need to do some manual calculation based on the with respect to image and cell size.

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];


sheet.Range["A1"].Text = "Align Picture Within A Cell:";
sheet.Range["A1"].Style.VerticalAlignment = VerticalAlignType.Top;


string picPath = @"C:\Users\Administrator\Desktop\scenery.jpg";
ExcelPicture picture = sheet.Pictures.Add(1, 1, picPath);

sheet.Columns[0].ColumnWidth = 50;
sheet.Rows[0].RowHeight = 150;

picture.LeftColumnOffset =100;
picture.TopRowOffset = 25;


wb.SaveToFile("AlignPicture.xlsx", ExcelVersion.Version2013);

Upvotes: -1

Related Questions