Tips for exporting time string data ("h:mm:ss") to Excel

ShinNagamine

When the DataTable has time string data (such as "h:mm:ss" format), it is not automatically converted to time data.

After struggling with the above behavior, I finally found the solution by referring to the following thread.
I would like to thank everyone who discussed and provided useful information there.

[What is the correct format to export dates with html5 to excel?]

Now I will share the source code for those who have the same problem as me.
Hope this will help.

<table id="table">
    <thead>
        <tr>
            <th>#</th>
            <th>time</th>
        </tr>
    </thead>

    <tbody>
        <tr>
            <td>1</td>
            <td>1:23:45</td>
        </tr>
        <tr>
            <td>2</td>
            <td>21:36:48</td>
        </tr>
    </tbody>
</table>

$(() => {
    // letter of time string data column
    const COL_LETTER = 'B';

    $('#table').DataTable({
        dom: 'tB',
        buttons: [{
            extend: 'excelHtml5',
            filename: 'time_string_data_export',

            // convert time string to timevalue
            exportOptions: {
                format: {
                    body: function(data, row, column, node) {
                        // index of target column
                        const colIndex = COL_LETTER.toUpperCase().charCodeAt(0) - 65;

                        if (column === colIndex) {
                            // "h:mm:ss" -> [ "h", "mm", "ss" ]
                            const parts = data.split(':');

                            return parseInt(parts[0]) / 24
                                + parseInt(parts[1]) / (24 * 60)
                                + parseInt(parts[2]) / (24 * 3600);
                        } else {
                            return data;
                        }
                    },
                }
            },

            // customize styles
            customize: function(xlsx) {
                // numbering format id for timestamp data
                //   18: h:mm AM/PM
                //   19: h:mm:ss AM/PM
                //   20: h:mm
                //   21: h:mm:ss
                // @see https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat?view=openxml-2.8.1
                const NUM_FMT_ID = 21;

                // index of new <xf> node
                const xfNodeIndex = (() => {
                    // root node of cell format nodes
                    const $cellXfs = $('cellXfs', xlsx.xl['styles.xml']);

                    // if the <xf> node does not exist
                    if ($(`xf[numFmtId="${NUM_FMT_ID}"]`, $cellXfs).length === 0) {
                        // add new <xf> node and update "count" attribute of <cellXfs> node
                        $cellXfs
                            .append(`<xf numFmtId="${NUM_FMT_ID}" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>`)
                            .attr('count', $('xf', $cellXfs).length);
                    }

                    return $(`xf[numFmtId="${NUM_FMT_ID}"]`, $cellXfs).index();
                })();

                // set and remove attributes of <c> node
                $(`row:gt(1) c[r^="${COL_LETTER.toUpperCase()}"]`, xlsx.xl.worksheets['sheet1.xml'])
                    .attr('s', xfNodeIndex)
                    .removeAttr('t');
            }
        }]
    });
});

styles.xml

<cellXfs count="69">
    ...
    <xf numFmtId="21" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1"/>
</cellXfs>

sheet1.xml

<row r="3">
    <c r="A3" s="65">
        <v>1</v>
    </c>
    <c r="B3" s="68">
        <v>0.05815972222222222</v>
    </c>
</row>
<row r="4">
    <c r="A4" s="65">
        <v>2</v>
    </c>
    <c r="B4" s="68">
        <v>0.9005555555555556</v>
    </c>
</row>

allan

The Excel creation is so painful isn't it. At some point we'll need to create an abstraction library for it so make it easier to work with. For now, many thanks for sharing your solution!

Allan