How to add background patterns to Excel cells with axlsx?

162 Views Asked by At

I create xlsx-files with axlsx (3.0.0.pre) with different cell formats.

Example:

require 'axlsx'

Axlsx::Package.new do |axlsxpackage|
      axlsxpackage.workbook{|wb|
        wb.styles do |style|
          black_cell = style.add_style( :bg_color => "00", :fg_color => "FF", :sz => 14, :alignment => { :horizontal=> :center }) #white text in black cell
          dotted = style.add_style( :bg_color => "00",
          :patternType => :lightGrid,  #does not work
          :alignment => { :horizontal=> :center })
          
          styles = [black_cell, dotted, black_cell, dotted, black_cell, dotted]
          wb.add_worksheet(:name => 'DATA') do |sheet|
            sheet.add_row(%w{col1 col2 col3 col4 col5 col6}, :style => styles)
            10.times{
              sheet.add_row(%w{1 2 3 4 5 6}, :style => styles)
            }#worksheet
            #sheet.column_widths nil, 3, 5, nil
        end #Workbook        
      end #styles
    } #wb
      
    axlsxpackage.serialize('test.xlsx')
end #Axlsx::Package

In Excel I can also define patterns (Sorry, German screenshot. It's in "Cell Formatting" SCREENSHOTS

How can I use this patterns via axls?

I looked to the axlsx-code and found some definitions as in Axlsx::PatternFill and a usage in Axlsx::Styles#parse_fill_options, but I found no standard way to use them.

I found a solution with a monkey patched axls:

module Axlsx
  class Styles
    def parse_fill_options(options={})
      return unless options[:bg_color]
      color = Color.new(:rgb=>options[:bg_color])
      dxf = options[:type] == :dxf
      color_key = dxf ? :bgColor : :fgColor
      #~ pattern = PatternFill.new(:patternType =>:solid, color_key=>color)
      pattern = PatternFill.new(:patternType => options[:patternType] || :solid, color_key=>color)
      fill = Fill.new(pattern)
      dxf ? fill : fills << fill
    end
  end
end

The result is enter image description here

Is there a way to avoid this monkey patched code? (beside sending a pull request and wait for the next version)

0

There are 0 best solutions below