I got different format when read the number from excel cells

[wxy1106]

I made some random integer numbers with different digit (from 1 to 16).

Note that I didn't change any format of the cells. So I think every number should show the same form when be printed.

But when I printed them out in my ruby program through spreadsheet, I found different digit shows different format.

Here is what I found:

1. the 16-digits number is scientific notation
2. the 7 and 8-digits number always keep integer
3. the 6 and 9-digits number sometimes become float and keep integer in the rest case
4. the rest numbers are float form though they are integer original.

I just want to know is this a bug or an intentional feature.

[zdavatz]

What OS are you using? What version of the gem are you using? Can you please post the script that created the files of which you posted the screenshots. Thank you.

My first guess is: Excel ist playing tricks with the numbers and interpreting them by its own inherent logic.

With which Office did you open the files?

[wxy1106]

@zdavatz Sorry for missing the detials

I'm using spreadsheet-1.0.7, ruby-2.2.3, Excel 2013, and I saved the file as xls suffix.

Here is the xlsx file, sheet2 shows how I create these numbers.

The problem exists on both Windows (win10/64bit) and Linux (Read Hat).

The rake task goes like this

namespace :excel do
  desc "excel_test"
  task excel_test: :environment do
    xls_path = Rails.root.join('tmp', 'excel_test.xls')
    book = Spreadsheet.open(xls_path)
    sheet = book.worksheet 0
    sheet.each do |row|
      row.each_with_index do |cell,index|
        puts "#{index+1}：#{cell}"
      end
    end
  end
end

I'm pretty sure these results are affected by the Excel. With hat format the cell treats a certain number causes different results.

But its still wired that not all the numbers have the same form when printed out, since I didn't change any format of any cell.

One more thing: After I change the cells' format to 'text'(original is 'common'), spreadsheet can read these numbers as String type correctly.

[zdavatz]

Can you please try the same with LibreOffice? Excel 2013 does have some issues. https://de.libreoffice.org/download/libreoffice-still/

Also, as you found out yourself Excel does some magic on its own ;) and that does not always have the expected effect ;).

[wxy1106]

@zdavatz Do you mean using LibreOffice to create the 'xls' file? If so, I tried and got this:

I think it's better than Excel. :smile:

[zdavatz]

Yes, I think LibreOffice is far better then Microsoft Office for this kind of stuff.