使用改变超链接位置的方式实现取消效果,效果如下。
取消后:
代码如下:
public void deleteExcel2007AllHyperLink(String filePath) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
Font hylinkFont = wb.createFont();
hylinkFont.setColor(Font.COLOR_NORMAL);
hylinkFont.setUnderline(Font.U_NONE);
int totalSheet = wb.getNumberOfSheets();
for (int i = 0; i < totalSheet; i++) {
XSSFSheet sheet = wb.getSheetAt(i);
int rowCount = sheet.getLastRowNum();
for (int r = 0; r <= rowCount; r++) {
XSSFRow row = sheet.getRow(r);
if (row != null) {
int lastCellNum = row.getLastCellNum();
XSSFCell cell = null;
for (int c = 0; c <= lastCellNum; c++) {
cell = row.getCell(c);
if (cell == null) {
continue;
}
XSSFHyperlink hylink = cell.getHyperlink();
if (hylink != null) {
//设置超链接位置为excel最后一个单元格位置,达到删除的功能
hylink.setFirstRow(1048573);
hylink.setLastRow(1048573);
hylink.setFirstColumn(16383);
hylink.setLastColumn(16383);
hylink.setLabel(null);
hylink.setAddress("");
RichTextString richStr = new XSSFRichTextString(cell.getRichStringCellValue().getString());
richStr.applyFont(hylinkFont);
cell.setCellValue(richStr);
}
}
}
}
}
saveWorkBook(wb);
}
public void printExcelHyperLinkInfo(String filePath) throws Exception {
Workbook wb = new XSSFWorkbook(new FileInputStream(filePath));
int totalSheet = wb.getNumberOfSheets();
for (int i = 0; i < totalSheet; i++) {
Sheet sheet = wb.getSheetAt(0);
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) {
StringBuffer sb = new StringBuffer();
sb.append(" 工作簿名称:").append(sheet.getSheetName());
sb.append(" 第").append((r + 1)).append("行");
sb.append(" 第").append((c + 1)).append("列");
sb.append(" 位置:").append(convertNumToLetter(c + 1)).append((r + 1));
sb.append(" 单元格内容:").append(cell.getRichStringCellValue().getString());
sb.append(" 超链接地址:").append(hylink.getAddress());
sb.append(" 超链接内容:").append(hylink.getLabel());
switch (hylink.getType()) {
case 1:
sb.append(" 超链接类型:").append("网页URL");
break;
case 2:
sb.append(" 超链接类型:").append("文档");
break;
case 3:
sb.append(" 超链接类型:").append("邮箱");
break;
case 4:
sb.append(" 超链接类型:").append("文件");
break;
default:
sb.append(" 超链接类型:").append("未知");
break;
}
sb.append(" 开始行:").append(hylink.getFirstRow()).append(" 开始列").append(hylink.getFirstColumn());
sb.append(" 结束行:").append(hylink.getLastRow()).append(" 结束列").append(hylink.getLastColumn());
System.out.println(sb.toString());
}
}
}
}
}
wb.close();
}
public String convertNumToLetter(int colNum) {
String colLetter = "";
do {
colNum--;
colLetter = ((char) (colNum % 26 + (int) 'A')) + colLetter;
colNum = (int) ((colNum - colNum % 26) / 26);
} while (colNum > 0);
return colLetter;
}
public void saveWorkBook(Workbook wb) throws Exception {
FileOutputStream fileOut = new FileOutputStream(
"f:/saveFile/temp/sys_xlsx_" + System.currentTimeMillis()
+ ".xlsx");
wb.write(fileOut);
fileOut.close();
}
全文完。