function setSheetName(xlsx, name) {

    if (name.length > 0) {

        var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];

        source.setAttribute('name', name);

    }

}

​

function getHeaderNames(dt) {

    var header = dt.columns().header().toArray();

    var names = [];

    var numColumns = header.length; // Get the total number of columns

    if ($(header[numColumns - 1]).text() === 'Actions') {

        numColumns -= 1; // Reduce the count by one to ignore the last column

    }

    header.forEach(function(th, index) {

        if (index < numColumns) { // Only add names of columns that are not the last 'Actions' column

            names.push($(th).text());

        }

    });

    return names;

}

​

function getFooterNames(dt) {

    var footer = dt.columns().footer().toArray();

    var names = [];

    var numColumns = footer.length; // Get the total number of columns

    // Check if the last column's name is 'Actions'

    if ($(footer[numColumns - 1]).text() === 'Actions') {

        numColumns -= 1; // Reduce the count by one to ignore the last column

    }

    footer.forEach(function(th, index) {

        if (index < numColumns) { // Only add names of columns that are not the last 'Actions' column

            names.push($(th).text());

        }

    });

    return names;

}

​

function buildCols(data) {

    var cols = '<cols>';

    for (i = 0; i < data.length; i++) {

        colNum = i + 1;

        cols += '<col min="' + colNum + '" max="' + colNum + '" width="20" customWidth="1"/>';

    }

    cols += '</cols>';

    return cols;

}

​

function containsUnwantedFilter(value) {

    // Check if the value is an object and contains the unwanted @data-filter attribute

    if (typeof value === 'object' && value !== null) {

        return Object.values(value).some(item => containsUnwantedFilter(item)) || value['@data-filter'] === 'action8925';

    }

    // For other types, return false

    return false;

}

​

function buildRow(data, rowNum, styleNum, cellStyle = {}) {

    // Builds row XML.

    //Params:

    // data: Row data.

    // rowNum: Excel row number.

    // styleNum: style number or empty string for no style.

    //Returns:

    // String of XML formatted row.

    var style = styleNum ? ' s="' + styleNum + '"' : '';

    var row = '<row r="' + rowNum + '">';

    for (i = 0; i < data.length; i++) {

        colNum = (i + 10).toString(36).toUpperCase(); // Convert to alpha

        var cr = colNum + rowNum;

        // Override default style if style defined for cell

        if (cellStyle.hasOwnProperty(i)) {

            style = ' s="' + cellStyle[i] + '"'

        }

        row += '<c t="inlineStr" r="' + cr + '"' + style + '>' +

            '<is>' +

            '<t>' + data[i] + '</t>' +

            '</is>' +

            '</c>';

    }

    row += '</row>';

    return row;

}

​

function extractPlainText(entry) {

    // Check if the entry is an object

    if (typeof entry === 'object' && entry !== null) {

        // If it's an object, proceed with the existing logic

        let extractedText = '';

        if (entry.hasOwnProperty('display')) {

            // Extract the text from the 'display' property and remove HTML tags

            extractedText = entry.display.replace(/<[^>]*>?/gm, '');

        }

        return extractedText;

    } else {

        return entry.replace(/<[^>]*>?/gm, '');

    }

}

​

function transformData(dataArray) {

    return dataArray.map(item => {

        // Initialize an object to hold the transformed data

        let transformedItem = {};

        if (typeof item === 'object' && item !== null) {

            // If it's an object, use the 'display' property directly

            transformedItem = item.display || ""; // Use an empty string if 'display' is not available

        } else if (typeof item === 'string') {

            transformedItem = stripHtmlJS(item);

        } else {

            // For any other types (e.g., null, undefined), use an empty string

            transformedItem = "";

        }

        return transformedItem;

    });

}

​

function stripHtmlJS(html) {

    // Create a new div element

    var temporalDivElement = document.createElement("div");

    // Set the HTML content with the provided

    temporalDivElement.innerHTML = html;

    // Retrieve the text property of the element (cross-browser support)

    return temporalDivElement.textContent || temporalDivElement.innerText || "";

}

​

