Can't Export UTC Date Strings #2810

New Issue

crazyjat · 2022-10-13T20:32:49Z

crazyjat commented

2022-10-13 20:32:49 +00:00

There is no option to export date strings as UTC. I want my Excel spreadsheet dates to all be UTC dates. Using aoa_to_sheet and a dateNF of "YYYY/MM/DD HH::mm::ss" will always export the date object as a local time string.

How do I export UTC date strings with aoa_to_sheet?

There is no option to export date strings as UTC. I want my Excel spreadsheet dates to all be UTC dates. Using `aoa_to_sheet` and a `dateNF` of `"YYYY/MM/DD HH::mm::ss"` will always export the date object as a local time string. How do I export UTC date strings with `aoa_to_sheet`?

sheetjs commented

2022-10-14 03:36:45 +00:00

Excel has no support for absolute time. See https://docs.sheetjs.com/docs/csf/features/dates#relative-epochs for more details.

For the described problem, it is strongly recommended to convert Date objects to strings before using aoa_to_sheet:

var new_aoa = aoa.map(row => row.map(val => val instanceof Date ? val.toISOString() : val));
var ws = XLSX.utils.aoa_to_sheet(new_aoa);

If you want that specific format, you can use native methods like getUTCDate

Excel has no support for absolute time. See https://docs.sheetjs.com/docs/csf/features/dates#relative-epochs for more details. For the described problem, it is strongly recommended to convert Date objects to strings before using aoa_to_sheet: ```js var new_aoa = aoa.map(row => row.map(val => val instanceof Date ? val.toISOString() : val)); var ws = XLSX.utils.aoa_to_sheet(new_aoa); ``` If you want that specific format, you can use native methods like `getUTCDate`

sheetjs commented

2022-10-17 19:23:21 +00:00

To close out this issue, the parts of the string can be generated as follows:

function format_date(date) {
  var YYYY = date.getUTCFullYear().toString().padStart(4, "0");
  var MM = (date.getUTCMonth()+1).toString().padStart(2, "0");
  var DD = date.getUTCDate().toString().padStart(2, "0");
  var HH = date.getUTCHours().toString().padStart(2, "0");
  var mm = date.getUTCMinutes().toString().padStart(2, "0");
  var ss = date.getUTCSeconds().toString().padStart(2, "0");
  return `${YYYY}/${MM}/${DD} ${HH}::${mm}::${ss}`;
}

var new_aoa = aoa.map(row => row.map(val => val instanceof Date ? format_date(val) : val));
var ws = XLSX.utils.aoa_to_sheet(new_aoa);

To close out this issue, the parts of the string can be generated as follows: ```js function format_date(date) { var YYYY = date.getUTCFullYear().toString().padStart(4, "0"); var MM = (date.getUTCMonth()+1).toString().padStart(2, "0"); var DD = date.getUTCDate().toString().padStart(2, "0"); var HH = date.getUTCHours().toString().padStart(2, "0"); var mm = date.getUTCMinutes().toString().padStart(2, "0"); var ss = date.getUTCSeconds().toString().padStart(2, "0"); return `${YYYY}/${MM}/${DD} ${HH}::${mm}::${ss}`; } var new_aoa = aoa.map(row => row.map(val => val instanceof Date ? format_date(val) : val)); var ws = XLSX.utils.aoa_to_sheet(new_aoa); ```

sheetjs closed this issue

2022-10-17 19:23:21 +00:00

Sign in to join this conversation.