POI插入图片的尺寸计算

最新推荐文章于 2024-09-09 09:34:57 发布

小鱼儿_1314

最新推荐文章于 2024-09-09 09:34:57 发布

阅读量5.5k

点赞数

分类专栏： POI

POI 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

 
  CreationHelper helper = wb.getCreationHelper();  
         Drawing drawing = sheet.createDrawingPatriarch();  
         ClientAnchor anchor = helper.createClientAnchor();  
         int pictureIdx = wb.addPicture(IOUtils.toByteArray(new FileInputStream("img.png")), Workbook.PICTURE_TYPE_PNG);  
           
         anchor.setRow1(5);  
         anchor.setCol1(3);  
         anchor.setDx1(40);  
         anchor.setDy1(8);  
           
         anchor.setRow2(5);  
         anchor.setCol2(3);  
         anchor.setDx2(741);  
         anchor.setDy2(152);  
           
         drawing.createPicture(anchor, pictureIdx);  
 

插入一个301PX * 301 px的图片代码如上。

经过计算

上面的例子列宽度16000，行高度8000

301px ≈ 701dx ≈ 144 dy
dx ≈ (301/701)*(16000/colWidth)*目标像素
dy ≈ (301/144)*(8000/r owHeight )*目标像素

    Java代码   
    
  
 /** 
      * excel的长度计算复杂有没有文档，也没找到相关蚊帐。 
      * POI EXCEL 默认colWidth 1023, rowHeight 255，单位未知，Anchor里坐标和colWidth, rowHeight有关。 
      * ExcelUtil方便我们将px值变为POI的坐标值或者宽高，但是会有些许误差。 
      * @author lazy_ 
      * 
      */  
     static class ExcelUtil{  
           
         public static  int getAnchorX(int px, int colWidth){  
             return (int) Math.round(( (double)701*16000.0/301)*((double)1/colWidth)*px);  
         }  
           
         public static int getAnchorY(int px, int rowHeight){  
             return (int) Math.round(( (double)144 * 8000/301)*((double)1/rowHeight)*px);  
         }  
           
         public static int getRowHeight(int px){  
             return (int) Math.round(((double)4480/300) * px);  
         }  
           
         public static int getColWidth(int px){  
             return (int) Math.round(((double)10971/300) * px);  
         }  
     }  

    Java代码   
    
  
 package com.lazyunderscore;  
   
 import java.io.ByteArrayInputStream;  
 import java.io.ByteArrayOutputStream;  
 import java.io.FileInputStream;  
 import java.io.FileOutputStream;  
 import java.io.IOException;  
 import java.util.ArrayList;  
 import java.util.LinkedHashMap;  
 import java.util.List;  
 import java.util.Map;  
   
 import org.apache.commons.io.IOUtils;  
 import org.apache.poi.hssf.usermodel.HSSFSheet;  
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
 import org.apache.poi.ss.util.CellRangeAddress;  
 import org.apache.poi.ss.usermodel.Cell;  
 import org.apache.poi.ss.usermodel.CellStyle;  
 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.Font;  
 import org.apache.poi.ss.usermodel.IndexedColors;  
 import org.apache.poi.ss.usermodel.Row;  
 import org.apache.poi.ss.usermodel.Workbook;  
   
 public class ExcelOperation {  
       
       
       
     public static void main(String[] args) throws IOException{  
         final int COL_WIDTH = 13000;  
         final int ROW_HEIGHT = 5000;  
         String name = "name";  
           
         String remark = "remark";  
           
         String period = "2013-01-01" + " ~ " + "2014-01-01";  
           
         String memo = "memo";  
           
         HSSFWorkbook wb = new HSSFWorkbook();  
         HSSFSheet sheet = wb.createSheet("sheet");  
           
         for(int i=0;i<4;i++){  
             sheet.setColumnWidth(i, 5000);  
         }  
         sheet.setColumnWidth(3, COL_WIDTH);  
           
         //header style  
         Font headerFont = wb.createFont();  
         headerFont.setBoldweight((short) 700);  
         CellStyle headerStyle = wb.createCellStyle();  
         headerStyle.setFont(headerFont);  
         headerStyle.setAlignment(CellStyle.ALIGN_CENTER);  
         headerStyle.setBorderBottom(CellStyle.BORDER_THIN);  
         headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());  
         headerStyle.setBorderLeft(CellStyle.BORDER_THIN);  
         headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());  
         headerStyle.setBorderRight(CellStyle.BORDER_THIN);  
         headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());  
         headerStyle.setBorderTop(CellStyle.BORDER_THIN);  
         headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());  
           
         //th style  
         Font thFont = wb.createFont();  
         thFont.setBoldweight((short) 700);  
         CellStyle thStyle = wb.createCellStyle();  
         thStyle.setFont(headerFont);  
         thStyle.setAlignment(CellStyle.ALIGN_LEFT);  
         thStyle.setBorderBottom(CellStyle.BORDER_THIN);  
         thStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());  
         thStyle.setBorderLeft(CellStyle.BORDER_THIN);  
         thStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());  
         thStyle.setBorderRight(CellStyle.BORDER_THIN);  
         thStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());  
         thStyle.setBorderTop(CellStyle.BORDER_THIN);  
         thStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());  
           
         //td style  
         CellStyle tdStyle = wb.createCellStyle();  
         tdStyle.setAlignment(CellStyle.ALIGN_LEFT);  
         tdStyle.setBorderBottom(CellStyle.BORDER_THIN);  
         tdStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());  
         tdStyle.setBorderLeft(CellStyle.BORDER_THIN);  
         tdStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());  
         tdStyle.setBorderRight(CellStyle.BORDER_THIN);  
         tdStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());  
         tdStyle.setBorderTop(CellStyle.BORDER_THIN);  
         tdStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());  
           
         LinkedHashMap<String,String> voucherInfoMap = new LinkedHashMap<String,String>();  
         voucherInfoMap.put("优惠券名称", name);  
         voucherInfoMap.put("优惠券描述", remark);  
         voucherInfoMap.put("有效期 ", period);  
         voucherInfoMap.put("使用说明", memo);  
           
         //1.头部优惠券信息  
         int rowCount = 0;  
         for(Map.Entry<String, String> entry: voucherInfoMap.entrySet()){  
             String key = entry.getKey();  
             String value = entry.getValue();  
               
             Row row = sheet.createRow(rowCount);  
              
             Cell cell0 = row.createCell(0);  
             cell0.setCellValue(key);  
             cell0.setCellStyle(headerStyle);  
               
             Cell cell1 = row.createCell(1);  
             cell1.setCellValue(value);  
             cell1.setCellStyle(headerStyle);  
             sheet.addMergedRegion(new CellRangeAddress(rowCount,rowCount,1,3));  
               
             row.createCell(2).setCellStyle(headerStyle);  
             row.createCell(3).setCellStyle(headerStyle);  
               
             rowCount++;  
         }  
         //2.列表头部  
         List<String> thNames =new ArrayList<String>();  
         thNames.add("序号");  
         thNames.add("门店");  
         thNames.add("门店名称");  
         thNames.add("二维码");  
           
         Row row = sheet.createRow(rowCount);  
         int thCellCount = 0;   
         for(String thName : thNames){  
             Cell cell = row.createCell(thCellCount);  
             cell.setCellValue(thName);  
             cell.setCellStyle(thStyle);  
             thCellCount++;  
         }  
         rowCount++;  
           
         //3.列表内容  
         row = sheet.createRow(rowCount);  
           
         row.setHeight((short) ROW_HEIGHT);  
          
           
         CreationHelper helper = wb.getCreationHelper();  
         Drawing drawing = sheet.createDrawingPatriarch();  
         ClientAnchor anchor = helper.createClientAnchor();  
         int pictureIdx = wb.addPicture(IOUtils.toByteArray(new FileInputStream("img.png")), Workbook.PICTURE_TYPE_PNG);//一个300*300的图片  
           
         anchor.setRow1(5);  
         anchor.setCol1(3);  
         anchor.setDx1(getAnchorX(5,COL_WIDTH));  
         anchor.setDy1(getAnchorY(5,ROW_HEIGHT));  
           
         anchor.setRow2(5);  
         anchor.setCol2(3);  
         anchor.setDx2(getAnchorX(305,COL_WIDTH));  
         anchor.setDy2(getAnchorY(305,ROW_HEIGHT));  
           
         drawing.createPicture(anchor, pictureIdx);  
           
         // Write the output to a file  
         ByteArrayOutputStream bos = new ByteArrayOutputStream();  
         wb.write(bos);  
         byte[] bytes = bos.toByteArray();  
         IOUtils.copy(new ByteArrayInputStream(bytes), new FileOutputStream("workbook.xls"));  
           
     }  
     private static int getAnchorX(int px, int colWidth){  
         return (int) Math.round(( (double)701*16000.0/301)*((double)1/colWidth)*px);  
     }  
       
     private static int getAnchorY(int px, int rowHeight){  
         return (int) Math.round(( (double)144 * 8000/301)*((double)1/rowHeight)*px);  
     }  
       
     private static int getRowHeight(int px){  
         return (int) Math.round(((double)4480/300) * px);  
     }  
       
     private static int getColWidth(int px){  
         return (int) Math.round(((double)10971/300) * px);  
     }  
 }  

