java web中导出excel数据是常见的功能,最近遇到一个需求是在excel中插入图片。处理excel及其他微软办公系列软件常用的就是apache poi,它也是支持图片插入的。插入图片最主要的用到HSSFClientAnchor,HSSFClientAnchor的文档介绍如下:
public HSSFClientAnchor(int dx1,
int dy1,
int dx2,
int dy2,
short col1,
int row1,
short col2,
int row2)
Creates a new client anchor and sets the top-left and bottom-right coordinates of the anchor. Note: Microsoft Excel seems to sometimes disallow higher y1 than y2 or higher x1 than x2, you might need to reverse them and draw shapes vertically or horizontally flipped!
Parameters:
dx1 - the x coordinate within the first cell.//定义了图片在第一个cell内的偏移x坐标,既左上角所在cell的偏移x坐标,一般可设0dy1 - the y coordinate within the first cell.//定义了图片在第一个cell的偏移y坐标,既左上角所在cell的偏移y坐标,一般可设0dx2 - the x coordinate within the second cell.//定义了图片在第二个cell的偏移x坐标,既右下角所在cell的偏移x坐标,一般可设0dy2 - the y coordinate within the second cell.//定义了图片在第二个cell的偏移y坐标,既右下角所在cell的偏移y坐标,一般可设0col1 - the column (0 based) of the first cell.//第一个cell所在列,既图片左上角所在列row1 - the row (0 based) of the first cell.//图片左上角所在行col2 - the column (0 based) of the second cell.//图片右下角所在列row2 - the row (0 based) of the second cell.//图片右下角所在行
具体demo 如下:
[java] view plain copy
- import java.awt.image.BufferedImage;
- import java.io.ByteArrayOutputStream;
- import java.io.File;
- import java.io.FileOutputStream;
- import javax.imageio.ImageIO;
- import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
- import org.apache.poi.hssf.usermodel.HSSFPatriarch;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- public class ExcelExport {
- public static void main(String[] args) {
- FileOutputStream fileOut = null;
- BufferedImage bufferImg = null;
- try {
- ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
- //加载图片
- bufferImg = ImageIO.read(new File("e:/1.jpg"));
- ImageIO.write(bufferImg, "jpg", byteArrayOut);
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet1 = wb.createSheet("sheet1");
- HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
- /**
- dx1 - the x coordinate within the first cell.//定义了图片在第一个cell内的偏移x坐标,既左上角所在cell的偏移x坐标,一般可设0
- dy1 - the y coordinate within the first cell.//定义了图片在第一个cell的偏移y坐标,既左上角所在cell的偏移y坐标,一般可设0
- dx2 - the x coordinate within the second cell.//定义了图片在第二个cell的偏移x坐标,既右下角所在cell的偏移x坐标,一般可设0
- dy2 - the y coordinate within the second cell.//定义了图片在第二个cell的偏移y坐标,既右下角所在cell的偏移y坐标,一般可设0
- col1 - the column (0 based) of the first cell.//第一个cell所在列,既图片左上角所在列
- row1 - the row (0 based) of the first cell.//图片左上角所在行
- col2 - the column (0 based) of the second cell.//图片右下角所在列
- row2 - the row (0 based) of the second cell.//图片右下角所在行
- */
- HSSFClientAnchor anchor = new HSSFClientAnchor(-100, 0, 0, 0,(short) 2, 2, (short) 5, 8);
- //插入图片
- patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
- fileOut = new FileOutputStream("e:/excel.xls");
- // 输出文件
- wb.write(fileOut);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
关于dx1的设置的说明,dx2,dy1等都是类似的
关于一个excel设置设置多张图片的demo
[java] view plain copy
- package com.poi;
- import java.awt.image.BufferedImage;
- import java.io.ByteArrayOutputStream;
- import java.io.File;
- import java.io.FileOutputStream;
- import javax.imageio.ImageIO;
- import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
- import org.apache.poi.hssf.usermodel.HSSFPatriarch;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- public class ExcelExport {
- public static void main(String[] args) {
- FileOutputStream fileOut = null;
- BufferedImage bufferImg = null;
- try {
- ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
- //加载图片
- bufferImg = ImageIO.read(new File("e:/1.jpg"));
- ImageIO.write(bufferImg, "jpg", byteArrayOut);
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet1 = wb.createSheet("sheet1");
- HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
- HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 2, 2, (short) 5, 8);
- //插入图片 1
- patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
- //图片2
- anchor = new HSSFClientAnchor(200, 0, 0, 0,(short) 2, 9, (short) 5, 15);
- patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
- fileOut = new FileOutputStream("e:/excel.xls");
- // 输出文件
- wb.write(fileOut);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
总体来说使用poi还是很方便的。