function getTableData(groupName, title, button, dt) {

    var dataSrc = getRow(dt);

    var header = getHeaderNames(dt);

    var rowNum = 1;

    var mergeCells = [];

    var mergeCol = ((header.length - 1) + 10).toString(36).toUpperCase();

    var ws = '';

    var selectorModifier = {};

    if (button.exportOptions.hasOwnProperty('modifier')) {

        selectorModifier = button.exportOptions.modifier;

    }

    ws += buildCols(header);

    ws += '<sheetData>';

    // Print button.title

    if (button.title.length > 0) {

        if (button.title === '*') {

            button.title = document.getElementsByTagName("title")[0].innerHTML;

        }

        ws += buildRow([button.title], rowNum, 51);

        mergeCells.push('<mergeCell ref="A' + rowNum + ':' + mergeCol + '' + rowNum + '"/>');

        rowNum++;

    }

    // Print button.messageTop

    if (button.messageTop.length > 0 && button.messageTop.length != '*') {

        ws += buildRow([button.messageTop], rowNum, 51);

        mergeCells.push('<mergeCell ref="A' + rowNum + ':' + mergeCol + '' + rowNum + '"/>');

        rowNum++;

    }

    // All rows on one page with group names separating groups

    if (Array.isArray(groupName)) {

        if (button.header) {

            ws += buildRow(header, rowNum, 2);

            rowNum++;

        }

        var currentGroup = '';

        // Loop through each row to append to sheet. 

        thisTable.rows(selectorModifier).every(function(rowIdx, tableLoop, rowLoop) {

            var _data = this.data().slice(0, header.length);

            var data = transformData(_data);

            if (data[dataSrc] !== currentGroup) {

                currentGroup = data[dataSrc];

                ws += buildRow([currentGroup], rowNum, 51);

                mergeCells.push('<mergeCell ref="A' + rowNum + ':' + mergeCol + '' + rowNum + '"/>');

                rowNum++;

            }

            // If data is object based then it needs to be converted 

            // to an array before sending to buildRow()

            ws += buildRow(data, rowNum, '');

            rowNum++;

        });

    } else {

        // Place each group on individual sheets

        if (title) {

            ws += buildRow([title], rowNum, 51);

            mergeCells.push('<mergeCell ref="A' + rowNum + ':' + mergeCol + '' + rowNum + '"/>');

            rowNum++;

        }

        if (button.header) {

            ws += buildRow(header, rowNum, 2);

            rowNum++;

        }

        // Loop through each row to append to sheet. 

        thisTable.rows(

            function(idx, data, node) {

                let displayText = data[dataSrc].display;

                const match = displayText.match(/<a[^>]*>(.*?)<\/a>/);

                if (match && match[1]) {

                    displayText = match[1].trim();

                } else {

                    displayText = displayText.trim();

                }

                if (displayText == '-') {

                    displayText = 'No Group';

                }

                return displayText === groupName ?

                    true : false;

            },

            selectorModifier

        ).every(function(rowIdx, tableLoop, rowLoop) {

            var _data = this.data().slice(0, header.length);

            var data = transformData(_data);

            // If data is object based then it needs to be converted 

            // to an array before sending to buildRow()

            ws += buildRow(data, rowNum, '');

            rowNum++;

        });

    }

    // Calculate totals for specific columns before the footer

    const totalColumns = [14, 15, 18, 19, 20, 21, 22];

    const totals = calculateColumnTotals(dt, totalColumns, groupName);

    // Build the totals row

    let totalsRow = Array(header.length).fill(''); // Fill all cells with empty string

    totalsRow[0] = groupName + ' :'; // Set the group name in the first cell

    totalColumns.forEach((colIndex, index) => {

        totalsRow[colIndex] = totals[index].toFixed(2); // Place totals in specified columns

    });

    ws += buildRow(totalsRow, rowNum, '');

    mergeCells.push('<mergeCell ref="A' + rowNum + ':N' + rowNum + '"/>'); // Merge first 14 columns

    rowNum++;

    // Output footer

    if (button.footer) {

        ws += buildRow(getFooterNames(dt), rowNum, 2);

        rowNum++;

    }

    // Print button.messageBottom

    if (button.messageBottom.length > 0 && button.messageBottom.length != '*') {

        ws += buildRow([button.messageBottom], rowNum, 51);

        mergeCells.push('<mergeCell ref="A' + rowNum + ':' + mergeCol + '' + rowNum + '"/>');

        rowNum++;

    }

    mergeCellsElement = '';

    if (mergeCells) {

        mergeCellsElement = '<mergeCells count="' + mergeCells.length + '">' +

            mergeCells +

            '</mergeCells>';

    }

    ws += '</sheetData>' + mergeCellsElement;

    return ws;

}

