CODEWITHSUNDEEP
ruby-on-railsrubyxlsx
Anand Jose
Anand Jose

Reputation: 658

WriteXLSX gem not print date format properly print as normal number format

Need to print date in xlsx file mm/dd/yyyy format, added the format using add_format method, but its printing as '2019-09-27 elabrated date

workbook = ::WriteXLSX.new(file, strings_to_urls: false)
worksheet = workbook.add_worksheet('Sheet 1')
row=column=1
date_val = Date.today
date_format = workbook.add_format({'num_format': 'dd/mm/yy'})
worksheet.write(1 + row, column, date_val, date_format)
workbook.close

then i tried write_date_time method, code sample below

workbook = ::WriteXLSX.new(file, strings_to_urls: false)
worksheet = workbook.add_worksheet('Sheet 1')
row=column=1
date_val = Date.today
date_format = workbook.add_format({'num_format': 'dd/mm/yy'})
worksheet.write_date_time(1 + row, column, item[:value]&.iso8601, date_format)
workbook.close

Upvotes: 0

Views: 429

Answers (1)

Gautam
Gautam

Reputation: 1812

The issue with what you are doing is that you are passing a date object, but if you have read the Date Time documentation then the first 2 lines says this -

  1. A date/time in Excel is a real number plus an Excel number format.
  2. WriteXLSX doesn’t automatically convert date/time strings in write() to an Excel date/time.

Now, to answer your question you need to convert the date to a number which the Excel understands.

date_time = DateTime.now.strftime('%Y-%m-%dT00:00:00.000Z')

I have added 00:00:00.000 as you are not concerned with time if you want time also then check strftime documentation

date_time_number = worksheet.convert_date_time(date_time)
format2 = workbook.add_format(:num_format => 'dd/mm/yy')
worksheet.write(row, col, number, format2)

This should solve it for you

This helped me understand how to use convert_date_time and its mention is at the bottom of the Date Time documentation

Upvotes: 2

Related Questions