how to merge matching values of column in jQuery datatables excelHtml5

Question

when i export excel from my datatable iam not able to merge col value. since the values of column B named Category. the duplicate values needed to be merged . i'am new to programming any help appreciated

this my my jsfiddle

this my code

<table id="example1" class="table table-sm cell-border compact stripe table-bordered table-hover dataTable dtr-inline"
    cellspacing="0" width="100%" aria-describedby="example1_info">


    <thead>
        <tr>
            <th class="d-none sorting_disabled" rowspan="1" colspan="1">Date</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Category</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Item Name</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Price</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Opening</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Recived</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Total</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Closing</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Sales</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Total</th>
        </tr>
    </thead>

    <tbody>

        <tr data-row-id="1761" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Golden Touch Brandy 180ml">Golden Touch Brandy 180ml</td>
            <td class="editable-col" col-index="2" oldval="250.00">250.00</td>
            <td class="editable-col" col-index="3" oldval="9.00">9.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="9">9</td>
            <td class="editable-col" col-index="6" oldval="9.00">9.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1763" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Golden Touch Brandy 750ml">Golden Touch Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="1000.00">1000.00</td>
            <td class="editable-col" col-index="3" oldval="3.00">3.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="3">3</td>
            <td class="editable-col" col-index="6" oldval="3.00">3.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
    
        <tr data-row-id="1948" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Whytehall Brandy 180ml">Whytehall Brandy 180ml</td>
            <td class="editable-col" col-index="2" oldval="250.00">250.00</td>
            <td class="editable-col" col-index="3" oldval="18.00">18.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="18">18</td>
            <td class="editable-col" col-index="6" oldval="18.00">18.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1949" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Whytehall Brandy 750ml">Whytehall Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="1000.00">1000.00</td>
            <td class="editable-col" col-index="3" oldval="4.00">4.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="4">4</td>
            <td class="editable-col" col-index="6" oldval="4.00">4.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1992" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Roulette Brandy 750ml">Roulette Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="2230.00">2230.00</td>
            <td class="editable-col" col-index="3" oldval="1.00">1.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="1">1</td>
            <td class="editable-col" col-index="6" oldval="1.00">1.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="2048" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Bejois VSOP 375ML">Bejois VSOP 375ML</td>
            <td class="editable-col" col-index="2" oldval="200.00">200.00</td>
            <td class="editable-col" col-index="3" oldval="20.00">20.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="20">20</td>
            <td class="editable-col" col-index="6" oldval="20.00">20.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
  
    </tbody>


    <tfoot>

        <tr>
            <th class="d-none" rowspan="1" colspan="1">0</th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"> 1900</th>
            <th rowspan="1" colspan="1"> 9</th>
            <th rowspan="1" colspan="1"> 1909</th>
            <th rowspan="1" colspan="1"> 1909</th>
            <th rowspan="1" colspan="1"> 0</th>
            <th rowspan="1" colspan="1"> 0</th>
        </tr>
    </tfoot>


 
</table>

my javascript

 $(document).ready(function () {

        document.title = 'shop';
        $('#example1').DataTable({
            "processing": true,
            "dom": 'Bfrtip',
            "lengthChange": false,
            "searching": false,
            "info": true,
            "autoWidth": false,
            "responsive": true,
            "retrieve": true,
            "paging": false,
            "lengthMenu": [
                [-1],
                ["All"]
            ],
            "bSort": false,
            "bLengthChange": false,

            "buttons": ["copy", {
                    extend: 'excelHtml5',
                    footer: true,
                    text: 'Save as Excel',
                    pageSize: 'A4',
                      customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        $('c[r=C1] t', sheet).text('Sudha Wines');
                        $('row:first c', sheet).attr('s', '32');

                        var col = $('col', sheet);
                        $(col[0]).attr('width', 8);
                        $(col[1]).attr('width', 24);
                        $(col[2]).attr('width', 9);
                        $(col[3]).attr('width', 8);
                        $(col[4]).attr('width', 7);
                        $(col[5]).attr('width', 8);
                        $('row* ', sheet).each(function (index) {
                            if (index > 0) {
                                $(this).attr('ht', 26);
                                $(this).attr('customHeight', 1);
                            }
                        });






                    },
                    
                  
                }
            ],


        });
    });

output i'am getting in MS excel

Desired output i want is

score 1 · Accepted Answer · answered Aug 07 '22 at 18:17

The OpenXML spreadsheet format used by Excel specifies merged ranges as follows:

<mergeCells  count="2">
    <mergeCell ref="B2:B5"/>
    <mergeCell ref="B6:B7"/>
</mergeCells>

The above represents 2 merged ranges in a worksheet.

We therefore need to add logic to scan the data in your "Category" column, to determine what ranges we need to create. Once we have these ranges we can build the above Excel XML fragment and insert it into the worksheet, during the export process.

You already have your customize: function (xlsx) { ... }, so we can add our logic there:

var ranges = buildRanges(sheet);
                        
// build the HTML string:
var mergeCellsHtml = '<mergeCells count="' + ranges.length + '">';
ranges.forEach(function(range) {
    mergeCellsHtml = mergeCellsHtml + '<mergeCell ref="' + range + '"/>';
})
mergeCellsHtml = mergeCellsHtml + '</mergeCells>';

