How to align image in merged cells

Hi,

I have to place inside merged cell(number of columns will be dynamic but only one row)
Now I have to place image(logo) inside merged cell which should be right aligned.
Is there any way to align image left, right or center?
Option to resize image is available but that is not sufficient for me to align it to center or to right.

@GauravKSingh,

Thanks for your query.

Well, alignment settings/attributes are not available for image inserted into the cells as you may confirm this in MS Excel. Well, to position or re-position a picture, you may make use of the following attributes:

  • UpperLeftColumn
  • UpperLeftRow
  • Top
  • Left
  • LowerRightRow
  • LowerRightColumn
  • UpperDeltaX
  • UpperDeltaY
  • LowerDeltaX

Moreover, for a simple scenario to place the picture to right most of the cell (merged), you may try to use AlignTopRightCorner API, see the following sample code on how to align to right corner and how to apply center alignment (fit the image in the cell) for your reference:
e.g
Sample code:

    Workbook wb = new Workbook();

    Worksheet ws = wb.Worksheets["Sheet1"];

    Cells cells = ws.Cells;

    //1. Right alignment in the merged range of cell
    cells.SetColumnWidth(0, 45);
    cells.SetRowHeight(0, 120);
    int temp = ws.Pictures.Add(0, 0, "e:\\test\\testscalepic\\zp_logo.jpg", 50, 50);
    cells.Merge(0, 0, 1, 5);//Merge cells A1:E1
    ws.Pictures[temp].AlignTopRightCorner(0, 4);//Move to right position of the merged range.

    //2. Center alignment of a cell (G4) (fit to the cell).
    cells.SetRowHeight(3, 100);
    cells.SetColumnWidth(6, 20);
    int index = ws.Pictures.Add(3, 6, 4, 7, "e:\\test\\school.jpg");
    Picture pic = ws.Pictures[index];
    pic.Placement = PlacementType.FreeFloating;
                
    wb.Save("e:\\test2\\out1.xlsx"); 

file1.zip (22.4 KB)

Hope, this helps a bit.

Thanks for your quick reply, It worked for me.
:slight_smile:

@GauravKSingh,

Good to know that your issue is sorted out by the suggested code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Hi, could add similar sample to align image vertically in cell to bottom or center?

@23W,

You got to devise your own code and logic to accomplish the task. The alignment settings/attributes are not available for image inserted into the cells as you may confirm this in MS Excel. To position or re-position a picture, you may make use of the following attributes:

  • UpperLeftColumn
  • UpperLeftRow
  • Top
  • Left
  • LowerRightRow
  • LowerRightColumn
  • UpperDeltaX
  • UpperDeltaY
  • LowerDeltaX
    etc.

I think, I don’t understand UpperDeltaX\UpperDeltaY exactly. What is unit of them? Is it percentage of cell range width \ height or something other. Why so strange range 1024 and 256 limits ?
For example, if i want to align image to bottom cell border, is it right code?

//C# code
var ws = m_workbook.Worksheets[0]; // my sheet
var p = ws.Pictures[0]; // my picture
var cell = ws.Cells[p.UpperLeftRow, p.UpperLeftColumn];
var rowHeight = ws.Cells.GetRowHeight(p.UpperLeftRow);
var height = p.Height;

// alignment
p.Top = (int)Math.Round(rowHeight - height);

@23W,

Please write your own code and logic to place the picture at your desired location in the merged area. I have written a sample code which places the picture at bottom/center point in the merged cell. Please refer to it and write/update your code accordingly (if needed):
e.g
Sample code:

Workbook wb = new Workbook();

            Worksheet ws = wb.Worksheets["Sheet1"];

            Cells cells = ws.Cells;

            //1. Right alignment in the merged range of cell
            cells.SetColumnWidth(0, 45);
            cells.SetRowHeight(0, 140);
            int temp = ws.Pictures.Add(0, 0, "e:\\test\\testscalepic\\zp_logo.jpg", 50, 50);
            cells.Merge(0, 0, 1, 5);//Merge cells A1:E1

            //width (in pixels) of the merged cells range
            
            int intWidth = 0;
            for(int i =0; i< 5; i++)
            {
                                   
                intWidth += ws.Cells.GetColumnWidthPixel(i);
            }

            //left position of image

            Picture objPicture = ws.Pictures[temp];

            int intLeft; 

            if(intWidth > objPicture.Width)
            {
                intLeft = (intWidth - objPicture.Width) / 2;

            }
            else
            {
             intLeft = 0;
            
            }

            //centralize position. the Left --> UpperLeftColumn 
            for(int intI=0; intI < 5; intI++)
            {
                if (ws.Cells.GetColumnWidthPixel(intI) > intLeft)
                {
                    objPicture.Left = intLeft;
                    break;
                 }
                else
                {
                    intLeft -= ws.Cells.GetColumnWidthPixel(intI);
                    objPicture.UpperLeftColumn += 1;
                }
            }

            //height (in pixels) of the row

            int intheight = 0;
            intheight = ws.Cells.GetRowHeightPixel(0);
            //at bottom vertically
            objPicture.Top = intheight - objPicture.Height; 


            wb.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.