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.