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
at

9 comments:

  1. AnonymousJune 28, 2007 at 6:59 AM

    This is useful stuff..please keep posting.

    ReplyDelete
  2. UnknownOctober 31, 2007 at 9:40 AM

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

    ReplyDelete
  3. AnonymousDecember 19, 2007 at 4:05 AM

    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.

    ReplyDelete
  4. David MulletDecember 20, 2007 at 8:43 PM

    @Anon:

    Try setting the WIN32OLE.codepage attribute before writing data:

    WIN32OLE.codepage = WIN32OLE::CP_UTF8

    I hope that helps.

    David

    ReplyDelete
  5. David MulletDecember 20, 2007 at 8:56 PM

    @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

    ReplyDelete
  6. AnonymousDecember 26, 2007 at 2:05 AM

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

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

    ReplyDelete
  7. AnonymousJanuary 8, 2008 at 2:14 AM

    Hi David,

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

    Thanks.

    ReplyDelete
  8. GSJanuary 28, 2009 at 12:49 AM

    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

    ReplyDelete
  9. David MulletJanuary 28, 2009 at 9:07 AM

    Thanks, GS!

    I've just posted a new article about this.

    David

    ReplyDelete