Wednesday, 2 June 2021

Why jQuery data tables not export date correctly when month is two digit in laravel when export as EXCEL file?

I want to get export data as an EXCEL file exactly as shown in the view, it works, but did not export date correctly, I tried like below:

 $('#get_hourly_report').click(function () {

        var id = $('#teacher').val();
        var first_date = $('#jalali-datepicker3').val();
        var second_date = $('#jalali-datepicker4').val();
        $(".h_report_icon").addClass('fa fa-spinner fa-spin');
        $.ajax({
            method: "get",
            url: "attendance/getTeacherAttendanceReport/",
            data: {
                'id': id,
                'f_date': first_date,
                's_date': second_date
            },
            success: function (data) {
                $.fn.dataTable.moment('DD/MM/YYYY');
                table = $('#example2').DataTable({

                    destroy: true,
                    "bPaginate": false,
                    dom: 'Bfrtip',
                    bFilter: false,
                    buttons: [
                        {
                            extend: 'excelHtml5',
                            footer: true,
                            customize: (xlsx, config, dataTable) => {
                                let sheet = xlsx.xl.worksheets['sheet1.xml'];
                                let footerIndex = $('sheetData row', sheet).length;
                                let $footerRows = $('tr', dataTable.footer());

                                // If there are more than one footer rows
                                if ($footerRows.length > 1) {
                                    // First header row is already present, so we start from the second row (i = 1)
                                    for (let i = 1; i < $footerRows.length; i++) {
                                        // Get the current footer row
                                        let $footerRow = $footerRows[i];

                                        // Get footer row columns
                                        let $footerRowCols = $('th', $footerRow);

                                        // Increment the last row index
                                        footerIndex++;

                                        // Create the new header row XML using footerIndex and append it at sheetData
                                        $('sheetData', sheet).append(`
                                                    <row r="${footerIndex}">
                                                      ${$footerRowCols.map((index, el) => `
                                                        <c t="inlineStr" r="${String.fromCharCode(65 + index)}${footerIndex}" s="2">
                                                          <is>
                                                            <t xml:space="preserve">${$(el).text()}</t>
                                                          </is>
                                                        </c>
                                                      `).get().join('')}
                                                    </row>
                                                  `);
                                    }
                                }
                            }
                        }
                    ],
                    "processing": true,
                    data: data.teacherAttendanceReport,
                    columns: [
                        {
                            title: 'شماره', render: function (data, type, row, meta) {
                                return meta.row + meta.settings._iDisplayStart + 1;
                            }
                        },
                        {title: 'نام کارمند', data: 'first_name'},
                        {title: 'روز هفته', data: 'persian_name'},
                        {title: 'ماه', data: 'month_name'},
                        {title: 'تاریخ مکمل', data: 'date'},
                        {title: 'ساعت درسی', data: 'full_time'},
                        {title: 'حالت حاضری', data: 'as_persian_name'},

                    ],


                });
                $(".h_report_icon").removeClass('fa fa-spinner fa-spin');
                $('#h_present').html(data.present)
                $('#h_absent').html(data.absent)


            },
            error: function () {

            }
        });
    });

When I take export as an EXCEL file, if the date is like 1400-2-15 the result is as expected. but if the date is like 1400-02-13 then it gets export like ######.

I want it to be like 1400-02-13, If anyone knows where is the problem, please help me!



from Why jQuery data tables not export date correctly when month is two digit in laravel when export as EXCEL file?

No comments:

Post a Comment