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:

  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:

  <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