Error writing back time with 1/0/1900 date

[nidu]

Hello. Found an interesting issue. If you enter the into Excel cell time only (e.g. "3:10") - it will be converted to "1/0/1900 3:10:00 AM" (Long date format). If you read it with read-cell - you'll get "1899-12-31T21:10:00.000-00:00" (due to my timezone) date in Clojure (ha, silly Clojure doesn't know about Jan 0). So when you write it back - it writes just -1 into the cell (probably because Excel can't handle dates before 1900).

So when just reading a value and writing it back you lose some data - looks bad to me.

You could easily reproduce it by creating Excel workbook with top-left cell value "3:10" (it will be converted to date-time), then execute following:

(let [fname "path_to_file.xlsx"
      wb (load-workbook fname)
      sh (.getSheetAt wb 0)
      c-in (.. sh (getRow 0) (getCell 0))]
  (add-row! sh [(read-cell c-in)])
  (save-workbook! fname wb))

What do you think about it?

P.S.: Jan 0 is event mentioned here.

[mjul]

Thanks for spotting this.
I agree Docjure should be able to round-trip the read-write, also in the edge cases.
Is this a general time-zone problem or just in at the 1900-limit?

[nidu]

It seems that problem occurs only at the 1900-limit when Java date is below minimum available Excel date. Taking into account that Excel max date is December 31, 9999 - i'm not sure this use case is viable. Don't you think this problem should be resolved on POI level?

[mjul]

Please try to raise it to POI. Until it is fixed perhaps we could add a test showing that POI has this problem so it breaks when they fix it, and in the interim throw an exception if we hit the edge case date that will cause problems to our users.