Unable to format date to dd-mm-yyyy while using XLSX.utils.json_to_sheet #2919

New Issue

ashwin · 2023-04-15T17:25:07Z

ashwin commented

2023-04-15 17:25:07 +00:00

This is my data

const data = [
      { date1: "2023-04-05T14:27:50.232Z" },
      { date1: "2023-04-08T14:27:50.232Z" },
    ];

I am trying to format dates(i.e. date1 ) in such a way that when I view these dates in excel they should be visible like:

But when I click to edit the cells. These dates should be like:

05-04-2023
08-04-2023

Here is the screenshot of what I want:

Here is the screenshot of what I am getting as output:

When I am trying to edit a cell I am also getting hh:mm:ss component of date (05-04-2023 00:00:10 00:00:10 is the component I do not want).

Here is my code converting date to excel:

import { saveAs } from "file-saver";
import * as XLSX from "xlsx";
const EXCEL_TYPE =
  "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
const EXCEL_EXTENSION = ".xlsx";
const data = [
      { date1: "2023-04-05T14:27:50.232Z" },
      { date1: "2023-04-08T14:27:50.232Z" },
    ];
    const ws = XLSX.utils.json_to_sheet(data);
    // I am looping through the data to change all the A cells i.e. A2 and A3. To set dates for them in the proper format I want
    data.forEach((item, index) => {
      // I am using index + 2 because index starts from 0, A1 is the main header data1(cell A1) which is not a date 
      // I need to change values A2 and A3. Loop runs twice for index 0 and 1 and we get index + 2 = 2 and 3
      //(Thus A2 and A3) for indexes 0 and 1 respectively
      const dateValue = new Date(ws[`A${index + 2}`].v);

      ws[`A${index + 2}`].t = "d";
      ws[`A${index + 2}`].v = dateValue.toLocaleDateString("en-US", {
        year: "numeric",
        month: "2-digit",
        day: "2-digit",
        hour: undefined,
        minute: undefined,
        second: undefined,
        hour12: false,
        timeZone: Intl.DateTimeFormat().resolvedOptions().timeZone,
      });
      ws[`A${index + 2}`].z = "dd-mmm-yy;@"; 
      ws[`A${index + 2}`].s = {
        numFmt: "dd-mm-yyyy;@",
        formatCode: "dd-mm-yyyy;@",
      };

  
    });
    const wb = {
      Sheets: {
        data: ws,
      },
      SheetNames: ["data"],
    };
    const eb = XLSX.write(wb, { bookType: "xlsx", type: "array" });
        const blob = new Blob([eb], { type: EXCEL_TYPE });
    saveAs(blob, "file_" + EXCEL_EXTENSION);

How can I remove time compoent from the date in the output excel sheet?

This is my data ``` const data = [ { date1: "2023-04-05T14:27:50.232Z" }, { date1: "2023-04-08T14:27:50.232Z" }, ]; ``` I am trying to format dates(i.e. date1 ) in such a way that when I view these dates in excel they should be visible like: ![image](/attachments/b4dffa33-d2e7-45d1-9112-867fb7dde54e) ![image](/attachments/5ce80338-7d41-4124-a99f-72015ee62bf9) But when I click to edit the cells. These dates should be like: 05-04-2023 08-04-2023 Here is the screenshot of what I want: ![Screenshot (1885).png](/attachments/923eacad-bbd8-4151-a1cd-08d1f6751b29) Here is the screenshot of what I am getting as output: ![Screenshot (1886).png](/attachments/4ff7e3ec-91f8-4aee-97a5-25174e84e4ac) When I am trying to edit a cell I am also getting hh:mm:ss component of date (05-04-2023 00:00:10 00:00:10 is the component I do not want). Here is my code converting date to excel: ``` import { saveAs } from "file-saver"; import * as XLSX from "xlsx"; const EXCEL_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8"; const EXCEL_EXTENSION = ".xlsx"; const data = [ { date1: "2023-04-05T14:27:50.232Z" }, { date1: "2023-04-08T14:27:50.232Z" }, ]; const ws = XLSX.utils.json_to_sheet(data); // I am looping through the data to change all the A cells i.e. A2 and A3. To set dates for them in the proper format I want data.forEach((item, index) => { // I am using index + 2 because index starts from 0, A1 is the main header data1(cell A1) which is not a date // I need to change values A2 and A3. Loop runs twice for index 0 and 1 and we get index + 2 = 2 and 3 //(Thus A2 and A3) for indexes 0 and 1 respectively const dateValue = new Date(ws[`A${index + 2}`].v); ws[`A${index + 2}`].t = "d"; ws[`A${index + 2}`].v = dateValue.toLocaleDateString("en-US", { year: "numeric", month: "2-digit", day: "2-digit", hour: undefined, minute: undefined, second: undefined, hour12: false, timeZone: Intl.DateTimeFormat().resolvedOptions().timeZone, }); ws[`A${index + 2}`].z = "dd-mmm-yy;@"; ws[`A${index + 2}`].s = { numFmt: "dd-mm-yyyy;@", formatCode: "dd-mm-yyyy;@", }; }); const wb = { Sheets: { data: ws, }, SheetNames: ["data"], }; const eb = XLSX.write(wb, { bookType: "xlsx", type: "array" }); const blob = new Blob([eb], { type: EXCEL_TYPE }); saveAs(blob, "file_" + EXCEL_EXTENSION); ``` How can I remove time compoent from the date in the output excel sheet?

image.png

2.0 KiB

image.png

2.0 KiB

image.png

2.1 KiB

image.png

2.1 KiB

Screenshot (1885).png

16 KiB

Screenshot (1886).png

20 KiB

sheetjs commented

2023-06-26 01:49:43 +00:00

Date handling has been overhauled in 0.20.0.

To generate the desired sheet: https://jsfiddle.net/wgtacevz/

var ws = XLSX.utils.aoa_to_sheet([
  [new Date("2023-04-05")],
  [new Date("2023-04-08")]
], {UTC: true, dateNF: "dd-mmm-yy"});
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "issue2919.xlsx");

If you are modifying the worksheet object directly, the dates that Excel will show in the formula bar are the dates as interpreted using the UTC methods. That means date.getUTCHours(), date.getUTCMinutes(), date.getUTCSeconds() and date.getUTCMilliseconds() must all be 0.

If you are using json_to_sheet or aoa_to_sheet, the UTC parameter controls whether to interpret the dates using UTC methods or using local methods.

https://docs.sheetjs.com/docs/csf/features/dates explains in more detail.

Date handling has been overhauled in 0.20.0. To generate the desired sheet: https://jsfiddle.net/wgtacevz/ ```js var ws = XLSX.utils.aoa_to_sheet([ [new Date("2023-04-05")], [new Date("2023-04-08")] ], {UTC: true, dateNF: "dd-mmm-yy"}); var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "issue2919.xlsx"); ``` If you are modifying the worksheet object directly, the dates that Excel will show in the formula bar are the dates **as interpreted using the UTC methods**. That means `date.getUTCHours()`, `date.getUTCMinutes()`, `date.getUTCSeconds()` and `date.getUTCMilliseconds()` must all be `0`. If you are using `json_to_sheet` or `aoa_to_sheet`, the `UTC` parameter controls whether to interpret the dates using UTC methods or using local methods. https://docs.sheetjs.com/docs/csf/features/dates explains in more detail.

sheetjs closed this issue

2023-06-26 01:49:43 +00:00

Sign in to join this conversation.