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:

worksheet.Columns.AutoFit
worksheet.Rows(1).AutoFit

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

9 comments:

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.
thanks.

David Mullet said...

@Anon:

Try setting the WIN32OLE.codepage attribute before writing data:

WIN32OLE.codepage = WIN32OLE::CP_UTF8

I hope that helps.

David

David Mullet said...

@wqp:

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:

worksheet.Pictures.Insert('c:\my_image.jpg')

David

Anonymous said...

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

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?

Thanks.

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
end
end

David Mullet said...

Thanks, GS!

I've just posted a new article about this.

David