How to set a cell to have duration type? #2848

New Issue

dennisat · 2023-01-04T14:54:44Z

dennisat commented

2023-01-04 14:54:44 +00:00

Hello sheetjs.

First of all, thanks for your excellent work.

Back in time, there was clear documentation of how to set the type of cell. This was here (old documentation), well there is no such clear definition anymore. Or please provide a link.

Anyway, according to the old documentation, it is not possible to define as a type of cell the "duration" type. Do we have an improvement about this? Or any other way to it?

For people who might need help with it, duration is not time! Time in real is a date, and date is a number of milliseconds started from 1970. In comparison, duration is a number not based on a time point.

Thank you in advance.

Hello `sheetjs`. First of all, thanks for your excellent work. Back in time, there was clear documentation of how to set the type of cell. This was [here (old documentation)](https://github.com/SheetJS/sheetjs/tree/f8c0a865814246181c9b62080f8c7c928391ccb8#data-types), well there is no such clear definition anymore. Or please provide a link. Anyway, according to the old documentation, it is not possible to define as a type of cell the "duration" type. Do we have an improvement about this? Or any other way to it? > For people who might need help with it, `duration` **is not** `time`! `Time` in real is a `date`, and `date` is a `number` of milliseconds started from 1970. In comparison, `duration` is a `number` not based on a time point. Thank you in advance.

sheetjs commented

2023-01-04 15:30:47 +00:00

The linked part was moved to https://docs.sheetjs.com/docs/csf/cell#data-types

Durations are specified by using a numeric cell (type n) with a number format that includes an absolute time token ([h] or [hh] for hours, [m] or [mm] for minutes, [s] or [ss] for seconds). The cell value is interpreted as an integer number of days.

For example: https://jsfiddle.net/w8t6qgLy/

var ws = XLSX.utils.aoa_to_sheet([
  ["h",   { t:"n", z: '[h] "h"', v: 1.2345 } ],
  ["m",   { t:"n", z: '[m] "m"', v: 1.2345 } ],
  ["s",   { t:"n", z: '[s] "s"', v: 1.2345 } ],
  ["hms", { t:"n", z: '[h] "h" m "m" s "s"', v: 1.2345 } ],
]);
ws["!cols"] = [, { wch: 14 } ];
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Durations");
XLSX.writeFile(wb, "issue2848.xlsx");

Please let us know how the documentation can be improved (what should be mentioned and where it should be added).

If you are coming from LibreOffice or Numbers, note that the applications and native formats (ODS/FODS and NUMBERS respectively) support more types of durations. They do not have exact equivalents, although the parsers try to determine the correct formatted value.

PS: doing a quick search, the quoted lines never were in the documentation.

The linked part was moved to https://docs.sheetjs.com/docs/csf/cell#data-types Durations are specified by using a numeric cell (type `n`) with a number format that includes an absolute time token (`[h]` or `[hh]` for hours, `[m]` or `[mm]` for minutes, `[s]` or `[ss]` for seconds). The cell value is interpreted as an integer number of days. For example: https://jsfiddle.net/w8t6qgLy/ ```js var ws = XLSX.utils.aoa_to_sheet([ ["h", { t:"n", z: '[h] "h"', v: 1.2345 } ], ["m", { t:"n", z: '[m] "m"', v: 1.2345 } ], ["s", { t:"n", z: '[s] "s"', v: 1.2345 } ], ["hms", { t:"n", z: '[h] "h" m "m" s "s"', v: 1.2345 } ], ]); ws["!cols"] = [, { wch: 14 } ]; var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Durations"); XLSX.writeFile(wb, "issue2848.xlsx"); ``` Please let us know how the documentation can be improved (what should be mentioned and where it should be added). If you are coming from LibreOffice or Numbers, note that the applications and native formats (ODS/FODS and NUMBERS respectively) support more types of durations. They do not have exact equivalents, although the parsers try to determine the correct formatted value. PS: doing a quick search, the quoted lines never were in the documentation.

dennisat commented

2023-01-04 15:57:27 +00:00

Thanks for your super fast response! Will have a look shortly.

The quoted text is mine.

Thanks for your **super fast** response! Will have a look shortly. _The quoted text is mine._

dennisat commented

2023-01-09 07:44:51 +00:00

It works! Thanks!

dennisat closed this issue

2023-01-09 07:44:51 +00:00

Sign in to join this conversation.