如何用axlsx给出一个背景颜色的日期？ [英] How to give a date a background color with axlsx?

问题描述

我用axslx创建一个excel。
一行应该着色。但是如果我这样做，我会放弃我的日期的日期格式。

我做了一些尝试的最小例子：

  require'axlsx'
 Axlsx :: Package.new do | p | 
 p.workbook.add_worksheet（：name =>test）do | ws | 
 style1 = ws.styles.add_style（：bg_color =>EF0920，：fg_color =>FFFFFF）
 ws.add_row [Date.today，No style defined  - ] 
 ws.add_row [Date.today，Style with colors --The date is no date any longer]，：style => style1 
 ws.add_row [Date.today，Style with colors，except date  -  ok，but not color]，：style => [nil，style1] 
 ws.add_row [Date.today，颜色和类型的样式 - 日期不再是日期]，：style => style1，：types => [：date，：string] 
 ws.add_row [Date.today，颜色和类型的样式 - 日期不再是日期]，：style => [style1，style1]，：types => [：date，：string] 
 ws.add_row [Date.today，No Style  -  ok，but not color]，：types => [：date，：string] 
 end 
 p.serialize（'test.xlsx'）
 end 
  

结果是：

如何在不丢失数据信息的情况下对日期单元进行着色？

解决方案

您需要指定数字格式，所以你的风格看起来像

  style1 = ws.styles.add_style（：bg_color =>EF0920 ：fg_color =>FFFFFF，：format_code =>dd.mm.yyyy）
  

添加样式时，将覆盖默认样式和所有格式。例如

  ws.add_row [Date.today，颜色风格 - 日期不再是日期]，：style => [style1，nil] 
  

这将产生一个红色日期格式，如其他行和无填充列B。

此样式仅适用于日期，如果您想要整列，我会推荐类似于

  red_style_h = {：bg_color => EF0920，：fg_color => FFFFFF} 
 red_date_h = red_style_h.merge（：format_code =>dd.mm.yyyy）
 
 red_style = ws.styles.add_style（red_style_h）
 red_date_style = ws.styles.add_style（red_date_h）
  

然后将行设置为

  ws.add_row [Date.today，颜色风格 - 日期不再是日期]，：style => [red_date_style，red_style] 
  

这将产生一个红色行（列A和B）与其他行一样。

Git Hub Source

还可以通过 numFmts 和< a href =https://github.com/randym/axlsx/blob/master/lib/axlsx/util/constants.rb#L263 =nofollow>全局命名常量 NUM_FMT_PERCENTAGE ， NUM_FMT_YYYYMMDD ， NUM_FMT_YYYYMMDDHHMMSS 等。

当定义许多不同的格式时，我发现使用YML文件最简单，然后解析定义样式，例如

  red_style：& red 
 bg_c​​olor：EF0920
 fg_color：FFFFFF
 red_date：
< < ;: * red 
 format_code：dd.mm.yyyy 
  

然后， p>

  class MyStylizedSheet< :: Axlsx :: Workbook 
 STYLES_FILE = YAML.load（File.read（YOUR_YML_FILE））。deep_symbolize_keys 
 PREDEFINED_STYLES = {} 
 def initialize（options = {}）
 super 
 initialize_with_styles 
 end 
 private 
 def initialize_with_styles 
 STYLES_FILE.each do | k，v | 
 PREDEFINED_STYLES [k] = @ styles.add_style（v）
 end 
 end 
 end 
  

然后，您可以通过 PREDEFINED_STYLES 常量从您的自定义类和参考样式启动您的工作簿。

I create an excel with axslx. One row should be colored. But if I do so, I loose the date format for my dates.

Minimal example with some attempts I did:

require 'axlsx'
Axlsx::Package.new do |p|
    p.workbook.add_worksheet(:name => "test") do |ws|
        style1 = ws.styles.add_style(:bg_color => "EF0920", :fg_color => "FFFFFF")
        ws.add_row [ Date.today, "No style defined --ok"]
        ws.add_row [ Date.today, "Style with colors --The date is no date any longer"], :style => style1
        ws.add_row [ Date.today, "Style with colors, except date -- ok, but not colored"], :style => [nil,style1]
        ws.add_row [ Date.today, "Style with colors and types --The date is no date any longer"], :style => style1, :types => [:date,:string]
        ws.add_row [ Date.today, "Style with colors and types --The date is no date any longer"], :style => [style1,style1], :types => [:date,:string]
        ws.add_row [ Date.today, "No Style -- ok, but not colored"], :types => [:date,:string]
    end
    p.serialize('test.xlsx')
end

The result is:

How can I color a date cell without losing the data information?

解决方案

You will need to specify the number format as well so your style would look like

style1 = ws.styles.add_style(:bg_color => "EF0920", :fg_color => "FFFFFF", :format_code => "dd.mm.yyyy")

When adding a style it will overwrite the default style and all formatting. e.g.

ws.add_row [ Date.today, "Style with colors --The date is no date any longer"], :style => [style1,nil]

This will produce a red date formatted like the other rows and a no fill column B.

This style will be for the date only so if you want the whole column I would recommend something like

red_style_h  = {:bg_color => "EF0920", :fg_color => "FFFFFF"} 
red_date_h = red_style_h.merge(:format_code => "dd.mm.yyyy")

red_style = ws.styles.add_style(red_style_h)
red_date_style = ws.styles.add_style(red_date_h)

Then style your rows as

ws.add_row [ Date.today, "Style with colors --The date is no date any longer"], :style => [red_date_style,red_style]

This will produce a red row (columns A and B) with the date formatted the same as the other rows.

Git Hub Source

There are also predefined styles available through numFmts and the global named constants NUM_FMT_PERCENTAGE,NUM_FMT_YYYYMMDD,NUM_FMT_YYYYMMDDHHMMSS, etc.

When defining many different formats I find it easiest to use a YML file and then parse that to define styles e.g.

red_style:&red
  bg_color: "EF0920"
  fg_color: "FFFFFF"
red_date: 
  <<: *red
  format_code: dd.mm.yyyy

Then something like

class MyStylizedSheet < ::Axlsx::Workbook
  STYLES_FILE = YAML.load(File.read(YOUR_YML_FILE)).deep_symbolize_keys
  PREDEFINED_STYLES = {}
  def initialize(options={})
    super
    initialize_with_styles
  end
  private
  def initialize_with_styles
    STYLES_FILE.each do |k,v|
      PREDEFINED_STYLES[k] = @styles.add_style(v)
    end
  end
end

Then you can initiate your Workbook from your custom class and reference styles through the PREDEFINED_STYLES constant.

这篇关于如何用axlsx给出一个背景颜色的日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！