Dates not read from time values #1381

New Issue

2018-12-10T06:02:09Z

dandv commented

2018-12-10 06:02:09 +00:00

(Migrated from github.com)

Parsing the following content from an ODS file doesn't convert the time-values to dates, even though cellDates is true:

<table:table-cell office:value-type="time" office:time-value="PT1042374H09M57.999999393S" calcext:value-type="time">
  <text:p>06:09:57</text:p>
</table:table-cell>

The code is:

const XLSX = require('xlsx');

const wb = XLSX.readFile('bug-date-formatted-as-time-only.ods', {
  cellDates: true,
});

const ws = wb.Sheets[wb.SheetNames[0]];

console.log(ws.A1);
console.log(ws.A2);

The first cell is parsed as type n:

{ t: 'n', v: 43432.25690972222, w: '06:09:57' }

Parsing the following content [from an ODS file](http://dandascalescu.com/static/bug-date-formatted-as-time-only.ods) doesn't convert the `time-value`s to dates, even though `cellDates` is true: ```xml <table:table-cell office:value-type="time" office:time-value="PT1042374H09M57.999999393S" calcext:value-type="time"> <text:p>06:09:57</text:p> </table:table-cell> ``` The code is: ```js const XLSX = require('xlsx'); const wb = XLSX.readFile('bug-date-formatted-as-time-only.ods', { cellDates: true, }); const ws = wb.Sheets[wb.SheetNames[0]]; console.log(ws.A1); console.log(ws.A2); ``` The first cell is parsed as type `n`: > { t: 'n', v: 43432.25690972222, w: '06:09:57' }

SheetJSDev commented

2021-09-15 20:56:58 +00:00

(Migrated from github.com)

https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part3-schema/OpenDocument-v1.3-os-part3-schema.html#attribute-office_value-type

time values are intended to be a duration (like 43432 days 6 hours 9 minutes 57 seconds), not representing a moment in time. That said, we'd accept a PR to do the conversion: https://github.com/SheetJS/sheetjs/blob/master/bits/80_parseods.js#L172

						case 'time': q.t = 'n'; q.v = parse_isodur(ctag['time-value'])/86400;
							if(opts.cellDates) { q.t = 'd'; q.v = numdate(q.v); }
							q.z = 'HH:MM:SS'; break;

https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part3-schema/OpenDocument-v1.3-os-part3-schema.html#attribute-office_value-type `time` values are intended to be a duration (like 43432 days 6 hours 9 minutes 57 seconds), not representing a moment in time. That said, we'd accept a PR to do the conversion: https://github.com/SheetJS/sheetjs/blob/master/bits/80_parseods.js#L172 ```js case 'time': q.t = 'n'; q.v = parse_isodur(ctag['time-value'])/86400; if(opts.cellDates) { q.t = 'd'; q.v = numdate(q.v); } q.z = 'HH:MM:SS'; break; ```

Sign in to join this conversation.