[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

reading/writing Excel formats (or CSV

ptkwt

4/5/2005 8:35:00 PM


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
24 Answers

Caleb Tennis

4/5/2005 9:02:00 PM

0

> 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
...

Caleb Tennis

4/5/2005 9:05:00 PM

0

> 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.

Hal E. Fulton

4/5/2005 9:09:00 PM

0

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."

Caleb Tennis

4/5/2005 9:11:00 PM

0

> 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).

Belorion

4/5/2005 9:14:00 PM

0

> 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/in...

Matt

Alan Chen

4/5/2005 9:21:00 PM

0

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

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

vruz

4/5/2005 10:47:00 PM

0

> 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/de...

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

cheers,
vruz

Dave Baldwin

4/6/2005 8:13:00 AM

0

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.

<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.

Lyndon Samson

4/6/2005 8:18:00 AM

0

On Apr 6, 2005 6:13 PM, Dave Baldwin <dave.baldwin@3dlabs.com> wrote:
> 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.
>
> <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? :-)


> Dave.
>
>


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

avik.sengupta@gmail.com

4/6/2005 9:47:00 AM

0

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... , which will let
you create real Excel files (not CVS or HTML) .