Copy image from xls to xslx using Apache Poi

Question

I'm trying to copy an image from a XLS to a XLSX, using Apache Poi 4.1.2. I'm not able to calculate the right value for dy2.

My code:

package poisamples;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.util.Units;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class HSSFtoXSSF {

  private static HSSFWorkbook createXLS() {
    String imageFile = "test.png";
    String outputFile = "oldFile.xls";
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Image");
    HSSFClientAnchor anchor = new HSSFClientAnchor(27,75,11,174,(short)0, (short)0, (short)7, (short)3);

    sheet.setColumnWidth(0, 6000);

    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    try {
      int index = sheet.getWorkbook().addPicture(imageToBytes(imageFile), HSSFWorkbook.PICTURE_TYPE_PNG);
      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
      HSSFPicture picture = patriarch.createPicture(anchor, index);
      FileOutputStream fos = new FileOutputStream(outputFile);
      workbook.write(fos);
      fos.close();
    }
    catch (IOException ioe) {
      System.out.println("Error reading image");
    }
    return workbook;
  }

  private static byte[] imageToBytes(String imageFilename) throws IOException {
    File imageFile;
    FileInputStream fis = null;
    ByteArrayOutputStream bos;
    int read;
    try {
      imageFile = new File(imageFilename);
      fis = new FileInputStream(imageFile);
      bos = new ByteArrayOutputStream();
      while ((read = fis.read()) != -1) {
        bos.write(read);
      }
      return (bos.toByteArray());
    }
    finally {
      if (fis != null) {
        try {
          fis.close();
          fis = null;
        }
        catch (IOException ioEx) {
          // Nothing to do here
        }
      }
    }
  }

  private static void transferImages (HSSFSheet sheet, XSSFSheet newSheet) {
    if ( sheet.getDrawingPatriarch() == null) {
      sheet.createDrawingPatriarch();
    }
    Drawing<?> drawing = sheet.getDrawingPatriarch();
    Iterator<?> it = drawing.iterator();
    while (it.hasNext()) {
      Object shape = it.next();
      if (shape instanceof Picture) {
        copyImage((Picture)shape, sheet, newSheet);
      }
    }
  }

  private static void copyImage(Picture shape, HSSFSheet oldSheet, XSSFSheet newSheet) {
    PictureData pictureData = shape.getPictureData();
    ClientAnchor oldAnchor = (ClientAnchor) shape.getAnchor();

    int col1 = oldAnchor.getCol1();
    int col2 = oldAnchor.getCol2();
    int row1 = oldAnchor.getRow1();
    int row2 = oldAnchor.getRow2();

    int x1 = oldAnchor.getDx1();
    int x2 = oldAnchor.getDx2();
    int y1 = oldAnchor.getDy1();
    int y2 = oldAnchor.getDy2();
    int DEFAULT_COL_WIDTH = 10 * 256;
    float DEFAULT_ROW_HEIGHT = 12.75f;

    // Calculate row height
    Row row = oldSheet.getRow(row1);
    float rowHeightInPoints1 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
    float rowHeightInPixels1 = rowHeightInPoints1 * Units.PIXEL_DPI / Units.POINT_DPI;
    row = oldSheet.getRow(row2);
    float rowHeightInPoints2 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
    float rowHeightInPixels2 = rowHeightInPoints2 * Units.PIXEL_DPI / Units.POINT_DPI;

    // Calculate column width
    int colwidth1 = oldSheet.getColumnWidth(col1);
    int colwidth2 = oldSheet.getColumnWidth(col2);

    y1 = (int)Math.round(y1 / Units.PIXEL_DPI * Units.POINT_DPI / 14.75 / DEFAULT_ROW_HEIGHT * rowHeightInPixels1);
    x1 = (int)Math.round((x1 / (Units.DEFAULT_CHARACTER_WIDTH * 256f) / 14.75) / DEFAULT_COL_WIDTH * colwidth1);
    y2 = (int)Math.round(y2 / Units.PIXEL_DPI * Units.POINT_DPI / 14.75 / DEFAULT_ROW_HEIGHT * rowHeightInPixels2);
    x2 = (int)Math.round((x2 / (Units.DEFAULT_CHARACTER_WIDTH * 256f) / 14.75) / DEFAULT_COL_WIDTH * colwidth2);

    Workbook newWb = newSheet.getWorkbook();
    CreationHelper newHelper = newWb.getCreationHelper();
    Drawing newDrawing = newSheet.getDrawingPatriarch();
    if ( newDrawing == null) {
      newDrawing = newSheet.createDrawingPatriarch();
    }
    ClientAnchor newAnchor = newHelper.createClientAnchor();
    newAnchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

    newAnchor.setRow1(row1);
    newAnchor.setCol1(col1);
    newAnchor.setDy1(y1 * Units.EMU_PER_POINT);
    newAnchor.setDx1((int)Math.round(x1 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));

    newAnchor.setCol2(col2);
    newAnchor.setRow2(row2); //second anchor determines bottom right position
    newAnchor.setDy2(y2 * Units.EMU_PER_POINT);
    newAnchor.setDx2((int)Math.round(x2 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));

    int newPictureIndex = newWb.addPicture(pictureData.getData(), pictureData.getPictureType());
    Picture picture = newDrawing.createPicture(newAnchor, newPictureIndex);
  }

  public static void main(String[] args) throws IOException {
    String outputFile = "newFile.xlsx";
    HSSFWorkbook oldWorkbook = createXLS();
    XSSFWorkbook newWorkbook = new XSSFWorkbook();

    HSSFSheet oldSheet = oldWorkbook.getSheetAt(0);
    String sheetName = oldSheet.getSheetName();
    XSSFSheet newSheet = newWorkbook.createSheet(sheetName);
    newSheet.setDefaultRowHeight(oldSheet.getDefaultRowHeight());
    newSheet.setDefaultColumnWidth(oldSheet.getDefaultColumnWidth());
    transferImages(oldSheet, newSheet);

    oldWorkbook.close();
    FileOutputStream fos = new FileOutputStream(outputFile);
    newWorkbook.write(fos);
    fos.close();
    newWorkbook.close();
  }
}

I read these answer:

apache poi XSSFClientAnchor not positioning picture with respect to dx1, dy1, dx2, dy2

How to set image size via HSSFWorkbook

Apache POI Excel sheet: resize a picture while keeping its ratio

What am I doing wrong?