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!
9 comments:
This is useful stuff..please keep posting.
One thing I can't figure out is how to insert graphics...Is it possible to insert a bmp into a certain cell?
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.
@Anon:
Try setting the WIN32OLE.codepage attribute before writing data:
WIN32OLE.codepage = WIN32OLE::CP_UTF8
I hope that helps.
David
@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
>>WIN32OLE.codepage = WIN32OLE::CP_UTF8
>>I hope that helps.
>>David
Hi David,
It works!!
Thanks. Have a Happy New Year 2008.
Hi David,
Can we add headers and footers to our Excel documents using Ruby?
Thanks.
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
Thanks, GS!
I've just posted a new article about this.
David
Post a Comment