Support to create links to documents

[chrismaster]

Local links to documents are not possible with the =HYPERLINK.... Would be a great enhancement my usecase I need a function to make a clickable link to some cells which opens a pdf.

[rzymek]

Worksheet ws = wb.newWorksheet("Worksheet 1");
ws.formula(0, 0, "=HYPERLINK(\"https://github.com\")");

This code does create a clickable link in Excel 365 (v1901). It is not styled though:

xlsx generated with fastexcel: hyperlink.xlsx

[rzymek]

For reference: Some technical info on current differences between fastexcel and Excel.

sheet.xml

Excel:

    <row r="1" spans="1:1" x14ac:dyDescent="0.25">
      <c r="A1" s="1" t="str">
        <f>HYPERLINK("https://wp.pl")</f>
        <v>https://wp.pl</v>
      </c>
    </row>

Fastexcel:

    <row r="1">
      <c r="A1" s="10">
        <f>=HYPERLINK("https://wp.pl")</f>
      </c>
    </row>

POI:

    <row r="2">
      <c r="B2" s="1" t="inlineStr">
        <is>
          <t>http://github.om/</t>
        </is>
      </c>

styles.xml

Excel:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" mc:Ignorable="x14ac x16r2 xr">
  <fonts count="2" x14ac:knownFonts="1">
    <font>
      <sz val="11"/>
      <color theme="1"/>
      <name val="Calibri"/>
      <family val="2"/>
      <scheme val="minor"/>
    </font>
    <font>
      <u/>
      <sz val="11"/>
      <color theme="10"/>
      <name val="Calibri"/>
      <family val="2"/>
      <scheme val="minor"/>
    </font>
  </fonts>
  <fills count="2">
    <fill>
      <patternFill patternType="none"/>
    </fill>
    <fill>
      <patternFill patternType="gray125"/>
    </fill>
  </fills>
  <borders count="1">
    <border>
      <left/>
      <right/>
      <top/>
      <bottom/>
      <diagonal/>
    </border>
  </borders>
  <cellStyleXfs count="2">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
    <xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyNumberFormat="0" applyFill="0" applyBorder="0" applyAlignment="0" applyProtection="0"/>
  </cellStyleXfs>
  <cellXfs count="2">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="1"/>
  </cellXfs>
  <cellStyles count="2">
    <cellStyle name="Hyperlink" xfId="1" builtinId="8"/>
    <cellStyle name="Normal" xfId="0" builtinId="0"/>
  </cellStyles>
  <dxfs count="0"/>
  <tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleLight16"/>
  <extLst>
    <ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}">
      <x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1"/>
    </ext>
    <ext xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" uri="{9260A510-F301-46a8-8635-F512D64BE5F5}">
      <x15:timelineStyles defaultTimelineStyle="TimeSlicerStyleLight1"/>
    </ext>
  </extLst>
</styleSheet>

Fastexcel:

<?xml version="1.0" encoding="UTF-8"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <numFmts count="0"/>
  <fonts count="1">
    <font>
      <sz val="11.00"/>
      <color rgb="000000"/>
      <name val="Calibri"/>
    </font>
  </fonts>
  <fills count="2">
    <fill>
      <patternFill patternType="none"/>
    </fill>
    <fill>
      <patternFill patternType="gray125"/>
    </fill>
  </fills>
  <borders count="1">
    <border>
      <left/>
      <right/>
      <top/>
      <bottom/>
      <diagonal/>
    </border>
  </borders>
  <cellStyleXfs count="1">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
  </cellStyleXfs>
  <cellXfs count="1">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
  </cellXfs>
  <dxfs count="0"/>
</styleSheet>

POI:

<?xml version="1.0" encoding="UTF-8"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <numFmts count="0"/>
  <fonts count="2">
    <font>
      <sz val="11.0"/>
      <color indexed="8"/>
      <name val="Calibri"/>
      <family val="2"/>
      <scheme val="minor"/>
    </font>
    <font>
      <name val="Calibri"/>
      <sz val="11.0"/>
      <u val="single"/>
      <color indexed="12"/>
    </font>
  </fonts>
  <fills count="2">
    <fill>
      <patternFill patternType="none"/>
    </fill>
    <fill>
      <patternFill patternType="darkGray"/>
    </fill>
  </fills>
  <borders count="1">
    <border>
      <left/>
      <right/>
      <top/>
      <bottom/>
      <diagonal/>
    </border>
  </borders>
  <cellStyleXfs count="1">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
  </cellStyleXfs>
  <cellXfs count="2">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="true"/>
  </cellXfs>
</styleSheet>

[rzymek]

Apache POI sample:

try (SXSSFWorkbook wb = new SXSSFWorkbook()) {
    CreationHelper createHelper = wb.getCreationHelper();
    SXSSFSheet sheet = wb.createSheet();
    SXSSFCell cell = sheet.createRow(0).createCell(0);

    // Create link (unstyled)
    XSSFHyperlink link = (XSSFHyperlink)createHelper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://github.om/");
    cell.setCellValue(link.getAddress());
    cell.setHyperlink((XSSFHyperlink) link);

    // Set link style
    XSSFCellStyle hlinkstyle = (XSSFCellStyle) wb.createCellStyle();
    XSSFFont hlinkfont = (XSSFFont) wb.createFont();
    hlinkfont.setUnderline(XSSFFont.U_SINGLE);
    hlinkfont.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
    hlinkstyle.setFont(hlinkfont);
    cell.setCellStyle(hlinkstyle);

    try (OutputStream out = new FileOutputStream("target/hpoi.xlsx")) {
        wb.write(out);
    }
}

[ochedru]

I think @chrismaster is trying to create a link to a PDF file in e.g. the same directory as the workbook and in this case HYPERLINK does not work.