title: Copy image from xls to xslx using Apache Poi
body: <p>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.</p>
  <p>My code:</p>
  <pre><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 = &quot;test.png&quot;;
      String outputFile = &quot;oldFile.xls&quot;;
      HSSFWorkbook workbook = new HSSFWorkbook();
      HSSFSheet sheet = workbook.createSheet(&quot;Image&quot;);
      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(&quot;Error reading image&quot;);
      }
      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&lt;?&gt; drawing = sheet.getDrawingPatriarch();
      Iterator&lt;?&gt; 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 = &quot;newFile.xlsx&quot;;
      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();
    }
  }
  </code></pre>
  <p>I read these answer:</p>
  <p><a href="https://stackoverflow.com/questions/48567203/apache-poi-xssfclientanchor-not-positioning-picture-with-respect-to-dx1-dy1-dx">apache poi XSSFClientAnchor not positioning picture with respect to dx1, dy1, dx2, dy2</a></p>
  <p><a href="https://stackoverflow.com/questions/61428809/how-to-set-image-size-via-hssfworkbook">How to set image size via HSSFWorkbook</a></p>
  <p><a href="https://stackoverflow.com/questions/37182688/apache-poi-excel-sheet-resize-a-picture-while-keeping-its-ratio">Apache POI Excel sheet: resize a picture while keeping its ratio</a></p>
  <p>What am I doing wrong?</p>