Write image in excel at row 3000 to 3005 in java using apache poi when using LibreOffice Calc to open file

Question

I am using poi 3.9 to create xlsx files everything is fine until my file length crossed 1600 rows. After crossing 1600 rows I am able to write data but I am unable to write images all images get appended on each other at row number 1640 this is weird and I am working on poi from long time and picked the issue that its library limitation and updated my poi to 3.15 but same issue, here I am able to write images up to 2000 rows then I tried poi 3.16 but again issue is same but here I can write Images up to 2500 rows. Below is my code for writing images

private void drawImageOnExcelSheet(XSSFSheet sitePhotosSheet, int row1,
        int row2, int col1, int col2, String fileName) {
    try {
        InputStream is = new FileInputStream(fileName);
        byte[] bytes = IOUtils.toByteArray(is);
        int pictureIdx = sitePhotosSheet.getWorkbook().addPicture(bytes,Workbook.PICTURE_TYPE_JPEG);
        is.close();
        CreationHelper helper = sitePhotosSheet.getWorkbook().getCreationHelper();

        Drawing drawing = sitePhotosSheet.createDrawingPatriarch();

        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

        anchor.setCol1(col1);
        anchor.setCol2(col2);
        anchor.setRow1(row1);
        anchor.setRow2(row2);
        drawing.createPicture(anchor, pictureIdx);
    } catch(Exception e) {
        e.printStackTrace();
}

Note: that I am able to write data but getting issue only for images. Please suggest how can I solve this issue. Please see image below here you can see that at row 1639 two images are appended on each other and there are many images behind these two ones since my last row for images that I have printed on console is 3400.

Answer 1

Using apache poi version 3.16 and Java 8 (I'm not a friend of ancient software versions)

Having following code:

import java.io.*;

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

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.util.IOUtils;

public class ExcelDrawImage {

 private static void drawImageOnExcelSheet(XSSFSheet sitePhotosSheet, int row1,
        int row2, int col1, int col2, String fileName) {
    try {
        InputStream is = new FileInputStream(fileName);
        byte[] bytes = IOUtils.toByteArray(is);
        int pictureIdx = sitePhotosSheet.getWorkbook().addPicture(bytes,Workbook.PICTURE_TYPE_JPEG);
        is.close();
        CreationHelper helper = sitePhotosSheet.getWorkbook().getCreationHelper();

        Drawing drawing = sitePhotosSheet.createDrawingPatriarch();

        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

        anchor.setCol1(col1);
        anchor.setCol2(col2);
        anchor.setRow1(row1);
        anchor.setRow2(row2);
        drawing.createPicture(anchor, pictureIdx);
    } catch(Exception e) {
        e.printStackTrace();
    }
 }

 public static void main(String[] args) throws Exception {
  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet();

  for (int r = 0; r < 10000; r+=10 ) {
   sheet.createRow(r).createCell(1).setCellValue("Picture " + (r/10+1) + ":");
   drawImageOnExcelSheet((XSSFSheet)sheet, r+1, r+6, 1, 4, "samplePict.jpeg");
  }

  wb.write(new FileOutputStream("ExcelDrawImage.xlsx"));
  wb.close();
 }
}

Result:

Same in Ubuntu Linux:

Result in Calc: