New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cell formatting #97

Open
nokome opened this Issue Jan 13, 2016 · 21 comments

Comments

Projects
None yet
4 participants
@nokome
Member

nokome commented Jan 13, 2016

There is currently no formatting (e.g. bold, color etc) for cells. It would be good to make formatting either static or dynamic (i.e. dependent on the value of the cell, ala "conditional formatting" in Excel).

One approach to this would be to have a formats.json file which specifies, for certain cell ranges, mappings between CSS attributes and host language expressions which are evaluated in the spread with the variable value being the value of the cell e.g.

[
       {
                "cells": "A1:B6",
                "background-color": "hsv(value,0.8,0.8)",
                "font-style": "ifelse(value<0,'italic','normal')"
        },
        ...
 ]

Expressions could just be constants too:

 {
     "cells": "A43",
     "background-color": "red",
},

In C++ this would need a std::vector<std::map<std::string,std::string>> Sheet::formats_ and a uint Cell::format index which points to the element in the formats vector and style attributes to be added to cells when generating HTML and in the JSON result from @update.

In the UI we would need a formatting tool that would allow a user to select CSS attributes and assign expressions to them - and buttons for quickly doing the normal stuff : bold, background color, borders etc

@nokome nokome self-assigned this Jan 13, 2016

@nokome nokome added web labels Jan 13, 2016

@michael

This comment has been minimized.

Show comment
Hide comment
@michael

michael Jan 13, 2016

Member

Oh that's actually a nice idea. I like the stylesheet approach.

Member

michael commented Jan 13, 2016

Oh that's actually a nice idea. I like the stylesheet approach.

@nokome nokome referenced this issue Jan 13, 2016

Closed

Sheet UI layout #99

@nokome nokome added 2 - Doing and removed 1 - Ready labels Feb 25, 2016

@nokome

This comment has been minimized.

Show comment
Hide comment
@nokome

nokome Mar 24, 2016

Member

Over on HN, @samfisher83, pointed out these Excel paintings: http://www.quertime.com/article/arn-2012-08-22-1-25-drawings-and-games-made-with-microsoft-excel/

Between cell mapping #118 and this cell formatting we should be able to do something similar quite easily. Would be a fun demo.

Member

nokome commented Mar 24, 2016

Over on HN, @samfisher83, pointed out these Excel paintings: http://www.quertime.com/article/arn-2012-08-22-1-25-drawings-and-games-made-with-microsoft-excel/

Between cell mapping #118 and this cell formatting we should be able to do something similar quite easily. Would be a fun demo.

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 7, 2017

Member

IMO conditional formatters should consist of three parts:

cells: <cell-or-range-expression>...,
expression: <expression evaluating to a boolean>, -> true = apply formatting
style: <css-style-string> -> more semantical would be a category/class that can be styled via css
Member

oliver---- commented Dec 7, 2017

IMO conditional formatters should consist of three parts:

cells: <cell-or-range-expression>...,
expression: <expression evaluating to a boolean>, -> true = apply formatting
style: <css-style-string> -> more semantical would be a category/class that can be styled via css
@nokome

This comment has been minimized.

Show comment
Hide comment
@nokome

nokome Dec 7, 2017

Member

That's an option but it would exclude the possibility to map data onto style attributes like color to do things like: http://policeanalyst.com/creating-heat-maps-from-excel-pivot-tables-with-conditional-formatting/

image

Anyway, let's come back to this later. I still see this as a different topic to cell issues/comments. It's more about data visualisation than tagging of cells.

Member

nokome commented Dec 7, 2017

That's an option but it would exclude the possibility to map data onto style attributes like color to do things like: http://policeanalyst.com/creating-heat-maps-from-excel-pivot-tables-with-conditional-formatting/

image

Anyway, let's come back to this later. I still see this as a different topic to cell issues/comments. It's more about data visualisation than tagging of cells.

@Integral

This comment has been minimized.

Show comment
Hide comment
@Integral

Integral Dec 7, 2017

Member
Member

Integral commented Dec 7, 2017

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 7, 2017

Member

I think we should talk about this now :)

So what you are saying is that it is desired to provide keys=css-style-keys with values being treated as templates, right?

Edit: or values = expressions that evaluate to strings using variables passed to the formatter (=lambda).

