CODEWITHSUNDEEP
excelgrailsgroovyapache-poi
AntennaQuad
AntennaQuad

Reputation: 13

Grails Excel importing Hyperlinks

I am trying to read some data from a .xslx spreadsheet in Grails using the excel-import plugin (which is based on the Apache POI).

It works well with all data types except the main title which has a hyperlink (click-able). -When I simply map the column into a String (code below), I get only the title without the hyperlink. I tried specifying the expected type as "hyperlink" (from org.apache.poi.ss.usermodel) but it didn't work.

I am using the code provided by the excel-import sample project

static Map CONFIG_BOOK_COLUMN_MAP = [
        sheet: 'Sheet1',
        startRow: 1,
        columnMap: [
                'A': 'title',
                'C': 'createdBy',
                'D': 'createdDate', 
        ]
]

When I export the Excel spreadsheet into the .csv format, the hyperlink gets lost, but when I export it to XML the Hyperlink is preserved (but it seems like the data is nested within the cell?) like in this sample:

<Row ss:AutoFitHeight="0" ss:Height="14.99">
   <Cell ss:HRef="http://longLink..." ss:StyleID="ce2">
        <Data ss:Type="String">Some Text</Data></Cell>

I'm fairly new to grails and groovy and I'm stuck for the last few days trying to solve this problem. Is there a way to do this, because I wouldn't want to convert all my XLSX files to XML and then parse those huge files. I've tried searching if someone had the same problem but I couldn't find anything similar.

Upvotes: 1

Views: 614

Answers (1)

tim_yates
tim_yates

Reputation: 171084

Looking at the source of the plugin, it looks like it's not possible (someone might know differently). Maybe post an issue requesting the feature -- or add the functionality and submit a pull request if you're feeling brave ;-)

It's possible to do this using raw POI though like so:

import org.apache.poi.hssf.usermodel.*

// Load a workbook with a hyperlink to google in cell 0,0
new File( '/tmp/test.xls' ).withInputStream { ins ->
    new HSSFWorkbook( ins ).with { workbook ->
        workbook.getSheetAt( 0 )
                .getRow( 0 )
                .getCell( 0 ).hyperlink.address == 'http://www.google.com'
    }
}

Upvotes: 1

Related Questions