Question 199398 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – how can i delete column with sum = 0 in excelhtml5 export

i use this code to export my table to excel in datatable-django.

$(document).ready(function() {

$('#example').DataTable( {

    dom: 'Bfrtip',

    buttons:

    [{extend: 'excelHtml5', text: 'export'}]

} );

} );

I have a table with 72 columns. I only want to export the columns to Excel whose cells sum is not zero. How can I do this?

i want just show the columns with sum>0

Tags:

2

Answers


  1. 0

    You can modify the export options by extending the excelHtml5 button and customizing the resulting Excel file. please find example below that shows how you can achieve this:

    $(document).ready(function() {
$('#example').DataTable({
    dom: 'Bfrtip',
    buttons: [
        {
            extend: 'excelHtml5',
            text: 'Export',
            customize: function(xlsx) {
                var sheet = xlsx.xl.worksheets['sheet1.xml']; // Modify 'sheet1.xml' according to your sheet name
                
                // Retrieve the table data
                var tableData = $('#example').DataTable().data().toArray();
                
                // Calculate the sum of each column
                var columnSums = tableData.reduce(function(acc, row) {
                    row.forEach(function(cell, columnIndex) {
                        if (!acc[columnIndex]) {
                            acc[columnIndex] = 0;
                        }
                        acc[columnIndex] += parseFloat(cell) || 0;
                    });
                    return acc;
                }, []);
                
                // Identify columns with a sum of 0
                var columnsToDelete = columnSums.reduce(function(acc, sum, columnIndex) {
                    if (sum === 0) {
                        acc.push(columnIndex + 1); // Adjust the index since Excel uses 1-based indexing
                    }
                    return acc;
                }, []);
                
                // Loop through each row to delete columns with a sum of 0
                $('row', sheet).each(function() {
                    var cells = $('c', this);
                    
                    for (var i = columnsToDelete.length - 1; i >= 0; i--) {
                        var columnIndex = columnsToDelete[i];
                        $('c[r^="' + columnIndex + '"]', this).remove();
                    }
                });
            }
        }
    ]
});
});

    Please note that this solution assumes you have the necessary JavaScript and CSS files for the DataTable library and that you have initialized your DataTable with the appropriate options.

    Login or Signup to reply.
  2. 0

    Use customizedData

    You can use customizedData callback function

    
customizeData: function(data) {
        var table = $('#example').DataTable();
        var columns = table.columns().data();
        var footer = table.columns().footer();

        var filteredColumns = [];
        for (var i = 0; i < columns.length; i++) {
          var columnData = columns[i].toArray();
          var columnSum = columnData.reduce(function(a, b) {
            return a + b;
          }, 0);

          if (columnSum > 0) {
            filteredColumns.push(columnData);
          }
        }

        data.body = filteredColumns;
        data.footer = footer;
        return data;
      }

    Explanation

    We retrieve the column data using table.columns().data() and then calculate the sum of each column, filtering out the columns whose sum is not greater than zero.
    After that we assign these filtered columns to data.body to replace the original data for export.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search