Member

oliver---- commented Dec 7, 2017

I think we should talk about this now :)

So what you are saying is that it is desired to provide keys=css-style-keys with values being treated as templates, right?

Edit: or values = expressions that evaluate to strings using variables passed to the formatter (=lambda).

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 7, 2017

Member

BTW, this is an important source for requirements for the implementation of 'issues' as I think all dynamic, cell based evaluators are similar: depend on a set of cells, executing an expression or built-in function, storing information on the cell-state. They differ in the way how the result is mapped.

Member

oliver---- commented Dec 7, 2017

BTW, this is an important source for requirements for the implementation of 'issues' as I think all dynamic, cell based evaluators are similar: depend on a set of cells, executing an expression or built-in function, storing information on the cell-state. They differ in the way how the result is mapped.

@nokome

This comment has been minimized.

Show comment
Hide comment
@nokome

nokome Dec 7, 2017

Member

I think we should talk about this now :)

Fine 😄

So what you are saying is that it is desired to provide keys=css-style-keys with values being treated as templates, right?

Yes, may be this concept is better called "data-driven styles" instead of "conditional formatting" (the legacy term). Each CSS property has a value generated by an expression which is evaluated against the value of the cell.

Now we have multiple languages in a single sheet we would need to add a language attribute:

var dataDriveStyle = {
   cells: "A1:B6",
   properties: {
     "background-color": {
         language: "r",
         expression: "hsv(sin(value),0.8,0.8)"
     }
  }
}

Note this example uses the hsv function in R which returns a hex colour string https://stat.ethz.ch/R-manual/R-devel/library/grDevices/html/hsv.html . But I'd suggest the language would default to mini and we implement analagous functions there.

Member

nokome commented Dec 7, 2017

I think we should talk about this now :)

Fine 😄

So what you are saying is that it is desired to provide keys=css-style-keys with values being treated as templates, right?

Yes, may be this concept is better called "data-driven styles" instead of "conditional formatting" (the legacy term). Each CSS property has a value generated by an expression which is evaluated against the value of the cell.

Now we have multiple languages in a single sheet we would need to add a language attribute:

var dataDriveStyle = {
   cells: "A1:B6",
   properties: {
     "background-color": {
         language: "r",
         expression: "hsv(sin(value),0.8,0.8)"
     }
  }
}

Note this example uses the hsv function in R which returns a hex colour string https://stat.ethz.ch/R-manual/R-devel/library/grDevices/html/hsv.html . But I'd suggest the language would default to mini and we implement analagous functions there.

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 7, 2017

Member

Would it be acceptable to use one language per formatter?

Edit: then it is more similar to a regular cell

Member

oliver---- commented Dec 7, 2017

Would it be acceptable to use one language per formatter?

Edit: then it is more similar to a regular cell

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 7, 2017

Member

I like the idea. While this is not very semantical, it gives the user great flexibility on the other hand.

Member

oliver---- commented Dec 7, 2017

I like the idea. While this is not very semantical, it gives the user great flexibility on the other hand.

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 7, 2017

Member

hsv function in R which returns a hex colour

I think we could do some auto-completion depending on the key.
E.g. in jquery (or Substance DOMElement) it is possible to set el.css('width, 100) which adds 'px' in that case.

Member

oliver---- commented Dec 7, 2017

hsv function in R which returns a hex colour

I think we could do some auto-completion depending on the key.
E.g. in jquery (or Substance DOMElement) it is possible to set el.css('width, 100) which adds 'px' in that case.

@nokome

This comment has been minimized.

Show comment
Hide comment
@nokome

nokome Dec 7, 2017

Member

Would it be acceptable to use one language per formatter?

Yes, I think that would be fine most of the time. If someone really wanted to do something funky (like use some special function only available in R) then it could be overriden:

var dataDriveStyle = {
   cells: "A1:B6",
   properties: {
     "font-weight": "max(value * 100, 500)",
     "background-color": {
         expression: "hsv(sin(value),0.8,0.8)"
         language: 'r'
     }
  }
}

I like the idea. While this is not very semantical, it gives the user great flexibility

Actually, I think it is semantic because it's formatting derived from the data (using expressions stored in the sheet) no ad-hoc formatting.

Member

nokome commented Dec 7, 2017

Would it be acceptable to use one language per formatter?

Yes, I think that would be fine most of the time. If someone really wanted to do something funky (like use some special function only available in R) then it could be overriden:

var dataDriveStyle = {
   cells: "A1:B6",
   properties: {
     "font-weight": "max(value * 100, 500)",
     "background-color": {
         expression: "hsv(sin(value),0.8,0.8)"
         language: 'r'
     }
  }
}

I like the idea. While this is not very semantical, it gives the user great flexibility

Actually, I think it is semantic because it's formatting derived from the data (using expressions stored in the sheet) no ad-hoc formatting.

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 7, 2017

Member

Ok. Thanks. Captured :)

💯

Member

oliver---- commented Dec 7, 2017

Ok. Thanks. Captured :)

