My Secret Life as a Spaghetti Coder
home | about | contact | privacy statement
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:
  1. Open Excel, making it invisible (or visible) to the user.
  2. Create a workbook and access individual worksheets
  3. Add data to a cell, or retrieve data from a cell
  4. Add a chart to a worksheet, with constants for various chart types
  5. 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!


Comments
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 - 5 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 - 5 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 - 5 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 - 5 hrs

A reference to the file formats is at http://msdn.microsoft.com/en-us/library/bb241279.a...

I located this through the suburb Ruby on Windows blog. See the link http://rubyonwindows.blogspot.com/search/label/exc...

Hope this helps.

Posted by Darren Henderson on Dec 05, 2010 at 03:13 AM UTC - 5 hrs

Thanks Darren, that is helpful!

Posted by Sammy Larbi on Dec 06, 2010 at 08:17 AM UTC - 5 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 - 5 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 - 5 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 - 5 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 - 5 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 - 5 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 - 5 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 - 5 hrs

I don't think it would be specific to Excel in that case.

You might look here:
http://www.gearheadforhire.com/articles/ruby/win32...

Posted by Sammy Larbi on Mar 08, 2017 at 01:30 PM UTC - 5 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 - 5 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 - 5 hrs

Leave a comment

Leave this field empty
Your Name
Email (not displayed, more info?)
Website

Comment:

Subcribe to this comment thread
Remember my details
Google
Web CodeOdor.com

Me
Picture of me

Topics
.NET (19)
AI/Machine Learning (14)
Answers To 100 Interview Questions (10)
Bioinformatics (2)
Business (1)
C and Cplusplus (6)
cfrails (22)
ColdFusion (78)
Customer Relations (15)
Databases (3)
DRY (18)
DSLs (11)
Future Tech (5)
Games (5)
Groovy/Grails (8)
Hardware (1)
IDEs (9)
Java (38)
JavaScript (4)
Linux (2)
Lisp (1)
Mac OS (4)
Management (15)
MediaServerX (1)
Miscellany (76)
OOAD (37)
Productivity (11)
Programming (168)
Programming Quotables (9)
Rails (31)
Ruby (67)
Save Your Job (58)
scriptaGulous (4)
Software Development Process (23)
TDD (41)
TDDing xorblog (6)
Tools (5)
Web Development (8)
Windows (1)
With (1)
YAGNI (10)

Resources
Agile Manifesto & Principles
Principles Of OOD
ColdFusion
CFUnit
Ruby
Ruby on Rails
JUnit



RSS 2.0: Full Post | Short Blurb
Subscribe by email:

Delivered by FeedBurner