公司要做导出excel功能,excel中要插入图表图片,研究了一下如何按照图片的尺寸插入excel,尽量使图片不失真
public static int savePic1(HSSFSheet sheet, HSSFWorkbook workbook, String fileUrl) throws Exception {
BufferedImage bufferImg = null;
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
try {
//excel的画图,一个sheet只能获取一个
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//bufferImg = ImageIO.read(inputStream);
//直接使用ImageIo.read方式会使图片蒙层
Image image = Toolkit.getDefaultToolkit().getImage(fileUrl);
//有缓存,需要加入这个丢弃之前的缓存
image.flush();
bufferImg = toBufferedImage(image);
ImageIO.write(bufferImg, "jpg", byteArrayOut);
//从数据的下面两行插入图片,由于使用 sheet.getLastRowNum(),所以该方式插入图片必须在数据下,并且一次只能插入一张图片,否则图片会重叠,如果同时插入多张图片则需手动计算行号
int cellNum = sheet.getLastRowNum() + 3;
//以像素计算列宽,列宽=列宽像素数/(96/2.54)
long col2 = Math.round((bufferImg.getWidth() / (96 / 2.54)) / (sheet.getColumnWidthInPixels(cellNum) / (96 / 2.54)));
//通过图片的高度计算行序号
//行高:以磅为单位。即行高是等于 Hight/28.35 厘米
int row2 = (int) Math.round((bufferImg.getHeight() / (96 / 2.54)) / (sheet.getDefaultRowHeightInPoints() / 28.35) + cellNum);
creatPic(0, 0, 0, 0, (short) 0, cellNum, (short) col2, row2, patriarch, workbook, byteArrayOut);
return row2;
} catch (Exception e) {
e.printStackTrace();
throw new Exception("插入图片失败");
} finally {
byteArrayOut.close();
}
}
/**
* @param dx1 起始单元格的x偏移量,表示直线起始位置距单元格左侧的距离;
* @param dy1 起始单元格的y偏移量,表示直线起始位置距单元格上侧的距离;
* @param dx2 终止单元格的x偏移量,表示直线终止位置距单元格右侧的距离;
* @param dy2 终止单元格的y偏移量,表示直线终止位置距单元格下侧的距离;
* @param col1 起始单元格列序号,从0开始计算;
* @param row1 起始单元格行序号,从0开始计算, col1=0,row1=0就表示起始单元格为A1;
* @param col2 终止单元格列序号,从0开始计算;
* @param row2 终止单元格行序号,从0开始计算, col2=2,row2=2就表示终止单元格为C3;
* @param patriarch Excel画图工具
* @param workbook Excel的工作书册
* @param byteArrayOut 图片输出流
*/
public static void creatPic(int dx1, int dy1, int dx2, int dy2, short col1, int rou1, short col2, int row2, HSSFPatriarch patriarch, HSSFWorkbook workbook, ByteArrayOutputStream byteArrayOut) {
HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, (short) col1, rou1, (short) col2, row2);
//一次加入多张图片则再次new XSSFClientAnchor
anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
//插入图片
patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
/**
* Image转为BufferedImage
*
* @param image
* @return
*/
public static BufferedImage toBufferedImage(Image image) {
if (image instanceof BufferedImage) {
return (BufferedImage) image;
}
// This code ensures that all the pixels in the image are loaded
image = new ImageIcon(image).getImage();
BufferedImage bimage = null;
GraphicsEnvironment ge = GraphicsEnvironment
.getLocalGraphicsEnvironment();
try {
int transparency = Transparency.OPAQUE;
GraphicsDevice gs = ge.getDefaultScreenDevice();
GraphicsConfiguration gc = gs.getDefaultConfiguration();
bimage = gc.createCompatibleImage(image.getWidth(null),
image.getHeight(null), transparency);
} catch (HeadlessException e) {
//使用了获取图形界面的方法,ge.getDefaultScreenDevice(),会抛出HeadlessException异常
}
if (bimage == null) {
// Create a buffered image using the default color model
int type = BufferedImage.TYPE_INT_RGB;
bimage = new BufferedImage(image.getWidth(null),
image.getHeight(null), type);
}
// Copy image to buffered image
Graphics g = bimage.createGraphics();
// Paint the image onto the buffered image
g.drawImage(image, 0, 0, null);
g.dispose();
return bimage;
}