Sunday, June 24, 2007

Automating Excel with Ruby: Formatting Worksheets

If you're going to automate the generation of an Excel worksheet, you might as well make it look good. There are a near-infinite number of methods that can be called upon to format rows, columns, ranges, and cells. Let's take a look at some of the most common.

To format the first row of a worksheet as bold-font, set the Font.Bold value:

worksheet.Rows(1).Font.Bold = true

To format numbers in a range, set the NumberFormat value.

To format column D as Currency:

worksheet.Columns(4).NumberFormat = "$###,##0.00"

To format column A as a date (mm/dd/yy):

worksheet.Column("A").NumberFormat = "mm/dd/yy"

Alternatively, you can set the Style value:

worksheet.Column("A").Style = "Currency"
worksheet.Column("B").Style = "Percent"

To set the alignment on a range, set its HorizontalAlignment value:

worksheet.Rows(1).HorizontalAlignment = 2 # Left
worksheet.Columns("A:F").HorizontalAlignment = 4 # Right
worksheet.Cells(3, 5).HorizontalAlignment = -4108 # Center

To auto-fit the width of a column, or the height of a row, call its AutoFit method:


To set the width of a column, set its ColumnWidth value:

worksheet.Columns(4).ColumnWidth = 25.0

To set the height of a row, set its RowHeight value:

worksheet.Rows.RowHeight = 15.0

To apply highlighting to a range, set its Interior.ColorIndex value:

worksheet.Rows(10).Interior.ColorIndex = 6 # Yellow

That's all for now, but feel free to post a comment here or send me email with questions, comments, or suggested topics.

Thanks for stopping by!

Digg my article


Anonymous said...

This is useful stuff..please keep posting.

WQP said...

One thing I can't figure out is how to insert graphics...Is it possible to insert a bmp into a certain cell?

Anonymous said...

thanks for the tips.
do you have any idea how to write unicodes into excel cell using ruby 1.8.6 ?
I have tried using
$KCODE="UTF-8" and
require 'jcode', but excel displays the cell value as junk.

David Mullet said...


Try setting the WIN32OLE.codepage attribute before writing data:

WIN32OLE.codepage = WIN32OLE::CP_UTF8

I hope that helps.


David Mullet said...


To insert an image, select the cell where you want to insert the image...

worksheet.Cells(2, 2).Select

...then call the Pictures.Insert method, passing it the path to the image file:



Anonymous said...

>>WIN32OLE.codepage = WIN32OLE::CP_UTF8
>>I hope that helps.

Hi David,
It works!!
Thanks. Have a Happy New Year 2008.

Anonymous said...

Hi David,

Can we add headers and footers to our Excel documents using Ruby?


GS said...

For everyone's information: I emailed David to ask how to format the contents of cells more selectively. In my case, I wanted surnames in bold and the rest non-bold. Here's the sample code he provided to do that (put some names like "Smith, John" in cells A1-A4 and have the worksheet open and active when running this code). It's brilliant! (Forgive the formatting; can't use 'pre' or 'code' tags in the comments.)

require 'win32ole'

xl = WIN32OLE.connect('Excel.Application')
ws = xl.ActiveSheet

ws.Range('A1:A4').Cells.each do |cell|
unless cell.Value.nil?
# find the index of the first comma:
comma = cell.Value.to_s.index(',')
# get the range of characters before the comma
lastname = cell.Characters({'Start' => 1, 'Length' => comma})
# get the range of characters after the comma
firstname = cell.Characters({'Start' => comma + 1, 'Length' => cell.Value.to_s.size})
# apply formatting:
lastname.Font.Bold = true
firstname.Font.Bold = false

David Mullet said...

Thanks, GS!

I've just posted a new article about this.