Javascript Question posted in
A very good W3school tutorial can be found here.

Date string to considered as date type of excel(exporting json data using javascript)

I have a json objects containing dynamic properties and want to export it to excel(using javascript) such that excel recognizes date string(2020-03-13) as date object(I don’t know type of any json key, so I can’t use type neither format, date string can also have time stamp).
When I am using xlsx to download excel, generated excel doesn’t recognize date string as date type of excel.
How can I resolve this with using xlsx library or without in javascript.

I tried using xlsx, it didn’t work and date was general type in excel.
And also tried to first create tsv string then export it as .xlsx using blob with mimetype of xlsx files.(but excel not able to open it)

Demo code for json tsv to excel

// Sample JSON data
    const jsonData = [
        { "name": "John Doe", "age": 28, "date": "2023-10-08" },
        { "name": "Jane Smith", "age": 32, "date": "2024-03-15" },
        { "name": "Alice Johnson", "age": 45, "date": "2024-01-20" }
    ];

    // Convert JSON to TSV
    function jsonToTsv(jsonData) {
        const headers = Object.keys(jsonData[0]).join("t");  // Get headers from JSON keys
        const rows = jsonData.map(row => 
            Object.values(row).join("t") // Join values with tabs
        ).join("n");  // Join rows with newline

        return `${headers}n${rows}`;
    }

    // Download the TSV as an Excel file
    function downloadAsExcel(tsvData, fileName) {
        // Create a Blob for the TSV data and specify the MIME type as Excel
        const blob = new Blob([tsvData], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });

        // Create a URL for the Blob
        const url = URL.createObjectURL(blob);

        // Create a link element
        const link = document.createElement('a');
        link.href = url;
        link.download = fileName; // File name for download

        // Append the link to the document and trigger click
        document.body.appendChild(link);
        link.click();

        // Remove the link element and revoke the object URL
        document.body.removeChild(link);
        URL.revokeObjectURL(url);
    }

    // Button click event to download JSON as Excel
    document.getElementById('download-btn').addEventListener('click', function () {
        // Convert JSON to TSV format
        const tsvData = jsonToTsv(jsonData);

        // Convert TSV to Excel format and trigger download
        downloadAsExcel(tsvData, "data.xlsx");
    });

Demo code by using xlsx:

<!DOCTYPE html>

<html lang="en">

<head>

   <meta charset="UTF-8">

    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Download Excel from JSON</title>

    <!-- Add xlsx script from CDN -->

    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>`

</head>

<body>

    <button onclick="downloadExcel()">Download Excel</button>

    <script>`

        // Sample JSON Data

        const jsonData = [

            { "name": "John Doe", "age": "28", "birthdate": "2023-10-08" },

            { "name": "Jane Smith", "age": "32", "birthdate": "2024-03-15" }

        ];

        // Function to Convert JSON Data to Excel and Trigger Download`

        function downloadExcel() {

            // Create a new workbook and worksheet

            const wb = XLSX.utils.book_new();  // Create a new workbook

            const ws = XLSX.utils.json_to_sheet(jsonData);  // Convert JSON to worksheet

            // Append worksheet to workbook

            XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

`            // Trigger a download of the Excel file`

            XLSX.writeFile(wb, 'data.xlsx');`

        }



</body>

</html>
Tags:

2

Answers


  1. 0

    "want to export it to excel such that excel recognizes date string(2020-03-13) as date object" – do you mean that you want to import a json file to Excel?

    You can just use the built-in option to import json file to Power Query.

     [
        { "name": "John Doe", "age": 28, "date": "2023-10-08" },
        { "name": "Jane Smith", "age": 32, "date": "2024-03-15" },
        { "name": "Alice Johnson", "age": 45, "date": "2024-01-20" }
  ]

    Go to Data, Get data, From file, From Json, navigate to the file and open it. The file is opened in Power Query:

    enter image description here

    Convert it to table, expand columns and format the required column as dates:

    let
    Source = Json.Document(File.Contents("C:UsersMichalOneDriveDocumentstest.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "age", "date"}, {"Column1.name", "Column1.age", "Column1.date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1.date", type date}})
in
    #"Changed Type"

    enter image description here

    Login or Signup to reply.
  2. 0

    Excel recognizes the data type when you enter it manually. In the programmatic way, the sheet content is highly dependent on the method json_to_sheet. If it writes a date as the text, Excel displays a text.

    In this case, the best way to get the result is to use the DATEVALUE function in Excel.

    enter image description here

    Or you can use the custom ‘json_to_sheet’ method, or use another library to work with Excel files.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search