POI插入图片的尺寸计算

lazy_

浏览: 148363 次

最近访客更多访客>>

techno_it

124823

Kavon

1345545983

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

比较细节看起来没有意义像浪费青春但又必须解决

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/rowHeight)*目标像素

/**
	 * 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);
		}
	}

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 测试例子如下：

<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>

分享到：