S：完整一个POI EXCEL 测试例子如下：

    Xml代码   
    
  
 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">  
   <modelVersion>4.0.0</modelVersion>  
   <groupId>com.lazyunderscore</groupId>  
   <artifactId>Word2HtmlExample</artifactId>  
   <packaging>jar</packaging>  
   <version>1.0-SNAPSHOT</version>  
   <name>Word2HtmlExample</name>  
   <url>http://maven.apache.org</url>  
   <dependencies>  
       
 <!--     <dependency> -->  
 <!--             <groupId>org.apache.poi</groupId> -->  
 <!--             <artifactId>poi-scratchpad</artifactId> -->  
 <!--             <version>3.9</version> -->  
 <!--     </dependency> -->  
     <dependency>  
             <groupId>org.apache.poi</groupId>  
             <artifactId>poi</artifactId>  
             <version>3.9</version>  
         </dependency>  
             <dependency>  
             <groupId>commons-io</groupId>  
             <artifactId>commons-io</artifactId>  
             <version>1.4</version>  
   
         </dependency>  
       
     <dependency>  
       <groupId>junit</groupId>  
       <artifactId>junit</artifactId>  
       <version>3.8.1</version>  
       <scope>test</scope>  
     </dependency>  
   </dependencies>  
 </project>  