Read Hyperlink From Excel

This example demonstrates how to read hyperlinks embedded in Excel worksheets using DataPipeline. Excel files can contain hyperlinks pointing to external URLs, files, email addresses, or internal sheet locations. DataPipeline automatically extracts the hyperlink metadata and makes it available with fields. This feature is disabled by default and can be enabled by setting readMetadata flag as true to ExcelReader.

Using ExcelReader, DataPipeline can read both the cell value and the hyperlink target. This is useful for data ingestion pipelines, reporting systems, data lineage, or spreadsheet-driven workflows.

DataPipeline can read all types of hyperlinks from excels:

1. Document – A hyperlink that points to another cell or location within the same Excel workbook (e.g., Sheet2!A10).
2. URL – A hyperlink that opens an external webpage using an HTTP/HTTPS address.
3. Email – A hyperlink that opens a new email draft using a mailto: address.
4. File – A hyperlink that opens a file or folder on the local system or network path.

Java Code

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.core.Field;
import com.northconcepts.datapipeline.core.ProxyReader;
import com.northconcepts.datapipeline.core.Record;
import com.northconcepts.datapipeline.csv.CSVWriter;
import com.northconcepts.datapipeline.excel.ExcelDocument;
import com.northconcepts.datapipeline.excel.ExcelDocument.ProviderType;
import com.northconcepts.datapipeline.excel.ExcelFieldMetadata;
import com.northconcepts.datapipeline.excel.ExcelHyperlink;
import com.northconcepts.datapipeline.excel.ExcelReader;
import com.northconcepts.datapipeline.job.Job;

public class ReadHyperlinksFromExcel {

    private static File INPUT_FILE = new File("example/data/input", "product_list.xlsx");
    private static File OUTPUT_FILE = new File("example/data/output", "product_list.csv");

    public static void main(String[] args) {
        ExcelDocument document = new ExcelDocument(ProviderType.POI_XSSF_SAX)
                .open(INPUT_FILE);
        DataReader reader = new ExcelReader(document)
                .setFieldNamesInFirstRow(true)
                .setAutoCloseDocument(true)
                .setReadMetadata(true);

        reader = new ExcelHyperlinkReader(reader);

        DataWriter writer = new CSVWriter(OUTPUT_FILE);

        Job.run(reader, writer);
    }
}

class ExcelHyperlinkReader extends ProxyReader {

    private ExcelFieldMetadata metadata = new ExcelFieldMetadata();

    public ExcelHyperlinkReader(DataReader dataReader) {
        super(dataReader);
    }

    @Override
    protected Record interceptRecord(Record record) throws Throwable {
        Field field = record.getField("product_name");
        metadata.setField(field);
        
        ExcelHyperlink excelHyperlink = metadata.getExcelHyperlink();
        record.addField("product_link", excelHyperlink == null ? null : excelHyperlink.getLocation());
        
        return super.interceptRecord(record);
    }

}

Code Walkthrough

1. First, ExcelDocument is created to open product_list.xlsx, which contains 
2. ExcelDocument.ProviderType indicates an Excel provider which is used to open the input Excel file. For this example POI_XSSF_SAX provider is selected.
3. Set readMetadata as true, then only ExcelReader will ready hyperlink
4. ExcelDocument.open() opens the specified File or InputStream.
5. ExcelReader is created to read from the input Excel file.
6. Use ProxyReader to intercept all the records and add a new field for each hyperlink. Here with input excel file, a field product has a hyperlink which we will write to CSV file as a new field - product_link.
7. Data are transferred from ExcelReader to the console via Job.run() method. See how to compile and run data pipeline jobs

Output CSV File

product_id,product_name,price,product_link
1,Apple iPhone 15,799,https://www.apple.com/iphone-15/
2,Samsung Galaxy S24,899,https://www.samsung.com/global/galaxy/galaxy-s24/
3,Sony WH-1000XM5 Headphones,399,https://electronics.sony.com/headphones/all-headphones/p/wh1000xm5
4,Nintendo Switch OLED,349,https://www.nintendo.com/switch/oled-model/
5,Dell XPS 13 Laptop,999,https://www.dell.com/en-us/shop/laptops/xps-13-laptop/spd/xps-13-9340-laptop
6,Logitech MX Master 3S Mouse,99,https://www.logitech.com/products/mice/mx-master-3s.910-006556.html
7,Fitbit Charge 6,159,https://www.fitbit.com/global/us/products/trackers/charge6
8,Bose QuietComfort Ultra Earbuds,299,https://www.bose.com/p/quietcomfort-ultra-earbuds/
9,GoPro HERO12 Black,399,https://gopro.com/en/us/shop/cameras/hero12-black/CHDHX-121-master.html
10,Anker PowerCore 20k Power Bank,59,https://www.anker.com/products/a1271