​

function calculateColumnTotals(dt, columns, groupName) {

    let totals = new Array(columns.length).fill(0);

    dt.rows({

        search: 'applied'

    }).every(function() {

        var data = this.data();

        var _currentGroup = data[getRow(dt)]; // Ensure this is fetching the correct group

        var currentGroup = extractPlainText(_currentGroup);

        if (currentGroup == '-') {

            currentGroup = 'No Group'

        }

        if (currentGroup === groupName) {

            columns.forEach((colIndex, i) => {

                let cleanValue = data[colIndex].toString().replace(/[^\d.-]/g, '');

                let value = parseFloat(cleanValue);

                if (!isNaN(value)) {

                    totals[i] += value;

                }

            });

        }

    });

    return totals;

}

​

function setSheetName(xlsx, name) {

    if (name.length > 0) {

        var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];

        source.setAttribute('name', name);

    }

}

​

function addSheet(xlsx, groupName, title, name, sheetId, button, dt) {

    var source = xlsx['[Content_Types].xml'].getElementsByTagName('Override')[1];

    var clone = source.cloneNode(true);

    clone.setAttribute('PartName', '/xl/worksheets/sheet' + sheetId + '.xml');

    xlsx['[Content_Types].xml'].getElementsByTagName('Types')[0].appendChild(clone);

    //Add sheet relationship to xl/_rels/workbook.xml.rels => Relationships

    //=====================================================================

    var source = xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationship')[0];

    var clone = source.cloneNode(true);

    clone.setAttribute('Id', 'rId' + sheetId + 1);

    clone.setAttribute('Target', 'worksheets/sheet' + sheetId + '.xml');

    xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationships')[0].appendChild(clone);

    //Add second sheet to xl/workbook.xml => <workbook><sheets>

    //=========================================================

    var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];

    var clone = source.cloneNode(true);

    clone.setAttribute('name', name);

    clone.setAttribute('sheetId', sheetId);

    clone.setAttribute('r:id', 'rId' + sheetId + 1);

    xlsx.xl['workbook.xml'].getElementsByTagName('sheets')[0].appendChild(clone);

    //Add sheet2.xml to xl/worksheets

    //===============================

    var newSheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +

        '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">' +

        getTableData(groupName, title, button, dt) +

        '</worksheet>';

    xlsx.xl.worksheets['sheet' + sheetId + '.xml'] = $.parseXML(newSheet);

}

​

function updateSheet1(xlsx, groupName, title, button, dt) {

    // Get number of columns to remove last hidden index column.

    var header = dt.columns().header().toArray();

    var numColumns = header.length;

    if ($(header[numColumns - 1]).text() === 'Actions') {

        numColumns -= 1;

    }

    var newSheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +

        '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">' +

        getTableData(groupName, title, button, dt) +

        '</worksheet>';

    // Get sheet.

    var sheet = xlsx.xl.worksheets['sheet1.xml'] = $.parseXML(newSheet);

    if (Array.isArray(groupName)) {

        setSheetName(xlsx, button.sheetName ? button.sheetName : document.getElementsByTagName("title")[0].innerHTML);

    } else {

        setSheetName(xlsx, groupName);

    }

}

​

function getDataSrc(dt) {

    var data = dt.rows({

        page: 'current'

    }).data().toArray();

    var preparedData = data.map(function(row) {

        var dataFilterValue = row[6]['@data-filter'];

        if (dataFilterValue.trim() === "") {

            dataFilterValue = "No Group";

        }

        return {

            dataFilterValue: dataFilterValue

        };

    });

    return preparedData;

}

​

function getRow(dt) {

    return 6;

}

