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;@"
file = Dir.pwd + "/" + @YourFilenameInHere
efile = getAbsolutePathName(file)
xl ='Excel.Application')
xl.visible = true
wb = xl.Workbooks.Open("#{efile}")
ws = wb.Worksheets(1)
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;@"

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

SociBook Digg Facebook Google Yahoo Buzz StumbleUpon
Tagged with: