From 482421e72c3ec92e02628ccf70742e6bbb2dbed2 Mon Sep 17 00:00:00 2001 From: Olle Jonsson Date: Mon, 1 Mar 2021 11:20:33 +0100 Subject: CI: Add JRuby 9.2 --- .travis.yml | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/.travis.yml b/.travis.yml index 465df230..f157727e 100644 --- a/.travis.yml +++ b/.travis.yml @@ -22,15 +22,17 @@ matrix: - rvm: 3.0.0 - rvm: jruby-19mode - rvm: jruby-9.1.17.0 + - rvm: jruby-9.2.15.0 - rvm: ruby-head - rvm: jruby-head allow_failures: - rvm: ruby-head - rvm: jruby-9.1.17.0 + - rvm: jruby-9.2.15.0 - rvm: jruby-head env: global: - JRUBY_OPTS="-Xcli.debug=true --debug" -# https://github.com/jruby/jruby/wiki/FAQs#why-is-jruby-so-slow-to-install-via-rvm \ No newline at end of file +# https://github.com/jruby/jruby/wiki/FAQs#why-is-jruby-so-slow-to-install-via-rvm -- cgit v1.2.3 From 0146438f571c9c36f77b525164d4b190a81b4981 Mon Sep 17 00:00:00 2001 From: Koza Date: Wed, 17 Feb 2021 17:20:34 +0100 Subject: Add conditional formatting example for text equality --- examples/README.md | 1 + .../conditional_formatting_text_equal_example.md | 37 +++++++++++++++++++++ .../conditional_formatting_text_equal_example.png | Bin 0 -> 24727 bytes 3 files changed, 38 insertions(+) create mode 100644 examples/conditional_formatting_text_equal_example.md create mode 100644 examples/images/conditional_formatting_text_equal_example.png diff --git a/examples/README.md b/examples/README.md index 5b1abdc2..f1ab1d5b 100644 --- a/examples/README.md +++ b/examples/README.md @@ -34,6 +34,7 @@ Usage: * [Color scale](conditional_formatting_color_scale_example.md) * [Data bar](conditional_formatting_data_bar_example.md) * [Icon set](conditional_formatting_icon_set_example.md) +* [Text equal](conditional_formatting_text_equal_example.md) ### Charts diff --git a/examples/conditional_formatting_text_equal_example.md b/examples/conditional_formatting_text_equal_example.md new file mode 100644 index 00000000..0d7bd8d7 --- /dev/null +++ b/examples/conditional_formatting_text_equal_example.md @@ -0,0 +1,37 @@ +## Description + +Conditional format example: Text equal + +1. You must specify `:containsText` for both type and operator. +2. You must craft a formula to match what you are looking for. The formula needs to reference the top-left cell of the range (the cell reference will be dynamically adapted when the formula gets evaluated for the other cells in the range). +3. The formula may turn out to be vendor specific. You will want to test extensively if interoperability beyond excel is a concern. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +profit = s.add_style bg_color: 'FF428751', type: :dxf + +wb.add_worksheet(name: 'Text Matching Conditional') do |sheet| + sheet.add_row ["Loss", "Loss", "Profit", "Loss", "Profit", "Loss", "Profit", "Loss", "Profit", "Profit"] + + # Highlight all the cells containing the text "Profit" + sheet.add_conditional_formatting('A1:J1', + type: :containsText, + operator: :containsText, + formula: 'NOT(ISERROR(SEARCH("Profit",A1)))', + dxfId: profit, + priority: 1) +end + +p.serialize 'conditional_formatting_text_equal_example.xlsx' +``` + +## Output + +![Output](images/conditional_formatting_text_equal_example.png "Output") diff --git a/examples/images/conditional_formatting_text_equal_example.png b/examples/images/conditional_formatting_text_equal_example.png new file mode 100644 index 00000000..60cfaf92 Binary files /dev/null and b/examples/images/conditional_formatting_text_equal_example.png differ -- cgit v1.2.3 From 927849bb091f9858fe54d2b48742aa531f21dfb3 Mon Sep 17 00:00:00 2001 From: Olle Jonsson Date: Fri, 19 Mar 2021 08:34:23 +0100 Subject: CI: Use jruby-9.2.16.0 See https://www.jruby.org/2021/03/03/jruby-9-2-16-0.html --- .travis.yml | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/.travis.yml b/.travis.yml index f157727e..23b66c5e 100644 --- a/.travis.yml +++ b/.travis.yml @@ -22,13 +22,13 @@ matrix: - rvm: 3.0.0 - rvm: jruby-19mode - rvm: jruby-9.1.17.0 - - rvm: jruby-9.2.15.0 + - rvm: jruby-9.2.16.0 - rvm: ruby-head - rvm: jruby-head allow_failures: - rvm: ruby-head - rvm: jruby-9.1.17.0 - - rvm: jruby-9.2.15.0 + - rvm: jruby-9.2.16.0 - rvm: jruby-head env: -- cgit v1.2.3 From 33e2771710a1f0a7740d15cf0b9925f9079d486b Mon Sep 17 00:00:00 2001 From: "Knut I. Stenmark" Date: Fri, 19 Mar 2021 11:47:49 +0100 Subject: Add support for format in pivot tables (#79) --- lib/axlsx/workbook/worksheet/pivot_table.rb | 9 +++++++-- test/workbook/worksheet/tc_pivot_table.rb | 8 ++++++++ 2 files changed, 15 insertions(+), 2 deletions(-) diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb index 55174968..d8c630ec 100644 --- a/lib/axlsx/workbook/worksheet/pivot_table.rb +++ b/lib/axlsx/workbook/worksheet/pivot_table.rb @@ -111,8 +111,12 @@ module Axlsx if data_field.is_a? String data_field = {:ref => data_field} end - data_field.values.each do |value| - DataTypeValidator.validate "#{self.class}.data[]", [String], value + data_field.each do |key, value| + if key == :num_fmt + DataTypeValidator.validate "#{self.class}.data[]", [Integer], value + else + DataTypeValidator.validate "#{self.class}.data[]", [String], value + end end @data << data_field end @@ -212,6 +216,7 @@ module Axlsx data.each do |datum_value| # The correct name prefix in ["Sum","Average", etc...] str << "" end diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb index 6d7f4d1c..a494dcfd 100644 --- a/test/workbook/worksheet/tc_pivot_table.rb +++ b/test/workbook/worksheet/tc_pivot_table.rb @@ -132,4 +132,12 @@ class TestPivotTable < Test::Unit::TestCase end shared_test_pivot_table_xml_validity(pivot_table) end + + def test_add_pivot_table_with_format_options_on_data_field + pivot_table = @ws.add_pivot_table('G5:G6', 'A1:E5') do |pt| + pt.data = [{:ref=>"Sales", :subtotal => 'sum', num_fmt: 4}] + end + doc = Nokogiri::XML(pivot_table.to_xml_string) + assert_equal('4', doc.at_css('dataFields dataField')['numFmtId'], 'adding format options to pivot_table') + end end -- cgit v1.2.3 From 421c383a1f8ba9b0257ac81da53f74178bae1446 Mon Sep 17 00:00:00 2001 From: Stefan Date: Fri, 19 Mar 2021 12:05:27 +0100 Subject: Number format in pivot tables: update changelog and pivot table example --- CHANGELOG.md | 1 + examples/pivot_table_example.md | 2 +- 2 files changed, 2 insertions(+), 1 deletion(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index 9d951aae..1ed85f1e 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -2,6 +2,7 @@ CHANGELOG --------- - **Unreleased** + - [PR #79](https://github.com/caxlsx/caxlsx/pull/79) - Add support for format in pivot tables - **January.5.21**: 3.0.4 - [PR #72](https://github.com/caxlsx/caxlsx/pull/72) - Relax Ruby dependency to allow for Ruby 3. This required Travis to be upgraded from Ubuntu Trusty to Ubuntu Bionic. rbx-3 was dropped. diff --git a/examples/pivot_table_example.md b/examples/pivot_table_example.md index 21176a73..8dd12955 100644 --- a/examples/pivot_table_example.md +++ b/examples/pivot_table_example.md @@ -27,7 +27,7 @@ wb.add_worksheet(name: 'Basic Worksheet') do |sheet| sheet.add_pivot_table 'G4:L17', 'A1:E31' do |pivot_table| pivot_table.rows = ['Month', 'Year'] pivot_table.columns = ['Type'] - pivot_table.data = ['Sales'] + pivot_table.data = [ref: 'Sales', num_fmt: 4] pivot_table.pages = ['Region'] end end -- cgit v1.2.3 From ed329dae5d9a69a11726a0ceba27e77aaf8adaa0 Mon Sep 17 00:00:00 2001 From: Stefan Date: Fri, 19 Mar 2021 12:12:27 +0100 Subject: Update changelog --- CHANGELOG.md | 2 ++ 1 file changed, 2 insertions(+) diff --git a/CHANGELOG.md b/CHANGELOG.md index 1ed85f1e..fb1808d7 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -2,6 +2,8 @@ CHANGELOG --------- - **Unreleased** + - [PR #57](https://github.com/caxlsx/caxlsx/pull/57) - Deprecate using #serialize with boolean argument: Calls like `Package#serialize("name.xlsx", false)` should be replaced with `Package#serialize("name.xlsx", confirm_valid: false)`. + - [PR #77](https://github.com/caxlsx/caxlsx/pull/77) - Fix special characters in table header - [PR #79](https://github.com/caxlsx/caxlsx/pull/79) - Add support for format in pivot tables - **January.5.21**: 3.0.4 -- cgit v1.2.3 From 37acfa2e3692fbc278e3a06df457fde8d88a5f01 Mon Sep 17 00:00:00 2001 From: Agustin Gomez <57372662+agomezcampero@users.noreply.github.com> Date: Fri, 19 Mar 2021 10:50:03 -0300 Subject: Implement :offset option for worksheet#add_row (#87) --- examples/complex_example.md | 48 +++++++++++++++---------------- lib/axlsx/workbook/worksheet/row.rb | 8 ++++-- lib/axlsx/workbook/worksheet/worksheet.rb | 4 +++ test/workbook/worksheet/tc_row.rb | 21 ++++++++++++++ 4 files changed, 54 insertions(+), 27 deletions(-) diff --git a/examples/complex_example.md b/examples/complex_example.md index 51b85e6b..4f395b02 100644 --- a/examples/complex_example.md +++ b/examples/complex_example.md @@ -22,34 +22,34 @@ t_money = s.add_style b: true, num_fmt: 5, bg_color: 'FFDFDEDF' wb.add_worksheet do |sheet| sheet.add_row - sheet.add_row [nil, 'College Budget'], style: [nil, header] + sheet.add_row ['College Budget'], style: header, offset: 1 sheet.add_row - sheet.add_row [nil, "What's coming in this month.", nil, nil, 'How am I doing'], style: tbl_header - sheet.add_row [nil, 'Item', 'Amount', nil, 'Item', 'Amount'], style: [nil, ind_header, col_header, nil, ind_header, col_header] - sheet.add_row [nil, 'Estimated monthly net income', 500, nil, 'Monthly income', '=C9'], style: [nil, label, money, nil, label, money] - sheet.add_row [nil, 'Financial aid', 100, nil, 'Monthly expenses', '=C27'], style: [nil, label, money, nil, label, money] - sheet.add_row [nil, 'Allowance from mom & dad', 20000, nil, 'Semester expenses', '=F19'], style: [nil, label, money, nil, label, money] - sheet.add_row [nil, 'Total', '=SUM(C6:C8)', nil, 'Difference', '=F6 - SUM(F7:F8)'], style: [nil, t_label, t_money, nil, t_label, t_money] + sheet.add_row ["What's coming in this month.", nil, nil, 'How am I doing'], style: tbl_header, offset: 1 + sheet.add_row ['Item', 'Amount', nil, 'Item', 'Amount'], style: [ind_header, col_header, nil, ind_header, col_header], offset: 1 + sheet.add_row ['Estimated monthly net income', 500, nil, 'Monthly income', '=C9'], style: [label, money, nil, label, money], offset: 1 + sheet.add_row ['Financial aid', 100, nil, 'Monthly expenses', '=C27'], style: [label, money, nil, label, money], offset: 1 + sheet.add_row ['Allowance from mom & dad', 20000, nil, 'Semester expenses', '=F19'], style: [label, money, nil, label, money], offset: 1 + sheet.add_row ['Total', '=SUM(C6:C8)', nil, 'Difference', '=F6 - SUM(F7:F8)'], style: [t_label, t_money, nil, t_label, t_money], offset: 1 sheet.add_row - sheet.add_row [nil, "What's going out this month.", nil, nil, 'Semester Costs'], style: tbl_header - sheet.add_row [nil, 'Item', 'Amount', nil, 'Item', 'Amount'], style: [nil, ind_header, col_header, nil, ind_header, col_header] - sheet.add_row [nil, 'Rent', 650, nil, 'Tuition', 200], style: [nil, label, money, nil, label, money] - sheet.add_row [nil, 'Utilities', 120, nil, 'Lab fees', 50], style: [nil, label, money, nil, label, money] - sheet.add_row [nil, 'Cell phone', 100, nil, 'Other fees', 10], style: [nil, label, money, nil, label, money] - sheet.add_row [nil, 'Groceries', 75, nil, 'Books', 150], style: [nil, label, money, nil, label, money] - sheet.add_row [nil, 'Auto expenses', 0, nil, 'Deposits', 0], style: [nil, label, money, nil, label, money] - sheet.add_row [nil, 'Student loans', 0, nil, 'Transportation', 30], style: [nil, label, money, nil, label, money] - sheet.add_row [nil, 'Other loans', 350, nil, 'Total', '=SUM(F13:F18)'], style: [nil, label, money, nil, t_label, t_money] - sheet.add_row [nil, 'Credit cards', 450], style: [nil, label, money] - sheet.add_row [nil, 'Insurance', 0], style: [nil, label, money] - sheet.add_row [nil, 'Laundry', 10], style: [nil, label, money] - sheet.add_row [nil, 'Haircuts', 0], style: [nil, label, money] - sheet.add_row [nil, 'Medical expenses', 0], style: [nil, label, money] - sheet.add_row [nil, 'Entertainment', 500], style: [nil, label, money] - sheet.add_row [nil, 'Miscellaneous', 0], style: [nil, label, money] - sheet.add_row [nil, 'Total', '=SUM(C13:C26)'], style: [nil, t_label, t_money] + sheet.add_row ["What's going out this month.", nil, nil, 'Semester Costs'], style: tbl_header, offset: 1 + sheet.add_row ['Item', 'Amount', nil, 'Item', 'Amount'], style: [ind_header, col_header, nil, ind_header, col_header], offset: 1 + sheet.add_row ['Rent', 650, nil, 'Tuition', 200], style: [label, money, nil, label, money], offset: 1 + sheet.add_row ['Utilities', 120, nil, 'Lab fees', 50], style: [label, money, nil, label, money], offset: 1 + sheet.add_row ['Cell phone', 100, nil, 'Other fees', 10], style: [label, money, nil, label, money], offset: 1 + sheet.add_row ['Groceries', 75, nil, 'Books', 150], style: [label, money, nil, label, money], offset: 1 + sheet.add_row ['Auto expenses', 0, nil, 'Deposits', 0], style: [label, money, nil, label, money], offset: 1 + sheet.add_row ['Student loans', 0, nil, 'Transportation', 30], style: [label, money, nil, label, money], offset: 1 + sheet.add_row ['Other loans', 350, nil, 'Total', '=SUM(F13:F18)'], style: [label, money, nil, t_label, t_money], offset: 1 + sheet.add_row ['Credit cards', 450], style: [label, money], offset: 1 + sheet.add_row ['Insurance', 0], style: [label, money], offset: 1 + sheet.add_row ['Laundry', 10], style: [label, money], offset: 1 + sheet.add_row ['Haircuts', 0], style: [label, money], offset: 1 + sheet.add_row ['Medical expenses', 0], style: [label, money], offset: 1 + sheet.add_row ['Entertainment', 500], style: [label, money], offset: 1 + sheet.add_row ['Miscellaneous', 0], style: [label, money], offset: 1 + sheet.add_row ['Total', '=SUM(C13:C26)'], style: [t_label, t_money], offset: 1 sheet.add_chart(Axlsx::Pie3DChart) do |chart| chart.title = sheet['B11'] diff --git a/lib/axlsx/workbook/worksheet/row.rb b/lib/axlsx/workbook/worksheet/row.rb index decd27c7..80937cf2 100644 --- a/lib/axlsx/workbook/worksheet/row.rb +++ b/lib/axlsx/workbook/worksheet/row.rb @@ -25,11 +25,12 @@ module Axlsx # @option options [Array, Symbol] types # @option options [Array, Integer] style # @option options [Float] height the row's height (in points) + # @option options [Integer] offset - add empty columns before values # @see Row#array_to_cells # @see Cell def initialize(worksheet, values=[], options={}) self.worksheet = worksheet - super(Cell, nil, values.size) + super(Cell, nil, values.size + options[:offset].to_i) self.height = options.delete(:height) worksheet.rows << self array_to_cells(values, options) @@ -147,14 +148,15 @@ module Axlsx # @option options [Array, Integer] style def array_to_cells(values, options={}) DataTypeValidator.validate :array_to_cells, Array, values - types, style, formula_values, escape_formulas = options.delete(:types), options.delete(:style), options.delete(:formula_values), options.delete(:escape_formulas) + types, style, formula_values, escape_formulas, offset = options.delete(:types), options.delete(:style), options.delete(:formula_values), options.delete(:escape_formulas), options.delete(:offset) + offset.to_i.times { |index| self[index] = Cell.new(self) } if offset values.each_with_index do |value, index| options[:style] = style.is_a?(Array) ? style[index] : style if style options[:type] = types.is_a?(Array) ? types[index] : types if types options[:escape_formulas] = escape_formulas.is_a?(Array) ? escape_formulas[index] : escape_formulas if escape_formulas options[:formula_value] = formula_values[index] if formula_values.is_a?(Array) - self[index] = Cell.new(self, value, options) + self[index + offset.to_i] = Cell.new(self, value, options) end end end diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index 9f3966f8..06a5e7f1 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -393,6 +393,9 @@ module Axlsx # @example - specify whether a certain cells in a row should escape formulas or not # ws.add_row ['=IF(2+2=4,4,5)', '=IF(13+13=4,4,5)'], :escape_formulas=>[true, false] # + # @example - add a column offset when adding a row (inserts 'n' blank, unstyled columns before data) + # ws.add_row ['I wish', 'for a fish', 'on my fish wish dish'], offset: 3 + # # @see Worksheet#column_widths # @return [Row] # @option options [Array] values @@ -400,6 +403,7 @@ module Axlsx # @option options [Array, Integer] style # @option options [Array] widths each member of the widths array will affect how auto_fit behavies. # @option options [Float] height the row's height (in points) + # @option options [Integer] offset - add empty columns before values # @option options [Array, Boolean] escape_formulas - Whether to treat a value starting with an equal # sign as formula (default) or as simple string. # Allowing user generated data to be interpreted as formulas can be dangerous diff --git a/test/workbook/worksheet/tc_row.rb b/test/workbook/worksheet/tc_row.rb index f909129c..e6dc3ef1 100644 --- a/test/workbook/worksheet/tc_row.rb +++ b/test/workbook/worksheet/tc_row.rb @@ -136,4 +136,25 @@ class TestRow < Test::Unit::TestCase assert_equal(r_s_xml.xpath(".//row[@r=1][@ht=20][@customHeight=1]").size, 1) end + def test_offsets + offset = 3 + values = [1,2,3,4,5] + r = @ws.add_row(values, offset: offset, style: 1) + r.cells.each_with_index do |c, index| + assert_equal(c.style, index < offset ? 0 : 1) + assert_equal(c.value, index < offset ? nil : values[index - offset]) + end + end + + def test_offsets_with_styles + offset = 3 + values = [1,2,3,4,5] + styles = [6,7,8,9,10] + r = @ws.add_row(values, offset: offset, style: styles) + r.cells.each_with_index do |c, index| + assert_equal(c.style, index < offset ? 0 : styles[index-offset]) + assert_equal(c.value, index < offset ? nil : values[index - offset]) + end + end + end -- cgit v1.2.3 From e7d3c5c452511bfb595159bab8667193d18fa38f Mon Sep 17 00:00:00 2001 From: Stefan Daschek Date: Fri, 19 Mar 2021 14:51:30 +0100 Subject: Update CHANGELOG.md --- CHANGELOG.md | 1 + 1 file changed, 1 insertion(+) diff --git a/CHANGELOG.md b/CHANGELOG.md index fb1808d7..265df641 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -5,6 +5,7 @@ CHANGELOG - [PR #57](https://github.com/caxlsx/caxlsx/pull/57) - Deprecate using #serialize with boolean argument: Calls like `Package#serialize("name.xlsx", false)` should be replaced with `Package#serialize("name.xlsx", confirm_valid: false)`. - [PR #77](https://github.com/caxlsx/caxlsx/pull/77) - Fix special characters in table header - [PR #79](https://github.com/caxlsx/caxlsx/pull/79) - Add support for format in pivot tables + - [PR #87|(https://github.com/caxlsx/caxlsx/pull/87) - Implement :offset option for worksheet#add_row - **January.5.21**: 3.0.4 - [PR #72](https://github.com/caxlsx/caxlsx/pull/72) - Relax Ruby dependency to allow for Ruby 3. This required Travis to be upgraded from Ubuntu Trusty to Ubuntu Bionic. rbx-3 was dropped. -- cgit v1.2.3 From 90bd6c37147312afca58b9862f109d06f6e29699 Mon Sep 17 00:00:00 2001 From: Stefan Daschek Date: Fri, 19 Mar 2021 14:51:50 +0100 Subject: Update CHANGELOG.md --- CHANGELOG.md | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index 265df641..a85a161a 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -5,7 +5,7 @@ CHANGELOG - [PR #57](https://github.com/caxlsx/caxlsx/pull/57) - Deprecate using #serialize with boolean argument: Calls like `Package#serialize("name.xlsx", false)` should be replaced with `Package#serialize("name.xlsx", confirm_valid: false)`. - [PR #77](https://github.com/caxlsx/caxlsx/pull/77) - Fix special characters in table header - [PR #79](https://github.com/caxlsx/caxlsx/pull/79) - Add support for format in pivot tables - - [PR #87|(https://github.com/caxlsx/caxlsx/pull/87) - Implement :offset option for worksheet#add_row + - [PR #87](https://github.com/caxlsx/caxlsx/pull/87) - Implement :offset option for worksheet#add_row - **January.5.21**: 3.0.4 - [PR #72](https://github.com/caxlsx/caxlsx/pull/72) - Relax Ruby dependency to allow for Ruby 3. This required Travis to be upgraded from Ubuntu Trusty to Ubuntu Bionic. rbx-3 was dropped. -- cgit v1.2.3 From 46c2ff10319cf068c15052ab2a320b974699e6e9 Mon Sep 17 00:00:00 2001 From: Stefan Date: Sat, 27 Mar 2021 01:15:54 +0100 Subject: Fix test MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit The styles need to actually exist, otherwise the test fails with “ArgumentError: Invalid cellXfs id”. --- test/workbook/worksheet/tc_row.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/test/workbook/worksheet/tc_row.rb b/test/workbook/worksheet/tc_row.rb index e6dc3ef1..38b13806 100644 --- a/test/workbook/worksheet/tc_row.rb +++ b/test/workbook/worksheet/tc_row.rb @@ -149,7 +149,7 @@ class TestRow < Test::Unit::TestCase def test_offsets_with_styles offset = 3 values = [1,2,3,4,5] - styles = [6,7,8,9,10] + styles = (1..5).map{ @ws.workbook.styles.add_style } r = @ws.add_row(values, offset: offset, style: styles) r.cells.each_with_index do |c, index| assert_equal(c.style, index < offset ? 0 : styles[index-offset]) -- cgit v1.2.3 From 6072a9f6052f2022f5281f4ce2a0df499c01d07a Mon Sep 17 00:00:00 2001 From: Jorne Kandziora Date: Sat, 27 Mar 2021 11:41:11 +0100 Subject: Replace mimemagic with marcel (#95) Mimemagic requires users to download or install additional files. Rails used Marcel as an abstration layer on this library. Marcel was updated to another file matching database. Following Rails in this matter will remove the need for users to do additional work, just to use caxlsx. --- axlsx.gemspec | 2 +- lib/axlsx.rb | 2 +- lib/axlsx/util/mime_type_utils.rb | 2 +- test/tc_helper.rb | 2 -- test/tc_package.rb | 2 +- 5 files changed, 4 insertions(+), 6 deletions(-) diff --git a/axlsx.gemspec b/axlsx.gemspec index 3f8a5008..2b5c96ec 100644 --- a/axlsx.gemspec +++ b/axlsx.gemspec @@ -19,7 +19,7 @@ Gem::Specification.new do |s| s.add_runtime_dependency 'nokogiri', '~> 1.10', '>= 1.10.4' s.add_runtime_dependency 'rubyzip', '>= 1.3.0', '< 3' s.add_runtime_dependency "htmlentities", "~> 4.3", '>= 4.3.4' - s.add_runtime_dependency "mimemagic", '~> 0.3' + s.add_runtime_dependency "marcel", '~> 1.0' s.add_development_dependency 'yard', "~> 0.9.8" s.add_development_dependency 'kramdown', '~> 2.3' diff --git a/lib/axlsx.rb b/lib/axlsx.rb index be4a2cd5..5be3c526 100644 --- a/lib/axlsx.rb +++ b/lib/axlsx.rb @@ -1,7 +1,7 @@ # encoding: UTF-8 require 'htmlentities' require 'axlsx/version.rb' -require 'mimemagic' +require 'marcel' require 'axlsx/util/simple_typed_list.rb' require 'axlsx/util/constants.rb' diff --git a/lib/axlsx/util/mime_type_utils.rb b/lib/axlsx/util/mime_type_utils.rb index 3fe2dbbd..9ad56630 100644 --- a/lib/axlsx/util/mime_type_utils.rb +++ b/lib/axlsx/util/mime_type_utils.rb @@ -5,7 +5,7 @@ module Axlsx # @param [String] v File path # @return [String] File mime type def self.get_mime_type(v) - MimeMagic.by_magic(File.open(v)).to_s + Marcel::MimeType.for(Pathname.new(v)) end end end diff --git a/test/tc_helper.rb b/test/tc_helper.rb index 396f19b2..af40a1e4 100644 --- a/test/tc_helper.rb +++ b/test/tc_helper.rb @@ -8,5 +8,3 @@ end require 'test/unit' require "timecop" require "axlsx.rb" -# MIME detection for Microsoft Office 2007+ formats -require 'mimemagic/overlay' diff --git a/test/tc_package.rb b/test/tc_package.rb index d313c2bd..f1ce0421 100644 --- a/test/tc_package.rb +++ b/test/tc_package.rb @@ -228,7 +228,7 @@ class TestPackage < Test::Unit::TestCase end def test_serialization_creates_files_with_excel_mime_type - assert_equal(MimeMagic.by_magic(@package.to_stream).type, + assert_equal(Marcel::MimeType.for(@package.to_stream), 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') end -- cgit v1.2.3 From aa361aa0518f785f2be32bb06c2621db631d17eb Mon Sep 17 00:00:00 2001 From: Stefan Daschek Date: Sat, 27 Mar 2021 11:44:28 +0100 Subject: Update CHANGELOG.md --- CHANGELOG.md | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index a85a161a..9970a462 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -2,10 +2,11 @@ CHANGELOG --------- - **Unreleased** - - [PR #57](https://github.com/caxlsx/caxlsx/pull/57) - Deprecate using #serialize with boolean argument: Calls like `Package#serialize("name.xlsx", false)` should be replaced with `Package#serialize("name.xlsx", confirm_valid: false)`. - - [PR #77](https://github.com/caxlsx/caxlsx/pull/77) - Fix special characters in table header - - [PR #79](https://github.com/caxlsx/caxlsx/pull/79) - Add support for format in pivot tables + - [PR #95](https://github.com/caxlsx/caxlsx/pull/95) - Replace mimemagic with marcel - [PR #87](https://github.com/caxlsx/caxlsx/pull/87) - Implement :offset option for worksheet#add_row + - [PR #79](https://github.com/caxlsx/caxlsx/pull/79) - Add support for format in pivot tables + - [PR #77](https://github.com/caxlsx/caxlsx/pull/77) - Fix special characters in table header + - [PR #57](https://github.com/caxlsx/caxlsx/pull/57) - Deprecate using #serialize with boolean argument: Calls like `Package#serialize("name.xlsx", false)` should be replaced with `Package#serialize("name.xlsx", confirm_valid: false)`. - **January.5.21**: 3.0.4 - [PR #72](https://github.com/caxlsx/caxlsx/pull/72) - Relax Ruby dependency to allow for Ruby 3. This required Travis to be upgraded from Ubuntu Trusty to Ubuntu Bionic. rbx-3 was dropped. -- cgit v1.2.3 From d94f489bc13715ecb3c87d7582bd931ff1a15737 Mon Sep 17 00:00:00 2001 From: Noel Peden Date: Sun, 28 Mar 2021 06:20:38 -0700 Subject: Version bump to 3.1.0 --- CHANGELOG.md | 2 +- lib/axlsx/version.rb | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index 9970a462..fa4d3353 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -1,7 +1,7 @@ CHANGELOG --------- -- **Unreleased** +- **March.27.21**: 3.1.0 - [PR #95](https://github.com/caxlsx/caxlsx/pull/95) - Replace mimemagic with marcel - [PR #87](https://github.com/caxlsx/caxlsx/pull/87) - Implement :offset option for worksheet#add_row - [PR #79](https://github.com/caxlsx/caxlsx/pull/79) - Add support for format in pivot tables diff --git a/lib/axlsx/version.rb b/lib/axlsx/version.rb index 89954f77..2ebcc670 100644 --- a/lib/axlsx/version.rb +++ b/lib/axlsx/version.rb @@ -1,5 +1,5 @@ module Axlsx # The current version - VERSION = "3.0.4" + VERSION = "3.1.0" end -- cgit v1.2.3 From e2778815929b81d7cc6e2ae2760bbe33916caf2a Mon Sep 17 00:00:00 2001 From: Noel Peden Date: Sun, 28 Mar 2021 06:23:58 -0700 Subject: Update README.md --- README.md | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/README.md b/README.md index 0e76f52e..da005b91 100644 --- a/README.md +++ b/README.md @@ -3,7 +3,7 @@ [![Gem Version](https://badge.fury.io/rb/caxlsx.svg)](http://badge.fury.io/rb/caxlsx) ![Total downloads](http://ruby-gem-downloads-badge.herokuapp.com/caxlsx?type=total) -![Downloads for 3.0.4 (latest)](http://ruby-gem-downloads-badge.herokuapp.com/caxlsx/3.0.4?label=downloads%203.0.4) +![Downloads for 3.1.0 (latest)](http://ruby-gem-downloads-badge.herokuapp.com/caxlsx/3.1.0?label=downloads%203.0.4) ## Notice: Community Axlsx Organization -- cgit v1.2.3 From 946f287a37c7a5d2b4fe2ae8d109848e257241b1 Mon Sep 17 00:00:00 2001 From: Noel Peden Date: Sun, 28 Mar 2021 06:24:29 -0700 Subject: Really update readme. --- README.md | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/README.md b/README.md index da005b91..fb07aecb 100644 --- a/README.md +++ b/README.md @@ -3,7 +3,7 @@ [![Gem Version](https://badge.fury.io/rb/caxlsx.svg)](http://badge.fury.io/rb/caxlsx) ![Total downloads](http://ruby-gem-downloads-badge.herokuapp.com/caxlsx?type=total) -![Downloads for 3.1.0 (latest)](http://ruby-gem-downloads-badge.herokuapp.com/caxlsx/3.1.0?label=downloads%203.0.4) +![Downloads for 3.1.0 (latest)](http://ruby-gem-downloads-badge.herokuapp.com/caxlsx/3.1.0?label=downloads%203.1.0) ## Notice: Community Axlsx Organization -- cgit v1.2.3 From 6e74fb8db66695649ad0726d88dbecea2dad1722 Mon Sep 17 00:00:00 2001 From: Oleg Yakovenko Date: Mon, 26 Apr 2021 23:14:09 +0300 Subject: Optimize Axlsx.cell_range (#94) Optimize Axlsx.cell_range by using minmax_by instead of sorting the cells (the sorted cells are not used for anything else in this method). --- lib/axlsx.rb | 9 +++++---- 1 file changed, 5 insertions(+), 4 deletions(-) diff --git a/lib/axlsx.rb b/lib/axlsx.rb index 5be3c526..0c8c3850 100644 --- a/lib/axlsx.rb +++ b/lib/axlsx.rb @@ -51,10 +51,11 @@ module Axlsx # determines the cell range for the items provided def self.cell_range(cells, absolute=true) return "" unless cells.first.is_a? Cell - cells = sort_cells(cells) - reference = "#{cells.first.reference(absolute)}:#{cells.last.reference(absolute)}" + + first_cell, last_cell = cells.minmax_by(&:pos) + reference = "#{first_cell.reference(absolute)}:#{last_cell.reference(absolute)}" if absolute - escaped_name = cells.first.row.worksheet.name.gsub ''', "''" + escaped_name = first_cell.row.worksheet.name.gsub ''', "''" "'#{escaped_name}'!#{reference}" else reference @@ -66,7 +67,7 @@ module Axlsx # @param [Array] cells # @return [Array] def self.sort_cells(cells) - cells.sort { |x, y| [x.index, x.row.row_index] <=> [y.index, y.row.row_index] } + cells.sort_by(&:pos) end #global reference html entity encoding -- cgit v1.2.3 From 8d55f0d2ad3c5e4f4c6e9be1cf1e7b065fc25e49 Mon Sep 17 00:00:00 2001 From: Zsolt Kozaroczy Date: Mon, 26 Apr 2021 22:32:49 +0200 Subject: Add option to define a series color for the BarSeries (#81) --- examples/README.md | 1 + examples/chart_series_color_example.md | 35 +++++++++++++++++++++++++ examples/images/chart_series_color_example.png | Bin 0 -> 43955 bytes lib/axlsx/drawing/bar_series.rb | 19 +++++++++++++- test/drawing/tc_bar_series.rb | 11 +++++++- 5 files changed, 64 insertions(+), 2 deletions(-) create mode 100644 examples/chart_series_color_example.md create mode 100644 examples/images/chart_series_color_example.png diff --git a/examples/README.md b/examples/README.md index f1ab1d5b..eca725dc 100644 --- a/examples/README.md +++ b/examples/README.md @@ -48,6 +48,7 @@ Types: Customizations: * [Chart colors](chart_colors_example.md) * [Hide gridlines](hide_gridlines_in_chart_example.md) +* [Chart series color](chart_series_example.md) ### Columns diff --git a/examples/chart_series_color_example.md b/examples/chart_series_color_example.md new file mode 100644 index 00000000..35d1f49e --- /dev/null +++ b/examples/chart_series_color_example.md @@ -0,0 +1,35 @@ +## Description + +You could change the colors of the series with providing a series_color. Do not confuse it with defining colors for each datapoint in the series. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Bar Chart') do |sheet| + sheet.add_row ['A Simple Bar Chart'] + + sheet.add_row ['', 'Jan.', 'Feb', 'March'] + sheet.add_row ['Year1', 10, 20, 30] + sheet.add_row ['Year2', 15, 25, 35] + + sheet.add_chart(Axlsx::BarChart, start_at: 'A6', end_at: 'F20') do |chart| + chart.barDir = :col + chart.bg_color = 'FFFFFF' + # The first series will be red, but some bars will be overwritten to blue and green + chart.add_series data: sheet['B3:D3'], title: sheet['A3'], labels: sheet['B2:D2'], series_color: 'FF0000', colors: ['0000FF', '00FF00'] + # The second series will be green + chart.add_series data: sheet['B4:D4'], title: sheet['A4'], labels: sheet['B2:D2'], series_color: '00FF00' + end +end + +p.serialize 'chart_series_color_example.xlsx' +``` + +## Output + +![Output](images/chart_series_color_example.png "Output") diff --git a/examples/images/chart_series_color_example.png b/examples/images/chart_series_color_example.png new file mode 100644 index 00000000..177dc5c2 Binary files /dev/null and b/examples/images/chart_series_color_example.png differ diff --git a/lib/axlsx/drawing/bar_series.rb b/lib/axlsx/drawing/bar_series.rb index d266dd09..0e5d1168 100644 --- a/lib/axlsx/drawing/bar_series.rb +++ b/lib/axlsx/drawing/bar_series.rb @@ -22,12 +22,18 @@ module Axlsx # An array of rgb colors to apply to your bar chart. attr_reader :colors + # The fill color for this series. + # Red, green, and blue is expressed as sequence of hex digits, RRGGBB. + # @return [String] + attr_reader :series_color + # Creates a new series # @option options [Array, SimpleTypedList] data # @option options [Array, SimpleTypedList] labels # @option options [String] title # @option options [String] shape # @option options [String] colors an array of colors to use when rendering each data point + # @option options [String] series_color a color to use when rendering series # @param [Chart] chart def initialize(chart, options={}) @shape = :box @@ -40,6 +46,10 @@ module Axlsx # @see colors def colors=(v) DataTypeValidator.validate "BarSeries.colors", [Array], v; @colors = v end + def series_color=(v) + @series_color = v + end + # @see shape def shape=(v) RestrictionValidator.validate "BarSeries.shape", [:cone, :coneToMax, :box, :cylinder, :pyramid, :pyramidToMax], v @@ -60,9 +70,16 @@ module Axlsx str << '' end + if series_color + str << '' + str << ('') + str << '' + str << '' + end + @labels.to_xml_string(str) unless @labels.nil? @data.to_xml_string(str) unless @data.nil? - # this is actually only required for shapes other than box + # this is actually only required for shapes other than box str << ('') end end diff --git a/test/drawing/tc_bar_series.rb b/test/drawing/tc_bar_series.rb index 483e561d..f076fae0 100644 --- a/test/drawing/tc_bar_series.rb +++ b/test/drawing/tc_bar_series.rb @@ -6,13 +6,21 @@ class TestBarSeries < Test::Unit::TestCase p = Axlsx::Package.new @ws = p.workbook.add_worksheet :name=>"hmmm" @chart = @ws.add_chart Axlsx::Bar3DChart, :title => "fishery" - @series = @chart.add_series :data=>[0,1,2], :labels=>["zero", "one", "two"], :title=>"bob", :colors => ['FF0000', '00FF00', '0000FF'], :shape => :cone + @series = @chart.add_series( + data: [0, 1, 2], + labels: ['zero', 'one', 'two'], + title: 'bob', + colors: ['FF0000', '00FF00', '0000FF'], + shape: :cone, + series_color: '5A5A5A' + ) end def test_initialize assert_equal(@series.title.text, "bob", "series title has been applied") assert_equal(@series.data.class, Axlsx::NumDataSource, "data option applied") assert_equal(@series.shape, :cone, "series shape has been applied") + assert_equal(@series.series_color, '5A5A5A', 'series color has been applied') assert(@series.data.is_a?(Axlsx::NumDataSource)) assert(@series.labels.is_a?(Axlsx::AxDataSource)) end @@ -33,5 +41,6 @@ class TestBarSeries < Test::Unit::TestCase assert_equal(doc.xpath("//c:dPt/c:idx[@val='#{index}']").size,1) assert_equal(doc.xpath("//c:dPt/c:spPr/a:solidFill/a:srgbClr[@val='#{@series.colors[index]}']").size,1) end + assert_equal(doc.xpath('//c:spPr[not(ancestor::c:dPt)]/a:solidFill/a:srgbClr').first.get_attribute('val'), '5A5A5A', 'series color has been applied') end end -- cgit v1.2.3 From 4c90348a99300f21bf206ac02e7ed365b41dae0d Mon Sep 17 00:00:00 2001 From: Pascal Andermatt Date: Mon, 31 May 2021 11:43:04 +0200 Subject: Fix typo in row.rb --- lib/axlsx/workbook/worksheet/row.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/lib/axlsx/workbook/worksheet/row.rb b/lib/axlsx/workbook/worksheet/row.rb index 80937cf2..63a8d328 100644 --- a/lib/axlsx/workbook/worksheet/row.rb +++ b/lib/axlsx/workbook/worksheet/row.rb @@ -57,7 +57,7 @@ module Axlsx attr_reader :outline_level alias :outlineLevel :outline_level - # The style applied ot the row. This affects the entire row. + # The style applied to the row. This affects the entire row. # @return [Integer] attr_reader :s -- cgit v1.2.3 From 8133937359b1a1213a34675ea519b2aece4da525 Mon Sep 17 00:00:00 2001 From: Muhamad Akbar Bin Widayat Date: Wed, 9 Jun 2021 10:51:16 +0700 Subject: Update CHANGELOG.md Hi guys, I have found incorrect markdown in the CHANGELOG.md file. For example: `** February.2.12**`. I changed to `**February.2.12**`. Thank you. --- CHANGELOG.md | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index fa4d3353..c55a2673 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -193,23 +193,23 @@ in value caches - Added support for specifying the color of data series in charts. - bugfix using add_cell on row mismanaged calls to update_column_info. -- ** April.25.12:**: 1.1.3 +- **April.25.12:**: 1.1.3 - Primarily because I am stupid.....Updates to readme to properly report version, add in missing docs and restructure example directory. -- ** April.25.12:**: 1.1.2 +- **April.25.12:**: 1.1.2 - Conditional Formatting completely implemented. - refactoring / documentation for Style#add_style - added in label rotation for chart axis labels - bugfix to properly assign style and type info to cells when only partial information is provided in the types/style option -- ** April.18.12**: 1.1.1 +- **April.18.12**: 1.1.1 - bugfix for autowidth calculations across multiple rows - bugfix for dimension calculations with nil cells. - REMOVED RMAGICK dependency WOOT! - Update readme to show screenshot of gem output. - Cleanup benchmark and add benchmark rake task -- ** April.3.12**: 1.1.0 +- **April.3.12**: 1.1.0 - bugfix patch name_to_indecies to properly handle extended ranges. - bugfix properly serialize chart title. - lower rake minimum requirement for 1.8.7 apps that don't want to move on to 0.9 NOTE this will be reverted for 2.0.0 with workbook parsing! @@ -224,7 +224,7 @@ in value caches - Major (like 7x faster!) performance updates. - Gem now supports for JRuby 1.6.7, as well as experimental support for Rubinius -- ** March.5.12**: 1.0.18 +- **March.5.12**: 1.0.18 https://github.com/randym/axlsx/compare/1.0.17...1.0.18 - bugfix custom borders are not properly applied when using styles.add_style - interop worksheet names must be 31 characters or less or some versions of office complain about repairs @@ -234,14 +234,14 @@ in value caches - added << alias for add_row - removed presetting of date1904 based on authoring platform. Now defaults to use 1900 epoch (date1904 = false) -- ** February.14.12**: 1.0.17 +- **February.14.12**: 1.0.17 https://github.com/randym/axlsx/compare/1.0.16...1.0.17 - Added in support for serializing to StringIO - Added in support for using shared strings table. This makes most of the features in axlsx interoperable with iWorks Numbers - Added in support for fixed column_widths - Removed unneeded dependencies on active-support and i18n -- ** February.2.12**: 1.0.16 +- **February.2.12**: 1.0.16 https://github.com/randym/axlsx/compare/1.0.15...1.0.16 - Bug fix for schema file locations when validating in rails - Added hyperlink to images -- cgit v1.2.3 From 158942c16b249a269e77633dd261b87787d191af Mon Sep 17 00:00:00 2001 From: Zsolt Kozaroczy Date: Sat, 24 Jul 2021 00:31:54 +0200 Subject: Add overlap to bar charts (#107) --- CHANGELOG.md | 9 ++++--- examples/README.md | 1 + examples/images/stacked_bar_chart_example.png | Bin 0 -> 28959 bytes examples/stacked_bar_chart_example.md | 35 ++++++++++++++++++++++++++ lib/axlsx/drawing/bar_chart.rb | 14 ++++++++++- test/drawing/tc_bar_chart.rb | 14 +++++++++++ 6 files changed, 69 insertions(+), 4 deletions(-) create mode 100644 examples/images/stacked_bar_chart_example.png create mode 100644 examples/stacked_bar_chart_example.md diff --git a/CHANGELOG.md b/CHANGELOG.md index c55a2673..d9c3fc45 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -1,6 +1,9 @@ CHANGELOG --------- +- **Unreleased** + - [PR #107](https://github.com/caxlsx/caxlsx/pull/107) - Add overlap to bar charts + - **March.27.21**: 3.1.0 - [PR #95](https://github.com/caxlsx/caxlsx/pull/95) - Replace mimemagic with marcel - [PR #87](https://github.com/caxlsx/caxlsx/pull/87) - Implement :offset option for worksheet#add_row @@ -11,7 +14,7 @@ CHANGELOG - **January.5.21**: 3.0.4 - [PR #72](https://github.com/caxlsx/caxlsx/pull/72) - Relax Ruby dependency to allow for Ruby 3. This required Travis to be upgraded from Ubuntu Trusty to Ubuntu Bionic. rbx-3 was dropped. - [PR #71](https://github.com/caxlsx/caxlsx/pull/71) - Adds date type to validator so sheet.add_data_validation works with date type. Addresses [I #26](https://github.com/caxlsx/caxlsx/issues/26) - Date Data Validation not working - - [PR #70](https://github.com/caxlsx/caxlsx/pull/70) - Fix worksheet title length enforcement caused by switching from size to bytesize. Addresses [I #67](https://github.com/caxlsx/caxlsx/issues/67) - character length error in worksheet name when using Japanese, which was introduced by addressing [I #588](https://github.com/randym/axlsx/issues/588) in the old Axlsx repo. + - [PR #70](https://github.com/caxlsx/caxlsx/pull/70) - Fix worksheet title length enforcement caused by switching from size to bytesize. Addresses [I #67](https://github.com/caxlsx/caxlsx/issues/67) - character length error in worksheet name when using Japanese, which was introduced by addressing [I #588](https://github.com/randym/axlsx/issues/588) in the old Axlsx repo. - **December.7.20**: 3.0.3 @@ -19,7 +22,7 @@ CHANGELOG - [PR #56](https://github.com/caxlsx/caxlsx/pull/56) - Add `zip_command` option to `#serialize` for faster serialization of large Excel files by using a zip binary - [PR #54](https://github.com/caxlsx/caxlsx/pull/54) - Fix type detection for floats with out-of-rage exponents - [I #67](https://github.com/caxlsx/caxlsx/issues/67) - Fix regression in worksheet name length enforcement: Some unicode characters were counted incorrectly, so that names that previously worked fine now stopped working. (This was introduced in 3.0.2) - + - **July.16.20**: 3.0.2 - [I #51](https://github.com/caxlsx/caxlsx/issues/51) - Images do not import on Windows. IO read set explicitly to binary mode. - [PR #53](https://github.com/caxlsx/caxlsx/pull/53) - Limit column width to 255. Maximum column width limit in MS Excel is 255 characters, see https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 @@ -152,7 +155,7 @@ CHANGELOG - added in interop requirements so that charts are properly exported to PDF from Libra Office - various readability improvements and work standardizing attribute - names to snake_case. Aliases are provided for backward compatiblity + names to snake_case. Aliases are provided for backward compatiblity - **June.11.12**: 1.1.7 - fix chart rendering issue when label offset is specified as a diff --git a/examples/README.md b/examples/README.md index eca725dc..5f53199e 100644 --- a/examples/README.md +++ b/examples/README.md @@ -44,6 +44,7 @@ Types: * [Line chart (3D)](3d_line_chart_example.md) * [Pie chart (3D)](3d_pie_chart_example.md) * [Scatter chart](scatter_chart_example.md) +* [Stacked bar chart](stacked_bar_chart_example.md) Customizations: * [Chart colors](chart_colors_example.md) diff --git a/examples/images/stacked_bar_chart_example.png b/examples/images/stacked_bar_chart_example.png new file mode 100644 index 00000000..da046983 Binary files /dev/null and b/examples/images/stacked_bar_chart_example.png differ diff --git a/examples/stacked_bar_chart_example.md b/examples/stacked_bar_chart_example.md new file mode 100644 index 00000000..3fbd2425 --- /dev/null +++ b/examples/stacked_bar_chart_example.md @@ -0,0 +1,35 @@ +## Description + +Stacked bar chart example + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Bar Chart') do |sheet| + sheet.add_row ['A Simple Bar Chart', 'X', 'Y'] + + sheet.add_row ['A', 3, 4] + sheet.add_row ['B', 10, 6] + sheet.add_row ['C', 7, 2] + + sheet.add_chart(Axlsx::BarChart, start_at: 'A6', end_at: 'F20') do |chart| + chart.add_series data: sheet['B2:B4'], labels: sheet['A2:A4'], title: sheet['B1'] + chart.add_series data: sheet['C2:C4'], labels: sheet['A2:A4'], title: sheet['C1'] + + chart.bar_dir = :col + chart.overlap = 100 + chart.grouping = :percentStacked + end +end + +p.serialize 'stacked_bar_chart_example.xlsx' +``` + +## Output + +![Output](images/stacked_bar_chart_example.png "Output") diff --git a/lib/axlsx/drawing/bar_chart.rb b/lib/axlsx/drawing/bar_chart.rb index e9af9400..cb596fce 100644 --- a/lib/axlsx/drawing/bar_chart.rb +++ b/lib/axlsx/drawing/bar_chart.rb @@ -49,6 +49,12 @@ module Axlsx @grouping ||= :clustered end + # Overlap between series + # @return [Integer] + def overlap + @overlap ||= 0 + end + # The shape of the bars or columns # must be one of [:cone, :coneToMax, :box, :cylinder, :pyramid, :pyramidToMax] # @return [Symbol] @@ -71,7 +77,7 @@ module Axlsx # @see Chart def initialize(frame, options={}) @vary_colors = true - @gap_width, @gap_depth, @shape = nil, nil, nil + @gap_width, @gap_depth, @overlap, @shape = nil, nil, nil, nil super(frame, options) @series_type = BarSeries @d_lbls = nil @@ -106,6 +112,11 @@ module Axlsx end alias :gapDepth= :gap_depth= + def overlap=(v) + RangeValidator.validate "BarChart.overlap", -100, 100, v + @overlap=(v) + end + # The shape of the bars or columns # must be one of [:cone, :coneToMax, :box, :cylinder, :pyramid, :pyramidToMax] def shape=(v) @@ -124,6 +135,7 @@ module Axlsx str << ('') @series.each { |ser| ser.to_xml_string(str) } @d_lbls.to_xml_string(str) if @d_lbls + str << ('') unless @overlap.nil? str << ('') unless @gap_width.nil? str << ('') unless @gap_depth.nil? str << ('') unless @shape.nil? diff --git a/test/drawing/tc_bar_chart.rb b/test/drawing/tc_bar_chart.rb index ca1ca016..d064e5be 100644 --- a/test/drawing/tc_bar_chart.rb +++ b/test/drawing/tc_bar_chart.rb @@ -45,6 +45,13 @@ class TestBarChart < Test::Unit::TestCase assert(@chart.gap_depth == "200%") end + def test_overlap + assert_raise(ArgumentError, "require valid overlap") { @chart.overlap = -101 } + assert_raise(ArgumentError, "require valid overlap") { @chart.overlap = 101 } + assert_nothing_raised("allow valid overlap") { @chart.overlap = 100 } + assert_equal(@chart.overlap, 100, 'overlap is incorrect') + end + def test_shape assert_raise(ArgumentError, "require valid shape") { @chart.shape = :star } assert_nothing_raised("allow valid shape") { @chart.shape = :cone } @@ -68,4 +75,11 @@ class TestBarChart < Test::Unit::TestCase val_axis_position = str.index(@chart.axes[:val_axis].id.to_s) assert(cat_axis_position < val_axis_position, "cat_axis must occur earlier than val_axis in the XML") end + + def test_to_xml_string_has_overlap + overlap_value = rand(-100..100) + @chart.overlap = overlap_value + doc = Nokogiri::XML(@chart.to_xml_string) + assert_equal(doc.xpath("//c:barChart/c:overlap").first.attribute('val').value, overlap_value.to_s) + end end -- cgit v1.2.3 From c4d7279de2bd20bc98c15ea64e9074ded19124ca Mon Sep 17 00:00:00 2001 From: Zsolt Kozaroczy Date: Sun, 25 Jul 2021 01:37:29 +0200 Subject: Fix gap width validator for bar charts (#108) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit `gap_width` and `gap_depth` now allow only integers in the range of 0–500. The previous behaviour (requiring a percentage value) was according to the current version of the OOXML spec, but Excel seems to rely on an older version, where the gap amount was required to be a simple integer. Also, `gapDepth` is only allowed in 3D bar charts, so it is now no longer available for 2D bar charts. --- CHANGELOG.md | 1 + lib/axlsx/drawing/bar_3D_chart.rb | 13 +++++-------- lib/axlsx/drawing/bar_chart.rb | 23 +++-------------------- test/drawing/tc_bar_3D_chart.rb | 37 ++++++++++++++++++++++++++----------- test/drawing/tc_bar_chart.rb | 25 +++++++++++++------------ 5 files changed, 48 insertions(+), 51 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index d9c3fc45..3558c392 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -3,6 +3,7 @@ CHANGELOG - **Unreleased** - [PR #107](https://github.com/caxlsx/caxlsx/pull/107) - Add overlap to bar charts + - [PR #108](https://github.com/caxlsx/caxlsx/pull/108) - Fix gap depth and gap depth validators for bar charts and 3D bar charts - **March.27.21**: 3.1.0 - [PR #95](https://github.com/caxlsx/caxlsx/pull/95) - Replace mimemagic with marcel diff --git a/lib/axlsx/drawing/bar_3D_chart.rb b/lib/axlsx/drawing/bar_3D_chart.rb index 7d44eca6..ae6c2007 100644 --- a/lib/axlsx/drawing/bar_3D_chart.rb +++ b/lib/axlsx/drawing/bar_3D_chart.rb @@ -31,17 +31,17 @@ module Axlsx alias :barDir :bar_dir # space between bar or column clusters, as a percentage of the bar or column width. - # @return [String] + # @return [Integer] attr_reader :gap_depth alias :gapDepth :gap_depth # space between bar or column clusters, as a percentage of the bar or column width. - # @return [String] + # @return [Integer] def gap_width @gap_width ||= 150 end alias :gapWidth :gap_width - + #grouping for a column, line, or area chart. # must be one of [:percentStacked, :clustered, :standard, :stacked] # @return [Symbol] @@ -56,9 +56,6 @@ module Axlsx @shape ||= :box end - # validation regex for gap amount percent - GAP_AMOUNT_PERCENT = /0*(([0-9])|([1-9][0-9])|([1-4][0-9][0-9])|500)%/ - # Creates a new bar chart object # @param [GraphicFrame] frame The workbook that owns this chart. # @option options [Cell, String] title @@ -102,14 +99,14 @@ module Axlsx # space between bar or column clusters, as a percentage of the bar or column width. def gap_width=(v) - RegexValidator.validate "Bar3DChart.gap_width", GAP_AMOUNT_PERCENT, v + RangeValidator.validate "Bar3DChart.gap_width", 0, 500, v @gap_width=(v) end alias :gapWidth= :gap_width= # space between bar or column clusters, as a percentage of the bar or column width. def gap_depth=(v) - RegexValidator.validate "Bar3DChart.gap_didth", GAP_AMOUNT_PERCENT, v + RangeValidator.validate "Bar3DChart.gap_depth", 0, 500, v @gap_depth=(v) end alias :gapDepth= :gap_depth= diff --git a/lib/axlsx/drawing/bar_chart.rb b/lib/axlsx/drawing/bar_chart.rb index cb596fce..67787361 100644 --- a/lib/axlsx/drawing/bar_chart.rb +++ b/lib/axlsx/drawing/bar_chart.rb @@ -31,12 +31,7 @@ module Axlsx alias :barDir :bar_dir # space between bar or column clusters, as a percentage of the bar or column width. - # @return [String] - attr_reader :gap_depth - alias :gapDepth :gap_depth - - # space between bar or column clusters, as a percentage of the bar or column width. - # @return [String] + # @return [Integer] def gap_width @gap_width ||= 150 end @@ -62,9 +57,6 @@ module Axlsx @shape ||= :box end - # validation regex for gap amount percent - GAP_AMOUNT_PERCENT = /0*(([0-9])|([1-9][0-9])|([1-4][0-9][0-9])|500)%/ - # Creates a new bar chart object # @param [GraphicFrame] frame The workbook that owns this chart. # @option options [Cell, String] title @@ -72,12 +64,11 @@ module Axlsx # @option options [Symbol] bar_dir # @option options [Symbol] grouping # @option options [String] gap_width - # @option options [String] gap_depth # @option options [Symbol] shape # @see Chart def initialize(frame, options={}) @vary_colors = true - @gap_width, @gap_depth, @overlap, @shape = nil, nil, nil, nil + @gap_width, @overlap, @shape = nil, nil, nil super(frame, options) @series_type = BarSeries @d_lbls = nil @@ -100,18 +91,11 @@ module Axlsx # space between bar or column clusters, as a percentage of the bar or column width. def gap_width=(v) - RegexValidator.validate "BarChart.gap_width", GAP_AMOUNT_PERCENT, v + RangeValidator.validate "BarChart.gap_width", 0, 500, v @gap_width=(v) end alias :gapWidth= :gap_width= - # space between bar or column clusters, as a percentage of the bar or column width. - def gap_depth=(v) - RegexValidator.validate "BarChart.gap_didth", GAP_AMOUNT_PERCENT, v - @gap_depth=(v) - end - alias :gapDepth= :gap_depth= - def overlap=(v) RangeValidator.validate "BarChart.overlap", -100, 100, v @overlap=(v) @@ -137,7 +121,6 @@ module Axlsx @d_lbls.to_xml_string(str) if @d_lbls str << ('') unless @overlap.nil? str << ('') unless @gap_width.nil? - str << ('') unless @gap_depth.nil? str << ('') unless @shape.nil? axes.to_xml_string(str, :ids => true) str << '' diff --git a/test/drawing/tc_bar_3D_chart.rb b/test/drawing/tc_bar_3D_chart.rb index 0cae7af6..b7a1eca4 100644 --- a/test/drawing/tc_bar_3D_chart.rb +++ b/test/drawing/tc_bar_3D_chart.rb @@ -32,18 +32,19 @@ class TestBar3DChart < Test::Unit::TestCase assert(@chart.grouping == :standard) end + def test_gap_width + assert_raise(ArgumentError, "require valid gap width") { @chart.gap_width = -1 } + assert_raise(ArgumentError, "require valid gap width") { @chart.gap_width = 501 } + assert_nothing_raised("allow valid gapWidth") { @chart.gap_width = 200 } + assert_equal(@chart.gap_width, 200, 'gap width is incorrect') + end - def test_gapWidth - assert_raise(ArgumentError, "require valid gap width") { @chart.gap_width = 200 } - assert_nothing_raised("allow valid gapWidth") { @chart.gap_width = "200%" } - assert(@chart.gap_width == "200%") - end - - def test_gapDepth - assert_raise(ArgumentError, "require valid gap_depth") { @chart.gap_depth = 200 } - assert_nothing_raised("allow valid gap_depth") { @chart.gap_depth = "200%" } - assert(@chart.gap_depth == "200%") - end + def test_gap_depth + assert_raise(ArgumentError, "require valid gap_depth") { @chart.gap_depth = -1 } + assert_raise(ArgumentError, "require valid gap_depth") { @chart.gap_depth = 501 } + assert_nothing_raised("allow valid gap_depth") { @chart.gap_depth = 200 } + assert_equal(@chart.gap_depth, 200, 'gap depth is incorrect') + end def test_shape assert_raise(ArgumentError, "require valid shape") { @chart.shape = :star } @@ -68,4 +69,18 @@ class TestBar3DChart < Test::Unit::TestCase val_axis_position = str.index(@chart.axes[:val_axis].id.to_s) assert(cat_axis_position < val_axis_position, "cat_axis must occur earlier than val_axis in the XML") end + + def test_to_xml_string_has_gap_depth + gap_depth_value = rand(0..500) + @chart.gap_depth = gap_depth_value + doc = Nokogiri::XML(@chart.to_xml_string) + assert_equal(doc.xpath("//c:bar3DChart/c:gapDepth").first.attribute('val').value, gap_depth_value.to_s) + end + + def test_to_xml_string_has_gap_width + gap_width_value = rand(0..500) + @chart.gap_width = gap_width_value + doc = Nokogiri::XML(@chart.to_xml_string) + assert_equal(doc.xpath("//c:bar3DChart/c:gapWidth").first.attribute('val').value, gap_width_value.to_s) + end end diff --git a/test/drawing/tc_bar_chart.rb b/test/drawing/tc_bar_chart.rb index d064e5be..c8bdc8d0 100644 --- a/test/drawing/tc_bar_chart.rb +++ b/test/drawing/tc_bar_chart.rb @@ -32,18 +32,12 @@ class TestBarChart < Test::Unit::TestCase assert(@chart.grouping == :standard) end - - def test_gapWidth - assert_raise(ArgumentError, "require valid gap width") { @chart.gap_width = 200 } - assert_nothing_raised("allow valid gapWidth") { @chart.gap_width = "200%" } - assert(@chart.gap_width == "200%") - end - - def test_gapDepth - assert_raise(ArgumentError, "require valid gap_depth") { @chart.gap_depth = 200 } - assert_nothing_raised("allow valid gap_depth") { @chart.gap_depth = "200%" } - assert(@chart.gap_depth == "200%") - end + def test_gap_width + assert_raise(ArgumentError, "require valid gap width") { @chart.gap_width = -1 } + assert_raise(ArgumentError, "require valid gap width") { @chart.gap_width = 501 } + assert_nothing_raised("allow valid gap width") { @chart.gap_width = 200 } + assert_equal(@chart.gap_width, 200, 'gap width is incorrect') + end def test_overlap assert_raise(ArgumentError, "require valid overlap") { @chart.overlap = -101 } @@ -76,6 +70,13 @@ class TestBarChart < Test::Unit::TestCase assert(cat_axis_position < val_axis_position, "cat_axis must occur earlier than val_axis in the XML") end + def test_to_xml_string_has_gap_width + gap_width_value = rand(0..500) + @chart.gap_width = gap_width_value + doc = Nokogiri::XML(@chart.to_xml_string) + assert_equal(doc.xpath("//c:barChart/c:gapWidth").first.attribute('val').value, gap_width_value.to_s) + end + def test_to_xml_string_has_overlap overlap_value = rand(-100..100) @chart.overlap = overlap_value -- cgit v1.2.3 From f9c8f9dfbced90b5263f80a010a112338d3f1638 Mon Sep 17 00:00:00 2001 From: Noel Peden Date: Wed, 22 Sep 2021 19:20:39 -0700 Subject: Version bump to 3.1.1 [skip ci] --- CHANGELOG.md | 2 +- README.md | 2 +- lib/axlsx/version.rb | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index 3558c392..b9a0d429 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -1,7 +1,7 @@ CHANGELOG --------- -- **Unreleased** +- **September.22.21**: 3.1.1 - [PR #107](https://github.com/caxlsx/caxlsx/pull/107) - Add overlap to bar charts - [PR #108](https://github.com/caxlsx/caxlsx/pull/108) - Fix gap depth and gap depth validators for bar charts and 3D bar charts diff --git a/README.md b/README.md index fb07aecb..76079ada 100644 --- a/README.md +++ b/README.md @@ -3,7 +3,7 @@ [![Gem Version](https://badge.fury.io/rb/caxlsx.svg)](http://badge.fury.io/rb/caxlsx) ![Total downloads](http://ruby-gem-downloads-badge.herokuapp.com/caxlsx?type=total) -![Downloads for 3.1.0 (latest)](http://ruby-gem-downloads-badge.herokuapp.com/caxlsx/3.1.0?label=downloads%203.1.0) +![Downloads for 3.1.1 (latest)](http://ruby-gem-downloads-badge.herokuapp.com/caxlsx/3.1.1?label=downloads%203.1.1) ## Notice: Community Axlsx Organization diff --git a/lib/axlsx/version.rb b/lib/axlsx/version.rb index 2ebcc670..bcd95e09 100644 --- a/lib/axlsx/version.rb +++ b/lib/axlsx/version.rb @@ -1,5 +1,5 @@ module Axlsx # The current version - VERSION = "3.1.0" + VERSION = "3.1.1" end -- cgit v1.2.3 From 7aa995d58f072d2b49166d43ef44232a5086042a Mon Sep 17 00:00:00 2001 From: Vladimir Támara Patiño Date: Mon, 22 Nov 2021 04:56:31 -0500 Subject: Correcting typo --- examples/README.md | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/examples/README.md b/examples/README.md index 5f53199e..a90dc87d 100644 --- a/examples/README.md +++ b/examples/README.md @@ -103,7 +103,7 @@ Customizations: * [Fonts](font_example.md) * [Format codes](format_codes_example.md) -* [Number formats and currencyc](number_format_example.md) +* [Number formats and currency](number_format_example.md) * [Rich text](rich_text_example.md) * [Wrap text](wrap_text_example.md) -- cgit v1.2.3 From 4278f88e5faa8e733409bb4256a01cc510f87570 Mon Sep 17 00:00:00 2001 From: Dan DeBruler Date: Tue, 28 Dec 2021 13:33:54 -0500 Subject: Return output stream in binmode --- lib/axlsx/package.rb | 2 +- test/tc_package.rb | 2 ++ 2 files changed, 3 insertions(+), 1 deletion(-) diff --git a/lib/axlsx/package.rb b/lib/axlsx/package.rb index d9865a48..1b30c815 100644 --- a/lib/axlsx/package.rb +++ b/lib/axlsx/package.rb @@ -124,7 +124,7 @@ module Axlsx def to_stream(confirm_valid=false) return false unless !confirm_valid || self.validate.empty? Relationship.initialize_ids_cache - zip = write_parts(Zip::OutputStream.new(StringIO.new, true)) + zip = write_parts(Zip::OutputStream.new(StringIO.new.binmode, true)) stream = zip.close_buffer stream.rewind stream diff --git a/test/tc_package.rb b/test/tc_package.rb index f1ce0421..8dea7ccf 100644 --- a/test/tc_package.rb +++ b/test/tc_package.rb @@ -303,6 +303,8 @@ class TestPackage < Test::Unit::TestCase # this is just a roundabout guess for a package as it is build now # in testing. assert(stream.size > 80000) + # Stream (of zipped contents) should have appropriate default encoding + assert_equal(stream.external_encoding, Encoding::ASCII_8BIT) # Cached ids should be cleared assert(Axlsx::Relationship.ids_cache.empty?) end -- cgit v1.2.3 From 6bc6e6303ac27ac11571760c036c244b1448aeaa Mon Sep 17 00:00:00 2001 From: Weston Ganger Date: Wed, 29 Dec 2021 12:00:26 -0800 Subject: Fix Worksheet#name_to_cell bug which returned reversed row/col indexes --- CHANGELOG.md | 3 +++ lib/axlsx.rb | 20 ++++++++++++++++--- lib/axlsx/workbook/worksheet/worksheet.rb | 18 ++++++++++++++++- test/workbook/worksheet/tc_worksheet.rb | 33 ++++++++++++++++++++++++++++++- 4 files changed, 69 insertions(+), 5 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index b9a0d429..700a6b31 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -1,6 +1,9 @@ CHANGELOG --------- +- **Unreleased** + - Improve error messages when incorrect ranges are provided to `Worksheet#[]` + - **September.22.21**: 3.1.1 - [PR #107](https://github.com/caxlsx/caxlsx/pull/107) - Add overlap to bar charts - [PR #108](https://github.com/caxlsx/caxlsx/pull/108) - Fix gap depth and gap depth validators for bar charts and 3D bar charts diff --git a/lib/axlsx.rb b/lib/axlsx.rb index 0c8c3850..4e804513 100644 --- a/lib/axlsx.rb +++ b/lib/axlsx.rb @@ -79,11 +79,25 @@ module Axlsx # returns the x, y position of a cell def self.name_to_indices(name) raise ArgumentError, 'invalid cell name' unless name.size > 1 + + letters_str = name[/[A-Z]+/] + # capitalization?!? - v = name[/[A-Z]+/].reverse.chars.reduce({:base=>1, :i=>0}) do |val, c| - val[:i] += ((c.bytes.first - 64) * val[:base]); val[:base] *= 26; val + v = letters_str.reverse.chars.reduce({:base=>1, :i=>0}) do |val, c| + val[:i] += ((c.bytes.first - 64) * val[:base]) + + val[:base] *= 26 + + next val end - [v[:i]-1, ((name[/[1-9][0-9]*/]).to_i)-1] + + col_index = (v[:i] - 1) + + numbers_str = name[/[1-9][0-9]*/] + + row_index = (numbers_str.to_i - 1) + + return [col_index, row_index] end # converts the column index into alphabetical values. diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index 06a5e7f1..f1ec815c 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -599,10 +599,20 @@ module Axlsx # @return [Cell, Array] def [](cell_def) return rows[cell_def] if cell_def.is_a?(Integer) + parts = cell_def.split(':').map{ |part| name_to_cell part } + if parts.size == 1 parts.first else + if parts.size > 2 + raise ArgumentError.new("Invalid cell definition `#{cell_def}`") + elsif parts.first.nil? + raise ArgumentError.new("Missing cell `#{cell_def.split(":").first}` for the specified range `#{cell_def}`") + elsif parts.last.nil? + raise ArgumentError.new("Missing cell `#{cell_def.split(":").last}` for the specified range `#{cell_def}`") + end + range(*parts) end end @@ -612,8 +622,12 @@ module Axlsx # @return [Cell] def name_to_cell(name) col_index, row_index = *Axlsx::name_to_indices(name) + r = rows[row_index] - r[col_index] if r + + if r + return r[col_index] + end end # shortcut method to access styles direclty from the worksheet @@ -688,11 +702,13 @@ module Axlsx def range(*cell_def) first, last = cell_def cells = [] + rows[(first.row.row_index..last.row.row_index)].each do |r| r[(first.index..last.index)].each do |c| cells << c end end + cells end diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb index 734096b5..c679e18c 100644 --- a/test/workbook/worksheet/tc_worksheet.rb +++ b/test/workbook/worksheet/tc_worksheet.rb @@ -493,6 +493,37 @@ class TestWorksheet < Test::Unit::TestCase assert_raise(ArgumentError, 'only accept Integer or Float') { @ws.column_widths 2, 7, "-1" } end + def test_ranges + @ws.add_row(["foo", "bar"]) + @ws.add_row(["a", "b", "c", "d", "e"]) + + valid_range = "A1:B2" + @ws[valid_range] + + valid_range = "A1:E2" ### Missing middle cells (C1 - E1), still allowed + @ws[valid_range] + + assert_raise ArgumentError, "Invalid cell definition" do + invalid_range_format = "A1:B2:C3" + @ws[invalid_range_format] + end + + assert_raise ArgumentError, "Missing cell `Z1` for the specified range." do + invalid_row_range = "A1:Z1" + @ws[invalid_row_range] + end + + assert_raise ArgumentError, "Missing cell `D1` for the specified range." do + invalid_cell_range = "D1:E2" ### Missing start cell, not allowed + @ws[invalid_cell_range] + end + + assert_raise ArgumentError, "Missing cell `A99` for the specified range." do + invalid_cell_range = "A1:A99" ### Missing end cell, not allowed + @ws[invalid_cell_range] + end + end + def test_protect_range assert(@ws.send(:protected_ranges).is_a?(Axlsx::SimpleTypedList)) assert_equal(0, @ws.send(:protected_ranges).size) @@ -504,8 +535,8 @@ class TestWorksheet < Test::Unit::TestCase @ws.add_row [1, 2, 3] assert_nothing_raised {@ws.protect_range(@ws.rows.first.cells) } assert_equal('A1:C1', @ws.send(:protected_ranges).last.sqref) - end + def test_merge_cells @ws.add_row [1,2,3] @ws.add_row [4,5,6] -- cgit v1.2.3 From 7f4045fc69683d51c7c3fbd6497cfadba4966484 Mon Sep 17 00:00:00 2001 From: Dan DeBruler Date: Fri, 21 Jan 2022 10:53:37 -0500 Subject: Check string encoding for validity _and_ value --- test/tc_package.rb | 1 + 1 file changed, 1 insertion(+) diff --git a/test/tc_package.rb b/test/tc_package.rb index 8dea7ccf..b98fa2f0 100644 --- a/test/tc_package.rb +++ b/test/tc_package.rb @@ -304,6 +304,7 @@ class TestPackage < Test::Unit::TestCase # in testing. assert(stream.size > 80000) # Stream (of zipped contents) should have appropriate default encoding + assert stream.string.valid_encoding? assert_equal(stream.external_encoding, Encoding::ASCII_8BIT) # Cached ids should be cleared assert(Axlsx::Relationship.ids_cache.empty?) -- cgit v1.2.3 From 34a287b33364657bc340e98be846b65fa8a77922 Mon Sep 17 00:00:00 2001 From: Weston Ganger Date: Mon, 24 Jan 2022 13:51:27 -0800 Subject: Refractor to use constant based error messages --- lib/axlsx/util/constants.rb | 6 ++++++ lib/axlsx/workbook/worksheet/worksheet.rb | 6 +++--- 2 files changed, 9 insertions(+), 3 deletions(-) diff --git a/lib/axlsx/util/constants.rb b/lib/axlsx/util/constants.rb index 25781983..3d67088c 100644 --- a/lib/axlsx/util/constants.rb +++ b/lib/axlsx/util/constants.rb @@ -307,6 +307,12 @@ module Axlsx # error message for non 'integerish' value ERR_INTEGERISH = "You value must be, or be castable via to_i, an Integer. You provided %s".freeze + # error message for invalid cell reference + ERR_CELL_REFERENCE_INVALID = "Invalid cell definition `%s`".freeze + + # error message for cell reference with last cell missing + ERR_CELL_REFERENCE_MISSING_CELL = "Missing cell `%s` for the specified range `%s`".freeze + # Regex to match forbidden control characters # The following will be automatically stripped from worksheets. # diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index f1ec815c..cc0e56ae 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -606,11 +606,11 @@ module Axlsx parts.first else if parts.size > 2 - raise ArgumentError.new("Invalid cell definition `#{cell_def}`") + raise ArgumentError, (ERR_CELL_REFERENCE_INVALID % cell_def) elsif parts.first.nil? - raise ArgumentError.new("Missing cell `#{cell_def.split(":").first}` for the specified range `#{cell_def}`") + raise ArgumentError, (ERR_CELL_REFERENCE_MISSING_CELL % [cell_def.split(":").first, cell_def]) elsif parts.last.nil? - raise ArgumentError.new("Missing cell `#{cell_def.split(":").last}` for the specified range `#{cell_def}`") + raise ArgumentError, (ERR_CELL_REFERENCE_MISSING_CELL % [cell_def.split(":").last, cell_def]) end range(*parts) -- cgit v1.2.3 From b7ebd233689beab182c10605b0691cb70ab69208 Mon Sep 17 00:00:00 2001 From: Weston Ganger Date: Tue, 25 Jan 2022 08:01:23 -0800 Subject: Remove Known Bugs Section in Readme --- README.md | 4 ---- 1 file changed, 4 deletions(-) diff --git a/README.md b/README.md index 76079ada..34929c91 100644 --- a/README.md +++ b/README.md @@ -155,10 +155,6 @@ p.use_shared_strings = true p.serialize('simple.xlsx') ``` -## Known Bugs - -There’s a [list of known bugs](https://github.com/caxlsx/caxlsx/issues?q=label%3A%22known+bug%22). (If you want to contribute to caxlsx, this is a good place to start!) - ## Contributing See [CONTRIBUTING.md](https://github.com/caxlsx/caxlsx/blob/master/CONTRIBUTING.md) -- cgit v1.2.3 From c8eb5fe13bfca2dcac17848dd15dc04f4f0dcf9b Mon Sep 17 00:00:00 2001 From: Weston Ganger Date: Thu, 13 Jan 2022 08:06:07 -0800 Subject: Fix invalid xml when pivot table created with more than one column in data field --- CHANGELOG.md | 3 +++ lib/axlsx/workbook/worksheet/pivot_table.rb | 6 +++-- test/workbook/worksheet/tc_pivot_table.rb | 37 +++++++++++++++++++++++++++++ 3 files changed, 44 insertions(+), 2 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index b9a0d429..9983d90d 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -1,6 +1,9 @@ CHANGELOG --------- +- **Unreleased** + - [PR #123](https://github.com/caxlsx/caxlsx/pull/123) - Fix invalid xml when pivot table created with more than one column in data field. Solves [Issue #110](https://github.com/caxlsx/caxlsx/issues/110) + - **September.22.21**: 3.1.1 - [PR #107](https://github.com/caxlsx/caxlsx/pull/107) - Add overlap to bar charts - [PR #108](https://github.com/caxlsx/caxlsx/pull/108) - Fix gap depth and gap depth validators for bar charts and 3D bar charts diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb index d8c630ec..bc500a8a 100644 --- a/lib/axlsx/workbook/worksheet/pivot_table.rb +++ b/lib/axlsx/workbook/worksheet/pivot_table.rb @@ -173,7 +173,9 @@ module Axlsx # @return [String] def to_xml_string(str = '') str << '' - str << ('') + + str << ('') + str << ('') str << ('') header_cell_values.each do |cell_value| @@ -195,7 +197,7 @@ module Axlsx end str << '' end - if columns.empty? + if columns.empty? && data.size <= 1 str << '' else str << ('') diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb index a494dcfd..591f0ba9 100644 --- a/test/workbook/worksheet/tc_pivot_table.rb +++ b/test/workbook/worksheet/tc_pivot_table.rb @@ -140,4 +140,41 @@ class TestPivotTable < Test::Unit::TestCase doc = Nokogiri::XML(pivot_table.to_xml_string) assert_equal('4', doc.at_css('dataFields dataField')['numFmtId'], 'adding format options to pivot_table') end + + def test_pivot_table_with_more_than_one_data_row + ### https://github.com/caxlsx/caxlsx/issues/110 + + pivot_table = @ws.add_pivot_table('G5:G6', 'A1:E5') do |pt| + pt.rows = ["Date", "Name"] + pt.data = [ + {ref: "Gross amount", num_fmt: 2}, + {ref: "Net amount", num_fmt: 2}, + ] + end + + xml = pivot_table.to_xml_string + + assert(xml.include?('colFields')) + + assert(!xml.include?('dataOnRows')) + assert(!xml.include?('colItems')) + end + + def test_pivot_table_with_only_one_data_row + ### https://github.com/caxlsx/caxlsx/issues/110 + + pivot_table = @ws.add_pivot_table('G5:G6', 'A1:E5') do |pt| + pt.rows = ["Date", "Name"] + pt.data = [ + {ref: "Gross amount", num_fmt: 2}, + ] + end + + xml = pivot_table.to_xml_string + + assert(xml.include?('dataOnRows')) + assert(xml.include?('colItems')) + + assert(!xml.include?('colFields')) + end end -- cgit v1.2.3 From d3cf7bca5728b166c7643ad839efeba60ed0e256 Mon Sep 17 00:00:00 2001 From: Tobias Egli Date: Wed, 2 Feb 2022 16:00:32 +0100 Subject: Autowidth cell calculation is now configurable On the workbook you can now configure the font_scale_divisor and the bold_font_multiplier to get better results for the automatic cell width calculationb --- examples/README.md | 1 + examples/fine_tuned_autowidth_example.md | 37 +++++++++++++++++++++++ examples/images/fine_tuned_autowidth_example.png | Bin 0 -> 56940 bytes lib/axlsx/workbook/workbook.rb | 25 +++++++++++++++ lib/axlsx/workbook/worksheet/cell.rb | 5 +-- lib/axlsx/workbook/worksheet/col.rb | 8 ++--- lib/axlsx/workbook/worksheet/worksheet.rb | 3 +- test/workbook/worksheet/tc_cell.rb | 14 ++++++++- 8 files changed, 85 insertions(+), 8 deletions(-) create mode 100644 examples/fine_tuned_autowidth_example.md create mode 100644 examples/images/fine_tuned_autowidth_example.png diff --git a/examples/README.md b/examples/README.md index a90dc87d..c1335892 100644 --- a/examples/README.md +++ b/examples/README.md @@ -57,6 +57,7 @@ Customizations: * [Column widths](column_widths_example.md) * [Outlines](column_outlines_example.md) * [No autowidth](no_autowidth_example.md) +* [Fine tuned autowidth](fine_tuned_autowidth_example.md) ### Filters diff --git a/examples/fine_tuned_autowidth_example.md b/examples/fine_tuned_autowidth_example.md new file mode 100644 index 00000000..42b332bc --- /dev/null +++ b/examples/fine_tuned_autowidth_example.md @@ -0,0 +1,37 @@ +## Description + +This is an example of how to use font_scale_divisor and bold_font_multiplier to fine-tune the automatic column width calculation. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook +wb.font_scale_divisor = 11.5 +wb.bold_font_multiplier = 1.05 +s = wb.styles +sm_bold = s.add_style b: true, sz: 5 +m_bold = s.add_style b: true, sz: 11 +b_bold = s.add_style b: true, sz: 16 +ub_bold = s.add_style b: true, sz: 21 +sm_text = s.add_style sz: 5 +m_text = s.add_style sz: 11 +b_text = s.add_style sz: 16 +ub_text = s.add_style sz: 21 + +wb.add_worksheet(name: 'Line Chart') do |sheet| + sheet.add_row(['Lorem', 'Lorem', 'Lorem', 'Lorem', 'Lorem', 'Lorem', 'Lorem', 'Lorem', 'WWW', 'www', 'iii', 'iii', + 'Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed', + 'Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed'], + style: [sm_bold, m_bold, b_bold, ub_bold, sm_text, m_text, b_text, ub_text, + b_bold, b_text, b_bold, b_text, b_bold, b_text]) +end + +p.serialize 'fine_tuned_autowidth_example.xlsx' +``` + +## Output + +![Output](images/fine_tuned_autowidth_example.png "Output") \ No newline at end of file diff --git a/examples/images/fine_tuned_autowidth_example.png b/examples/images/fine_tuned_autowidth_example.png new file mode 100644 index 00000000..56f99706 Binary files /dev/null and b/examples/images/fine_tuned_autowidth_example.png differ diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index 48b7f882..edf719d1 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -85,6 +85,9 @@ require 'axlsx/workbook/worksheet/selection.rb' # *workbookPr is only supported to the extend of date1904 class Workbook + BOLD_FONT_MULTIPLIER = 1.5 + FONT_SCALE_DIVISOR = 10.0 + # When true, the Package will be generated with a shared string table. This may be required by some OOXML processors that do not # adhere to the ECMA specification that dictates string may be inline in the sheet. # Using this option will increase the time required to serialize the document as every string in every cell must be analzed and referenced. @@ -213,6 +216,8 @@ require 'axlsx/workbook/worksheet/selection.rb' @use_autowidth = true + @bold_font_multiplier = BOLD_FONT_MULTIPLIER + @font_scale_divisor = FONT_SCALE_DIVISOR self.date1904= !options[:date1904].nil? && options[:date1904] yield self if block_given? @@ -243,6 +248,26 @@ require 'axlsx/workbook/worksheet/selection.rb' # see @use_autowidth def use_autowidth=(v=true) Axlsx::validate_boolean v; @use_autowidth = v; end + # Font size of bold fonts is multiplied with this + # Used for automatic calculation of cell widths with bold text + # @return [Float] + attr_reader :bold_font_multiplier + + def bold_font_multiplier=(v) + Axlsx::validate_float v + @bold_font_multiplier = v + end + + # Font scale is calculated with this value (font_size / font_scale_divisor) + # Used for automatic calculation of cell widths + # @return [Float] + attr_reader :font_scale_divisor + + def font_scale_divisor=(v) + Axlsx::validate_float v + @font_scale_divisor = v + end + # inserts a worksheet into this workbook at the position specified. # It the index specified is out of range, the worksheet will be added to the end of the # worksheets collection diff --git a/lib/axlsx/workbook/worksheet/cell.rb b/lib/axlsx/workbook/worksheet/cell.rb index 51a14494..3277b8c5 100644 --- a/lib/axlsx/workbook/worksheet/cell.rb +++ b/lib/axlsx/workbook/worksheet/cell.rb @@ -409,7 +409,7 @@ module Axlsx # This is still not perfect... # - scaling is not linear as font sizes increase def string_width(string, font_size) - font_scale = font_size / 10.0 + font_scale = font_size / row.worksheet.workbook.font_scale_divisor (string.to_s.size + 3) * font_scale end @@ -418,8 +418,9 @@ module Axlsx # imagemagick and loading metrics for every character. def font_size return sz if sz + font = styles.fonts[styles.cellXfs[style].fontId] || styles.fonts[0] - (font.b || (defined?(@b) && @b)) ? (font.sz * 1.5) : font.sz + font.b || (defined?(@b) && @b) ? (font.sz * row.worksheet.workbook.bold_font_multiplier) : font.sz end # Utility method for setting inline style attributes diff --git a/lib/axlsx/workbook/worksheet/col.rb b/lib/axlsx/workbook/worksheet/col.rb index 95204028..3b3775c4 100644 --- a/lib/axlsx/workbook/worksheet/col.rb +++ b/lib/axlsx/workbook/worksheet/col.rb @@ -125,12 +125,12 @@ module Axlsx # to this value and the cell's attributes are ignored. # @param [Boolean] use_autowidth If this is false, the cell's # autowidth value will be ignored. - def update_width(cell, fixed_width=nil, use_autowidth=true) + def update_width(cell, fixed_width = nil, use_autowidth = true) if fixed_width.is_a? Numeric - self.width = fixed_width + self.width = fixed_width elsif use_autowidth - cell_width = cell.autowidth - self.width = cell_width unless (width || 0) > (cell_width || 0) + cell_width = cell.autowidth + self.width = cell_width unless (width || 0) > (cell_width || 0) end end diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index cc0e56ae..adb9839f 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -762,11 +762,12 @@ module Axlsx def workbook=(v) DataTypeValidator.validate "Worksheet.workbook", Workbook, v; @workbook = v; end - def update_column_info(cells, widths=nil) + def update_column_info(cells, widths = nil) cells.each_with_index do |cell, index| width = widths ? widths[index] : nil col = find_or_create_column_info(index) next if width == :ignore + col.update_width(cell, width, workbook.use_autowidth) end end diff --git a/test/workbook/worksheet/tc_cell.rb b/test/workbook/worksheet/tc_cell.rb index 04f6bed3..bdbfd59d 100644 --- a/test/workbook/worksheet/tc_cell.rb +++ b/test/workbook/worksheet/tc_cell.rb @@ -44,7 +44,7 @@ class TestCell < Test::Unit::TestCase end def test_wide_r - assert_equal(@cAA.r, "AA2", "calculate cell reference") + assert_equal(@cAA.r, "AA2", "calculate cell reference") end def test_r_abs @@ -64,6 +64,18 @@ class TestCell < Test::Unit::TestCase assert_in_delta(6.6, @c.autowidth, 0.01) end + def test_autowidth_with_bold_font_multiplier + style = @c.row.worksheet.workbook.styles.add_style(b: true) + @c.row.worksheet.workbook.bold_font_multiplier = 1.05 + @c.style = style + assert_in_delta(6.93, @c.autowidth, 0.01) + end + + def test_autowidth_with_font_scale_divisor + @c.row.worksheet.workbook.font_scale_divisor = 11.0 + assert_in_delta(6.0, @c.autowidth, 0.01) + end + def test_time @c.type = :time now = DateTime.now -- cgit v1.2.3 From 196862524f94c58b1521ef84a6cf0397b411a685 Mon Sep 17 00:00:00 2001 From: Weston Ganger Date: Sat, 5 Feb 2022 15:39:05 -0800 Subject: Allow border style to accept an Array (#117) Co-authored-by: Noel Peden --- CHANGELOG.md | 1 + examples/borders_example.md | 9 ++++ lib/axlsx/stylesheet/border.rb | 2 + lib/axlsx/stylesheet/styles.rb | 110 ++++++++++++++++++++++++++++++++++------- test/stylesheet/tc_styles.rb | 48 ++++++++++++++++++ 5 files changed, 152 insertions(+), 18 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index 6e5604fa..3e642869 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -2,6 +2,7 @@ CHANGELOG --------- - **Unreleased** + - [PR #117](https://github.com/caxlsx/caxlsx/pull/117) - Allow passing an Array of border hashes to the `border` style. Change previous behaviour where `border_top`, `border_*` styles would not be applied unless `border` style was also defined. - [PR #122](https://github.com/caxlsx/caxlsx/pull/122) - Improve error messages when incorrect ranges are provided to `Worksheet#[]` - [PR #123](https://github.com/caxlsx/caxlsx/pull/123) - Fix invalid xml when pivot table created with more than one column in data field. Solves [Issue #110](https://github.com/caxlsx/caxlsx/issues/110) diff --git a/examples/borders_example.md b/examples/borders_example.md index d637681c..2607d17c 100644 --- a/examples/borders_example.md +++ b/examples/borders_example.md @@ -14,9 +14,18 @@ s = wb.styles red_border = s.add_style border: { style: :thick, color: 'FFFF0000', edges: [:left, :right] } blue_border = s.add_style border: { style: :thick, color: 'FF0000FF' } +complex_border = workbook.styles.add_style( + border: [ + { style: :thin, color: '000' }, + { style: :double, edges: [:top, :bottom] }, + { style: :thick, edges: [:left, :right] }, + ] +) + wb.add_worksheet(name: 'Custom Borders') do |sheet| sheet.add_row ['wrap', 'me', 'up in red'], style: red_border sheet.add_row [1, 2, 3], style: blue_border + sheet.add_row [4, 5, 6], style: complex_border end p.serialize 'borders_example.xlsx' diff --git a/lib/axlsx/stylesheet/border.rb b/lib/axlsx/stylesheet/border.rb index 422a4466..0a823c7a 100644 --- a/lib/axlsx/stylesheet/border.rb +++ b/lib/axlsx/stylesheet/border.rb @@ -6,6 +6,8 @@ module Axlsx include Axlsx::SerializedAttributes include Axlsx::OptionsParser + EDGES = [:left, :right, :top, :bottom].freeze + # Creates a new Border object # @option options [Boolean] diagonal_up # @option options [Boolean] diagonal_down diff --git a/lib/axlsx/stylesheet/styles.rb b/lib/axlsx/stylesheet/styles.rb index 2460ab10..d1ee1f66 100644 --- a/lib/axlsx/stylesheet/styles.rb +++ b/lib/axlsx/stylesheet/styles.rb @@ -310,33 +310,108 @@ module Axlsx # may include an :edges entry that references an array of symbols identifying which border edges # you wish to apply the style or any other valid Border initializer options. # If the :edges entity is not provided the style is applied to all edges of cells that reference this style. - # Also available :border_top, :border_right, :border_bottom and :border_left options with :style and/or :color - # key-value entries, which override :border values. + # Also available :border_top, :border_right, :border_bottom and :border_left options with :style and/or :color + # key-value entries, which override :border values. # @example # #apply a thick red border to the top and bottom # { :border => { :style => :thick, :color => "FFFF0000", :edges => [:top, :bottom] } # @return [Border|Integer] def parse_border_options(options={}) - return unless options[:border] - b_opts = options[:border] - if b_opts.is_a?(Hash) - raise ArgumentError, (ERR_INVALID_BORDER_OPTIONS % b_opts) unless b_opts.keys.include?(:style) && b_opts.keys.include?(:color) - border = Border.new b_opts - (b_opts[:edges] || [:left, :right, :top, :bottom]).each do |edge| - edge_options = options["border_#{edge}".to_sym] || {} - border_edge = b_opts.merge(edge_options) - b_options = { :name => edge, :style => border_edge[:style], :color => Color.new(:rgb => border_edge[:color]) } - border.prs << BorderPr.new(b_options) + if options[:border].nil? && Border::EDGES.all?{|x| options["border_#{x}".to_sym].nil? } + return nil + end + + if options[:border].is_a?(Integer) + if options[:border] >= borders.size + raise ArgumentError, (ERR_INVALID_BORDER_ID % options[:border]) end - options[:type] == :dxf ? border : borders << border - elsif b_opts.is_a? Integer - raise ArgumentError, (ERR_INVALID_BORDER_ID % b_opts) unless b_opts < borders.size + if options[:type] == :dxf - borders[b_opts].clone + return borders[options[:border]].clone + else + return options[:border] + end + end + + validate_border_hash = ->(val){ + if !(val.keys.include?(:style) && val.keys.include?(:color)) + raise ArgumentError, (ERR_INVALID_BORDER_OPTIONS % options[:border]) + end + } + + borders_array = [] + + if options[:border].nil? + base_border_opts = {} + else + if options[:border].is_a?(Array) + borders_array += options[:border] + + base_border_opts = {} + + options[:border].each do |b_opts| + if b_opts[:edges].nil? + base_border_opts = base_border_opts.merge(b_opts) + end + end else - border = b_opts + borders_array << options[:border] + + base_border_opts = options[:border] + + validate_border_hash.call(base_border_opts) + end + end + + Border::EDGES.each do |edge| + val = options["border_#{edge}".to_sym] + + if val + borders_array << val.merge(edges: [edge]) + end + end + + border = Border.new(base_border_opts) + + Border::EDGES.each do |edge| + edge_b_opts = base_border_opts + + skip_edge = true + + borders_array.each do |b_opts| + if b_opts[:edges] && b_opts[:edges].include?(edge) + edge_b_opts = edge_b_opts.merge(b_opts) + skip_edge = false + end + end + + if options["border_#{edge}".to_sym] + edge_b_opts = edge_b_opts.merge(options["border_#{edge}".to_sym]) + skip_edge = false + end + + if skip_edge && base_border_opts[:edges] + next + end + + if !edge_b_opts.empty? + if base_border_opts.empty? + validate_border_hash.call(edge_b_opts) + end + + border.prs << BorderPr.new({ + :name => edge, + :style => edge_b_opts[:style], + :color => Color.new(:rgb => edge_b_opts[:color]) }, + ) end end + + if options[:type] == :dxf + return border + else + return borders << border + end end # Parses Style#add_style options for number formatting. @@ -417,4 +492,3 @@ module Axlsx end end end - diff --git a/test/stylesheet/tc_styles.rb b/test/stylesheet/tc_styles.rb index 72bf1466..c46b6bdb 100644 --- a/test/stylesheet/tc_styles.rb +++ b/test/stylesheet/tc_styles.rb @@ -17,6 +17,7 @@ class TestStyles < Test::Unit::TestCase end assert(errors.size == 0) end + def test_add_style_border_hash border_count = @styles.borders.size @styles.add_style :border => {:style => :thin, :color => "FFFF0000"} @@ -26,6 +27,32 @@ class TestStyles < Test::Unit::TestCase assert_equal @styles.borders.last.prs.size, 4 end + def test_add_style_border_array + prev_border_count = @styles.borders.size + + borders_array = [ + {:style => :thin, :color => "DDDDDD"}, + {:edges => [:top], :style => :thin, :color => "000000"}, + {:edges => [:bottom], :style => :thick, :color => "FF0000"}, + {:edges => [:left], :style => :dotted, :color => "FFFF00"}, + {:edges => [:right], :style => :dashed, :color => "FFFFFF"}, + {:style => :thick, :color => "CCCCCC"}, + ] + + @styles.add_style(border: borders_array) + + assert_equal(@styles.borders.size, (prev_border_count+1)) + + current_border = @styles.borders.last + + borders_array.each do |b_opts| + if b_opts[:edges] + border_pr = current_border.prs.detect{|x| x.name == b_opts[:edges].first } + assert_equal(border_pr.color.rgb, "FF#{b_opts[:color]}") + end + end + end + def test_add_style_border_edges @styles.add_style :border => { :style => :thin, :color => "0000FFFF", :edges => [:top, :bottom] } parts = @styles.borders.last.prs @@ -258,4 +285,25 @@ class TestStyles < Test::Unit::TestCase end assert(errors.size == 0) end + + def test_border_top_without_border_regression + ### https://github.com/axlsx-styler-gem/axlsx_styler/issues/31 + + borders = { + top: { style: :double, color: '0000FF' }, + right: { style: :thick, color: 'FF0000' }, + bottom: { style: :double, color: '0000FF' }, + left: { style: :thick, color: 'FF0000' } + } + + borders.each do |edge, b_opts| + @styles.add_style("border_#{edge}".to_sym => b_opts) + + current_border = @styles.borders.last + + border_pr = current_border.prs.detect{|x| x.name == edge } + assert_equal(border_pr.color.rgb, "FF#{b_opts[:color]}") + end + + end end -- cgit v1.2.3