var thisTable = $('#table').DataTable({

    dom: 'Bfrtip', 

    "stateDuration": 31536000,

    "stateSaveParams": function(settings, data) {

        data.search.search = "";

    },

    order: [

        [6, 'desc']

    ], 

    "createdRow": function(row, data, dataIndex) {

        $('td:eq(0)', row).html(dataIndex + 1);

    },

    rowGroup: {

        enable: false

    },

    "aoColumnDefs": [{

            "bVisible": false,

            "aTargets": 'noshow'

        },

        {

            "bSortable": false,

            "aTargets": 'nosort'

        }

    ],

    buttons: [{

            text: 'Group',

            action: function(e, dt, node, config) {

                // Check if rowGroup is already enabled

                if (dt.rowGroup().enabled()) {

                    // Disable row grouping

                    dt.rowGroup().disable();

                    dt.order.fixed({}); // Clear fixed ordering

                    node.text('Group'); // Update button text

                    $('.group-total').remove();

                } else {

                    // Enable row grouping

                    dt.rowGroup().enable();

                    dt.rowGroup().dataSrc(function(row) {

                        var cellContent = $(row[6]);

                        return cellContent.attr('@data-filter');

                    }).enable();

                    dt.order.fixed({

                        pre: [

                            [6, 'desc']

                        ]

                    });

                    node.text('Ungroup'); // Update button text

                }

                dt.draw(); // Redraw the table to apply changes

            }

        },

        {

            extend: 'excelHtml5',

            footer: true,

            exportOptions: {

                columns: [17, ':visible :not(:last-child)'],

                modifier: {

                    search: 'applied',

                }

            }

        },

        {

            extend: 'excelHtml5',

            text: 'Group Export',

            className: 'btn btn-default',

            header: true,

            title: '',

            sheetName: 'Single Sheet',

            footer: true,

            exportOptions: {

                columns: [17, ':visible :not(:last-child)'],

                modifier: {

                    search: 'applied',

                },

                format: {

                    body: function(data, row, column, node) {

                        // Check if the row has the 'group-total' class

                        if ($(node).closest('tr').hasClass('group-total')) {

                            return data; // Return data for 'group-total' rows

                        }

                        // For other rows, return data normally

                        return data;

                    }

                }

            },

            customize: function(xlsx, button, dt) {

                //console.log(button);

                multiSheet = true; // Export all groups in one sheet or multiple

                var dataSrc = getRow(dt);

                // Get unique values from rowGroup.dataSrc column

                var rawData = dt.column(dataSrc).data().toArray();

                var filteredData = rawData

                    .filter(item => item !== null) // Ensure the item exists

                    .map(item => {

                        let textContent = "";

                        if (typeof item === 'string' || item instanceof HTMLElement) {

                            const match = item.match(/<a[^>]*>(.*?)<\/a>/);

                            if (match && match[1]) {

                                textContent = match[1].trim();

                            } else {

                                if (item == '-') {

                                    textContent = 'No Group';

                                } else {

                                    textContent = item.trim();

                                }

                            }

                        } else if (typeof item === 'object' && item['@data-filter']) {

                            textContent = item['@data-filter'].trim();

                        }

                        return textContent || "No Group";

                    });

                var groupNames = [...new Set(filteredData)];

                //console.log('Groups:', groupNames);

                if (multiSheet) {

                    // Export each group in their own sheet

                    var sheetId = 1;

                    // Get row data belonging to each group

                    groupNames.forEach(function(groupName, index) {

                        if (sheetId === 1) {

                            // First sheet is already built, just update it

                            updateSheet1(xlsx, groupName, groupName, button, dt);

                        } else {

                            // Create new sheet for remaining groups

                            addSheet(xlsx, groupName, groupName, groupName, sheetId, button, dt);

                        }

                        sheetId++;

                    });

                } else {

                    // All groups shown on one page

                    updateSheet1(xlsx, groupNames, '', button, dt);

                }

            }

        },

    ],

    responsive: false,

    iDisplayLength: -1,

    "aaSorting": [],

    drawCallback: function(settings) {

        if (thisTable && typeof thisTable.on === 'function') {

            thisTable.on('column-visibility.dt', function(e, settings, column, state) {

                // Now safely attach the event listener

                thisTable.rows().invalidate().draw();

                calculateAndDisplayRowGroupTotals(thisTable);

                thisTable.api().columns.adjust().draw();

            });

        }

        $('[data-toggle="tooltip"]').tooltip(),

            $('[data-toggle="popover"]').popover()

    },

    "paging": false,

    rowGroup: {

        enable: false, // Start with row grouping disabled

        dataSrc: 6,

        endRender: function(rows, group) {

            var columnIndices = [14, 15, 17, 18, 19, 20, 21, 22]; // Adjusted indices for 0-based index

            var totals = new Array(columnIndices.length).fill(0); // Initialize totals array

            rows.data().each(function(row) {

                columnIndices.forEach(function(colIndex, index) {

                    let cValue = row[colIndex].toString().replace(/[\s,]+/g, '');

                    totals[index] += parseFloat(cValue) || 0; // Calculate totals

                });

            });

            var totalRow = $('<tr class="group-total"/>').append('<td colspan="12">Total for ' + group + ':</td>');

            totals.forEach(function(total, index) {

                // Append the total for the current column

                var columnName = thisTable.column(columnIndices[index]).header().textContent;

                if (columnName.includes('Note')) {

                    totalRow.append('<td style="text-align:center;">&nbsp;</td>');

                } else {

                    totalRow.append('<td style="text-align:right;">' + total.toFixed(2) + '</td>');

                }

            });

            // Append empty cells until the next total column or the end

            var nextColumnIndex = columnIndices[columnIndices.length - 1] + 1;

            for (var j = columnIndices[columnIndices.length - 1] + 1; j < nextColumnIndex; j++) {

                totalRow.append('<td>&nbsp;</td>');

            }

            return totalRow;

        }

    }

});

