Exporting datatable to excel using {data: null}

jrowan20

Hi All,

I am populating columns based upon calculations made from other columns, with no actual data source under the hood for the calculated columns. The problem I have is that when trying to export the data to excel, none of the calculated columns contain the correct data, they are being ignored. So I guess what im doing is updating the text of the column but not the actual object.

Is there a way to update the object as well as the display without a on click event. I am far too deep into the code to turn back now

Below is a snippet from the code I am using and the functions that generate the data

const otherBankHolCount = (data, type, row) => {
    let count = 0;
    let hoursCount = 0
    var other = row["other"]
    var result = dataTable.rows().data().toArray();
    let nightShift = moment(data.otherEnd).format('DD-MM-YYYY')
    let nightHours = moment(data.otherEnd).diff(moment(data.shiftDate).endOf('day'), 'hours') * 60
    let startDate = moment().startOf('week')
    let endDate = moment().endOf('week')
    let bank = moment('09-04-2022', 'DD-MM-YYYY').format('DD-MM-YYYY')
    let nightCount = count - nightHours * -1

    result.map(item => {


        if (moment(data.shiftDate).format('DD-MM-YYYY') == bank && data.other > 0 && item.employeeId === data.employeeId && data.other > 0 && (count += item.voluntary) && (count += item.basic) && (count += item.extendedHours) && (count += item.other) > 2250) {
            let otherRemainder = count - 2250 > other ? other : count - 2250
            hoursCount = otherRemainder

        }
        else if (nightShift == bank && nightHours > 0 && item.employeeId === data.employeeId && (count += item.voluntary) && (count += item.basic) && (count += item.extendedHours) && (count += item.other) > 2250) {

            let bhCount = count - 2250
            let countCheck = count - nightCount
            hoursCount = countCheck > 2250 ? nightCount : bhCount

        }

        else { hoursCount = 0 }

        var hours = Number(hoursCount / 60).toFixed(2)
        return hours

    })

    var hours = Number(hoursCount / 60).toFixed(2)
    return hours
}columns: [
 { "data": null }
]

kthorngren

Its hard to say from your code snippet but I built this simple example showing that using columns.render with a data: null column is exported to Excel.
http://live.datatables.net/fikeyuqo/1/edit

If this doesn't help please provide a link to your page or a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

jrowan20

Hey Kevin thank you for the quick response. I will complete a test case in the morning if thats ok and get back to you

Just a quick idea, my table data looks like the below and this is how i want my export looking

but my export is as follows