$( 'sheetData', sheet ).after( mergeCellsHtml );
// don't know why, but Excel auto-adds an extra mergeCells tag, so remove it:
$( 'mergeCells', sheet ).last().remove();

The work of building the ranges is in the buildRanges(sheet) function. I expect this code could be streamlined/improved, but it shows the overall approach:

function buildRanges(sheet) {

  let prevCat = ''; // previous category
  let currCat = ''; // current category
  let currCellRef = ''; // current cell reference
  let rows = $('row', sheet);
  let startRange = '';
  let endRange = '';
  let ranges = [];

  rows.each(function (i) {
    if (i > 0 && i < rows.length) { // skip first (headings) row
      let cols = $('c', $(this));
      cols.each(function (j) {
        if (j == 1) { // the "Category" column
          currCat = $(this).text(); // current row's category
          currCellRef = $(this).attr('r'); // e.g. "B3"
          if (currCat !== prevCat) {
            if (i == 1) {
              // start of first range
              startRange = currCellRef;
              endRange = currCellRef;
              prevCat = currCat;
            } else {
              // end of previous range
              if (endRange !== startRange) {
                // capture the range:
                ranges.push( startRange + ':' + endRange );
              }
               // start of a new range
              startRange = currCellRef;
              endRange = currCellRef;
              prevCat = currCat;
            }
          } else {
            // extend the current range end:
            endRange = currCellRef;
          }
          //console.log( $(this).attr('r') );
        }
      });
      if (i == rows.length -1 && endRange !== startRange) {
        // capture the final range:
        ranges.push( startRange + ':' + endRange );
      }
    }
  });
  return ranges;
}

This function scans your categories column looking for when the value in that column changes.

It assumes the values are already sorted/grouped, to support this.

One Important Note

I added the following line to your buttons logic:

title: '', // no title row in excel sheet

This ensures that the spreadsheet does not already have any merged-cell ranges created. If you want this feature, then my logic would need to be adjusted, as there would already be a <mergeCells> tag in the spreadsheet (in the first row, containing the title).

The end result looks something like this:

Here are all the pieces in one demo (not sure if you can actually run the Excel download from within a Stack Snippet):

  $(document).ready(function () {

        document.title = 'shop';
        $('#example1').DataTable({
            "processing": true,
            "dom": 'Bfrtip',
            "lengthChange": false,
            "searching": false,
            "info": true,
            "autoWidth": false,
            "responsive": true,
            "retrieve": true,
            "paging": false,
            "lengthMenu": [
                [-1],
                ["All"]
            ],
            "bSort": false,
            "bLengthChange": false,

            "buttons": ["copy", {
                    extend: 'excelHtml5',
                    footer: true,
                    text: 'Save as Excel',
                    title: '', // no title row in excel sheet
                    pageSize: 'A4',
                      customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        
                        $('row:first c', sheet).attr('s', '32');

                        var col = $('col', sheet);
                        $(col[0]).attr('width', 8);
                        $(col[1]).attr('width', 24);
                        $(col[2]).attr('width', 9);
                        $(col[3]).attr('width', 8);
                        $(col[4]).attr('width', 7);
                        $(col[5]).attr('width', 8);
                        $('row* ', sheet).each(function (index) {
                            if (index > 0) {
                                $(this).attr('ht', 26);
                                $(this).attr('customHeight', 1);
                            }
                        });

                        var ranges = buildRanges(sheet);
                        
                        // build the HTML string:
                        var mergeCellsHtml = '<mergeCells count="' + ranges.length + '">';
                        ranges.forEach(function(range) {
                            mergeCellsHtml = mergeCellsHtml + '<mergeCell ref="' + range + '"/>';
                        })
                        mergeCellsHtml = mergeCellsHtml + '</mergeCells>';

                        $( 'sheetData', sheet ).after( mergeCellsHtml );
                        // don't know why, but Excel auto-adds an extra mergeCells tag, so remove it:
                        $( 'mergeCells', sheet ).last().remove(); 



                    }
                    
                  
                }
            ]


        });
    });
    
function buildRanges(sheet) {

  let prevCat = ''; // previous category
  let currCat = ''; // current category
  let currCellRef = ''; // current cell reference
  let rows = $('row', sheet);
  let startRange = '';
  let endRange = '';
  let ranges = [];

  rows.each(function (i) {
    if (i > 0 && i < rows.length) { // skip first (headings) row
      let cols = $('c', $(this));
      cols.each(function (j) {
        if (j == 1) { // the "Category" column
          currCat = $(this).text(); // current row's category
          currCellRef = $(this).attr('r'); // e.g. "B3"
          if (currCat !== prevCat) {
            if (i == 1) {
              // start of first range
              startRange = currCellRef;
              endRange = currCellRef;
              prevCat = currCat;
            } else {
              // end of previous range
              if (endRange !== startRange) {
                // capture the range:
                ranges.push( startRange + ':' + endRange );
              }
               // start of a new range
              startRange = currCellRef;
              endRange = currCellRef;
              prevCat = currCat;
            }
          } else {
            // extend the current range end:
            endRange = currCellRef;
          }
          //console.log( $(this).attr('r') );
        }
      });
      if (i == rows.length -1 && endRange !== startRange) {
        // capture the final range:
        ranges.push( startRange + ':' + endRange );
      }
    }
  });
  return ranges;
}

<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>

  <script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.min.css"/>

  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.5/css/buttons.dataTables.min.css"/> 
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/dataTables.buttons.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.colVis.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.flash.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.html5.min.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.print.min.js"></script>

</head>

<body>

<div style="margin: 20px;">

    <table id="example1" class="table table-sm cell-border compact stripe table-bordered table-hover dataTable dtr-inline"
    cellspacing="0" width="100%" aria-describedby="example1_info">


    <thead>
        <tr>
            <th class="d-none sorting_disabled" rowspan="1" colspan="1">Date</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Category</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Item Name</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Price</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Opening</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Recived</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Total</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Closing</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Sales</th>
            <th class="sorting_disabled" rowspan="1" colspan="1">Total</th>
        </tr>
    </thead>

    <tbody>

        <tr data-row-id="1761" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Golden Touch Brandy 180ml">Golden Touch Brandy 180ml</td>
            <td class="editable-col" col-index="2" oldval="250.00">250.00</td>
            <td class="editable-col" col-index="3" oldval="9.00">9.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="9">9</td>
            <td class="editable-col" col-index="6" oldval="9.00">9.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1763" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Golden Touch Brandy 750ml">Golden Touch Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="1000.00">1000.00</td>
            <td class="editable-col" col-index="3" oldval="3.00">3.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="3">3</td>
            <td class="editable-col" col-index="6" oldval="3.00">3.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
    
        <tr data-row-id="1948" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Whytehall Brandy 180ml">Whytehall Brandy 180ml</td>
            <td class="editable-col" col-index="2" oldval="250.00">250.00</td>
            <td class="editable-col" col-index="3" oldval="18.00">18.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="18">18</td>
            <td class="editable-col" col-index="6" oldval="18.00">18.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1949" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
            <td class="editable-col" col-index="2" oldval="Whytehall Brandy 750ml">Whytehall Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="1000.00">1000.00</td>
            <td class="editable-col" col-index="3" oldval="4.00">4.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="4">4</td>
            <td class="editable-col" col-index="6" oldval="4.00">4.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="1992" class="odd">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Port</th>
            <td class="editable-col" col-index="2" oldval="Roulette Brandy 750ml">Roulette Brandy 750ml</td>
            <td class="editable-col" col-index="2" oldval="2230.00">2230.00</td>
            <td class="editable-col" col-index="3" oldval="1.00">1.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="1">1</td>
            <td class="editable-col" col-index="6" oldval="1.00">1.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
        <tr data-row-id="2048" class="even">
            <td class="editable-col d-none" col-index="0" oldval="2022-08-07">2022-08-07</td>
            <th class="editable-col dtr-control" col-index="1" oldval="Brandy" tabindex="0">Port</th>
            <td class="editable-col" col-index="2" oldval="Bejois VSOP 375ML">Bejois VSOP 375ML</td>
            <td class="editable-col" col-index="2" oldval="200.00">200.00</td>
            <td class="editable-col" col-index="3" oldval="20.00">20.00</td>
            <td class="editable-col" col-index="4" oldval=""></td>
            <td class="editable-col" col-index="5" oldval="20">20</td>
            <td class="editable-col" col-index="6" oldval="20.00">20.00</td>
            <td class="editable-col" col-index="7" oldval="0">0</td>
            <td class="editable-col" col-index="8" oldval="0">0</td>
        </tr>
  
    </tbody>


    <tfoot>

        <tr>
            <th class="d-none" rowspan="1" colspan="1">0</th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"></th>
            <th rowspan="1" colspan="1"> 1900</th>
            <th rowspan="1" colspan="1"> 9</th>
            <th rowspan="1" colspan="1"> 1909</th>
            <th rowspan="1" colspan="1"> 1909</th>
            <th rowspan="1" colspan="1"> 0</th>
            <th rowspan="1" colspan="1"> 0</th>
        </tr>
    </tfoot>


 
</table>

</div>



</body>
</html>

do you have any solutions to his question also any help appreciated https://stackoverflow.com/q/73353748/18209690 — gopal, Aug 14 '22 at 17:41
sorry i have a questiion about this ,, can i merge cell only the tfoot tags ?? i mean merge rows. like the example above can you mergecell the A8:D8 ?? thanks — Vendetta, Jun 08 '23 at 08:26
this can be manually merge by edit the `mergeCellsHtml = mergeCellsHtml + ''` right ?? but how about merge it automaticly without change the `` ?? — Vendetta, Jun 08 '23 at 08:32
@andrewJames - i just made the question here [link](https://stackoverflow.com/questions/76436973/how-to-merge-cells-only-the-tfoot-tag-in-export-datatables-excel) — Vendetta, Jun 09 '23 at 03:15

how to merge matching values of column in jQuery datatables excelHtml5

1 Answers1

Linked