How to remove a hyperlink in poi 3.8?

Question

I have a problem reading an excel file containing the hyperlink text in poi.

Data like this (excel file):
| 1 | type | category | job_type | position | name | Email
| 2 | Test | developer | part time | manager | hong | ASDF ## @ dsaf.com (hyperlink)
| 3 | Test | developer | part time | manager | asde | [e-mail] (hyperlink)
| 4 | Test | developer | part time | manager | asde | aaaaaaa (not a hyperlink)

To create a workbook object, I use the WorkbookFactory.create(InputStream inp) method.
The codes are here:

 public POIExcelImport(String name, InputStream inputStream) throws ExcelImportException { super(name, null); try { logger.debug("before work : {}", this.workbook); this.workbook = WorkbookFactory.create(inputStream);// exception } catch (InvalidFormatException e) { throw new ExcelImportException(e); } catch (IOException e) { throw new ExcelImportException(e); } if(XSSFWorkbook.class.isAssignableFrom(workbook.getClass())) this.type = ExcelFileType.XLSX; else this.type = ExcelFileType.XLS; } 

When I call the create method, an Exception is thrown.

 java.lang.IllegalStateException: The hyperlink for cell F2 references relation rId1, but that didn't exist! at org.apache.poi.xssf.usermodel.XSSFHyperlink.<init>(XSSFHyperlink.java:71) ~[poi-ooxml-3.8.jar:3.8] at org.apache.poi.xssf.usermodel.XSSFSheet.initHyperlinks(XSSFSheet.java:204) ~[poi-ooxml-3.8.jar:3.8] at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:157) ~[poi-ooxml-3.8.jar:3.8] at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:129) ~[poi-ooxml-3.8.jar:3.8] at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:269) ~[poi-ooxml-3.8.jar:3.8] at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:159) ~[poi-ooxml-3.8.jar:3.8] at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:183) ~[poi-ooxml-3.8.jar:3.8] at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:73) ~[poi-ooxml-3.8.jar:3.8] at dreaminfra.ipams.common.excel.poi.POIExcelImport.<init>(POIExcelImport.java:49) ~[ipams-core-1.0.0.jar:na] 

I want to remove the hyperlink, but there are only problems associated with creating the hyperlink.
I have no idea, are there any ideas?

Answer 1

I found this to work on my system:

 ICell oCell = workbook.GetSheetAt(0).GetRow(0).GetCell(0); IHyperlink oLink = oCell.Hyperlink; oLink.setFirstRow(0); oLink.setLastRow(0); oLink.setFirstColumn(0); oLink.setLastColumn(0); oLink.setLabel(null); oLink.setAddress(""); 

Note. I mainly work with NPOI (C # version) and not with POI (Java version), but it seems to work the same. Also, be sure to keep .setAddress() last.

EDIT: Unfortunately, this apparently causes documents that, for some reason, cannot be saved by Excel. (excel detects damage when saved). This practically does not work in practice, it seems that someone will have to fix the NPOI repository for this.