POI, Excel 2010 - howto remove Hyperlink from cell

[nicolas diogo]

hello

i am trying to delete the hyperlinks from cells withing a range.

and i can not understand:

- which method to use to remove the hyperlink of a cell
- if i can use Excel coordinates to define a range (eg: B2:L45)

i have managed to delete the content of cells but the hyperlinks are still there.
in fact, i have searched the documentation of POI website on google:
excel poi (remove OR delete) hyperlink site:http://poi.apache.org

but nothing has been found


these are the portion of the class that i am working on:

        
/**
* creates an instance of an Excel Workbook using WorkbookFactory 
* to avoid problems with Excel versions
*
* @param filepath is the path to a valid Excel file
* @return an instance of a workbook
*/
public Workbook workbookOpenFile(String filepath){
              
    File file = new File(filepath);
    Workbook workbook = null;
              
    try {
        workbook = WorkbookFactory.create(file);
    } catch (InvalidFormatException | IOException e) {

    System.out.println("WorkbookFactory has failed"
        + " to create a valid object for this Excel file");
    e.printStackTrace();
                       
    }
    return workbook;
}

public static void removeCellLink(Workbook workbook, int sheetIndex, 
int rowIndex,int colIndex) {
               
    //TODO: need to find method that removes the link, it is possible to 
    //remove the content of a cell by the Hyperlink persists 
    //(ie: hyperlinks still available)
               
    Cell cell = workbook.getSheetAt(sheetIndex).getRow(rowIndex)
		.getCell(colIndex);
               
    cell.???           
               
}

thanks in advance,

[Anayonkar Shivalkar]

Hi nicolas,

Welcome to CodeRanch!

Please, UseCodeTags. I've added those for you this time.

Coming to your question, the most important thing to understand is - which cell is having hyperlink.

I think Apache POI quick guide(<-click) might help you.

I guess setting the hyperlink to null should resolve the issue - but frankly, I haven't tried it yet.

I hope this helps.

[nicolas diogo]

thanks for the editing.

i have checked your suggested page. and bookmarked it.

but i am still not able to remove links from existing cells.

maybe someone else will be able to give a suggestion.

i am sure it will be something simple.

i tried the following without success:

	public void removeCellLink(
		Workbook workbook
		, int sheetIndex
		, int rowIndex
		, int colIndex) {

		CreationHelper creationHelper = workbook
								.getCreationHelper();

		Cell cell = workbook.getSheetAt(sheetIndex)
						.getRow(rowIndex)
						.getCell(colIndex);

		Hyperlink link = creationHelper
						.createHyperlink(Hyperlink.LINK_URL);
		link.setAddress("");
		
		cell.setHyperlink(link);
	}

thanks,

[Anayonkar Shivalkar]

Hi nicolas,

You are still setting an empty hyperlink.

Did you try this:

if(cell.getHyperlink() != null) {
    cell.setHyperlink(null);
}

Again, I'm not 100% sure about it (I hope it won't throw a NPE).

[nicolas diogo]

tried by it does not accept NULL

really frustrating..

[Rob Spoor]

Have you tried overriding the cell contents completely, e.g. using setCellValue?