Add Support for MS Excel Data Entity Types

[jankrynauw]

MS Excel supports Data Entity Types which allows one to store and display richer data structures within Excel.

https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-data-types-entity-card

I have attached a workbook with an example: Data Entity Example.xlsx

Are there any plans to support these data types, for example something along the lines of

e := excelize.NewFile()
e.SetCellValue("Sheet1", "A1", "hello")

dataEntityJson := '...json representation of the excel entity...'
e.SetCellDataEntity("Sheet1", "A2",  dataEntityJson)

We have written a lightweight go library which creates the correct Entity Type objects in Go: Alis Build - Excel This package has a .ToJSON() property which creates a valid Data Entity object required by MS Excel:

package main

import (
    "go.alis.build/excel"
    "google.golang.org/genproto/googleapis/type/date"
)

func main() {
    e := excel.EntityValue(
        "Card Top Title",
        map[string]excel.CellValue{
            "Total Amount": excel.DoubleValue(7777.77),
            "Price":        excel.FormattedNumber(55.40, "$0.00"),
            "Validated":    excel.BoolValue(true),
            "Owner":        excel.StringValue("Jan Krynauw"),
            "Items": excel.ArrayValue([][]excel.CellValue{
                {
                    excel.StringValue("Thomas"),
                    excel.StringValue("Scholtz"),
                    excel.FormattedNumber(8.03, "$0.0"),
                },
                {
                    excel.StringValue("James"),
                    excel.StringValue("Spanjaard"),
                    excel.FormattedNumber(28.3, "$0.0"),
                },
            }),
            "Effective Date": excel.DateValue(&date.Date{
                Year:  1980,
                Month: 2,
                Day:   2,
            }, "yyyy-mm-dd"),
            "Sub Properties A": excel.EntityValue(
                "Another one",
                map[string]excel.CellValue{
                    "Key 1": excel.StringValue("Value 1"),
                    "Key 2": excel.StringValue("Value 2"),
                },
                nil, nil),
        },
        &excel.Layouts{
            Compact: &excel.Compact{
                Icon: "Cloud",
            },
            Card: &excel.Card{
                Title: &excel.CardProperty{
                    Property: "Owner",
                },
                SubTitle: &excel.CardProperty{
                    Property: "Effective Date",
                },
            },
        },
        &excel.Provider{
            Description:       "Some nice description",
            LogoTargetAddress: "",
        },
    )

    // Generates a JSON property which is a valid Data Entity Type.
    jsonBytes, _ := e.ToJSON()
    _ = jsonBytes

    // Generate ScriptLabImportYAML
    importXML, _ := e.ToScriptLabYAML()
    _ = importXML
}

[jankrynauw]

The Excel Data Types are fairly complex, here is the definition in javascript: https://learn.microsoft.com/en-us/javascript/api/excel/excel.entitycellvalue?view=excel-js-preview

@xuri as you are very familiar with the Excel internal structures, how much effort would do you think it will take to implement support for these Excel Data Types?

[HamzaAnis]

@jankrynauw Implementing this.

[xuri]

Thanks for your issue. Sorry, after evaluating, I'm afraid no plan to add this feature recently. I'll certainly accept that patch if somebody did that.

[HamzaAnis]

I am applying the patch for it soon, would love to have your review on it.

[dolmen]

Note that go.alis.build/excel has a different license (Apache 2.0) than Excelize (BSD 3 clauses): https://github.com/alis-exchange/go-alis-build/blob/main/excel/LICENSE

[jankrynauw]

Hi, I'm the owner of the go.alis.build/excel library, happy to update the licence to BSD make this happen?

[jankrynauw]

Note that go.alis.build/excel has a different license (Apache 2.0) than Excelize (BSD 3 clauses): https://github.com/alis-exchange/go-alis-build/blob/main/excel/LICENSE

I've updated the package to a BSD 3 clauses license.