From: David Masover <ninja@...>
Date: 2009-09-29T09:58:31+09:00
Subject: Re: format problem

First, I should apologize for anyone following along -- that should be 
a_file.puts, not just puts.

On Monday 28 September 2009 05:10:16 pm Li Chen wrote:
> When I open the written file I get my expected format correctly. But it
> is odd that after I open it with Excel or import it into Excel the data
> in one column are not formatted. Is it caused by Excel?

Let's see...

> # file is open with Excel
>
> a  b  c
>
> c1  2  1
>
> d1  3  4

Most likely. My understanding is that Excel doesn't make a distinction between 
integers and floats. For example, try creating this table:

1
=A1/2

That second shell should be 0.5, right?

A quick experiment with OpenOffice shows the exact same behavior. I don't really 
know of a good way around it. However, a few things to consider:

First, does it matter? It's very quick to format a range of cells in Excel.

Second, do you really want to be doing this by hand? There's a CSV generator 
in the Ruby standard library that's at least as easy to use as what you came 
up with, probably easier. Watch this:

c=[  ['a','b','c'],['c1',2,1],['d1',3,4]    ]

require 'csv'
CSV.open 'test.txt', 'w' do |a_csv|
  c.each do |row|
    a_csv << row
  end
end

This doesn't fix the problem, but it avoids potential future problems -- for 
example, if any of your strings had tabs, quotes, commas, etc, that library 
will take care of it.

But the fact that you're using exactly two decimal places for everything, and 
you're in Excel, tells me this is probably currency of some kind, right? One 
possible workaround I noticed is this:

c=[  ['a','b','c'],['c1',2,1],['d1',3,4]    ]

require 'csv'
CSV.open 'test.txt', 'w' do |csv|
  c.each do |row|
    csv << row.map{|x| x.kind_of?(Numeric) ? "$#{x}" : x}
  end
end

That adds a dollarsign to all the numbers. Without even adding decimal places, 
OpenOffice suddenly sees them as $1.00, $2.00, etc.

Hope that helps.

If Excel behaves differently, I'm not really sure what to tell you...