Hyperlinks in XLSX cell

[ikola]

Hi Jason,

I am trying to add cell value to the xlsx template that has cell defined as href.

But after adding the cell value and its not coming as HREF.

Once I double click the cell then it becomes HREF.

can you please provide the steps to make the cell as HREF?

Here is snippet of code I am trying .

Cell cell = Context.getsmlObjectFactory().createCell();
try{

cell.setT(STCellType.fromValue(content));

}catch(IllegalArgumentException il)
{
cell.setT(STCellType.STR);
}
//value = value == null ? "N/A" : value;
cell.setV(content);

row.getC().add(cell);

I tried below but then the xlsx gets corrupted. can you provide some steps needed to make the cell HREF?

org.xlsx4j.sml.CTHyperlink link = new org.xlsx4j.sml.CTHyperlink();

if("bulletinurl".equalsIgnoreCase(vKey))
{
link.setRef("L"+l);
link.setId("rId"+id);
cell.setT(STCellType.S);
cell.setR("L"+l);
cell.setV(Integer.toString(sharedStrings.getJaxbElement().getSi().size()));
CTRst element = new CTRst();
element.setT(value);
sharedStrings.getJaxbElement().getSi().add(element);
row.getC().add(cell);
}

Regards,
IK

[jason]

Where you do link.setId("rId"+id), what value are you using for id?

Do you have a relationship set up for the hyperlink?

[ikola]

Hi Jason,

link.setId("rId"+id), what value are you using for id?
I am starting the ID value from 1 and increment by one for each row.

Do you have a relationship set up for the hyperlink?

Also adding the value to shared strings. Is there any other relation need to be set ?

If the cell is defined as HREF in input template why only on double click of cell value it activates as HREF?

Can you help with the required steps for all relations?


Thanks for the help.
IK

[jason]

You can't invent numbers for a rel ID out of thin air! They need to match an actual rel of the correct type.

To see what you need, create an xlsx containing a hyperlink in Excel, then inspect it (run it through PartsList, and unzip to look at the parts).

If it is anything like the docx case, the docx sample https://github.com/plutext/docx4j/blob/ ... kTest.java should help.

[ikola]

Thank you after looking carefully its working now .Here are the required things .

Worksheet ws1 = sheet1.getJaxbElement();
SheetData data1 = ws1.getSheetData();
Row row = Context.getsmlObjectFactory().createRow();
row.setCustomFormat(true);

Cell cell = Context.getsmlObjectFactory().createCell();
cell.setT(STCellType.STR);
org.docx4j.relationships.ObjectFactory factory =
new org.docx4j.relationships.ObjectFactory();

org.docx4j.relationships.Relationship rel = factory.createRelationship();
rel.setType( Namespaces.HYPERLINK );
rel.setTarget("http://cisco.com");
rel.setTargetMode("External");
RelationshipsPart rp1 = (RelationshipsPart) relationMap.get("/xl/worksheets/sheet1.xml");

rp1.addRelationship(rel);



org.xlsx4j.sml.CTHyperlink link = new org.xlsx4j.sml.CTHyperlink();

link.setRef("A"+3);
link.setId("rId3");
cell.setT(STCellType.S);
cell.setR("A"+3);
cell.setS(new Long(1));
cell.setV(Integer.toString(sharedStrings.getJaxbElement().getSi().size()));
CTRst element = new CTRst();
element.setT("http://yahoo.com");
sharedStrings.getJaxbElement().getSi().add(element);
ws1.getHyperlinks().getHyperlink().add(link);
row.getC().add(cell);
data1.getRow().add(row);