💯

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 8, 2017

Member

Proposal for the first iteration:

cells: "C1:B10", 
language: 'r',
styles: {
  "font-weight": max(value * 100, 500),
  "background-color": "hsv(sin(value),0.8,0.8)"
}

one range, one language, a map of style expressions

Member

oliver---- commented Dec 8, 2017

Proposal for the first iteration:

cells: "C1:B10", 
language: 'r',
styles: {
  "font-weight": max(value * 100, 500),
  "background-color": "hsv(sin(value),0.8,0.8)"
}

one range, one language, a map of style expressions

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 8, 2017

Member

@nokome What is your take on conflicting formatters?

One option would be 'extend', i.e. overriding on css-property level.
Last one wins (in order of definition = position in the XML).

Member

oliver---- commented Dec 8, 2017

@nokome What is your take on conflicting formatters?

One option would be 'extend', i.e. overriding on css-property level.
Last one wins (in order of definition = position in the XML).

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 8, 2017

Member

Just realized that the previous proposal is inconvenient for applying different styles for the same premise.
Should we change it to ?

cells: "C1:B10", 
language: 'r',
expression: "true",  ->  if true style is applied
styles: {
  "font-weight": "max(value * 100, 500)",
  "background-color": "hsv(sin(value),0.8,0.8)"
}
Member

oliver---- commented Dec 8, 2017

Just realized that the previous proposal is inconvenient for applying different styles for the same premise.
Should we change it to ?

cells: "C1:B10", 
language: 'r',
expression: "true",  ->  if true style is applied
styles: {
  "font-weight": "max(value * 100, 500)",
  "background-color": "hsv(sin(value),0.8,0.8)"
}
@nokome

This comment has been minimized.

Show comment
Hide comment
@nokome

nokome Dec 8, 2017

Member

@nokome What is your take on conflicting formatters?

I was thinking that there would only be one formatter per cell.

One option would be 'extend', i.e. overriding on css-property level.

If we stick with one formatter per cell then this redundant - there would be no conflicts.

Just realized that the previous proposal is inconvenient for applying different styles for the same premise.

Not sure what this means, sorry.

Member

nokome commented Dec 8, 2017

@nokome What is your take on conflicting formatters?

I was thinking that there would only be one formatter per cell.

One option would be 'extend', i.e. overriding on css-property level.

If we stick with one formatter per cell then this redundant - there would be no conflicts.

Just realized that the previous proposal is inconvenient for applying different styles for the same premise.

Not sure what this means, sorry.

@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 8, 2017

Member

I was thinking that there would only be one formatter per cell.

Coming from a technical point of view: formatters are associated to cells via a cell or range expression, i.e. multiple formatters could be associated to the same cell.

Re the other point:

cells: "C1:B10", 
language: 'r',
styles: {
  "font-weight": max(value * 100, 500),
  "background-color": "hsv(sin(value),0.8,0.8)"
}

This is supporting sophisticated use-cases like the 'heat-map'.

IMO this case is much more often:

cells: "C1:B10", 
language: 'mini',
expression: 'value < 0',
styles: {
  "background-color": "red"
}

i.e. dynamic selector, static styles vs static selector, dynamic styles.

Member

oliver---- commented Dec 8, 2017

I was thinking that there would only be one formatter per cell.

Coming from a technical point of view: formatters are associated to cells via a cell or range expression, i.e. multiple formatters could be associated to the same cell.

Re the other point:

