How do I create a date-formatted cell using RubyXL?

Question

I'm using RubyXL to dynamically generate a spreadsheet file that contains some dates. The documentation is kind of sparse so I've been looking at the source, and it appears the library only has special handling for Date and DateTime when you call change_contents, so that's what I'm doing:

cell = sheet.add_cell_obj RubyXL::Cell.new(sheet, row_index, col_index)
cell.change_contents(Time.now.to_datetime)

When I create a spreadsheet this way, Excel does not format those cells as dates. I'm guessing that I need to set some other field, perhaps cell.datatype, but I'm not sure. Or maybe I'm barking up the wrong tree. Does anybody know what to do?

score 7 · Accepted Answer · answered Dec 02 '15 at 21:14

7

See https://github.com/weshatheleopard/rubyXL/issues/210

c = workbook[0].add_cell(0,0)
c.set_number_format('m/d/yy')
c.change_contents(Date.today)

answered Dec 02 '15 at 21:14

Winston Kotzan

1,979
20
25

This doesn't work. I'm still trying to figure out how to do this. – Ricardo Jacas May 09 '18 at 13:34
@RicardoJacas that work, but you should ensure, that you are inserting `Date` object into the cell. In my case there was `TimeWithTimezone` object, so it worked only if I call `c.change_contents(value.to_date)` – Gleb Tarasov Nov 18 '18 at 17:07
Thanks @GlebTarasov, it did work at the time checking the correct type. – Ricardo Jacas Nov 19 '18 at 12:43
It works, but you have to include (from version 3.4.0) [convenience methods](https://github.com/weshatheleopard/rubyXL#convenience-methods-) – Foton Mar 22 '23 at 12:34

score -3 · Answer 2 · answered Nov 08 '12 at 19:54

-3

After finally forking the library and attempting to fix it myself, I've decided that RubyXL is a mess beyond repair. Fortunately, before descending completely into madness, I discovered Axlsx, a much better written, actively supported Ruby gem. Ditch RubyXL and use Axlsx instead.

answered Nov 08 '12 at 19:54

benzado

82,288
22
110
138

I just inherited a project which used the `writeexcel` gem. This is primarily flawed in that it does not include READING of spreadsheets. So instead, I added the `spreadsheet` gem to do this. A little while later, I needed to upgrade things and do a gem clean-up, so replaced both of these tools with `rubyXL`. The library may be a mess, but at least it finally solves all my needs (including xlsx support) in one place. Unfortunately, Axlsx would not do this, due to its lack of support for READING spreadsheets. (And in addition, it contains loads of advanced features I simply do not need.) – Tom Lord Apr 15 '16 at 10:47

How do I create a date-formatted cell using RubyXL?

2 Answers2