Thanks very much for getting back with this solution
using Reflection to call this method is something that i did not consider.
i have tried to implement your suggestion but it does not actually remove
the links.
on the output file, i have all the cells with a text set (see below) but the
links are still there?
is there anything obvious that i missing here?
the method that create is:
/
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
public Workbook removeLinksFromSheet(Workbook workbook, int sheetIndex,
int rowIndex, int colIndex) throws Exception {
if (workbook.getSheetAt(sheetIndex) != null) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
Cell cell = sheet.getRow(rowIndex).getCell(colIndex);
if ( cell != null) {
Hyperlink hyperlink = cell.getHyperlink();
/*DEBUG*/
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("removed link");
System.out.println("removing link from CELL at
(s,r,c): "
+ String.valueOf(sheetIndex)
+", "
+ String.valueOf(rowIndex)
+", "
+ String.valueOf(colIndex)
);
try {
Field field = XSSFSheet.class
.getDeclaredField("hyperlinks");
field.setAccessible(true);
List<Hyperlink> hyperlinkList =
(List<Hyperlink>) field
.get(sheet);
hyperlinkList.remove(hyperlink);
} catch (NoSuchFieldException |
SecurityException
| IllegalArgumentException |
IllegalAccessException e) {
e.printStackTrace();
}
}
}
else {
throw new Exception(
"could not retrieve a valid Sheet from
this workbook");
}
return workbook;
}
/
and i have tested it with:
/
@Test
public final void testRemoveLinksFromSheet(){
/*
* calling method
* public Workbook removeLinksFromSheet(Workbook workbook, int
sheetIndex,int rowIndex, int colIndex) {
*
*/
/* loop over rows */
for(int iterRows = 0; iterRows < 6 ; iterRows++){
for(int iterCols = 0; iterCols < 52; iterCols++){
try {
workbook =
excelUtils.removeLinksFromSheet(workbook,0,iterRows,iterCols);
} catch (Exception e) {
e.printStackTrace();
}
}
}
/
-----
Try our Fund Tools demo:
http://www.kurtosys.com/fundtools
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/POI-Excel-2010-howto-remove-Hyperlink-from-cell-tp5711601p5711661.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
