HyperlinkAttribute for LoadFromCollection

[swmal]

If you recall, I was looking for a way to specify that a column is a hyperlink, and then have another column contain the actual link. I had implemented mine as a simple attribute:

[AttributeUsage(AttributeTargets.Property)]
public class HyperlinkAttribute : Attribute
{
  public string HyperLinkField { get; init; }

  public HyperlinkAttribute(string hyperLinkField) => HyperLinkField = hyperLinkField;
}

And then I’d use it like so:

   [EpplusTableColumn(Header = "Requested For", Order = 2), Hyperlink(nameof(RequestedForEmail))]
   public string RequestedFor { get; init; }

   [EpplusIgnore]
   public string RequestedForEmail { get; init; }

That means I have to have custom processing though to build out the table, and I’m having to explicitly specify the ordering again, vs. being able to use your custom attribute for sort order of the table itself.

[grosch-intl]

I'm manually doing things like this. Ideally, LoadFromCollection would handle the hyperlink stuff for me, without requiring me to explicitly specify the order.

    internal static void AddSheet<T>(this ExcelPackage package, IEnumerable<T> data, string sheetName = "Sheet1")
    {
        var sheet = package.Workbook.Worksheets.Add(sheetName);
        sheet.Cells["A1"].LoadFromCollection(data);

        var hyperlinkMap = new Dictionary<int, string>();
        var genericType = typeof(T);

        foreach (var property in genericType.GetProperties(BindingFlags.Public | BindingFlags.Instance)) {
            var column = property.GetCustomAttribute<EpplusTableColumnAttribute>();
            if (column is null)
                continue;

            var link = property.GetCustomAttribute<HyperlinkAttribute>();
            if (link != null)
                hyperlinkMap.Add(column.Order, link.HyperLinkField);
        }

        if (!hyperlinkMap.Any())
            return;

        // It has to start at 2 because the table got created and thus line 1 is now line 2.
        var rowNumber = 2;

        foreach (var row in data) {
            foreach (var entry in hyperlinkMap) {
                var link = genericType.GetProperty(entry.Value)?.GetValue(row, null)?.ToString();
                if (link is null || !Uri.TryCreate(link, UriKind.Absolute, out var hyperlink))
                    continue;

                var cell = sheet.Cells[rowNumber, entry.Key];
                cell.Hyperlink = hyperlink;
                cell.Style.Font.UnderLine = true;
                cell.Style.Font.Color.SetColor(Color.Blue);
            }

            rowNumber++;
        }
    }

[swmal]

I had a look at this and Hyperlinks are supported by LoadFromCollection if the property type is either System.Uri or OfficeOpenXml.ExcelHyperLink. See example from our unit tests below, where EMailAddress is ignored and MailTo becomes a hyperlink in the spreadsheet:

internal class UrlClass : BClass
{
    [EpplusIgnore]
    public string EMailAddress { get; set; }
    [EpplusTableColumn(Order = 5, Header = "My Mail To")]
    public ExcelHyperLink MailTo
    {
        get
        {
            var url = new ExcelHyperLink("mailto:" + EMailAddress);
            url.Display = Name;
            return url;
        }
    }
    [EpplusTableColumn(Order = 4)]
    public Uri Url
    {
        get;
        set;
    }
}

See this wiki page for docs.

[grosch-intl]

@swmal Mats, I was able to successfully implement this. However, the data doesn't look like a hyperlink in the generated XLSX file. If I click it, then it open the email. However, it's not the normal blue/underlined that a hyperlink would be. If I right-click on the cell and choose Edit Hyperlink, and then immediately hit OK, then the cell looks like a normal hyperlink.

Also, this seems to be messing with my column ordering again. I specified the email to be last, but it's showing up first in a nested table.

[swmal]

@grosch-intl, sorry for the late response, but we had to investigate this a bit.

With EPPlus 6 you have to solve this the following way:

[TestMethod]
public void HyperlinkTest1()
{
    var hl1 = new ExcelHyperLink("mailto:[email protected]");
    hl1.Display = "[email protected]";
    using (var package = new ExcelPackage())
    {
        var items = new List<OuterWithHyperLink>
        {
            new OuterWithHyperLink
            {
                Number = 1,
                Name = "First",
                Email1 = new Uri("mailto:[email protected]"),
                Email2 = hl1
            },
            new OuterWithHyperLink
            {
                Number = 1,
                Name = "Second",
                Email1 = new Uri("mailto:[email protected]"),
                Email2 = hl1
            }
        };
        var sheet = package.Workbook.Worksheets.Add("test");
        var range = sheet.Cells["A1"].LoadFromCollection(items);
        var table = sheet.Tables.GetFromRange(range);
        // create hyperlink style
        var hls = package.Workbook.Styles.CreateNamedStyle("Hyperlink");
        hls.Style.Font.Color.SetColor(Color.Blue);
        hls.Style.Font.UnderLine = true;

        table.Columns[0].DataCellStyleName = "Hyperlink";
        table.Columns[1].DataCellStyleName = "Hyperlink";
        package.SaveAs(@"c:\Temp\LoadFromColl_Hyperlinks.xlsx");
    }
}

