Java处理Excel公式，外部超链接（Url、文件）-白红宇

Excel HYPERLINK 语法

其格式为：HYPERLINK(link_location，friendly_name).
其中：HYPERLINK 为函数名, link_location 为链接位置, friendly_name 为显示文本.
代码示例
import org.apache.poi.xssf.usermodel.*;import java.io.File;import java.io.FileOutputStream;public class TestExcelFormula {	public static void main(String[] args) throws Exception {		formula();	}	public static void formula() throws Exception {		XSSFWorkbook workbook = new XSSFWorkbook();		XSSFSheet sheet = workbook.createSheet("formula");		int rowIdx = 0;		// row0		XSSFRow row = sheet.createRow(rowIdx++);		XSSFCell cell_00 = row.createCell(0);		XSSFCell cell_01 = row.createCell(1);		cell_00.setCellValue("A =");		cell_01.setCellValue(2);		// row1		row = sheet.createRow(rowIdx++);		XSSFCell cell_11 = row.createCell(0);		XSSFCell cell_12 = row.createCell(1);		cell_11.setCellValue("B =");		cell_12.setCellValue(4);		// row2 reate SUM formula		row = sheet.createRow(rowIdx++);		XSSFCell cell_20 = row.createCell(0);		XSSFCell cell_21 = row.createCell(1);		XSSFCell cell_22 = row.createCell(2);		cell_20.setCellValue("Total =");		cell_21.setCellType(XSSFCell.CELL_TYPE_FORMULA);		cell_21.setCellFormula("SUM(B1:B2)");		cell_22.setCellValue("SUM(B1:B2)");		// row3 create POWER formula		row = sheet.createRow(rowIdx++);		XSSFCell cell_30 = row.createCell(0);		XSSFCell cell_31 = row.createCell(1);		XSSFCell cell_32 = row.createCell(2);		cell_30.setCellValue("POWER =");		cell_31.setCellType(XSSFCell.CELL_TYPE_FORMULA);		cell_31.setCellFormula("POWER(B1,B2)");		cell_32.setCellValue("POWER(B1,B2)");		// row4 Create MAX formula		row = sheet.createRow(rowIdx++);		XSSFCell cell_40 = row.createCell(0);		XSSFCell cell_41 = row.createCell(1);		XSSFCell cell_42 = row.createCell(2);		cell_40.setCellValue("MAX =");		cell_41.setCellType(XSSFCell.CELL_TYPE_FORMULA);		cell_41.setCellFormula("MAX(B1,B2)");		cell_42.setCellValue("MAX(B1,B2)");		// row5 Create FACT formula		row = sheet.createRow(rowIdx++);		XSSFCell cell_50 = row.createCell(0);		XSSFCell cell_51 = row.createCell(1);		XSSFCell cell_52 = row.createCell(2);		cell_50.setCellValue("FACT =");		cell_51.setCellType(XSSFCell.CELL_TYPE_FORMULA);		cell_51.setCellFormula("FACT(B2)");		cell_52.setCellValue("FACT(B2)");		// row6 Create SQRT formula		row = sheet.createRow(rowIdx++);		XSSFCell cell_60 = row.createCell(0);		XSSFCell cell_61 = row.createCell(1);		XSSFCell cell_62 = row.createCell(2);		cell_60.setCellValue("SQRT =");		cell_61.setCellType(XSSFCell.CELL_TYPE_FORMULA);		cell_61.setCellFormula("SQRT(B4)");		cell_62.setCellValue("SQRT(B4)");		// 单元格居中样式		XSSFCellStyle style = workbook.createCellStyle();		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);		// row7 超链接 打开 Internet URL		row = sheet.createRow(rowIdx++);		// 设置行高		row.setHeightInPoints(100);		XSSFCell cell_70 = row.createCell(0);		XSSFCell cell_71 = row.createCell(1);		XSSFCell cell_72 = row.createCell(2);		cell_70.setCellValue("Internet URL");		cell_71.setCellType(XSSFCell.CELL_TYPE_FORMULA);		cell_71.setCellFormula("HYPERLINK(\"https://www.baidu.com\", \"百度\")");		cell_71.setCellStyle(style);		cell_72.setCellValue("https://www.baidu.com");		// row8 超链接 打开（本地）文件		String relativeUrl = "..\\picture\\user_001\\test.jpg";		row = sheet.createRow(rowIdx++);		row.setHeightInPoints(30);		XSSFCell cell_80 = row.createCell(0);		XSSFCell cell_81 = row.createCell(1);		XSSFCell cell_82 = row.createCell(2);		cell_80.setCellValue("打开（本地）文件");		cell_81.setCellType(XSSFCell.CELL_TYPE_FORMULA);		cell_81.setCellFormula("HYPERLINK(\"..\\picture\\user_001\\test.jpg\", \"test.jpg\")");		cell_81.setCellStyle(style);		cell_82.setCellValue(relativeUrl);		// 设置列宽		sheet.setColumnWidth(0,	5000);		sheet.setColumnWidth(1,	4000);		sheet.setColumnWidth(2, relativeUrl.getBytes().length * 500);		workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();				FileOutputStream out = new FileOutputStream(new File("formula.xlsx"));		workbook.write(out);		out.close();		System.out.println("fromula.xlsx written successfully");	}}复制代码