From b5092ea58f15e265f6a444cd5037d148a90d66a4 Mon Sep 17 00:00:00 2001 From: Stefan Daschek Date: Mon, 27 Jul 2020 21:08:59 +0200 Subject: Fix type detection for floats with out-of-rage exponents (#54) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Prior to this change, strings like "1e12345" would be interpreted as float values, regardless of the actual value of the exponent (which easily could be out of range for Ruby). In case the exponent was greater than `Float::MAX_10_EXP` (usually 308), this would result in a cell of type `:float` containing the literal string `"Infinity"`. Excel can not parse such cells and therefore gives a “corrupt data” error. In case the exponent was less than `Float::MIN_10_EXP` (usually -307) the cell would contain `0.0`. This does not result in Excel throwing an error, but probably isn't the expected result either. Note that this problem is quite likely to happen when creating a worksheet with hexadecimal strings, because e.g. "1234e567" is a perfectly valid hex value. The additional range check of the exponent introduces a slight performance overhead, so I decided to split the code path: I presume parsing floats with exponents < 100 (or no exponents at all) is way more common, so this code path behaves exactly like before. Only in the case of a 3 digit exponent the additional range check is introduced. --- lib/axlsx/util/constants.rb | 3 ++- lib/axlsx/workbook/worksheet/cell.rb | 4 +++- test/workbook/worksheet/tc_cell.rb | 6 ++++++ 3 files changed, 11 insertions(+), 2 deletions(-) diff --git a/lib/axlsx/util/constants.rb b/lib/axlsx/util/constants.rb index aa8eb626..25781983 100644 --- a/lib/axlsx/util/constants.rb +++ b/lib/axlsx/util/constants.rb @@ -393,7 +393,8 @@ module Axlsx ISO_8601_REGEX = /\A(-?(?:[1-9][0-9]*)?[0-9]{4})-(1[0-2]|0[1-9])-(3[0-1]|0[1-9]|[1-2][0-9])T(2[0-3]|[0-1][0-9]):([0-5][0-9]):([0-5][0-9])(\.[0-9]+)?(Z|[+-](?:2[0-3]|[0-1][0-9]):[0-5][0-9])?\Z/.freeze # FLOAT recognition - FLOAT_REGEX = /\A[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?\Z/.freeze + SAFE_FLOAT_REGEX = /\A[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]{1,2})?\Z/.freeze + MAYBE_FLOAT_REGEX = /\A[-+]?[0-9]*\.?[0-9]+[eE](?[-+]?[0-9]{3})\Z/.freeze # Numeric recognition NUMERIC_REGEX = /\A[+-]?\d+?\Z/.freeze diff --git a/lib/axlsx/workbook/worksheet/cell.rb b/lib/axlsx/workbook/worksheet/cell.rb index 5a00a97d..a066ba91 100644 --- a/lib/axlsx/workbook/worksheet/cell.rb +++ b/lib/axlsx/workbook/worksheet/cell.rb @@ -453,7 +453,9 @@ module Axlsx :boolean elsif v.to_s =~ Axlsx::NUMERIC_REGEX :integer - elsif v.to_s =~ Axlsx::FLOAT_REGEX + elsif v.to_s =~ Axlsx::SAFE_FLOAT_REGEX + :float + elsif (matchdata = v.to_s.match(MAYBE_FLOAT_REGEX)) && (Float::MIN_10_EXP..Float::MAX_10_EXP).cover?(matchdata[:exp].to_i) :float elsif v.to_s =~ Axlsx::ISO_8601_REGEX :iso_8601 diff --git a/test/workbook/worksheet/tc_cell.rb b/test/workbook/worksheet/tc_cell.rb index 9e0bdc0e..d6f5aeae 100644 --- a/test/workbook/worksheet/tc_cell.rb +++ b/test/workbook/worksheet/tc_cell.rb @@ -100,6 +100,12 @@ class TestCell < Test::Unit::TestCase def test_cell_type_from_value assert_equal(@c.send(:cell_type_from_value, 1.0), :float) + assert_equal(@c.send(:cell_type_from_value, "1e1"), :float) + assert_equal(@c.send(:cell_type_from_value, "1e#{Float::MAX_10_EXP}"), :float) + assert_equal(@c.send(:cell_type_from_value, "1e#{Float::MAX_10_EXP + 1}"), :string) + assert_equal(@c.send(:cell_type_from_value, "1e-1"), :float) + assert_equal(@c.send(:cell_type_from_value, "1e#{Float::MIN_10_EXP}"), :float) + assert_equal(@c.send(:cell_type_from_value, "1e#{Float::MIN_10_EXP - 1}"), :string) assert_equal(@c.send(:cell_type_from_value, 1), :integer) assert_equal(@c.send(:cell_type_from_value, Date.today), :date) assert_equal(@c.send(:cell_type_from_value, Time.now), :time) -- cgit v1.2.3