# Write data into Excel Sheet

**URL:** <https://www.ruby-forum.com/t/write-data-into-excel-sheet/133400>\
**Category:** Ruby\
**Created:** [March 31, 2008, 8:41am UTC](https://www.ruby-forum.com/t/write-data-into-excel-sheet/133400 "2008-03-31T08:41:53Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![pranjal\_jain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac91a4/32.png) [@pranjal\_jain](https://www.ruby-forum.com/u/pranjal_jain)\
**Post date:** [March 31, 2008, 8:41am UTC](https://www.ruby-forum.com/t/write-data-into-excel-sheet/133400/1 "2008-03-31T08:41:53Z")

</div>

Hi I am new to Ruby.

can any one suggest me how we can write the data into the Excel Sheet  
from the array.

Can we use the command like this

$worksheet.Range(“b1:b12”).Insert = a[k]

Can I insert in excel sheet via the Insert command or some other command  
is used??

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![pranjal\_jain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac91a4/32.png) [@pranjal\_jain](https://www.ruby-forum.com/u/pranjal_jain)\
**Post date:** [March 31, 2008, 5:12pm UTC](https://www.ruby-forum.com/t/write-data-into-excel-sheet/133400/2 "2008-03-31T17:12:10Z")

</div>

Pranjal J. wrote:

> Hi I am new to Ruby.
> 
> can any one suggest me how we can write the data into the Excel Sheet  
> from the array.
> 
> Can we use the command like this
> 
> $worksheet.Range(“b1:b12”).Insert = a[k]
> 
> Can I insert in excel sheet via the Insert command or some other command  
> is used??
> 
> Thanks in advance.

check this out:

> **[Ruby on Windows](http://rubyonwindows.blogspot.com/)**
>
> On using the Ruby programming language on the Microsoft Windows platform

$worksheet.Range(“b1:b12”).value=a[k]

-r.

---

<div class="post-metadata">

**Author:** ![pranjal\_jain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac91a4/32.png) [@pranjal\_jain](https://www.ruby-forum.com/u/pranjal_jain)\
**Post date:** [April 1, 2008, 7:35am UTC](https://www.ruby-forum.com/t/write-data-into-excel-sheet/133400/3 "2008-04-01T07:35:48Z")

</div>

Rodrigo B. wrote:

> Pranjal J. wrote:
> 
> > Hi I am new to Ruby.
> > 
> > can any one suggest me how we can write the data into the Excel Sheet  
> > from the array.
> > 
> > Can we use the command like this
> > 
> > $worksheet.Range(“b1:b12”).Insert = a[k]
> > 
> > Can I insert in excel sheet via the Insert command or some other command  
> > is used??
> > 
> > Thanks in advance.
> 
> check this out:  
> [http://rubyonwindows.blogspot.com/](http://rubyonwindows.blogspot.com/)
> 
> $worksheet.Range(“b1:b12”).value=a[k]
> 
> -r.

Hi  
I tried the method told by you , but sorry to say it is writing only  
the last value in the excel sheet.  
The code is as follows :

require ‘watir’  
require ‘test/unit’

include Watir

class ExcelTest  
$excel = WIN32OLE.new(‘excel.Application’)  
$path = File.join(‘D:’,‘Ruby’,‘array.xls’)  
$workbook = $excel.WorkBooks.Open($path)  
$worksheet = $workbook.WorkSheets(“One”)  
$worksheet.Select

```
a = Array.new

```

a =  
[“3118”,“2102”,“1789”,“1301”,“1302”,“1303”,“1304”,“1245”,“1230”,“121”,“4293”,“122”,“209”,“1235”,“1247”,“2104”,]

```
i = 0
until i > 15
puts a[i]
$worksheet.Range("a1:a16").value = a[i]
i += 1

```

end

end

The output coming is as follows:  
2104  
2104  
2104  
2104  
2104  
2104  
2104  
2104  
2104  
2104  
2104  
2104  
2104  
2104  
2104  
2104

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![pranjal\_jain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac91a4/32.png) [@pranjal\_jain](https://www.ruby-forum.com/u/pranjal_jain)\
**Post date:** [April 1, 2008, 1:00pm UTC](https://www.ruby-forum.com/t/write-data-into-excel-sheet/133400/4 "2008-04-01T13:00:08Z")

</div>

On Dienstag, 2008-April-1 07:35:50, Pranjal J. wrote:

> I tried the method told by you , but sorry to say it is writing only  
> the last value in the excel sheet.

But to the all cells in the range, doesn’t it?

> ```
> i = 0
> until i > 15
> puts a[i]
> $worksheet.Range("a1:a16").value = a[i]
> i += 1
> 
> ```
> 
> end

Well, this code takes each of the 16 values, one after another,  
and tells Excel to assign it to the range A1:A16.  
When Excel gets one value for a range, it will write it  
to all the cells in the range. So all your values are written,  
thus only the last value remains. ☹

However, you could try the following:

```
$worksheet.Range("a#{i+1}").value = a[i]

```

or (writing the loop more in ruby-style):

a.each\_with\_index do |val, i|  
puts val  
$worksheet.Range(“a#{i+1}”).value = val  
end

or you can write the whole range in one go (that’s what I would do).  
If the range is horizontal, this is trivial:

$worksheet.Range(“a1:p1”).value = a

In your case, the cells form (a piece of) a column,  
so let’s create a column-array,  
that is an array of one-element arrays:

$worksheet.Range(“a1:a16”).value = [a].transpose

Hope that helps.

Sven

PS: In case you are not aware of it,  
your code cantains unnecessary things.  
For example the line  
a = Array.new  
or the class definition.
