diff options
| -rw-r--r-- | CHANGELOG.md | 12 | ||||
| -rw-r--r-- | axlsx.gemspec | 1 | ||||
| -rw-r--r-- | examples/add_border_example.md | 28 | ||||
| -rw-r--r-- | examples/append_styles_example.md | 31 | ||||
| -rw-r--r-- | lib/axlsx/package.rb | 8 | ||||
| -rw-r--r-- | lib/axlsx/stylesheet/styles.rb | 37 | ||||
| -rw-r--r-- | lib/axlsx/workbook/workbook.rb | 28 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/border_creator.rb | 82 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/cell.rb | 31 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/worksheet.rb | 38 | ||||
| -rw-r--r-- | lib/caxlsx.rb | 10 | ||||
| -rw-r--r-- | test/tc_axlsx_styler.rb | 287 |
12 files changed, 593 insertions, 0 deletions
diff --git a/CHANGELOG.md b/CHANGELOG.md index 6791d637..fac4015a 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -1,6 +1,18 @@ CHANGELOG --------- +- **Unreleased - Axlsx Styler** + - [PR #xx](https://github.com/caxlsx/caxlsx/pull/xx)Merge in the gem [`axlsx_styler`](https://github.com/axlsx-styler-gem/axlsx_styler) + - Add ability to both apply or append to existing styles after rows have been created using `worksheet.add_style` + - `worksheet.add_style "A1", {b: true}` + - `worksheet.add_style "A1:B2", {b: true}` + - `worksheet.add_style ["A1", "B2:C7", "D8:E9"], {b: true}` + - Add ability to create borders upon specific areas of the page using `worksheet.add_border` + - `worksheet.add_border "A1", {style: :thin}` + - `worksheet.add_border "A1:B2", {style: :thin}` + - `worksheet.add_border ["A1", "B2:C7", "D8:E9"], {style: :thin}` + - Add `BorderCreator` - TODO: Do we mention this at all? + - **Unreleased** - [PR #155](https://github.com/caxlsx/caxlsx/pull/155) - Add `hideDropDown` alias for `showDropDown` setting, as the latter is confusing to use (because its logic seems inverted). - [PR #143](https://github.com/caxlsx/caxlsx/pull/143) - Add setting `sort_on_headers` for pivot tables diff --git a/axlsx.gemspec b/axlsx.gemspec index 5da4d73f..e9b1f0a9 100644 --- a/axlsx.gemspec +++ b/axlsx.gemspec @@ -18,6 +18,7 @@ Gem::Specification.new do |s| s.add_runtime_dependency 'rubyzip', '>= 1.3.0', '< 3' s.add_runtime_dependency "htmlentities", "~> 4.3", '>= 4.3.4' s.add_runtime_dependency "marcel", '~> 1.0' + s.add_runtime_dependency "activesupport" ### TODO: replace with local? s.add_development_dependency 'yard', "~> 0.9.8" s.add_development_dependency 'kramdown', '~> 2.3' diff --git a/examples/add_border_example.md b/examples/add_border_example.md new file mode 100644 index 00000000..934cf183 --- /dev/null +++ b/examples/add_border_example.md @@ -0,0 +1,28 @@ +## Description + +Shows how to use `add_border` to add a border to four edges of the selected cell range. + +## Code + +```ruby +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row ["", "Product", "Category", "Price"] + sheet.add_row ["", "Butter", "Dairy", 4.99] + sheet.add_row ["", "Bread", "Baked Goods", 3.45] + sheet.add_row ["", "Broccoli", "Produce", 2.99] + + sheet.add_border "B2:D5" + sheet.add_border "B3:D3", { edges: [:top], style: :thick } + sheet.add_border ["C3:C4", "D3:D4"] +end + +p.serialize "add_border.xlsx" +``` + +## Output + + diff --git a/examples/append_styles_example.md b/examples/append_styles_example.md new file mode 100644 index 00000000..0dc7792c --- /dev/null +++ b/examples/append_styles_example.md @@ -0,0 +1,31 @@ +## Description + +Shows how to append styles after rows have been created using `worksheet.add_style` + +## Code + +```ruby +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row ["", "Product", "Category", "Price"] + sheet.add_row ["", "Butter", "Dairy", 4.99] + sheet.add_row ["", "Bread", "Baked Goods", 3.45] + sheet.add_row ["", "Broccoli", "Produce", 2.99] + + sheet.add_style "B2:D2", b: true + sheet.add_style "B2:B5", b: true + sheet.add_style "B2:D2", bg_color: "95AFBA" + sheet.add_style "B3:D5", bg_color: "E2F89C" + sheet.add_style "D3:D5", alignment: { horizontal: :left } + sheet.add_style ["C3:C4", "D3:D4"], fg_color: "00FF00" +nd + +p.serialize "append_styles.xlsx" +``` + +## Output + + diff --git a/lib/axlsx/package.rb b/lib/axlsx/package.rb index ccabf799..6baaa173 100644 --- a/lib/axlsx/package.rb +++ b/lib/axlsx/package.rb @@ -101,6 +101,10 @@ module Axlsx # s = p.to_stream() # File.open('example_streamed.xlsx', 'wb') { |f| f.write(s.read) } def serialize(output, options = {}, secondary_options = nil) + if !workbook.styles_applied + workbook.apply_styles + end + confirm_valid, zip_command = parse_serialize_options(options, secondary_options) return false unless !confirm_valid || self.validate.empty? zip_provider = if zip_command @@ -122,6 +126,10 @@ module Axlsx # @param [Boolean] confirm_valid Validate the package prior to serialization. # @return [StringIO|Boolean] False if confirm_valid and validation errors exist. rewound string IO if not. def to_stream(confirm_valid=false) + if !workbook.styles_applied + workbook.apply_styles + end + return false unless !confirm_valid || self.validate.empty? Relationship.initialize_ids_cache zip = write_parts(Zip::OutputStream.new(StringIO.new.binmode, true)) diff --git a/lib/axlsx/stylesheet/styles.rb b/lib/axlsx/stylesheet/styles.rb index fd741ac5..3d557a1a 100644 --- a/lib/axlsx/stylesheet/styles.rb +++ b/lib/axlsx/stylesheet/styles.rb @@ -120,6 +120,10 @@ module Axlsx load_default_styles end + def style_index + @style_index ||= {} + end + # Drastically simplifies style creation and management. # @return [Integer] # @option options [String] fg_color The text color @@ -219,7 +223,40 @@ module Axlsx # f = File.open('example_differential_styling', 'wb') # p.serialize(f) # + # + # + # + # An index for cell styles where keys are styles codes as per Axlsx::Style and values are Cell#raw_style + # The reason for the backward key/value ordering is that style lookup must be most efficient, while `add_style` can be less efficient def add_style(options={}) + ### TODO: Refractor this + + if options[:type] == :dxf + style_id = original_add_style(options) + else + # Add styles to style_index cache for re-use + + font_defaults = {name: @fonts.first.name, sz: @fonts.first.sz, family: @fonts.first.family} + + raw_style = {type: :xf}.merge(font_defaults).merge(options) + + if raw_style[:format_code] + raw_style.delete(:num_fmt) + end + + style_id = style_index.key(raw_style) + + if !style_id + style_id = original_add_style(options) + + style_index[style_id] = raw_style + end + end + + return style_id + end + + def original_add_style(options={}) # Default to :xf options[:type] ||= :xf raise ArgumentError, "Type must be one of [:xf, :dxf]" unless [:xf, :dxf].include?(options[:type] ) diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index edf719d1..f9e857fc 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -187,6 +187,34 @@ require 'axlsx/workbook/worksheet/selection.rb' @styles end + # An array that holds all cells with styles + attr_accessor :styled_cells + + # Checks if styles are indexed to make it work for pre 0.1.5 version + # users that still explicitly call @workbook.apply_styles + attr_accessor :styles_applied + + # A helper to apply styles that were added using `worksheet.add_style` + # @return [Boolean] + require 'active_support/core_ext/hash/deep_merge' ### TODO: keep or replace with local solution + def apply_styles + return false if !styled_cells + + styled_cells.each do |cell| + current_style = styles.style_index[cell.style] + + if current_style + new_style = current_style.deep_merge(cell.raw_style) + else + new_style = cell.raw_style + end + + cell.style = styles.add_style(new_style) + end + + self.styles_applied = true + end + # Indicates if the epoc date for serialization should be 1904. If false, 1900 is used. @@date1904 = false diff --git a/lib/axlsx/workbook/worksheet/border_creator.rb b/lib/axlsx/workbook/worksheet/border_creator.rb new file mode 100644 index 00000000..a38e78b3 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/border_creator.rb @@ -0,0 +1,82 @@ +# encoding: UTF-8 + +module Axlsx + class BorderCreator + attr_reader :worksheet, :cells, :edges, :width, :color + + def initialize(worksheet, cells, args) + @worksheet = worksheet + @cells = cells + if args.is_a?(Hash) + @edges = args[:edges] || :all + @width = args[:style] || :thin + @color = args[:color] || '000000' + else + @edges = args || :all + @width = :thin + @color = '000000' + end + end + + def draw + selected_edges(edges).each { |edge| add_border(edge, width, color) } + end + + private + + def selected_edges(edges) + all_edges = [:top, :right, :bottom, :left] + if edges == :all + all_edges + elsif edges.is_a?(Array) && edges - all_edges == [] + edges.uniq + else + [] + end + end + + def add_border(position, width, color) + style = { + border: { + style: width, color: color, edges: [position.to_sym] + } + } + worksheet.add_style border_cells[position.to_sym], style + end + + def border_cells + # example range "B2:D5" + { + top: "#{first_cell}:#{last_col}#{first_row}", # "B2:D2" + right: "#{last_col}#{first_row}:#{last_cell}", # "D2:D5" + bottom: "#{first_col}#{last_row}:#{last_cell}", # "B5:D5" + left: "#{first_cell}:#{first_col}#{last_row}" # "B2:B5" + } + end + + def first_cell + @first_cell ||= cells.first.r + end + + def last_cell + @last_cell ||= cells.last.r + end + + def first_row + @first_row ||= first_cell.scan(/\d+/).first + end + + def first_col + @first_col ||= first_cell.scan(/\D+/).first + end + + def last_row + @last_row ||= last_cell.scan(/\d+/).first + end + + def last_col + @last_col ||= last_cell.scan(/\D+/).first + end + + end +end diff --git a/lib/axlsx/workbook/worksheet/cell.rb b/lib/axlsx/workbook/worksheet/cell.rb index 3277b8c5..626cf60f 100644 --- a/lib/axlsx/workbook/worksheet/cell.rb +++ b/lib/axlsx/workbook/worksheet/cell.rb @@ -82,6 +82,37 @@ module Axlsx defined?(@style) ? @style : 0 end + attr_accessor :raw_style + + require 'active_support/core_ext/hash/deep_merge' ### TODO: can/should we remove this dependency + # The index of the cellXfs item to be applied to this cell. + # @param [Hash] styles + # @see Axlsx::Styles + require 'set' ### TODO: move to appropriate place + def add_style(style) + self.raw_style ||= {} + + # using deep_merge from active_support: + # with regular Hash#merge adding borders fails miserably + new_style = raw_style.deep_merge(style) + + all_edges = [:top, :right, :bottom, :left] + + if !raw_style[:border].nil? && !style[:border].nil? + border_at = (raw_style[:border][:edges] || all_edges) + (style[:border][:edges] || all_edges) + new_style[:border][:edges] = border_at.uniq.sort + elsif !style[:border].nil? + new_style[:border] = style[:border] + end + + self.raw_style = new_style + + wb = row.worksheet.workbook + + wb.styled_cells ||= Set.new + wb.styled_cells << self + end + # The row this cell belongs to. # @return [Row] attr_reader :row diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index 921687c8..527d2e71 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -1,4 +1,7 @@ # encoding: UTF-8 + +require_relative "border_creator" + module Axlsx # The Worksheet class represents a worksheet in the workbook. @@ -560,6 +563,41 @@ module Axlsx cells.each { |cell| cell.style = style } end + # Set the style for cells in a specific column + # @param [String|Array] cell references + # @param styles TODO: how to specify this + def add_style(cell_refs, *styles) + if !cell_refs.is_a?(Array) + cell_refs = [cell_refs] + end + + cell_refs.each do |cell_ref| + item = self[cell_ref] + + cells = item.is_a?(Array) ? item : [item] + + cells.each do |cell| + styles.each do |style| + cell.add_style(style) + end + end + end + end + + # Set the style for cells in a specific column + # @param [String|Array] cell references + # @param [Hash|Symbol] options TODO: describe this + def add_border(cell_refs, options = :all) ### TODO: will we support the :all argument + if !cell_refs.is_a?(Array) + cell_refs = [cell_refs] + end + + cell_refs.each do |cell_ref| + cells = self[cell_ref] + Axlsx::BorderCreator.new(self, cells, options).draw + end + end + # Returns a sheet node serialization for this sheet in the workbook. def to_sheet_node_xml_string(str='') add_autofilter_defined_name_to_workbook diff --git a/lib/caxlsx.rb b/lib/caxlsx.rb index a82d49fb..1dcc6005 100644 --- a/lib/caxlsx.rb +++ b/lib/caxlsx.rb @@ -1,2 +1,12 @@ # encoding: UTF-8 require 'axlsx.rb' + +begin + require "axlsx_styler" + + if defined?(AxlsxStyler) + raise StandardError.new("Please remove `axlsx_styler` from your Gemfile, the associated functionality is now built-in to `caxlsx` directly.") + end +rescue LoadError + # Do nothing, all good +end diff --git a/test/tc_axlsx_styler.rb b/test/tc_axlsx_styler.rb new file mode 100644 index 00000000..1ce25481 --- /dev/null +++ b/test/tc_axlsx_styler.rb @@ -0,0 +1,287 @@ +# encoding: UTF-8 +require 'tc_helper.rb' + +class TestAxlsxStyler < Test::Unit::TestCase + + def setup + FileUtils.mkdir_p("tmp/") ### TODO: remove + end + + def test_to_stream_automatically_performs_apply_styles + p = Axlsx::Package.new + wb = p.workbook + + filename = 'to_stream_automatically_performs_apply_styles' + assert_nil wb.styles_applied + wb.add_worksheet do |sheet| + sheet.add_row ['A1', 'B1'] + sheet.add_style 'A1:B1', b: true + end + File.open("tmp/#{filename}.xlsx", 'wb') do |f| + f.write p.to_stream.read + end + assert_equal 1, wb.styles.style_index.count + end + + def test_serialize_automatically_performs_apply_styles + p = Axlsx::Package.new + wb = p.workbook + + filename = 'without_apply_styles_serialize' + assert_nil wb.styles_applied + wb.add_worksheet do |sheet| + sheet.add_row ['A1', 'B1'] + sheet.add_style 'A1:B1', b: true + end + p.serialize("tmp/#{filename}.xlsx") + assert_equal true, wb.styles_applied + assert_equal 1, wb.styles.style_index.count + end + + def test_merge_styles_1 + p = Axlsx::Package.new + wb = p.workbook + + filename = 'merge_styles_1' + bold = wb.styles.add_style b: true + + wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row ['', '1', '2', '3'], style: [nil, bold] + sheet.add_row ['', '4', '5', '6'], style: bold + sheet.add_row ['', '7', '8', '9'] + sheet.add_style 'B2:D4', b: true + sheet.add_border 'B2:D4', { style: :thin, color: '000000' } + end + wb.apply_styles + assert_equal 9, wb.styles.style_index.count + p.serialize("tmp/#{filename}.xlsx") + assert_equal true, wb.styles_applied + end + + def test_merge_styles_2 + p = Axlsx::Package.new + wb = p.workbook + + filename = 'merge_styles_2' + bold = wb.styles.add_style b: true + + wb.add_worksheet do |sheet| + sheet.add_row ['A1', 'B1'], style: [nil, bold] + sheet.add_row ['A2', 'B2'], style: bold + sheet.add_row ['A3', 'B3'] + sheet.add_style 'A1:A2', i: true + end + wb.apply_styles + assert_equal 3, wb.styles.style_index.count + p.serialize("tmp/#{filename}.xlsx") + assert_equal true, wb.styles_applied + end + + def test_merge_styles_3 + p = Axlsx::Package.new + wb = p.workbook + + filename = 'merge_styles_3' + bold = wb.styles.add_style b: true + + wb.add_worksheet do |sheet| + sheet.add_row ['A1', 'B1'], style: [nil, bold] + sheet.add_row ['A2', 'B2'] + sheet.add_style 'B1:B2', bg_color: 'FF0000' + end + wb.apply_styles + assert_equal 3, wb.styles.style_index.count + p.serialize("tmp/#{filename}.xlsx") + assert_equal true, wb.styles_applied + end + + def test_table_with_borders + p = Axlsx::Package.new + wb = p.workbook + + filename = 'borders_test' + wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row ['', 'Product', 'Category', 'Price'] + sheet.add_row ['', 'Butter', 'Dairy', 4.99] + sheet.add_row ['', 'Bread', 'Baked Goods', 3.45] + sheet.add_row ['', 'Broccoli', 'Produce', 2.99] + sheet.add_row ['', 'Pizza', 'Frozen Foods', 4.99] + sheet.column_widths 5, 20, 20, 20 + + sheet.add_style 'B2:D2', b: true + sheet.add_style 'B2:B6', b: true + sheet.add_style 'B2:D2', bg_color: '95AFBA' + sheet.add_style 'B3:D6', bg_color: 'E2F89C' + sheet.add_style 'D3:D6', alignment: { horizontal: :left } + sheet.add_border 'B2:D6' + sheet.add_border 'B3:D3', [:top] + sheet.add_border 'B3:D3', edges: [:bottom], style: :medium + sheet.add_border 'B3:D3', edges: [:bottom], style: :medium, color: '32f332' + end + p.serialize("tmp/#{filename}.xlsx") + assert_equal true, wb.styles_applied + assert_equal 12, wb.styles.style_index.count + assert_equal 12 + 2, wb.styles.style_index.keys.max + end + + def test_duplicate_borders + p = Axlsx::Package.new + wb = p.workbook + + filename = 'duplicate_borders_test' + wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row ['', 'B2', 'C2', 'D2'] + sheet.add_row ['', 'B3', 'C3', 'D3'] + sheet.add_row ['', 'B4', 'C4', 'D4'] + + sheet.add_border 'B2:D4' + sheet.add_border 'B2:D4' + end + p.serialize("tmp/#{filename}.xlsx") + assert_equal true, wb.styles_applied + assert_equal 8, wb.styles.style_index.count + assert_equal 8, wb.styled_cells.count + end + + def test_multiple_style_borders_on_same_cells + p = Axlsx::Package.new + wb = p.workbook + + filename = 'multiple_style_borders' + wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row ['', 'B2', 'C2', 'D2'] + sheet.add_row ['', 'B3', 'C3', 'D3'] + + sheet.add_border 'B2:D3', :all + sheet.add_border 'B2:D2', edges: [:bottom], style: :thick, color: 'ff0000' + end + p.serialize("tmp/#{filename}.xlsx") + assert_equal true, wb.styles_applied + assert_equal 6, wb.styles.style_index.count + assert_equal 6, wb.styled_cells.count + + b2_cell_style = { + border: { + style: :thick, + color: 'ff0000', + edges: [:bottom, :left, :top] + }, + type: :xf, + name: 'Arial', + sz: 11, + family: 1 + } + assert_equal b2_cell_style, wb.styles.style_index.values.find{|x| x == b2_cell_style} + + d3_cell_style = { + border: { + style: :thin, + color: '000000', + edges: [:bottom, :right] + }, + type: :xf, + name: 'Arial', + sz: 11, + family: 1 + } + assert_equal d3_cell_style, wb.styles.style_index.values.find{|x| x == d3_cell_style} + end + + def test_mixed_borders_1 + p = Axlsx::Package.new + wb = p.workbook + + filename = 'mixed_borders_1' + wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row ['', '1', '2', '3'] + sheet.add_row ['', '4', '5', '6'] + sheet.add_row ['', '7', '8', '9'] + sheet.add_style 'B2:D4', border: { style: :thin, color: '000000' } + sheet.add_border 'C3:D4', style: :medium + end + wb.apply_styles + assert_equal 9, wb.styled_cells.count + assert_equal 2, wb.styles.style_index.count + p.serialize("tmp/#{filename}.xlsx") + assert_equal true, wb.styles_applied + end + + def test_mixed_borders_2 + p = Axlsx::Package.new + wb = p.workbook + + filename = 'mixed_borders_2' + wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row ['', '1', '2', '3'] + sheet.add_row ['', '4', '5', '6'] + sheet.add_row ['', '7', '8', '9'] + sheet.add_border 'B2:D4', style: :medium + sheet.add_style 'D2:D4', border: { style: :thin, color: '000000' } + end + wb.apply_styles + assert_equal 8, wb.styled_cells.count + assert_equal 6, wb.styles.style_index.count + p.serialize("tmp/#{filename}.xlsx") + assert_equal true, wb.styles_applied + end + + def test_dxf_cell + p = Axlsx::Package.new + wb = p.workbook + + wb.add_worksheet do |sheet| + sheet.add_row (1..2).to_a + sheet.add_style "A1:A1", { bg_color: "AA0000" } + + sheet.add_row (1..2).to_a + sheet.add_style "B1:B1", { bg_color: "CC0000" } + + sheet.add_row (1..2).to_a + sheet.add_style "A3:B3", { bg_color: "00FF00" } + + wb.styles.add_style(bg_color: "0000FF", type: :dxf) + end + + p.serialize("tmp/test_dxf_cell.xlsx") + assert_equal true, wb.styles_applied + + assert_equal 1, wb.styles.dxfs.count + + assert_equal 6, wb.styles.cellXfs.count + end + + def test_default_font_with_style_index + p = Axlsx::Package.new + wb = p.workbook + + wb.styles.fonts[0].name = 'Pontiac' ### TODO, is this a valid font name in all environments + wb.styles.fonts[0].sz = 12 + + wb.add_worksheet do |sheet| + sheet.add_row [1,2,3] + sheet.add_style "A1:C1", { color: "FFFFFF" } + end + + wb.apply_styles + + assert_equal 1, wb.styles.style_index.size + + assert_equal( + { + type: :xf, + name: "Pontiac", + sz: 12, + family: 1, + color: "FFFFFF", + }, + wb.styles.style_index.values.first + ) + end + +end |