【POI】使用POI处理xlsx的cell中的超链接和插入图片和设置打印区域

使用POI对xlsx中插入超链接和插入图片

  1 package com.it.poiTest;
  2 
  3 import java.awt.image.BufferedImage;
  4 import java.io.ByteArrayOutputStream;
  5 import java.io.File;
  6 import java.io.FileNotFoundException;
  7 import java.io.FileOutputStream;
  8 import java.io.IOException;
  9 
 10 import javax.imageio.ImageIO;
 11 
 12 import org.apache.poi.common.usermodel.Hyperlink;
 13 import org.apache.poi.hslf.examples.CreateHyperlink;
 14 import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
 15 import org.apache.poi.hssf.usermodel.HSSFPatriarch;
 16 import org.apache.poi.hssf.util.HSSFColor;
 17 import org.apache.poi.ss.usermodel.Cell;
 18 import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
 19 import org.apache.poi.ss.usermodel.CreationHelper;
 20 import org.apache.poi.ss.usermodel.IndexedColors;
 21 import org.apache.poi.ss.usermodel.Row;
 22 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
 23 import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
 24 import org.apache.poi.xssf.usermodel.XSSFDrawing;
 25 import org.apache.poi.xssf.usermodel.XSSFFont;
 26 import org.apache.poi.xssf.usermodel.XSSFHyperlink;
 27 import org.apache.poi.xssf.usermodel.XSSFPicture;
 28 import org.apache.poi.xssf.usermodel.XSSFPrintSetup;
 29 import org.apache.poi.xssf.usermodel.XSSFSheet;
 30 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 31 
 32 public class AddPhotoUrl {
 33 
 34     public static void main(String[] args) {
 35         XSSFWorkbook workbook = new XSSFWorkbook();
 36         try {
 37             FileOutputStream out = new FileOutputStream("AddPhotoUrl.xlsx");
 38             XSSFSheet sheet = workbook.createSheet("1");
 39             XSSFCellStyle style = workbook.createCellStyle();
 40             XSSFFont font = workbook.createFont();
 41             font.setUnderline(XSSFFont.U_DOUBLE);
 42             font.setColor(IndexedColors.RED.getIndex());
 43             style.setFont(font);
 44             
 45             
 46             /**
 47              * cell中实现URL超链接
 48              */
 49             sheet.setColumnWidth(2, 4000);
 50             Row row = sheet.createRow(2);
 51             Cell cell = row.createCell(2);
 52             cell.setCellValue("Angel挤一挤的博客");
 53             cell.setCellStyle(style);
 54             
 55             
 56             CreationHelper createHelper = workbook.getCreationHelper();
 57             //实现超链接的类                                                        参数有4.5种，有URL，FILE等不同的类型
 58             XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
 59             link.setAddress("http://www.cnblogs.com/sxdcgaq8080/");
 60             cell.setHyperlink(link);
 61             
 62             
 63             /**
 64              * cell实现File超链接
 65              */
 66             XSSFCellStyle style2 = workbook.createCellStyle();
 67             row = sheet.createRow(3);
 68             cell = row.createCell(2);
 69             cell.setCellValue("新建文件夹.txt");
 70             XSSFFont font2 = workbook.createFont();
 71             font2.setColor(HSSFColor.YELLOW.index);
 72             style2.setFont(font2);
 73             cell.setCellStyle(style2);
 74             link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_FILE);
 75             link.setAddress("f:/新建文本.txt");//路径不对，可能会出问题
 76             cell.setHyperlink(link);
 77             
 78             
 79             /**
 80              * cell实现邮箱超链接   注意邮箱的这个address的写法
 81              */
 82             XSSFCellStyle style3 = workbook.createCellStyle();
 83             row = sheet.createRow(4);
 84             cell = row.createCell(2);
 85             cell.setCellValue("Angel的邮箱");
 86             XSSFFont font3 = workbook.createFont();
 87             font3.setColor(HSSFColor.VIOLET.index);
 88             style3.setFont(font3);
 89             link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
 90             link.setAddress("mailto:18292813417@163.com?"+"subject=Hyperlink");
 91             cell.setHyperlink(link);
 92             
 93     
 94             
 95             /**
 96              * cell实现 插入图片
 97              */
 98             row = sheet.createRow(5);
 99             cell = row.createCell(5);
100             row.setHeight((short) 1000);
101             //画图的顶级管理器
102             XSSFDrawing patriarch = sheet.createDrawingPatriarch();
103             //为图片管理器配置参数  
104             //参数1  第一个单元格中的x轴坐标
105             //参数2 第一个单元格中的y轴坐标
106             //参数3 第二个单元格中的x轴坐标
107             //参数4 第二个单元格中的y轴坐标
108             //参数5
109             //参数6
110             //参数7
111             //参数8   
112             XSSFClientAnchor anchor = new XSSFClientAnchor(100, 100, 255, 255, 13, 9, 14, 16);
113             anchor.setAnchorType(AnchorType.DONT_MOVE_DO_RESIZE);
114             
115             
116             ByteArrayOutputStream byteOutPut = new ByteArrayOutputStream();
117             //读取到图片信息
118             BufferedImage  bufferImage =ImageIO.read(new File("F:/酷我音乐.png"));
119             //将图片写入到ByteArrayOutputStream中
120             ImageIO.write(bufferImage, "png", byteOutPut);
121             //参数1 代表图片的位置信息               参数2 代表图片来源
122             patriarch.createPicture(anchor, workbook.addPicture(byteOutPut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
123             
124             
125             /**
126              * 设置打印区域
127              */
128             //设置哪一个sheet中的第几行到第几行  第几列到第几列
129             workbook.setPrintArea(0, 1, 9, 1, 10);
130             //设置纸张大小
131             sheet.getPrintSetup().setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
132             //设置是否显示网格线
133             sheet.setDisplayGridlines(true);
134             //设置是否打印网格线
135             sheet.setPrintGridlines(true);
136             
137             
138             workbook.write(out);
139             out.close();
140         } catch (FileNotFoundException e) {
141             e.printStackTrace();
142         } catch (IOException e) {
143             e.printStackTrace();
144         }
145     }
146 
147 }