We cannot change the behaviour in EPPlus 6 (the current version) because it will require breaking changes, but we are considering to improve this in EPPlus 7 which could include a stylename property on the attribute and a default style built in for hyperlinks.

I did some testing with various column ordering for hyperlink properties but could not replicate any issue. Can you provide some more details?

[grosch-intl]

Here's a self-contained example I put in LINQPad to show the issue. The columns come out in the wrong order.

void Main() {
	var space = new ExcelSpaceRow {
		Owner = new() {
			Active = true,
			Email = "[email protected]",
			Id = 1,
			Name = "Mr. Foo"
		},
		Something = ""
	};

	using var package = new ExcelPackage();
	var sheet = package.Workbook.Worksheets.Add("Sheet1");
	sheet.Cells["A1"].LoadFromCollection(new ExcelSpaceRow[] { space });

	package.SaveAs(@"c:\users\grosch\desktop\example.xlsx");
}


[EpplusTable(AutofitColumns = true, PrintHeaders = true, TableStyle = TableStyles.Medium2),
	EPPlusTableColumnSortOrder(Properties = new string[] {
	nameof(Id), nameof(Name), nameof(EmailLink)
})
]
public class EmployeeDTO {
	[EpplusIgnore]
	public required bool Active { get; init; }

	[EpplusIgnore]
	public required string? Email { get; init; }

	[EpplusTableColumn(Header = "Email")]
	public ExcelHyperLink? EmailLink
	{
		get
		{
			if (Email is null)
				return null;

			var url = new ExcelHyperLink($"mailto:{Email}");
			url.Display = Email;
			return url;
		}
	}

	[EpplusTableColumn(Header = "WWID")]
	public required int Id { get; init; }

	[EpplusTableColumn(Header = "Name")]
	public required string? Name { get; init; }
}

[
EpplusTable(AutofitColumns = true, PrintHeaders = true, TableStyle = TableStyles.Medium2),
EPPlusTableColumnSortOrder(Properties = new string[] {
	 nameof(Owner), nameof(Something)})]
public sealed class ExcelSpaceRow {
	[EpplusNestedTableColumn(HeaderPrefix = "Space Manager")]
	public required EmployeeDTO Owner { get; set; }
	
	[EpplusTableColumn]
	public required string Something {get; set;}
}

[grosch-intl]

@swmal Unsure if it's related or not, but interestingly if you delete the Something property, then it doesn't expand the Owner data.

[grosch-intl]

@swmal Any ETA you can provide for a resolution?

[swmal]

@grosch-intl As it works today you can only use the EPPlusTableColumnSortOrder attribute on the outer class. Then, instead of using nameof, you use the "path" to the property on the inner class (i.e. a property with a complex type). Your example will work if you change the attribute on ExcelSpaceRow to this:

[EpplusTable(AutofitColumns = true, PrintHeaders = true, TableStyle = TableStyles.Medium2),
    EPPlusTableColumnSortOrder(Properties = new string[] {
        "Owner.Id", "Owner.Name", "Owner.EmailLink", nameof(Something)})]

I agree that it would be more elegant if it worked as per your example and I will keep this issue open as a feature to implement.

[swmal]

Was able to replicate the issue when removing the Something property. Not related to the above, but it's a bug and will get back here when it is fixed.

[grosch-intl]

@swmal OK, thanks. That ends up being super fragile with strings like that. How hard of a fix do you envision this being? Is it something you think might be fixed soonish?

[swmal]

I need to move the handling of the EPPlusTableColumnSortOrder attribute from here

https://github.com/EPPlusSoftware/EPPlus/blob/develop/src/EPPlus/LoadFunctions/LoadFromCollection.cs#L44

to somewhere inside the recursive handling of nested properties in this function

https://github.com/EPPlusSoftware/EPPlus/blob/develop/src/EPPlus/LoadFunctions/LoadFromCollectionColumns.cs#L68

and then ensure that the sortorder is updated when we hit a sort order attribute on a nested class. I'll try to find time to do it when we have released EPPlus 7 Beta which is our top priority. Depends on what you mean with soonish, but within a month or so.

[grosch-intl]

Hi @swmal, just checking in on the status of this one and how the 7 beta is coming.

[swmal]

Hi @grosch-intl - we released the beta yesterday and I have just started to work on updates on the LoadFromCollection method.

[grosch-intl]

Hey @swmal just wanted to check on this one. This specific piece kinda got merged with other stuff I think, but specifically this is the part related to the nested tables not placing the columns in the correct order.