[简单]poi删除excel 2007超链接

最新推荐文章于 2022-05-01 20:07:58 发布

最新推荐文章于 2022-05-01 20:07:58 发布 · 427 阅读

文章标签：

#java

poi 专栏收录该内容

39 篇文章

订阅专栏

本文介绍了一种通过解析sheet.xml文件来移除Excel 2007中所有超链接的方法。该方法涉及使用Java编程语言，并且需要两次打开文件：一次是为了读取并修改工作簿的内容，另一次则是为了直接操作XML文件删除超链接。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

采用解析sheet.xml方式删除超链接，缺点是要打开文件2次,代码如下:

public void removeExcel2007AllHyperLink(String filePath) throws Exception {
		OPCPackage ocPkg = OPCPackage.open(new FileInputStream(filePath));
		Workbook wb = new XSSFWorkbook(ocPkg);
		int totalSheet = wb.getNumberOfSheets();
		String savePath = "f:/saveFile/temp/sys_xlsx_"+ System.currentTimeMillis() + ".xlsx";
		CellStyle cellStype=wb.createCellStyle();
		Font defaultFont = wb.createFont();
		defaultFont.setColor(IndexedColors.BLACK.getIndex());
		defaultFont.setUnderline(Font.U_NONE);
		cellStype.setFont(defaultFont);
		for (int i = 0; i < totalSheet; i++) {
			Sheet sheet = wb.getSheetAt(i);
			int rowCount = sheet.getLastRowNum();
			for (int r = 0; r <= rowCount; r++) {
				Row row = sheet.getRow(r);
				if (row != null) {
					int lastCellNum = row.getLastCellNum();
					Cell cell = null;
					for (int c = 0; c <= lastCellNum; c++) {
						cell = row.getCell(c);
						if (cell == null) {
							continue;
						}
						Hyperlink hylink = cell.getHyperlink();
						if (hylink != null) {
							RichTextString richStr = new XSSFRichTextString(cell
									.getRichStringCellValue().getString());
							richStr.applyFont(defaultFont);
							cell.setCellValue(richStr);
						}
					}
				}
			}
		}
		saveWorkBook(wb, savePath);
		ocPkg = OPCPackage.open(new FileInputStream(savePath));
		for (int sheetIndex = 0; sheetIndex < totalSheet; sheetIndex++) {
			List<PackagePart> rs = ocPkg.getPartsByName(Pattern
					.compile("/xl/worksheets/sheet" + (sheetIndex + 1)
							+ "\\.xml"));
			for (PackagePart pkgPart : rs) {
				if (pkgPart instanceof ZipPackagePart) {
					ZipPackagePart zipPart = (ZipPackagePart) pkgPart;
					Document doc = getDocument(zipPart.getInputStream());
					if (doc != null) {
						NodeList hyperlinks = doc
								.getElementsByTagName("hyperlinks");
						for (int i = hyperlinks.getLength() - 1; i >= 0; i--) {
							Node hyperlink = hyperlinks.item(i);
							hyperlink.getParentNode().removeChild(hyperlink);
						}
						StreamHelper.saveXmlInStream(doc,
								zipPart.getOutputStream());
					}
				}
			}
		}
		wb = new XSSFWorkbook(ocPkg);
		saveWorkBook(wb, savePath);
	}

	public Document getDocument(InputStream inStream) {
		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		DocumentBuilder db = null;
		Document doc = null;
		try {
			db = dbf.newDocumentBuilder();
			doc = db.parse(inStream);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return doc;
	}

	public void saveWorkBook(Workbook wb, String filePath) throws Exception {
		FileOutputStream fileOut = new FileOutputStream(filePath);
		wb.write(fileOut);
		fileOut.close();
	}

全文完。