cells: "C1:B10", 
language: 'r',
styles: {
  "font-weight": max(value * 100, 500),
  "background-color": "hsv(sin(value),0.8,0.8)"
}

This is supporting sophisticated use-cases like the 'heat-map'.

IMO this case is much more often:

cells: "C1:B10", 
language: 'mini',
expression: 'value < 0',
styles: {
  "background-color": "red"
}

i.e. dynamic selector, static styles vs static selector, dynamic styles.

@nokome

This comment has been minimized.

Show comment
Hide comment
@nokome

nokome Dec 8, 2017

Member

Coming from a technical point of view: formatters are associated to cells via a cell or range expression, i.e. multiple formatters could be associated to the same cell.

You're right, sorry didn't think about that long enough. Order of definition, last one wins makes sense then.

OK, get your other point now, yes need to have both. Suggest using apply instead of expression:

cells: "C1:B10", 
language: 'r',
apply: 'true',
styles: {
  "font-weight": "max(value * 100, 500)",
  "background-color": "hsv(sin(value),0.8,0.8)"
}
cells: "C1:B10", 
language: 'r',
apply: 'value < 0',
styles: {
  "background-color": "red"
}
Member

nokome commented Dec 8, 2017

Coming from a technical point of view: formatters are associated to cells via a cell or range expression, i.e. multiple formatters could be associated to the same cell.

You're right, sorry didn't think about that long enough. Order of definition, last one wins makes sense then.

OK, get your other point now, yes need to have both. Suggest using apply instead of expression:

cells: "C1:B10", 
language: 'r',
apply: 'true',
styles: {
  "font-weight": "max(value * 100, 500)",
  "background-color": "hsv(sin(value),0.8,0.8)"
}
cells: "C1:B10", 
language: 'r',
apply: 'value < 0',
styles: {
  "background-color": "red"
}
@oliver----

This comment has been minimized.

Show comment
Hide comment
@oliver----

oliver---- Dec 8, 2017

Member

What do you think about this?

<formatters>
  <formatter apply="value < 0" language="mini">
    <cells>A1:B10</cells>
    <styles>
      <style property="font-weight" value="red" />
    </styles>
  </formatter>
</formatters>
<formatters>
  <formatter apply="true">
    <cells>A1:B10</cells>
    <styles>
      <style property="font-weight" expression="max(value * 100, 500)" language="r" />
      <style property="background-color" expression="hsv(sin(value),0.8,0.8)" language="r" />
    </styles>
  </formatter>
</formatters>
Member

oliver---- commented Dec 8, 2017

What do you think about this?

<formatters>
  <formatter apply="value < 0" language="mini">
    <cells>A1:B10</cells>
    <styles>
      <style property="font-weight" value="red" />
    </styles>
  </formatter>
</formatters>
<formatters>
  <formatter apply="true">
    <cells>A1:B10</cells>
    <styles>
      <style property="font-weight" expression="max(value * 100, 500)" language="r" />
      <style property="background-color" expression="hsv(sin(value),0.8,0.8)" language="r" />
    </styles>
  </formatter>
</formatters>
@nokome

This comment has been minimized.

Show comment
Hide comment
@nokome

nokome Dec 11, 2017

Member

I keep on wanting to use the name "style" instead of "format". Not a strong preference but it slighly differentiates it from more adhoc formatting and Excel-style conditional formatting and more reflects that it's based-on CSS. So I would suggest:

<styles>
  <style apply="true">
    <cells>A1:B10</cells>
    <properties>
      <property name="font-weight" value="max(value * 100, 500)" language="r" /> <!-- a dynamic property -->
      <property name="background-color" value="red" /> <!-- a static property -->
    </properties>
  </style>
</style>
Member

nokome commented Dec 11, 2017

I keep on wanting to use the name "style" instead of "format". Not a strong preference but it slighly differentiates it from more adhoc formatting and Excel-style conditional formatting and more reflects that it's based-on CSS. So I would suggest:

<styles>
  <style apply="true">
    <cells>A1:B10</cells>
    <properties>
      <property name="font-weight" value="max(value * 100, 500)" language="r" /> <!-- a dynamic property -->
      <property name="background-color" value="red" /> <!-- a static property -->
    </properties>
  </style>
</style>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment