Hi.
I'm trying to store the current date and time in a Excel file and completely fail doing so...
Here's what I did (online available at http://askwar.pastebin.ca/1333863):
require 'spreadsheet'
Spreadsheet.client_encoding = 'UTF-8'
book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet
book.add_format Spreadsheet::Format.new(:number_format => 'DD.MM.YYYY hh:mm:ss')
row = sheet1.row(0)
row[0] = "Date.new 1975, 8, 21"
row[1] = "Time.now.utc"
row[2] = "DateTime.new 2008, 10, 12, 11, 59"
row[3] = "DateTime.civil(y=2009, m=2, d=21, h=19, min=20, s=13, of=0)"
row[4] = "Date.new"
row[5] = "DateTime.now"
row = sheet1.row(1)
row[0] = Date.new 1975, 8, 21
row.set_format 1, Spreadsheet::Format.new(:number_format => 'YYYY-MM-DD hh:mm:ss')
row[1] = Time.now.utc
row[2] = DateTime.new 2008, 10, 12, 11, 59
row[3] = DateTime.civil(y=2009, m=2, d=21, h=19, min=20, s=13, of=0)
row[4] = Date.new
row.set_format 4, Spreadsheet::Format.new(:number_format => 'YYYY-MM-DD hh:mm:ss')
row[5] = DateTime.now
book.write 'test,' + rand().to_s + '.xls'
The generated XLS file is at https://dl.getdropbox.com/u/135660/Ruby-Spreadsheet/test%2C0.919995945902332.xls .
When I open the generated XLS file in Excel 2000, it seems like there's nothing at all stored in [1,1], where the value of "Time.now.utc" should be. And in [1,5] (DateTime.now.utc), there seems to be "11.02.2009 09:00:00" stored. Which is *NOT* the current time (I ran that code at about 10:20 UTC).
I *have* read the GUIDE on http://spreadsheet.rubyforge.org/, but can't make heads nor tails of it. As you can see in my source code, I tried setting :number_format, but that didn't do anything, it seems.
Could some kind soul please post a short example of how to store the current time and date in ONE Excel field in such a way, that Excel 2000 recognizes the value as a Date/Time value?
C:\Documents and Settings\askwar\spread>ruby --version
ruby 1.8.6 (2007-09-24 patchlevel 111) [i386-mswin32]
spreadsheet 0.6.3 is used.
Thanks a lot,
Alexander
|
Start New Thread
RE: Storing current date & time as a Excel time [ reply ]