search

cxn03651 / write_xlsx

Rubygem for create excel xlsx file
http://cxn03651.github.io/write_xlsx/
MIT License
287 stars 64 forks source link

Dealing with NAN #47

Open PhilipTB opened 5 years ago

PhilipTB commented 5 years ago

Excel uses NaN to indicate missing points in a chart, so the charting skips over the point. However, if you set a data point to Float::NaN in ruby, and write an array containing this value, the gem falls over here:

[worksheet.rb: ~line 5773] def write_cell_value(value = '') #:nodoc: value ||= '' value = value.to_i if value == value.to_i @writer.data_element('v', value) end

on the == value.to_i test,

You get a FloatDomainError:

   31: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/workbook.rb:159:in `close'
    30: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/workbook.rb:1315:in `store_workbook'
    29: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/package/packager.rb:36:in `create_package'
    28: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/package/packager.rb:75:in `write_worksheet_files'
    27: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/sheets.rb:47:in `write_worksheet_files'
    26: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/sheets.rb:47:in `each_with_index'
    25: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/sheets.rb:47:in `each'
    24: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/sheets.rb:48:in `block in write_worksheet_files'
    23: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/sheets.rb:250:in `write_sheet_files'
    22: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:397:in `assemble_xml_file'
    21: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/utility.rb:523:in `write_xml_declaration'
    20: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:398:in `block in assemble_xml_file'
    19: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/package/xml_writer_simple.rb:27:in `tag_elements'
    18: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:404:in `block (2 levels) in assemble_xml_file'
    17: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6749:in `write_sheet_data'
    16: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/package/xml_writer_simple.rb:27:in `tag_elements'
    15: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6749:in `block in write_sheet_data'
    14: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6759:in `write_rows'
    13: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6759:in `each'
    12: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6769:in `block in write_rows'
    11: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6795:in `write_row_element'
    10: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/package/xml_writer_simple.rb:27:in `tag_elements'
     9: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6796:in `block in write_row_element'
     8: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6770:in `block (2 levels) in write_rows'
     7: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6786:in `write_cell_column_dimension'
     6: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6786:in `each'
     5: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:6787:in `block in write_cell_column_dimension'
     4: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet/cell_data.rb:50:in `write_cell'
     3: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/package/xml_writer_simple.rb:27:in `tag_elements'
     2: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet/cell_data.rb:51:in `block in write_cell'
     1: from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:5773:in `write_cell_value'

C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/write_xlsx-0.85.3/lib/write_xlsx/worksheet.rb:5773:in `to_i': NaN (FloatDomainError)

I have fixed my local version, by adding a line to the start of the method to convert the cell to a formula:

def write_cell_value(value = '') #:nodoc:
  return write_cell_formula('=NA()') if !value.nil? && value.is_a?(Float) && value.nan?
  value ||= ''
  value = value.to_i if value == value.to_i
  @writer.data_element('v', value)
end

Was wondering whether you could provide a permanent fix for the problem? I am not sure whether my approach is the correct solution, but it does work. I would be reluctant to have to write a wrapper around the gem which checks all array writes for NaNs and then convert to writing individual cells as formulas? The problem exists because Excel represents NaN as a formula rather than a type of Float.

axelzito commented 2 years ago

This work for me x)

cxn03651 commented 2 years ago

Thank you for your PR. PR has been merged and released v.1.09.3.

gabriel-andreoli commented 1 year ago

I have the same problem in the last version 1.10.2, how i resolve?

cxn03651 commented 1 year ago

send me a sample which raises exception, please.