In a report I was building, we were doing all our underlying math in BigDecimal for accuracy. But when I go to spool it out to an excel export via Spreadsheet, it fails pretty miserably with very odd behaviors.

A cell that should have ‘1844.05’ instead had the value: ‘1844.046875’.

I didn’t go hunting much further than lib/spreadsheet/excel/writer/worksheet.rb, but there are lots of explicit typechecks in there, and apparently BigDecimal confuses them.

Quick Fix: use .to_f on your BigDecimals before handing them off

Correct Fix: Use the Axlsx gem instead, which seems a bunch more stable, and nicer. Plus it spits out .xlsx files instead of the stupid BIFF8 .xls files.

This entry was posted on Thursday, August 2nd, 2012 at 8:55 am and is filed under Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.