diff options
42 files changed, 671 insertions, 143 deletions
diff --git a/.travis.yml b/.travis.yml index 465df230..23b66c5e 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.16.0 - rvm: ruby-head - rvm: jruby-head allow_failures: - rvm: ruby-head - rvm: jruby-9.1.17.0 + - rvm: jruby-9.2.16.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 diff --git a/CHANGELOG.md b/CHANGELOG.md index e79c88b5..55366dd2 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -3,11 +3,25 @@ CHANGELOG - **Unreleased** - [PR #75](https://github.com/caxlsx/caxlsx/pull/85) - Added manageable markers for scatter series + - [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) + +- **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 + +- **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 + - [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. - [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 @@ -15,7 +29,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 @@ -148,7 +162,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 @@ -189,23 +203,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! @@ -220,7 +234,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 @@ -230,14 +244,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 @@ -3,7 +3,7 @@ [](http://badge.fury.io/rb/caxlsx)  - + ## Notice: Community Axlsx Organization @@ -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) 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/examples/README.md b/examples/README.md index 5b1abdc2..c1335892 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 @@ -43,10 +44,12 @@ 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) * [Hide gridlines](hide_gridlines_in_chart_example.md) +* [Chart series color](chart_series_example.md) ### Columns @@ -54,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 @@ -100,7 +104,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) 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/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 + + 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/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 + + 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 + +
\ No newline at end of file diff --git a/examples/images/chart_series_color_example.png b/examples/images/chart_series_color_example.png Binary files differnew file mode 100644 index 00000000..177dc5c2 --- /dev/null +++ b/examples/images/chart_series_color_example.png diff --git a/examples/images/conditional_formatting_text_equal_example.png b/examples/images/conditional_formatting_text_equal_example.png Binary files differnew file mode 100644 index 00000000..60cfaf92 --- /dev/null +++ b/examples/images/conditional_formatting_text_equal_example.png diff --git a/examples/images/fine_tuned_autowidth_example.png b/examples/images/fine_tuned_autowidth_example.png Binary files differnew file mode 100644 index 00000000..56f99706 --- /dev/null +++ b/examples/images/fine_tuned_autowidth_example.png diff --git a/examples/images/stacked_bar_chart_example.png b/examples/images/stacked_bar_chart_example.png Binary files differnew file mode 100644 index 00000000..da046983 --- /dev/null +++ b/examples/images/stacked_bar_chart_example.png 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 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 + + diff --git a/lib/axlsx.rb b/lib/axlsx.rb index be4a2cd5..4e804513 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' @@ -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 @@ -78,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/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 e9af9400..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 @@ -49,6 +44,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] @@ -56,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 @@ -66,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, @shape = nil, nil, nil + @gap_width, @overlap, @shape = nil, nil, nil super(frame, options) @series_type = BarSeries @d_lbls = nil @@ -94,17 +91,15 @@ 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) + def overlap=(v) + RangeValidator.validate "BarChart.overlap", -100, 100, v + @overlap=(v) end - alias :gapDepth= :gap_depth= # The shape of the bars or columns # must be one of [:cone, :coneToMax, :box, :cylinder, :pyramid, :pyramidToMax] @@ -124,8 +119,8 @@ module Axlsx str << ('<c:varyColors val="' << vary_colors.to_s << '"/>') @series.each { |ser| ser.to_xml_string(str) } @d_lbls.to_xml_string(str) if @d_lbls + str << ('<c:overlap val="' << @overlap.to_s << '"/>') unless @overlap.nil? str << ('<c:gapWidth val="' << @gap_width.to_s << '"/>') unless @gap_width.nil? - str << ('<c:gapDepth val="' << @gap_depth.to_s << '"/>') unless @gap_depth.nil? str << ('<c:shape val="' << @shape.to_s << '"/>') unless @shape.nil? axes.to_xml_string(str, :ids => true) str << '</c:barChart>' 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 << '</a:solidFill></c:spPr></c:dPt>' end + if series_color + str << '<c:spPr><a:solidFill>' + str << ('<a:srgbClr val="' << series_color << '"/>') + str << '</a:solidFill>' + str << '</c:spPr>' + 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 << ('<c:shape val="' << shape.to_s << '"></c:shape>') end end 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/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/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/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/lib/axlsx/version.rb b/lib/axlsx/version.rb index 89954f77..bcd95e09 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.1" end 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/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb index 55174968..bc500a8a 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 @@ -169,7 +173,9 @@ module Axlsx # @return [String] def to_xml_string(str = '') str << '<?xml version="1.0" encoding="UTF-8"?>' - str << ('<pivotTableDefinition xmlns="' << XML_NS << '" name="' << name << '" cacheId="' << cache_definition.cache_id.to_s << '" dataOnRows="1" applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0" applyPatternFormats="0" applyAlignmentFormats="0" applyWidthHeightFormats="1" dataCaption="Data" showMultipleLabel="0" showMemberPropertyTips="0" useAutoFormatting="1" indent="0" compact="0" compactData="0" gridDropZones="1" multipleFieldFilters="0">') + + str << ('<pivotTableDefinition xmlns="' << XML_NS << '" name="' << name << '" cacheId="' << cache_definition.cache_id.to_s << '"' << (data.size <= 1 ? ' dataOnRows="1"' : '') << ' applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0" applyPatternFormats="0" applyAlignmentFormats="0" applyWidthHeightFormats="1" dataCaption="Data" showMultipleLabel="0" showMemberPropertyTips="0" useAutoFormatting="1" indent="0" compact="0" compactData="0" gridDropZones="1" multipleFieldFilters="0">') + str << ('<location firstDataCol="1" firstDataRow="1" firstHeaderRow="1" ref="' << ref << '"/>') str << ('<pivotFields count="' << header_cells_count.to_s << '">') header_cell_values.each do |cell_value| @@ -191,7 +197,7 @@ module Axlsx end str << '</rowItems>' end - if columns.empty? + if columns.empty? && data.size <= 1 str << '<colItems count="1"><i/></colItems>' else str << ('<colFields count="' << columns.size.to_s << '">') @@ -212,6 +218,7 @@ module Axlsx data.each do |datum_value| # The correct name prefix in ["Sum","Average", etc...] str << "<dataField name='#{(datum_value[:subtotal]||'')} of #{datum_value[:ref]}' fld='#{header_index_of(datum_value[:ref])}' baseField='0' baseItem='0'" + str << " numFmtId='#{datum_value[:num_fmt]}'" if datum_value[:num_fmt] str << " subtotal='#{datum_value[:subtotal]}' " if datum_value[:subtotal] str << "/>" end diff --git a/lib/axlsx/workbook/worksheet/row.rb b/lib/axlsx/workbook/worksheet/row.rb index decd27c7..63a8d328 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) @@ -56,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 @@ -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..adb9839f 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 @@ -595,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, (ERR_CELL_REFERENCE_INVALID % cell_def) + elsif parts.first.nil? + raise ArgumentError, (ERR_CELL_REFERENCE_MISSING_CELL % [cell_def.split(":").first, cell_def]) + elsif parts.last.nil? + raise ArgumentError, (ERR_CELL_REFERENCE_MISSING_CELL % [cell_def.split(":").last, cell_def]) + end + range(*parts) end end @@ -608,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 @@ -684,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 @@ -742,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/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 ca1ca016..c8bdc8d0 100644 --- a/test/drawing/tc_bar_chart.rb +++ b/test/drawing/tc_bar_chart.rb @@ -32,18 +32,19 @@ class TestBarChart < 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 gap width") { @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_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 } @@ -68,4 +69,18 @@ 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_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 + 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 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 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 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..b98fa2f0 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 @@ -303,6 +303,9 @@ 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 stream.string.valid_encoding? + assert_equal(stream.external_encoding, Encoding::ASCII_8BIT) # Cached ids should be cleared assert(Axlsx::Relationship.ids_cache.empty?) 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 diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb index 6d7f4d1c..591f0ba9 100644 --- a/test/workbook/worksheet/tc_pivot_table.rb +++ b/test/workbook/worksheet/tc_pivot_table.rb @@ -132,4 +132,49 @@ 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 + + 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 diff --git a/test/workbook/worksheet/tc_row.rb b/test/workbook/worksheet/tc_row.rb index f909129c..38b13806 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 = (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]) + assert_equal(c.value, index < offset ? nil : values[index - offset]) + end + end + 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] |
