javaexcelapacheapache-poi
apache poi XSSFClientAnchor not positioning picture with respect to dx1, dy1, dx2, dy2
I am trying to add an image to excel using apach-poi version 3.16. I am able to do that with HSSFWorkbook and XSSFWorkbook. But when i am trying to add spacing for the image i.e if I set dx1, dy1, dx2, dy2 coordinates on XSSFClientAnchor it is not taking effect. Same thing is working on HSSFClientAnchor. I am attaching both classes and corresponding excel file generated. Could you please help me how can i do achieve the same result using XSSFClientAnchor.
HSSF Class
package poisamples;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
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;
public class HSSFImage {
public static void main(String[] args) throws IOException {
String imageFile = "test.png";
String outputFile = "image-sutpid.xls";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Image");
HSSFClientAnchor anchor = new HSSFClientAnchor(100,100,100,100,(short)0, (short)0, (short)0, (short)3);
sheet.setColumnWidth(0, 6000);
anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
int index = sheet.getWorkbook().addPicture(imageToBytes(imageFile), HSSFWorkbook.PICTURE_TYPE_PNG);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFPicture picture = patriarch.createPicture(anchor, index);
picture.resize();
FileOutputStream fos = new FileOutputStream(outputFile);
workbook.write(fos);
}
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
}
}
}
}
}
XSSF Class
package poisamples;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
public class XSSFImage {
public static void main(String[] args) throws IOException {
String imageFile = "test.png";
String outputFile = "image-sutpid.xlsx";
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Image");
XSSFClientAnchor anchor = new XSSFClientAnchor(100,100,100,100,0, 0, 0, 3);
sheet.setColumnWidth(0, 6000);
anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
int index = sheet.getWorkbook().addPicture(imageToBytes(imageFile), XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
XSSFPicture picture = patriarch.createPicture(anchor, index);
picture.resize();
FileOutputStream fos = new FileOutputStream(outputFile);
workbook.write(fos);
}
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
}
}
}
}
}
HSSF Result:
XSSF Result:
Image used:
Solution
The problems are the different strange measurement units which Microsoft is using and the fact that the binary file system *.xls and the Office Open XML *.xlsx are very different not only in file storing but in general approaches also.
As mentioned in ClientAnchor: "Note - XSSF and HSSF have a slightly different coordinate system, values in XSSF are larger by a factor of Units.EMU_PER_PIXEL". But this is not the whole truth. The meaning of the dx and dy is totally different. In the binary file system *.xls, the values are dependent on the factor of column-width / default column-width and row-height / default row-height. Don't ask me about the factor 14.75 used in my example. It is just trial&error.
To mention about your code is that if you wants resizing the picture to its native size, then only a one cell anchor is needed. This anchors the picture's upper left edge. A two cell anchor only is needed if the anchor shall determining the picture's size. Then the first cell in the anchor anchors the picture's upper left edge while the second cell in the anchor anchors the picture's bottom right edge.
The following example uses the measurement unit 1/256th of a character width for dx since column widths also are in this measurement unit. And it uses point as measurement unit for dy since row heights also are in this measurement unit.
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
public class CreateExcelWithPictures {
private static Picture drawImageOnExcelSheet(Sheet sheet,
int col1, int row1, int dx1/*1/256th of a character width*/, int dy1/*points*/,
int col2, int row2, int dx2/*1/256th of a character width*/, int dy2/*points*/,
String pictureurl, int picturetype, boolean resize) throws Exception {
int DEFAULT_COL_WIDTH = 10 * 256;
float DEFAULT_ROW_HEIGHT = 12.75f;
Row row = sheet.getRow(row1);
float rowheight1 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
row = sheet.getRow(row2);
float rowheight2 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
int colwidth1 = sheet.getColumnWidth(col1);
int colwidth2 = sheet.getColumnWidth(col2);
InputStream is = new FileInputStream(pictureurl);
byte[] bytes = IOUtils.toByteArray(is);
int pictureIdx = sheet.getWorkbook().addPicture(bytes, picturetype);
is.close();
CreationHelper helper = sheet.getWorkbook().getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
anchor.setRow1(row1); //first anchor determines upper left position
if (sheet instanceof XSSFSheet) {
anchor.setDy1(dy1 * Units.EMU_PER_POINT);
} else if (sheet instanceof HSSFSheet) {
anchor.setDy1((int)Math.round(dy1 * Units.PIXEL_DPI / Units.POINT_DPI * 14.75 * DEFAULT_ROW_HEIGHT / rowheight1));
}
anchor.setCol1(col1);
if (sheet instanceof XSSFSheet) {
anchor.setDx1((int)Math.round(dx1 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
} else if (sheet instanceof HSSFSheet) {
anchor.setDx1((int)Math.round(dx1 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH / colwidth1));
}
if (!resize) {
anchor.setRow2(row2); //second anchor determines bottom right position
if (sheet instanceof XSSFSheet) {
anchor.setDy2(dy2 * Units.EMU_PER_POINT);
} else if (sheet instanceof HSSFSheet) {
anchor.setDy2((int)Math.round(dy2 * Units.PIXEL_DPI / Units.POINT_DPI * 14.75 * DEFAULT_ROW_HEIGHT / rowheight2));
}
anchor.setCol2(col2);
if (sheet instanceof XSSFSheet) {
anchor.setDx2((int)Math.round(dx2 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
} else if (sheet instanceof HSSFSheet) {
anchor.setDx2((int)Math.round(dx2 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH / colwidth2));
}
}
Picture picture = drawing.createPicture(anchor, pictureIdx);
if (resize) picture.resize();
return picture;
}
public static void main(String[] args) throws Exception {
Workbook workbook = new XSSFWorkbook();
//Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
sheet.setColumnWidth(1, 6000/*1/256th of a character width*/);
Row row = sheet.createRow(0);
row.setHeightInPoints(100/*points*/);
row = sheet.createRow(10);
row.setHeightInPoints(50/*points*/);
Picture picture;
//two cell anchor in the same cell (B1) used without resizing the picture
picture = drawImageOnExcelSheet(sheet,
1, 0, 1000/*1/256th of a character width*/, 10/*points*/,
1, 0, 5000/*1/256th of a character width*/, 90/*points*/,
"mikt1.png", Workbook.PICTURE_TYPE_PNG, false);
//one cell anchor (B3) used with resizing the picture
picture = drawImageOnExcelSheet(sheet,
1, 2, 1000/*1/256th of a character width*/, 10/*points*/,
0, 0, 0, 0,
"mikt1.png", Workbook.PICTURE_TYPE_PNG, true);
//two cell anchor (B10 to B12) used without resizing the picture
picture = drawImageOnExcelSheet(sheet,
1, 9, 1000/*1/256th of a character width*/, 10/*points*/,
1, 11, 5000/*1/256th of a character width*/, 10/*points*/,
"mikt1.png", Workbook.PICTURE_TYPE_PNG, false);
if (workbook instanceof XSSFWorkbook) {
workbook.write(new FileOutputStream("image-sutpid.xlsx"));
} else if (workbook instanceof HSSFWorkbook) {
workbook.write(new FileOutputStream("image-sutpid.xls"));
}
workbook.close();
}
}
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.
Having that, the code should be like so:
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
public class CreateExcelWithPictures {
private static Picture drawImageOnExcelSheet(Sheet sheet,
int col1, int row1, int dx1/*1/256th of a character width*/, int dy1/*points*/,
int col2, int row2, int dx2/*1/256th of a character width*/, int dy2/*points*/,
String pictureurl, int picturetype, boolean resize) throws Exception {
int DEFAULT_COL_WIDTH = 10 * 256;
float DEFAULT_ROW_HEIGHT = 12.75f;
Row row = sheet.getRow(row1);
float rowheight1 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
row = sheet.getRow(row2);
float rowheight2 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
int colwidth1 = sheet.getColumnWidth(col1);
int colwidth2 = sheet.getColumnWidth(col2);
InputStream is = new FileInputStream(pictureurl);
byte[] bytes = IOUtils.toByteArray(is);
int pictureIdx = sheet.getWorkbook().addPicture(bytes, picturetype);
is.close();
CreationHelper helper = sheet.getWorkbook().getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
anchor.setRow1(row1); //first anchor determines upper left position
if (sheet instanceof XSSFSheet) {
anchor.setDy1(dy1 * Units.EMU_PER_POINT);
} else if (sheet instanceof HSSFSheet) {
anchor.setDy1((int)Math.round(dy1 * Units.PIXEL_DPI / Units.POINT_DPI * 256f / (rowheight1 * Units.PIXEL_DPI / Units.POINT_DPI)));
}
anchor.setCol1(col1);
if (sheet instanceof XSSFSheet) {
anchor.setDx1((int)Math.round(dx1 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
} else if (sheet instanceof HSSFSheet) {
anchor.setDx1((int)Math.round(dx1 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 1024f / (colwidth1 * Units.DEFAULT_CHARACTER_WIDTH / 256f)));
}
if (!resize) {
anchor.setRow2(row2); //second anchor determines bottom right position
if (sheet instanceof XSSFSheet) {
anchor.setDy2(dy2 * Units.EMU_PER_POINT);
} else if (sheet instanceof HSSFSheet) {
anchor.setDy2((int)Math.round(dy2 * Units.PIXEL_DPI / Units.POINT_DPI * 256f / (rowheight2 * Units.PIXEL_DPI / Units.POINT_DPI)));
}
anchor.setCol2(col2);
if (sheet instanceof XSSFSheet) {
anchor.setDx2((int)Math.round(dx2 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
} else if (sheet instanceof HSSFSheet) {
anchor.setDx2((int)Math.round(dx2 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 1024f / (colwidth2 * Units.DEFAULT_CHARACTER_WIDTH / 256f)));
}
}
Picture picture = drawing.createPicture(anchor, pictureIdx);
if (resize) picture.resize();
return picture;
}
public static void main(String[] args) throws Exception {
//Workbook workbook = new XSSFWorkbook();
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
sheet.setColumnWidth(1, 6000/*1/256th of a character width*/);
Row row = sheet.createRow(0);
row.setHeightInPoints(100/*points*/);
row = sheet.createRow(10);
row.setHeightInPoints(50/*points*/);
Picture picture;
//two cell anchor in the same cell (B1) used without resizing the picture
picture = drawImageOnExcelSheet(sheet,
1, 0, 1000/*1/256th of a character width*/, 10/*points*/,
1, 0, 5000/*1/256th of a character width*/, 90/*points*/,
"mikt1.png", Workbook.PICTURE_TYPE_PNG, false);
//one cell anchor (B3) used with resizing the picture
picture = drawImageOnExcelSheet(sheet,
1, 2, 1000/*1/256th of a character width*/, 10/*points*/,
0, 0, 0, 0,
"mikt1.png", Workbook.PICTURE_TYPE_PNG, true);
//two cell anchor (B10 to B12) used without resizing the picture
picture = drawImageOnExcelSheet(sheet,
1, 9, 1000/*1/256th of a character width*/, 10/*points*/,
1, 11, 5000/*1/256th of a character width*/, 10/*points*/,
"mikt1.png", Workbook.PICTURE_TYPE_PNG, false);
if (workbook instanceof XSSFWorkbook) {
workbook.write(new FileOutputStream("image-sutpid.xlsx"));
} else if (workbook instanceof HSSFWorkbook) {
workbook.write(new FileOutputStream("image-sutpid.xls"));
}
workbook.close();
}
}
But then it would be better having all lengths in measurement unit pixel to avoid the conversion from pt and/or 256'th of a character width to pixels. See Why the same image export excel using HSSFWorkbook can use SXSSFWorkbook can not for example.
- Keep numbers which appear in both columns, in J lang
- Differentiation in J
- How to reshape an array with an arbitrary size in one dimension?
- Why is Insert (fold) right associative
- Write 4 : 'x&{.&.;: y' tacitly
- Alignment issue when printing formatted prime numbers in J language
- How can I define a verb in J that applies a different verb alternately to each atom in a list?
- How to get user input in the J programming language
- How to unbox a list of boxed lists of differing lengths in J?
- How can I fix 'noun result was required' error in J?
- In j, how can I define a verb locally in one scope and pass it to a defined adverb?
- Convert boxed array to normal array?
- Read column of CSV file as array
- Replace atom in array of strings
- What does the dyad `=` do to boxed strings?
- Index of minimum element using J
- How can I take the outer product of string vectors in J?
- Building an array of verbs in J
- Reading in multidigit command line parameter
- Amend with bond to new data shows unexpected behaviour
- How to turn a table or matrix into a (flat) list in J
- How to run dissect in J?
- How to define selection using index function in J
- How to exit the J console?
- Find 4-neighbors using J
- Writing custom verbs in J
- How do I negate a selector in J lang?
- How to use arbitrary selector in interchange in J lang?
- different result once square root is added inside tacit
- Sum of arrays with repeated indices