Kendo-ui-core: Support user-defined names

[tsvetomir]

This story includes an API, persistence and UI elements to support defining custom names.

The Spreadsheet already implements the internals needed to use the defined names in formulas.

[tsvetomir]

Early design mock-up for the UI:

[mishoo]

This feature is almost done (on branch spread-user-names) and I'd like to get some feedback before I merge the branch in. Here are some notes.

Test in playground/spreadsheet/sheet.html.

First, a styling issue that perhaps @gyoshev can help with. To place the name editor to the left of the formula bar, I've used a <table> (see code). Looks almost good to me, but somehow the formula bar has a black border now (I guess the <table> prevents border-color from being inherited).

If you prefer me to attempt to do it without <table> please let me know. Also, the name editor styles are still in playground.

Brief description of current functionality:

• select a range and type a name in the name editor, press ENTER. Now you've defined a name for that range, and can use it in formulas instead of typing the range.
• to delete a name, click the "x" in the dropdown. This operation is undo-able (as well as defining a name), so I did not include a confirmation dialog. Still, we could display a warning if the name is referenced in formulas — should I work on this?
• the named references created from the UI are absolute and fully qualified (i.e. Sheet1!$A$1 instead of just A1). They are properly adjusted on insert/delete rows/columns. XLSX import/export works fine.
• names are adjusted when a sheet is renamed.
• we also support relative references through the API. Example:

js spreadsheet.defineName("TOTAL", "SUM(R1C:R[-1]C)");

Now we can type "=TOTAL" in any cell, in any sheet, to get the sum of all cells above it. It's a pity this doesn't work in any other tool (tried Excel, LibreOffice, Google Sheets and Gnumeric Spreadsheet). The reference there _has_ to be in RC notation, because only this notation can represent a relative reference unambiguously (to properly read a reference like A1 we need know the cell where it is used; but there's no such thing for defining names).

Excel does support a form of relative references (they must always have the Sheet1! part though), but only in A1 notation. I tried the following. Select cell D3, open Name Manager (in tab Formulas) and create new name TEST with value Sheet1!A1:C3. Typing =SUM(TEST) works as expected both in D3 and D4 (the reason why the value differs is because the reference is relative; it's correct). Then select cell A5 and open Name Manager, and look what it did to the name:

It even serializes the name like this:

xml <definedName name="TEST">Sheet1!XFB1048575:XFD1</definedName>

I don't get how, but Excel is able to read back that file. Our import doesn't work properly yet in this case. UPDATE: figured it out.

Google Sheets does not support relative references in names, and no formulas either — names must be fully qualified absolute ranges.

Neither one supports references that have no sheet in a name definition. Edit: need to dig this further (Gnumeric and LibreOffice seem to support this actually).

[tsvetomir]

Great work, Mihai :+1:

• A confirmation dialog would be a bit of annoyance. I'm more in favor of letting know the user that the action can be undone, GMail style. But that's something @yordanov can think about, not a requirement for this task.
• Can you get the user-defined names to appear in the formula auto-complete?

@ivanchev we'll also need to add the corresponding support in the server-side export module as well

[gyoshev]

I pushed some basic styling and used a few other-than-a-table elements. Currently looks like this, but it should be checked by @yordanov, too.

Currently, the UI does not use any widget, and has a separate rendering. Why not use the ComboBox, so that we inherit its keyboard support and styles?

[mishoo]

@gyoshev the bar looks much better, but the popup seems broken:

I'll try to fiddle with the ComboBox. Indeed, keyboard support would be nice.

[mishoo]

@gyoshev I changed it to use a ComboBox. The only problem now is that when I mouse over a resize handle, the popup closes:

I suspect it's because the Spreadsheet's render is triggered, and it steals focus from the combo (maybe the clipboard stuff?) — any idea how to fix this?

[gyoshev]

The combobox seems great, nicely done!

The problem with hovering the resize handles is quite annoying. I believe it is triggered also when using all other drop-downs in the toolbar. A way to workaround this is to raise a flag in the worksheet object when a drop-down is opened, so that the resize handles do not trigger a refresh. Not very great, but the only other alternative that I can come up with is to pull out the resize handles outside the view rendering code, and that seems to be a bigger hack.

[mishoo]

Indeed, it's happening with the font family/size selectors. Not with the border or color pickers though, which is strange. I'll investigate further, perhaps there is an even simpler solution.

[mishoo]

@gyoshev this fixes it for the user names popup. Not great either, but I like it more than a flag on the spreadsheet.

[tsvetomir]

Stopping the mousemove propagation here actually seems pretty decent solution. And it reuses the flag that is already in the browser event code :) #saynotoflags