[BUG] Hyperlinks have no style

[zoeesilcock]

🐛 Bug Report

When generating documents with hyperlinks the resulting Excel file contains the links, but they aren't styled correctly. This is very confusing for the consumer of the document.

I have tried both setting the value directly on the cell and passing the value including hyperlink into addRow or addRows. I have also tried streaming writer or regular. Nothing seems to help.

Lib version: 3.9.0

Steps To Reproduce

Here is some code taken from the readme which recreates the problem:

var filename = './hyperlink-bug.xlsx';
var workbook = new ExcelJS.Workbook();
var worksheet = workbook.addWorksheet('My Sheet');

worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 },
  { header: 'D.O.B.', key: 'dob', width: 10, outlineLevel: 1 },
];

worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: { text: 'Jane Doe', hyperlink: 'http://www.mylink.com' }, dob: new Date(1965,1,7)});

worksheet.getCell('B2').value = {
  text: 'John Doe',
  hyperlink: 'http://www.mylink.com',
  tooltip: 'www.mylink.com',
};

await workbook.xlsx.writeFile(filename);

The resulting document looks like this:

The expected behaviour:

This is what the document should look like:

Possible solution (optional, but very helpful):

The only workaround I have found is to apply a color and underline to the cells. This works to an extent, but the links don't have the "Hyperlink" style in Excel and so they don't change color when clicked. This seems like a bug, if not is there some other workaround that I am missing?

[jeffersoneagley]

I'm looking for something similar. Anyone know a good workaround?

[zoeesilcock]

Here is current workaround which at least makes the links blue and underliend in Excel. Keep in mind that they just have a style applied so they don't change color when clicked for example. It's far from optimal, but it works for my needs now until the library has a mechanism for setting the correct link styles that Excel expects.

Workaround

As you can see it's pretty straight forward, we just apply a style to a list of cells. The tricky part is building this list of cells while preparing your data for ExcelJS, see the next section for an example approach.

const linkStyle = {
  underline: true,
  color: { argb: 'FF0000FF' },
};

function applyLinkStyle(sheet, cells) {
  cells.forEach(cellInfo => {
    const cell = sheet.getRow(cellInfo.row).getCell(cellInfo.column);
    if (cell) {
      cell.font = linkStyle;
    }
  });
}

Usage

Here is an example of how I tend to use that workaround. Keep in mind that this example makes assumptions about the structure of the items array, this is pretty much pseudo code, it doesn't work as is. The two parts I have commented are the important parts that need to be dropped into whatever code you are using to build your ExcelJS data.

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet(tableView.name);
const items = getMyItems();
const linkCells = [];

const rows = items.map((item, rowIndex) => {
  const row = {};
  const rowNumber = rowIndex + 1;

  item.columns.forEach((column, columnIndex) => {
    const columnNumber = columnIndex + 1;

    // Keep track of which cells contain URLs,
    // this would be wrapped in an if-statement
    // checking wether the cell contains a URL.
    linkCells.push({
      row: rowNumber,
      column: columnNumber,
    });

    row[column.key] = column.value;
  });

  return row;
});

sheets.columns = getMyColumns();
sheet.addRows(rows);

// Apply our link style to the array of cells we prepared.
applyLinkStyle(sheet, linkCells);

const buffer = await workbook.xlsx.writeBuffer();
fs.writeFile('my-example.xlsx', buffer);

[hoangnguyen-xenia]

Thank you!

[Flo0806]

Hello!

First, thx for this great library - I love it! Will this bug be fixed? It's not the biggest problem but the user experience and the behavior that the user expects when working with hyperlinks is not given with the actual version.

Greetings, Florian

[jossue45]

Use getColumn with the index column hyperlink. worksheet.getColumn(9).width = 20;

[PRR24]

I think the best solution is currently: cell.font = { ...cell.font, underline: true, color: { theme: 10 } };

10 is Excel's theme color for links.

[Flo0806]

@PRR24 Perfect! This solves the main problem! The visited state works as well, too!

Thx!