From time to time I like to actually post a bit of code on this programming blog, so here's
a stream-of-conscious (as in "not a lot of thought went into design quality") example that shows how to:
- Open Excel, making it invisible (or visible) to the user.
- Create a workbook and access individual worksheets
- Add data to a cell, or retrieve data from a cell
- Add a chart to a worksheet, with constants for various chart types
- Save as Excel 97-2003 format and close Excel
If you know where I can find the constants for file type numbers, that would be appreciated. Calling SaveAs
without the type seems to use whatever version of Excel you are running, but I'd like to find how to save as
CSV or other formats.
Needless to say, this requires Excel be on the computer that's running the code.
require 'win32ole'
xl = WIN32OLE.new("Excel.Application")
puts "Excel failed to start" unless xl
xl.Visible = false
workbook = xl.Workbooks.Add
sheet = workbook.Worksheets(1)
#create some fake data
data_a = []
(1..10).each{|i| data_a.push i }
data_b = []
(1..10).each{|i| data_b.push((rand * 100).to_i) }
#fill the worksheet with the fake data
#showing 3 ways to populate cells with values
(1..10).each do |i|
sheet.Range("A#{i}").Select
xl.ActiveCell.Formula = data_a[i-1]
sheet.Range("B#{i}").Formula = data_b[i-1]
cell = sheet.Range("C#{i}")
cell.Formula = "=A#{i} - B#{i}"
end
#chart type constants (via http://support.microsoft.com/kb/147803)
xlArea = 1
xlBar = 2
xlColumn = 3
xlLine = 4
xlPie = 5
xlRadar = -4151
xlXYScatter = -4169
xlCombination = -4111
xl3DArea = -4098
xl3DBar = -4099
xl3DColumn = -4100
xl3DLine = -4101
xl3DPie = -4102
xl3DSurface = -4103
xlDoughnut = -4120
#creating a chart
chart_object = sheet.ChartObjects.Add(10, 80, 500, 250)
chart = chart_object.Chart
chart_range = sheet.Range("A1", "B10")
chart.SetSourceData(chart_range, nil)
chart.ChartType = xlXYScatter
#get the value from a cell
val = sheet.Range("C1").Value
puts val
#saving as pre-2007 format
excel97_2003_format = -4143
pwd = Dir.pwd.gsub('/','\\') << '\\'
#otherwise, it sticks it in default save directory- C:\Users\Sam\Documents on my system
workbook.SaveAs("#{pwd}whatever.xls", excel97_2003_format)
xl.Quit
It's also posted
in my Miscellany project at GitHub
Hey! Why don't you make your life easier and subscribe to the full post
or short blurb RSS feed? I'm so confident you'll love my smelly pasta plate
wisdom that I'm offering a no-strings-attached, lifetime money back guarantee!
Leave a comment
Can you send me an email about how to get started with Ruby. I first need to use it to open Excel, go to a certain sheet, and enter some data (that I will read from a text file using Ruby.)I have been programming since 1970. I like new stuff. THANKS. Charlie
Posted by Charlie Koch
on May 03, 2010 at 07:03 PM UTC - 6 hrs
Hi Charlie,
The first few lines of code open Excel and go to the first sheet:
require 'win32ole'
xl = WIN32OLE.new("Excel.Application")
puts "Excel failed to start" unless xl
xl.Visible = false
workbook = xl.Workbooks.Add
sheet = workbook.Worksheets(1)
....
To enter a value in a cell use:
sheet.Range("A1").Formula = whateverData
Where "A1" is the cell.
To read from a file in Ruby, you can use:
File.open(file_path) do |file|
file.each_line do |line|
process the line here ... maybe use line.split(delimiter) to get it to an array
end
end
Hope that helps!
Posted by
Sammy Larbi
on May 04, 2010 at 07:17 AM UTC - 6 hrs
Hi
I have to read and fetch data from the ruby file(eg: properties.rb). The pattern of file is as:
##LOGIN PAGE##
xpath = "//input[@value='Login']"
I need to put all required xpath in one file and then fetch them as required in each test case. please let me know if this can be done and the code example. thanks
Posted by Harry
on Jun 22, 2010 at 07:00 AM UTC - 6 hrs
Harry,
It's not quite related to Excel as far as I can tell, and I'm not sure I fully understand the problem.
If you'd like, drop me a line via the contact page (
http://www.codeodor.com/Contact.cfm ) and I'll try to help you through email.
Thanks,
Sam
Posted by
Sammy Larbi
on Jun 22, 2010 at 07:31 AM UTC - 6 hrs
Thanks Darren, that is helpful!
Posted by
Sammy Larbi
on Dec 06, 2010 at 08:17 AM UTC - 6 hrs
hello Sam,
I would like to know that how can i use dir.pwd in next page.
My concern :-
I have create a login page object name as login.
now i would like to call the functions which are create in login page object in my actions.
plz reply
Kuntal.sugandhi@gmail.com
QA Engineer
Posted by kuntal
on Sep 25, 2014 at 04:07 AM UTC - 6 hrs
@kuntal: I'm afraid I don't understand the question. What is preventing you from using Dir.pwd, or what is it doing that you're not expecting?
Posted by
Sammy Larbi
on Sep 27, 2014 at 04:04 PM UTC - 6 hrs
Hi, I am able to open the excel sheet wat I need is to get the no of rows present in the excel sheet can u please let me know the method to be used to get the count of the rows.
I tried with count,display etc but unable to retrieve it
Posted by farooq
on Jun 12, 2015 at 08:54 AM UTC - 6 hrs
Farooq,
Check out the Excel Object Model reference for the version of Excel you're using. Here's 2013:
https://msdn.microsoft.com/en-us/library/office/ff...Essentially it looks to me like you have to read each row until you find a blank one that's not supposed to be blank, or read backwards until you find one that is not blank.
Posted by
Sammy Larbi
on Jun 12, 2015 at 09:55 AM UTC - 6 hrs
How to verify an excel object?
Scenario: There is a button "Export to Excel". My requirement is to save the file and verify the excel object has been downloaded correctly with the correct file name.
Please suggest
Posted by Saurav
on Mar 08, 2017 at 05:58 AM UTC - 6 hrs
I don't think you can verify it once it leaves your server and is downloaded.
Unless you mean writing an automated test to verify it works as you expect, to be run before deploying your code. In which case, that is too far beyond the scope here.
Posted by
Sammy Larbi
on Mar 08, 2017 at 07:19 AM UTC - 6 hrs
HI ,
Does anyone know gem which is used for excel GUI automation(Excel interactive).Am able to open excel launch excel file by using win32ole gem but not switching excel ribbon.
Posted by Appu
on Mar 08, 2017 at 01:14 PM UTC - 6 hrs
Hi Does anyone know any method for automate treeview(SysTreeView) for excel.
Posted by Appu
on Apr 05, 2017 at 02:38 PM UTC - 6 hrs
Hi
I have been studying Ruby and I have understood that It is possible controlling excel with Ruby.
I woukd like to know if all VBA's sintax rules are available once win32ole have been required.
For instance, is still .cells(row,column) a valid instruction? Or, again, Is still .currentregion a valid and usabile method?
Posted by Luca Monardi
on Jun 20, 2020 at 02:54 AM UTC - 6 hrs
Leave a comment