Read the numeric field

[asiniy]

I have trouble with roo and don't know how to solve it. I need help

I have a table with values like (in OpenOffice)

1st column is numeric. When I try to get the value from (2,1) I get this value without '0' value (600000383.0 instead of 0600000383). Is there any way to get the numeric value as string without any formatting with roo?

[asiniy]

I found that topic: http://thr3ads.net/rails/2013/10/2695463-Fwd-Read-a-Number-column-with-leading-zeros-using-Roo-gem but it given't happy to me. I don't know how much digits in the string I have. I need just copy that value as string

[emerson-h]

There is a way to get the value as a string. I'm one of the new Roo maintainers and I'll be merging in a way to get untyped values from a field in a few days.

I'll post here again when it has been merged.

[asiniy]

ok, thanks!

@emerson-h this will be very helpful. I waiting for your response

[andymeneely]

+1 and thanks for picking up maintenance @emerson-h!

Here's my current workaround using excelx_value (only works for Excelx).

cell = s.cell(row,col)
cell = s.excelx_value(row,col) if s.excelx_type(row,col) == [:numeric_or_formula, "General"]

[stevendaniels]

I've been looking into this issue for ods and I think I have a solution. I'm working on adding test cases and a pull request.

[stevendaniels]

When I started working on the tests, I ran into some major regressions when dealing with spreadsheets that used commas instead of periods before decimal places, i.e. 12,50 instead of 12.50.

Here are my original modifications:

# lib/roo/open_office.rb#read_cells
# when '', nil, 'date', 'percentage', 'float'
when '', nil, 'date', 'percentage'
  #
when 'float'
   # <table:table-cell office:value-type="float" office:value="1234">
   #   <text:p>01234</text:p>
   # </table:table-cell>
   # check for zero_padded numbers
   cell.children.each do |child|
      next unless child.name == 'p'
      # make sure the numeric office value == the value of the text
      next if cell.attributes["value"].value == child.text
      str_v = child.text
       value_type = 'string'
 end
 ...

That solved the initial issue, finding zero-padded numbers, but several of the worksheets in the test suite had commas instead of periods:

<table:table-cell office:value-type="float" office:value="12.5">
  <text:p>12,50</text:p>
</table:table-cell>

I don't know how to accurately determine whether a number is using commas instead of periods for decimals. Is 1.000 == 1000 or 1.000 == 1.0?

Creating a solution similar to what you use for excel would ideal, but it would require making major changes to how roo deals with OpenOffice files.

Does anyone else have any ideas on how to solve this issue?

[simonoff]

Coma or dot in number it's a locale specific thing.

[stevendaniels]

It is for locales. Unfortunately, the xml files that roo currently processes don't indicate which locale the file is based on. I could also imagine a situation where a file is modified by people from many different locales, so the locale information might not be accurate.

I prefer having a ods_value method for special case like zero-padded numbers.

[nicolasgarnil]

+1
Is there a workaround to get the original value without decimals?

[MrHubble]

@nicolasgarnil I'm not sure if the fix by @emerson-h was merged, but the workaround by @andymeneely (shown below) worked for me.

cell = s.cell(row,col)
cell = s.excelx_value(row,col) if s.excelx_type(row,col) == [:numeric_or_formula, "General"]

[stevendaniels]

I believe this issue is fixed by #240, but we need to add some specs or tests to confirm it works properly.

[stevendaniels]

So, I got around to testing this today. It's still broken, but I think I know where to look now.