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

最新推荐文章于 2024-08-09 02:30:56 发布

weixin_33801856

最新推荐文章于 2024-08-09 02:30:56 发布

阅读量1k

点赞数

文章标签： java

原文链接：https://juejin.im/post/5b594a7e5188251b1a7b42a0

版权

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");
	}

}


复制代码