​

thisTable.on('order.dt search.dt', function() {

    thisTable.column(0, {

        search: 'applied',

        order: 'applied'

    }).nodes().each(function(cell, i) {

        cell.innerHTML = i + 1;

    });

}).draw();

​

function calculateAndDisplayRowGroupTotals(table) {

    // Assuming you have a way to identify the columns used for calculating totals

    const columnIndices = [14, 15, 17, 18, 19, 20, 21, 22]; // Adjusted indices for 0-based index

    // Iterate over each row group

    table.rowGroups().every(function(group) {

        const groupLabel = this.group();

        const groupRows = this.rows({

            visible: true

        }).nodes();

        // Initialize totals for this group

        let groupTotals = new Array(columnIndices.length).fill(0); // Initialize totals array

        // Calculate totals for each row in the group

        $(groupRows).each(function(rowIndex, tr) {

            const rowData = table.row(tr).data();

            columnIndices.forEach(function(colIndex, index) {

                groupTotals[index] += parseFloat(rowData[colIndex]) || 0; // Calculate totals

            });

        });

        // Create a summary row for this group

        const summaryRow = $('<tr class="group-total"></tr>').append(

            $('<td colspan="' + columnIndices.length + '">' + groupLabel + ': Total</td>')

        );

        // Populate the summary row with the calculated totals

        columnIndices.forEach(function(colIndex, index) {

            var columnName = table.column(colIndex).header().textContent;

            if (columnName.includes('Note')) {

                summaryRow.append('<td style="text-align:center;">&nbsp;</td>');

            } else {

                summaryRow.append('<td style="text-align:right;">' + groupTotals[index].toFixed(2) + '</td>');

            }

        });

        // Append empty cells until the next total column or the end

        var nextColumnIndex = columnIndices[columnIndices.length - 1] + 1;

        for (var j = columnIndices[columnIndices.length - 1] + 1; j < nextColumnIndex; j++) {

            summaryRow.append('<td>&nbsp;</td>');

        }

        // Insert the summary row above the group

        table.row.add(summaryRow).draw(false);

    });

}

if (thisTable.rowGroup().enabled()) {

    // Handle the row-grouping-end event for grouped rows

    thisTable.on('row-grouping-end', function(e, settings, row, rowData, rowIndex) {

        // Update the index for each row in the group

        var groupStart = rowIndex - settings.oRowGroup.bFooter;

        var groupEnd = rowIndex;

        var index = 1;

        for (var i = groupStart; i <= groupEnd; i++) {

            $(thisTable.row(i).node()).find('td:first').html(index++);

        }

    });

}

thisTable.on('draw', function() {

    if (!thisTable.rowGroup().enabled()) {

        thisTable.rows({

            page: 'current'

        }).every(function(rowIdx, tableLoop, rowLoop) {

            var node = this.node();

            $(node).find('td:first').html(rowIdx + 1);

        });

    }

});

$(window).bind('resize', function() {

    thisTable.columns.adjust().draw();

});