excel导出图片中的单位问题

参考文档

Java POI excel 操作之图片导出 - 掘金 (juejin.cn)
POI精确设置Excel的行高和列宽 - dts - 博客园 (cnblogs.com)
Apache POI - Component Overview
[译]Points、inches和EMUs：Office Open XML中的度量单位 - 知乎 (zhihu.com)

excel 各种单位介绍

1英寸=72磅=25.4毫米=1440缇
1 cm = 360000 EMUs
emu 是一种虚拟单位，便于

操作方案

居中计算工具类

 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
 String path = "D://1.jpg";
 BufferedImage image = ImageIO.read(new File(path));
 InputStream is = new FileInputStream(new File(path));
 byte[] bytes = IOUtils.toByteArray(is);

 XSSFWorkbook  wb = new XSSFWorkbook();
 XSSFSheet sheet = wb.createSheet("测试生成图片");
 sheet.setDefaultRowHeightInPoints(80);
 sheet.setColumnWidth(1,80*256);

 XSSFRow row = sheet.createRow(1);
 XSSFCell cell = row.createCell(1);
 int[] anchorArray = calCellAnchorCenter(row.getHeightInPoints(),sheet.getColumnWidth(1),image);）
 final XSSFDrawing drawing = sheet.createDrawingPatriarch();
 XSSFClientAnchor anchor = new XSSFClientAnchor(10,10,10,10, (short) 1, 1, (short) 2, 2);
 anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
 drawing.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
 fileOut = new FileOutputStream("23.xlsx");
 // 写入excel文件
 wb.write(fileOut);
static Integer PADDING = Units.toEMU(10);
 /**
   *
   * @param height 单位磅 就是getPointheight
   * @param width  单位字符
   * @param image  
   * @return
   */
  public static int[] calCellAnchorCenter(float height, int width, BufferedImage image) {
      int cellWidthEMU = Units.columnWidthToEMU(width);
      int cellHeightEMU = Units.TwipsToEMU((short) (height * 20));
      boolean flag = image.getHeight()>image.getWidth();
      int realHeightEMU = 0;
      int realWidthEMU = 0;
      // 判断那边先碰上
      if(flag){
          // 按高度放大
          realWidthEMU = (int) (1.0*(cellHeightEMU-2*PADDING)*image.getWidth()/image.getHeight());
          int x = (int) ((cellWidthEMU-realWidthEMU)/2.0);
          return new int[]{x,PADDING,-x,-PADDING};
      }else{
          realHeightEMU = (int) (1.0*(cellWidthEMU-2*PADDING)*image.getHeight()/image.getWidth());
          int y = (int) ((cellHeightEMU-realHeightEMU)/2.0);
          return new int[]{PADDING,y,-PADDING,-y};
      }
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

官网导出类

地址

常见问题

1. XSSFClientAnchor 前四个参数单位是像素
2. POINT_DPI 常见于打印机 PIXEL_DPI 常见于电脑
3. resize是放大或缩小的函数。用了这个函数后，HSSFClientAnchor构造函数中的图片显示的终了cell位置就不起作用了。