The Excel isn't blank but contains very small pictures using XSSF or SXSSF. XSSFClientAnchor.setDx1 states:
Note - XSSF and HSSF have a slightly different coordinate system,
values in XSSF are larger by a factor of
Units.EMU_PER_PIXEL.
But that's not the whole truth. The meaning of the dx and dy is totally different. In the binary file system *.xls, the values are also dependent on the factor of column-width / default column-width and row-height / default row-height. See apache poi XSSFClientAnchor not positioning picture with respect to dx1, dy1, dx2, dy2.
I had found out that same value of dx and dy means different length in pixel for different column widths or different row heights. The higher column width the more pixels are occupied from the same value of dx for example. Same for row height and dy.
Found at least the definition of dx and dy for binary *-xls file format. It is defined in 2.5.193 OfficeArtClientAnchorSheet.
dx: The value is expressed as 1024th’s of that cell’s width.
dy: The value is expressed as 256th’s of that cell’s height.
Additional Microsoft uses many strange different measurement units for length in it's Office applications. There are Twip (Twentieth of an inch point), EMU (English Metric Uint), half points, and so on, additional to pixels and points.
Taking all this into account, it can be a big challenge when the task is to create same sized and positioned pictures in drawings of binary *.xls and Office Open XML *.xlsx.
At first we should choose one measurement unit for length. In my example, i choose pixels.
Having that, we will see, that XSSF is most logically, if we know the Units.EMU_PER_PIXEL factor.
It is HSSF which needs more recalculations. In your example the values 1023 and 245 used in your calculations seems to be trial&error values optimized for HSSF. I suspect the 10 means a margin of 10. But 10 what? Pixels? Points? Your HSSF-result should have shown that it cannot be one of that. So your statement: "I have no problem exporting pictures to excel using HSSFWorkbook." is not really true.
The following complete example should work using HSSF, XSSF and also SXSSF and producing the 4 pictures same aligned over cell A1 in sheet-drawing.
It is tested and works using current Apache POI version 5.2.3.
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.streaming.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
class ExcelCreatePictures {
static final String[] imgs = {
"./Bild1.jpg",
"./Bild2.jpg",
"./Bild3.jpg",
"./Bild4.jpg"
};
public static void main(String[] args) throws Exception {
//Workbook workbook = new HSSFWorkbook(); String filePath = "./Excel.xls";
//Workbook workbook = new XSSFWorkbook(); String filePath = "./Excel.xlsx";
Workbook workbook = new SXSSFWorkbook(); String filePath = "./Excel.xlsx";
Sheet sheet = workbook.createSheet();
sheet.setColumnWidth(0, 5000 * imgs.length);
int colWidthInPx = (int)Math.round(sheet.getColumnWidthInPixels(0));
Row imgRow = sheet.createRow(0);
imgRow.setHeight((short) 1000);
int rowHeightInPx = (int)Math.round(imgRow.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI);
//int mar = 10 + 10 + (imgs.length - 1) * 10;
//int ave = (1023 - mar) / imgs.length;
int mar = 10;
int marSum = mar + mar + (imgs.length - 1) * mar;
int avePicW = (colWidthInPx - marSum) / imgs.length;
// get drawing
Drawing drawing = sheet.createDrawingPatriarch();
for (int i = 0; i < imgs.length; i++) {
// add picture to workbook, get pictureIdx
FileInputStream in = new FileInputStream(imgs[i]);
byte[] bytes = IOUtils.toByteArray(in);
int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
in.close();
// create anchor for picture shape
CreationHelper helper = workbook.getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setDx1(mar * (i + 1) + avePicW * i);
if (drawing instanceof XSSFDrawing || drawing instanceof SXSSFDrawing) {
anchor.setDx1(anchor.getDx1() * Units.EMU_PER_PIXEL);
} else if (drawing instanceof HSSFPatriarch) {
anchor.setDx1((int)Math.round(anchor.getDx1() * 1024f / colWidthInPx));
}
anchor.setDy1(mar);
if (drawing instanceof XSSFDrawing || drawing instanceof SXSSFDrawing) {
anchor.setDy1(anchor.getDy1() * Units.EMU_PER_PIXEL);
} else if (drawing instanceof HSSFPatriarch) {
anchor.setDy1((int)Math.round(anchor.getDy1() * 256f / rowHeightInPx));
}
anchor.setDx2((mar + avePicW) * (i + 1));
if (drawing instanceof XSSFDrawing || drawing instanceof SXSSFDrawing) {
anchor.setDx2(anchor.getDx2() * Units.EMU_PER_PIXEL);
} else if (drawing instanceof HSSFPatriarch) {
anchor.setDx2((int)Math.round(anchor.getDx2() * 1024f / colWidthInPx));
}
anchor.setDy2(rowHeightInPx - mar);
if (drawing instanceof XSSFDrawing || drawing instanceof SXSSFDrawing) {
anchor.setDy2(anchor.getDy2() * Units.EMU_PER_PIXEL);
} else if (drawing instanceof HSSFPatriarch) {
anchor.setDy2((int)Math.round(anchor.getDy2() * 256f / rowHeightInPx));
}
anchor.setCol1(0);
anchor.setRow1(0);
anchor.setCol2(0);
anchor.setRow2(0);
// create picture shape
Picture picture = drawing.createPicture(anchor, pictureIdx);
}
FileOutputStream out = new FileOutputStream(filePath);
workbook.write(out);
out.close();
workbook.close();
if (workbook instanceof SXSSFWorkbook) ((SXSSFWorkbook)workbook).dispose();
}
}
Result:
