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 Styles like background color #36
Comments
Very important for what I need to do. :) What can I do to help make this happen? |
@elad love your enthusiasm :) As is usually the case, the hardest part is settling on a JS representation. For example, XLSB uses a bit field for representing certain properties whereas XLSX uses its own kinda-sorta-like-HTML-but-not-quite rich text format. My initial thought was to save an HTML representation for each cell (and in fact, XLSX does generate HTML by parsing the rich text runs), but that makes the reverse process somewhat tricky (how do you handle CSS styles? Do you parse the CSS to figure out if the text is bold?). NOTE: since the XLSX writer recomputes styles anyway, we don't need to stick to a style array or some other representation that is tightly coupled to the actual file representation Extracting the style information is not too difficult, once we settle on a representation:
@elad what do you think is the best way to store this information? Note that we don't necessarily have to store a pretty format: we can (and should) write functions that "parse" the intermediate representation and give output. @jokerslab @gcoonrod @artemryzhov since you raised issues on the matter, hopefully you can chime in as well :) |
(keep in mind I'm not very familiar with this stuff yet :) I'm not sure HTML representation is the best way to do this. Some libraries seem to just expose the "raw" values for each field, a few examples: http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.backgroundcolor.aspx http://stackoverflow.com/questions/10756206/getting-cell-backgroundcolor-in-excel-with-open-xml-2-0 http://stackoverflow.com/questions/12043973/how-to-read-the-xlsx-color-infomation-by-using-openpyxl By the way, I say "raw" because I printed the So it seems like the best way would be to at leas have each cell maybe have a |
The overall cell style is linked to the cell's "s" attribute (page 1589 of ECMA-376 pdf i linked to). The relevant logic here is in the worksheet processing: https://github.com/SheetJS/js-xlsx/blob/master/bits/72_wsxml.js#L75-L80
If you want to see the raw value, it's already exposed by default in the (.r) field:
Unfortunately, XLS and XLSB and XLML use different representations :/ There are three ways around this:
I'll think about it a bit more. @elad do you know specifically what you need from the styles? In particular, do you need something like an HTML representation or just certain vitals (like background color, font, etc)? In the latter case, we probably could craft a short style object with the basic details |
First, thanks for being so quick on the replies! :) I had a feeling the mapping happened through I think the example you provided works only for cell-specific styling. For example, my worksheet applies styling to the entire row, and this is what I get:
The color used is Aqua, Accent 5, Lighter 40%, which I believe corresponds to this:
What am I missing in order to access this styling through What I need from the styles is the background color. I suspect a lot of people use row colors to signify meaning that isn't otherwise conveyed through an actual column. In my case, the row color represents "type" and in order to import data from Excel to a database I need to figure out what the row color is. I think having something simple like you suggest would cover the needs of most folks who are interested in this feature, and if not would serve as a great foundation to further expand. |
@elad row-level information is currently not made available via Let's settle on putting each cell's background in Upon reflection, it requires a bit of work (because the themes are not currently processed). I will take a stab at it later today:
|
Okay, I did as you said - except for the themes.xml part, because I'm not yet sure how to do that - and this is what I get:
So now the cell's Hopefully it is, in which case - what do we do about themes.xml? I assume it requires changes in |
Following up... It seems the answer to my question is "yes." I looked and saw that
So I printed the contents of this XML file and found the I'm now writing |
@elad it sounds like you have the right idea :) Looking forward to the PR |
I think I got it. I added basic support for parsing the theme and tested it. I also added some utility functions to provide the RGB color with the tint applied, to make it easier to get the actual color. It seems that the tinting algorithm is either incorrect or I'm missing something though because it doesn't work if I use the version from the specification verbatim. Also, there should probably be a lot of testing here because I'm sure my use case isn't the only one. :) Output:
This shows the data for the Will submit a pull request shortly. Please note that at the very least it should be marked as experimental. :) |
@elad add an option |
The code implementing the functionality requested by this issue has been merged, I think it's safe to close it. |
@elad we'll close once XLSB and ODS also use the same format |
Gotcha. If you manage to find a moment, please provide some status on date/style issues raised elsewhere - I'd like to help with the code but since I know you're working on a new version I'm afraid changes would be conflicting. :) |
Wow! thanks @elad for this. My question now would be... how much effort should it be to add "XML Styles" compatibility? Example: Given I have <Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial Unicode MS" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s58">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s59">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders/>
<Font ss:Bold="1"/>
<Interior ss:Color="#99CC00" ss:Pattern="Solid"/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s60">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" ss:Size="14" ss:Bold="1"/>
<Interior ss:Color="#FF6600" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s61">
<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
<Font ss:FontName="Calibri" ss:Size="16" ss:Color="#FFFFFF" ss:Bold="1"/>
<Interior ss:Color="#333333" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s62">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font ss:Color="#FFFFFF" />
<Interior ss:Color="#333333" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s63">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<NumberFormat ss:Format="0.0%"/>
</Style>
<Style ss:ID="s64" ss:Parent="s62">
<NumberFormat ss:Format="0.0%"/>
</Style>
</Styles> I would like to keep those styles (or convert them) when reading an XML and writing an XLSX. |
Can I get a demo on how these styles to be applied in the latest version? |
Is there no link on how the styles can be applied to cells? |
Hello All, it would be really awesome if there is an example of how to provide style metadata |
We offer this in the Pro compendium. Since companies have paid for the features already, it would be unfair to them if we turned around and made it available as open source. We have a longer comment in a gist. |
How do I get the background color of a cell?
The text was updated successfully, but these errors were encountered: