Merge branch 'master' into feature/manageable-scatter-markers

12 files changed, 183 insertions, 26 deletions

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
+
+![Output](images/chart_series_color_example.png "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
+
+![Output](images/conditional_formatting_text_equal_example.png "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
+
+![Output](images/fine_tuned_autowidth_example.png "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
new 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
new 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
new 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
new 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
+
+![Output](images/stacked_bar_chart_example.png "Output")