preload
Importing Excel Constants values into Ruby Drawing Borders in Excel using Ruby
Nov 27

Formatting a cell in Excel for a date format using Ruby is done like so:

def dateFormat(ws, range)
   range.NumberFormat = "[$-409]dd-mmm-yy;@"
   #range.NumberFormat = "[$-409]mmm-dd-yyyy;@"
   #range.NumberFormat = "[$-409]mmm-yy;@"
end
...
file = Dir.pwd + "/" + @YourFilenameInHere
efile = getAbsolutePathName(file)
xl = WIN32OLE.new('Excel.Application')
xl.visible = true
wb = xl.Workbooks.Open("#{efile}")
ws = wb.Worksheets(1)
ws.Select
...
dateFormat(ws, ws.Range("c3"))

This number format doesn’t seem to work the way some other websites seem to suggest. If I use the Excel constant ExcelConst::XlDMYFormat, all I get is a ‘4′ in the cell. So on a lark, I called the ‘NumberFormat’ method to see if it would return what the format was:

irb(main):249:0> y = ws.Range("g8")
=> #<WIN32OLE:0x3d6c9c0>
irb(main):250:0> y.NumberFormat
=> "[$-409]dd-mmm-yy;@"
irb(main):251:0>

which is how I got the correct format value. This seems to work for both Excel 2003 and Excel 2007.

SociBook del.icio.us Digg Facebook Google Yahoo Buzz StumbleUpon

Leave a Reply