Export to Excel from SciTE Result Log

Ruby
1

Hi All,

i am new to Ruby, i wondering is ther any way to export to Excel from
SciTE log

My code
…
…code continues
elements = get_text_fields(ie)
elements.each{|e|
p 'Name - ’ + e.name
p 'Value - ’ + e.value
…
…code continues

after executing i am getting “Value”, “Name”;
Now i need to export to Excel, Name as Header and Value in each cells
(rows and columns)

please help me out

2

after executing i am getting “Value”, “Name”;
Now i need to export to Excel, Name as Header and Value in each cells
(rows and columns)

You can export data using CSV library:

http://www.ruby-doc.org/stdlib/libdoc/csv/rdoc/index.html

CSV can then be opened in Excel.

3

Thanks Jason; but i am really sorry to ask you this stupid
question…can you guide me how to start it; i dont have any idea about
Ruby, i m very new to Ruby testing. my current task is capture the Name
and Value of the fields present in the web page and put it in the excel
and compare with the existing Data’s

Jason L. wrote:

after executing i am getting “Value”, “Name”;
Now i need to export to Excel, Name as Header and Value in each cells
(rows and columns)

You can export data using CSV library:

http://www.ruby-doc.org/stdlib/libdoc/csv/rdoc/index.html

CSV can then be opened in Excel.

4

Well, actually you may not need the csv library yet. But working with
Excel means you may want to take a look at it.

Anyway, maybe you could do something like this:

elements = [{:name => ‘John’, :value => 35}, {:name => ‘Henry’, :value
=> 45}, {:name => ‘Sue’, :value => 23}]

File.open(“out.csv”, “w”) do |row|
row << “Name, Value\n”
elements.each do |e|
row << “#{e[:name]}, #{e[:value]}\n”
end
end

that should return a csv file in your directory that looks like this
when opened in a spreadsheet:

Name Value
John 35
Henry 45
Sue 23

I am a newbie too, so someone else might have a way better plan. PS -
I’m assuming your ‘elements’ is an array of hashes.

5

Jason,

please find my code…i m able to open the Excel but im not able to put
the values, it shows errors.

require ‘watir’
ie=Watir::IE.attach(:title,/.Policy./)

ie.links.each{|link| #'Searching the Links in the Website
if link.id =~/policyForm:autoMainTabsList/ #Giving the Link Id using
the Regular Expression
link.click # Click the Link and higlight it
ie.links.each{|sub_link| # go into the sublink from the Link

Giving sublink ids

if sub_link.id=~/(policyForm:basegeneralTabsList)/ ||
sub_link.id=~/(policyForm:baseInsuredTabsList)/ ||
sub_link.id=~/(policyForm:underwritingTabsList)/ ||
sub_link.id=~/(policyForm:driverTabsList)/||
sub_link.id=~/(policyForm:vehicleTabsList)/

      sub_link.click # Click the Submink under specified id
      elements = get_text_fields(ie) # getting the Fields value and

Name
elements.each{|e| # Loop fo each
p 'Name - ’ + e.name #Prinitng the Name of the Fields

          p 'Value - ' + e.value # Printing the Value of the Fields
        }
    end
  }

end
}

I iterating the Loop with different User id, so the Name and value
differs…now i have to export it to the Excel.

  1. Let me know where to Start the Excel Coding, opening the Excel, how
    to load the contents to Excel,how to name with Header (Name,whet we are
    getting)
  2. Put the Label names or how to map the Fields name in the Excel

Sorry to bother you a lot, awaiting your feedback

Jason L. wrote:

Well, actually you may not need the csv library yet. But working with
Excel means you may want to take a look at it.

Anyway, maybe you could do something like this:

elements = [{:name => ‘John’, :value => 35}, {:name => ‘Henry’, :value
=> 45}, {:name => ‘Sue’, :value => 23}]

File.open(“out.csv”, “w”) do |row|
row << “Name, Value\n”
elements.each do |e|
row << “#{e[:name]}, #{e[:value]}\n”
end
end

that should return a csv file in your directory that looks like this
when opened in a spreadsheet:

Name Value
John 35
Henry 45
Sue 23

I am a newbie too, so someone else might have a way better plan. PS -
I’m assuming your ‘elements’ is an array of hashes.

6

Thanks a lot Jason, i got it;
now i am able to read the Text Box, List box contents and store it in
Excel sheet. thanks for your support;

i have a one more query,how can take the Labels and store it in Excel
sheet??

Eg: my Website has 10 pages, each page 5 t0 10 labels…how can i get
those and store in different worksheets

Thanks

7 
        elements.each{|e| # Loop fo each
          p 'Name - ' + e.name #Prinitng the Name of the Fields

          p 'Value - ' + e.value # Printing the Value of the Fields
        }

I don’t know what to say about the other stuff, but let’s look at just
this part above.

I think elements is an array of hashes, right? So it could be something
like this: elements = [{:name => ‘John’, :value => 35}, {:name =>
‘Henry’, :value
=> 45}, {:name => ‘Sue’, :value => 23}]

I used File.open - this method is like File.new where a new file is
created. Except I have associated a block with File.open. The block is
involked with File as parameter. This method will close your file when
the block is done.

See what file you get when you do this:

irb(main):006:0> File.open(‘test.txt’, ‘w’) do |file|
irb(main):007:1* file << “Hello”
irb(main):008:1> file << " How are you?"
irb(main):009:1> file << “\n”
irb(main):010:1> file << “I am fine”
irb(main):011:1> end

You should get a txt file that has this in it:

Hello How are you?
I am fine

All I am doing is appending strings to the file.

So with your example, you can iterate on your array elements using the
‘Array.each’ command, where you grab the name and value on each element
of the array using a block - which is done by grabbing the value of each
hash key.

I iterating the Loop with different User id, so the Name and value
differs…now i have to export it to the Excel.

  1. Let me know where to Start the Excel Coding, opening the Excel, how
    to load the contents to Excel,how to name with Header (Name,whet we are
    getting)

Then you make sure your file has ‘.csv’ behind it. Excel can read csv
files. there is no Excel coding. All you do is create the file

  1. Put the Label names or how to map the Fields name in the Excel

I forced the name and value on the first row in the spreadsheet file by
doing

row << “Name, Value\n”

If those are not changing, that should work.

Are you shooting for a table like this:

Name Value
John 35
Henry 45
Sue 23

in Excel?

Sorry to bother you a lot, awaiting your feedback

Jason L. wrote:

Well, actually you may not need the csv library yet. But working with
Excel means you may want to take a look at it.

Anyway, maybe you could do something like this:

elements = [{:name => ‘John’, :value => 35}, {:name => ‘Henry’, :value
=> 45}, {:name => ‘Sue’, :value => 23}]

File.open(“out.csv”, “w”) do |row|
row << “Name, Value\n”
elements.each do |e|
row << “#{e[:name]}, #{e[:value]}\n”
end
end

that should return a csv file in your directory that looks like this
when opened in a spreadsheet:

Name Value
John 35
Henry 45
Sue 23

I am a newbie too, so someone else might have a way better plan. PS -
I’m assuming your ‘elements’ is an array of hashes.

8

Rase In wrote:

Thanks a lot Jason, i got it;
now i am able to read the Text Box, List box contents and store it in
Excel sheet. thanks for your support;

no problem. I’m happy you got it to work!

i have a one more query,how can take the Labels and store it in Excel
sheet??

If they are not changing, I would just append those statically to your
csv file by doing:

row << “Name, Age, Favorite Color\n”

before you do your block iteration on the element array. If they are
changing, you need to dynamically refer to those labels. I encourage you
to experiment with IRB using methods on arrays and hashes to return the
keys of your hashes. Once you are able to do that, you just simply
append those values to your file using the << operator.

Eg: my Website has 10 pages, each page 5 t0 10 labels…how can i get
those and store in different worksheets

I am not a good source for Rails questions. You may want to re-post this
to a Rails forum, but I know that if you need multiple worksheets in a
single xls file, you will need to do some post processing of results.
Maybe you could create one csv file for each webpage. Then have Excel
read multiple csv files into multiple worksheets. When Excel saves in
csv format, it will only save the left most sheet. Therefore, you need a
different Excel file to grab all that data.

Try this: If you use Data -> Import External Data and point to the csv
file as the external data source, then you will have a data range that
can be refreshed
at any time. You may want to consult Excel wizards, but that is how I
would do it.