POI】使用 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 }