Support formatting excel currencies

roo-rb · simonoff · Mar 19, 2022 · Mar 11, 2021 · Mar 12, 2021 · Mar 15, 2021
commit f982d13c807374563815edc23c9f2df9f5f0f4b4
diff --git a/lib/roo/excelx/cell/number.rb b/lib/roo/excelx/cell/number.rb
@@ -66,6 +66,16 @@ def generate_formatter(format)
           when '##0.0E+0' then '%.1E'
           when "_-* #,##0.00\\ _€_-;\\-* #,##0.00\\ _€_-;_-* \"-\"??\\ _€_-;_-@_-" then number_format('%.2f', '-%.2f')
           when '@' then proc { |number| number }
+          when /^"([^"]*)"(.+)/
+            proc do |number|
+              formatted_number = generate_formatter($2).call(number)
+              "#{$1}#{formatted_number}"
+            end
+          when /^_\(\[\$([^-]+)[^#]+([^_]+)_\)/
+            proc do |number|
+              formatted_number = generate_formatter($2).call(number)
+              "#{$1}#{formatted_number}"
+            end
           else
             raise "Unknown format: #{format.inspect}"
           end

diff --git a/spec/lib/roo/excelx_spec.rb b/spec/lib/roo/excelx_spec.rb
@@ -326,6 +326,22 @@
         expect(subject.formatted_value(4, 1)).to eq '05010'
       end
     end
+
+    context 'contains US currency' do
+      let(:path) { 'test/files/currency-us.xlsx' }
+
+      it 'returns a zero-padded number' do
+        expect(subject.formatted_value(4, 1)).to eq '$20.51'
+      end
+    end
+
+    context 'contains other currency' do
+      let(:path) { 'test/files/currency-euro.xlsx' }
+
+      it 'returns a zero-padded number' do
+        expect(subject.formatted_value(4, 1)).to eq '€20.51'
+      end
+    end
   end
 
   describe '#row' do

diff --git a/test/files/currency-euro.xlsx b/test/files/currency-euro.xlsx
diff --git a/test/files/currency-us.xlsx b/test/files/currency-us.xlsx