Leading 0 in number fields

[itmart]

Hi,

I have a spreadsheet with some account numbers and some of them start with 0 (for example 0123) and when I tried to print out the rows like this:

@xls = Spreadsheet.open('./nscc.xls')
@sheet = @xls.worksheet 0
@sheet.each do |row|
  row
end

I see the account numbers are being printed as float, like 123.0. Is there a way I can print it out as a string 0123 instead? Thanks for helping!

[itmart]

Also tried

@xls = Spreadsheet.open('./nscc.xls')
@sheet = @xls.worksheet 0
@sheet.each do |row|
  row.set_format(7, Spreadsheet::Format.new(:number_format => 'GENERAL'))
  row
end

In an attempt to make it plain text but it's still displaying number instead. I am missing something?

[zdavatz]

what software created the file?

[itmart]

I believe Excel but I'm not sure which version since I'm downloading the spreadsheet from an external source and need to parse the data there into my app. You can look at it here. It's the clearing no column that I'm trying to parse.

[zdavatz]

There is stuff in the file like 5078@ and then there are many lines like 4508 4575 2028.

So the formatting in the source file is not always the same. You need to put every cell into a string or similar. Then I guess it will work.

The 123.0 is something Excel does.

[itmart]

Yeah I'm aware of that, but I was just mainly wondering if there's a way to keep the leading 0? I saw in excel if I change the data type of that column to GENERAL then it keeps the leading 0, is there a way I can do that in the code?

Manually changing is my last resort. I would really prefer it if I could do it programmatically so I can set up my app to fetch from that link every month and just parse the data without me having to manually change the data first. They update that link every month so I'd need to keep it updated in my app as well.

[zdavatz]

You can do it programmatically but not with spreadsheet, because XLS does some magic there and we can not influence that. You have to format the strings with Ruby before your write the file. We do that all the time.