Строка даты, которая должна рассматриваться как тип даты в Excel (экспорт данных JSON)

Question 1

У меня есть объекты JSON, содержащие динамические свойства, и я хочу экспортировать их в Excel так, чтобы Excel распознавал строку даты (2020-03-13) как объект даты (я не знаю тип никакого ключа JSON, поэтому не могу использовать тип или формат, строка даты также может включать временную метку). Когда я использую xlsx для загрузки Excel, сгенерированный Excel не распознает строку даты как тип даты Excel. Как я могу это решить с использованием библиотеки xlsx или без нее.

Я пытался использовать xlsx, но это не сработало. Также я пытался сначала создать строку tsv, а затем экспортировать ее как .xlsx, используя blob с типом mimetype файлов xlsx (но Excel не может его открыть).

Демо-код для JSON tsv в Excel

// Пример данных JSON
    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" }
    ];

    // Преобразование JSON в TSV
    function jsonToTsv(jsonData) {
        const headers = Object.keys(jsonData[0]).join("\t");  // Получить заголовки из ключей JSON
        const rows = jsonData.map(row => 
            Object.values(row).join("\t") // Объединить значения с помощью табуляции
        ).join("\n");  // Объединить строки с помощью новой строки

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

    // Загрузка TSV как файла Excel
    function downloadAsExcel(tsvData, fileName) {
        // Создание Blob для данных TSV и указание MIME типа как Excel
        const blob = new Blob([tsvData], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });

        // Создание URL для Blob
        const url = URL.createObjectURL(blob);

        // Создание элемента ссылки
        const link = document.createElement('a');
        link.href = url;
        link.download = fileName; // Имя файла для загрузки

        // Добавление ссылки в документ и вызов клика
        document.body.appendChild(link);
        link.click();

        // Удаление элемента ссылки и отзыв объекта URL
        document.body.removeChild(link);
        URL.revokeObjectURL(url);
    }

    // Событие клика по кнопке для загрузки JSON как Excel
    document.getElementById('download-btn').addEventListener('click', function () {
        // Преобразование JSON в формат TSV
        const tsvData = jsonToTsv(jsonData);

        // Преобразование TSV в формат Excel и вызов загрузки
        downloadAsExcel(tsvData, "data.xlsx");
    });

Демо-код с использованием xlsx

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="UTF-8">

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

<title>Загрузка Excel из JSON</title>

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

</head>

<body>

<button onclick="downloadExcel()">Загрузить Excel</button>

<script>

// Пример данных JSON

const jsonData = [

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

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

];

// Функция для преобразования данных JSON в Excel и вызова загрузки

function downloadExcel() {

// Создание новой книги и листа

const wb = XLSX.utils.book_new(); // Создание новой книги

const ws = XLSX.utils.json_to_sheet(jsonData); // Преобразование JSON в лист

// Добавление листа в книгу

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

// Вызов загрузки файла Excel

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

}

</body>

</html>

Question 2

“хочу экспортировать его в Excel так, чтобы Excel распознавал строку даты (2020-03-13) как объект даты” – вы имеете в виду, что хотите импортировать файл JSON в Excel?

Вы можете использовать встроенную опцию для импорта файла JSON в 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" }
  ]

Перейдите в раздел Данные, Получить данные, Из файла, Из Json, найдите файл и откройте его. Файл откроется в Power Query:

Преобразуйте его в таблицу, разверните столбцы и отформатируйте необходимый столбец как даты:

let
    Source = Json.Document(File.Contents("C:\Users\Michal\OneDrive\Documents\test.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"

Строка даты, которая должна рассматриваться как тип даты в Excel (экспорт данных JSON)

Вопрос или проблема

Ответ или решение

Использование библиотеки xlsx

Альтернативный способ без использования xlsx