Reading/writing Excel formats (or CSV)

ruby-talk
1

I'm supposed to put together an Excel spreadsheet. I've always been the
type of person who would rather write a script than use Excel, but in
this case I need to share data with people who use Excel.

I'm wondering if it's possible to get Ruby to create files that Excel can
read? I suspect that I should be able to use the CSV module for doing
this. Do I need to create a seperate header file or can the header be
placed at the top of the CSV file? (I don't have Excel, so I can't test
this)

So for example, I need to make a spredsheet of students (columns) and
quiz/homework/test scores, something like:

desc student1 student2 student3 student4
quiz1 80 92 78 87
hw1 85 90 81 91
test1 92 100 88 79
hw2 100 99 83 100
final 84 80 77 85

How would I set this up so that the CSV file written by Ruby can be read
by Excel?

I suspect that at the first line of the file I would need to be:
desc, student1, student2, student3, student4
correct? Then I would use CSV to write out the data which I would
probably store in hashes.

Phil

2

desc student1 student2 student3 student4
quiz1 80 92 78 87
hw1 85 90 81 91
test1 92 100 88 79
hw2 100 99 83 100
final 84 80 77 85

How would I set this up so that the CSV file written by Ruby can be read
by Excel?

What you have here is perfect. Excel is pretty flexible - you can delimit
by commas, tabs, spaces, whatever you like. The above is definitely more
easily readable than comma delimitation, but I think you'll have yourself
an extra mouseclick or two if you do use commas.

decs,student1,student2,student3,student4
quiz1,80,92,78,87
hw1,85,90,81,91
...

3

I'm supposed to put together an Excel spreadsheet. I've always been the
type of person who would rather write a script than use Excel, but in
this case I need to share data with people who use Excel.

I'm wondering if it's possible to get Ruby to create files that Excel can
read? I suspect that I should be able to use the CSV module for doing
this. Do I need to create a seperate header file or can the header be
placed at the top of the CSV file? (I don't have Excel, so I can't test
this)

So for example, I need to make a spredsheet of students (columns) and
quiz/homework/test scores, something like:

desc student1 student2 student3 student4
quiz1 80 92 78 87
hw1 85 90 81 91
test1 92 100 88 79
hw2 100 99 83 100
final 84 80 77 85

How would I set this up so that the CSV file written by Ruby can be read
by Excel?

I suspect that at the first line of the file I would need to be:
desc, student1, student2, student3, student4
correct? Then I would use CSV to write out the data which I would
probably store in hashes.

Check out Win32OLE for Ruby. You can actually interface directly with
Excel if your heart so desires.
http://homepage1.nifty.com/markey/ruby/win32ole/index_e.html

Matt

4

I'm wondering if it's possible to get Ruby to create files that Excel can
read? I suspect that I should be able to use the CSV module for doing
this. Do I need to create a seperate header file or can the header be
placed at the top of the CSV file? (I don't have Excel, so I can't test
this)

No need for extra modules if you don't need formulas or visual styling.

CSV headers will do. Tab separated fields will work regardless of
locale setting.
If you rename your CSV file as .XLS and double-click it in the
explorer, Excel will open it just like any other spreadsheet.

Also, if you copy tab-delimited CSV data to the clipboard, Excel will
paste it just as well.

If you need more complex stuff like formulas, and visual styles, you
can create Excel XML files, following Microsof'ts schemas:
http://www.microsoft.com/office/xml/default.mspx

If you need interoperability among platforms and open formats you may
also want to have a look at:
http://xml.openoffice.org/

cheers,
                                vruz

5

<snip>

I have had some success generating html files and importing these into Excel. The data is held in a table. One advantage of this over CVS is the formatting embodied in the html is also imported into Excel.

Dave.

···

On 5 Apr 2005, at 21:54, Phil Tomson wrote:

I'm supposed to put together an Excel spreadsheet. I've always been the
type of person who would rather write a script than use Excel, but in
this case I need to share data with people who use Excel.

6

easily readable than comma delimitation, but I think you'll have yourself
an extra mouseclick or two if you do use commas.

Make that "don't use" commas.

7

If you go the OLE route, this link will also save you some time..

http://www.rubygarden.org/ruby?ScriptingExcel

8

>
> I'm supposed to put together an Excel spreadsheet. I've always been
> the
> type of person who would rather write a script than use Excel, but in
> this case I need to share data with people who use Excel.

<snip>

I have had some success generating html files and importing these into
Excel. The data is held in a table. One advantage of this over CVS is
the formatting embodied in the html is also imported into Excel.

Excel as source code control? :slight_smile:

···

On Apr 6, 2005 6:13 PM, Dave Baldwin <dave.baldwin@3dlabs.com> wrote:

On 5 Apr 2005, at 21:54, Phil Tomson wrote:

Dave.

--
Into RFID? www.rfidnewsupdate.com Simple, fast, news.

9

Caleb Tennis wrote:

easily readable than comma delimitation, but I think you'll have yourself
an extra mouseclick or two if you do use commas.

Make that "don't use" commas.

Funny, I thought you typed "have" instead of "save."
But whatever...

Hal
"Don't use no double negatives."

10

And if you dont want to go the OLE route, or want to do this on Mac or
Linux, see http://jakarta.apache.org/poi/poi-ruby.html , which will let
you create real Excel files (not CVS or HTML) .

11

Sure. Managers have been doing it for years. :wink:

Dan

12

Caleb Tennis wrote:

easily readable than comma delimitation, but I think you'll have
yourself
an extra mouseclick or two if you do use commas.

Make that "don't use" commas.

Funny, I thought you typed "have" instead of "save."
But whatever...

You're right. I guess that's what I get for multitasking, and typing
faster than I think.

Let's try it one last time:

Using commas will make the import process go the fastest (in my experience).