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

8 files changed, 85 insertions, 8 deletions

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
--- /dev/null
+++ b/examples/images/fine_tuned_autowidth_example.png
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