Horizontally center an image in merged cells using apache poi

Question

The situation is , I merged all the five cells of the first row and inserted an image to the first cell of the first row. My requirements is to make the image horizontally centered for the first row.

I've tried cellStyle.setAlignment(CellStyle.ALIGN_CENTER); but it centers text not images.

Can anyone help?

Note: all the cells have different widths.

Answer 1

Positioning a image in an Excel sheet is a tricky thing since the picture is anchored on two cells. The upper left anchor cell plus a delta-x and a delta-y to this determines the position of the upper left edge of the picture. The bottom right anchor cell plus a delta-x and a delta-y to this determines the size.

Whether cells are merged or not is not important for this process.

So for centering horizontally we need to calculate which is the upper left anchor cell plus a delta-x to this. Fortunately the bottom right anchor cell plus a delta-x and a delta-y to this can be determined automatically through resizing the image to its original size after the upper left anchor cell is set. Of course only if the picture shall appear in its original size.

Example with comments:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;


class CenterImageTest {

 public static void main(String[] args) {
  try {

   Workbook wb = new XSSFWorkbook();
   Sheet sheet = wb.createSheet("Sheet1");

   //create the cells A1:F1 with different widths
   Cell cell = null;
   Row row = sheet.createRow(0);
   for (int col = 0; col < 6; col++) {
    cell = row.createCell(col);
    sheet.setColumnWidth(col, (col+1)*5*256);
   }

   //merge A1:F1
   sheet.addMergedRegion(new CellRangeAddress(0,0,0,5));

   //load the picture
   InputStream inputStream = new FileInputStream("/home/axel/Bilder/Unbenannt.png");
   byte[] bytes = IOUtils.toByteArray(inputStream);
   int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
   inputStream.close();

   //create an anchor with upper left cell A1
   CreationHelper helper = wb.getCreationHelper();
   ClientAnchor anchor = helper.createClientAnchor();
   anchor.setCol1(0); //Column A
   anchor.setRow1(0); //Row 1

   //create a picture anchored to A1
   Drawing drawing = sheet.createDrawingPatriarch();
   Picture pict = drawing.createPicture(anchor, pictureIdx);

   //resize the pictutre to original size
   pict.resize();

   //get the picture width
   int pictWidthPx = pict.getImageDimension().width;
System.out.println(pictWidthPx);

   //get the cell width A1:F1
   float cellWidthPx = 0f;
   for (int col = 0; col < 6; col++) {
    cellWidthPx += sheet.getColumnWidthInPixels(col);
   }
System.out.println(cellWidthPx);

   //calculate the center position
   int centerPosPx = Math.round(cellWidthPx/2f - (float)pictWidthPx/2f);
System.out.println(centerPosPx);

   //determine the new first anchor column dependent of the center position 
   //and the remaining pixels as Dx
   int  anchorCol1 = 0;
   for (int col = 0; col < 6; col++) {
    if (Math.round(sheet.getColumnWidthInPixels(col)) < centerPosPx) {
     centerPosPx -= Math.round(sheet.getColumnWidthInPixels(col));
     anchorCol1 = col + 1;
    } else {
     break;
    }
   }
System.out.println(anchorCol1);
System.out.println(centerPosPx);

   //set the new upper left anchor position
   anchor.setCol1(anchorCol1);
   //set the remaining pixels up to the center position as Dx in unit EMU
   anchor.setDx1( centerPosPx * Units.EMU_PER_PIXEL);

   //resize the pictutre to original size again
   //this will determine the new bottom rigth anchor position
   pict.resize();

   FileOutputStream fileOut = new FileOutputStream("CenterImageTest.xlsx");
   wb.write(fileOut);
   fileOut.close();

  } catch (IOException ioex) {
  }
 }
}

For a scaled picture have a scale factor:

   double scaleFactor = 0.25d;

and then:

   //calculate the center position
   int centerPosPx = Math.round(cellWidthPx/2f - (float)pictWidthPx/2f*(float)scaleFactor);

and:

   //resize the pictutre to original size again
   //this will determine the new bottom rigth anchor position with original size
   pict.resize();
   //resize the pictutre to scaled size
   //this will determine the new bottom rigth anchor position with scaled size
   pict.resize(scaleFactor);

Note: Resize to original size first. So determine bottom right anchor position with original size. Then resize scaled. So get bottom right anchor position with scaled size.