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 
Desired output i want is
CodePudding user response：
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"
cellspacing="0" width="100%" aria-describedby="example1_info">
<thead>
<tr>
<th rowspan="1" colspan="1">Date</th>
<th rowspan="1" colspan="1">Category</th>
<th rowspan="1" colspan="1">Item Name</th>
<th rowspan="1" colspan="1">Price</th>
<th rowspan="1" colspan="1">Opening</th>
<th rowspan="1" colspan="1">Recived</th>
<th rowspan="1" colspan="1">Total</th>
<th rowspan="1" colspan="1">Closing</th>
<th rowspan="1" colspan="1">Sales</th>
<th rowspan="1" colspan="1">Total</th>
</tr>
</thead>
<tbody>
<tr data-row-id="1761" >
<td col-index="0" oldval="2022-08-07">2022-08-07</td>
<th col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
<td col-index="2" oldval="Golden Touch Brandy 180ml">Golden Touch Brandy 180ml</td>
<td col-index="2" oldval="250.00">250.00</td>
<td col-index="3" oldval="9.00">9.00</td>
<td col-index="4" oldval=""></td>
<td col-index="5" oldval="9">9</td>
<td col-index="6" oldval="9.00">9.00</td>
<td col-index="7" oldval="0">0</td>
<td col-index="8" oldval="0">0</td>
</tr>
<tr data-row-id="1763" >
<td col-index="0" oldval="2022-08-07">2022-08-07</td>
<th col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
<td col-index="2" oldval="Golden Touch Brandy 750ml">Golden Touch Brandy 750ml</td>
<td col-index="2" oldval="1000.00">1000.00</td>
<td col-index="3" oldval="3.00">3.00</td>
<td col-index="4" oldval=""></td>
<td col-index="5" oldval="3">3</td>
<td col-index="6" oldval="3.00">3.00</td>
<td col-index="7" oldval="0">0</td>
<td col-index="8" oldval="0">0</td>
</tr>
<tr data-row-id="1948" >
<td col-index="0" oldval="2022-08-07">2022-08-07</td>
<th col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
<td col-index="2" oldval="Whytehall Brandy 180ml">Whytehall Brandy 180ml</td>
<td col-index="2" oldval="250.00">250.00</td>
<td col-index="3" oldval="18.00">18.00</td>
<td col-index="4" oldval=""></td>
<td col-index="5" oldval="18">18</td>
<td col-index="6" oldval="18.00">18.00</td>
<td col-index="7" oldval="0">0</td>
<td col-index="8" oldval="0">0</td>
</tr>
<tr data-row-id="1949" >
<td col-index="0" oldval="2022-08-07">2022-08-07</td>
<th col-index="1" oldval="Brandy" tabindex="0">Brandy</th>
<td col-index="2" oldval="Whytehall Brandy 750ml">Whytehall Brandy 750ml</td>
<td col-index="2" oldval="1000.00">1000.00</td>
<td col-index="3" oldval="4.00">4.00</td>
<td col-index="4" oldval=""></td>
<td col-index="5" oldval="4">4</td>
<td col-index="6" oldval="4.00">4.00</td>
<td col-index="7" oldval="0">0</td>
<td col-index="8" oldval="0">0</td>
</tr>
<tr data-row-id="1992" >
<td col-index="0" oldval="2022-08-07">2022-08-07</td>
<th col-index="1" oldval="Brandy" tabindex="0">Port</th>
<td col-index="2" oldval="Roulette Brandy 750ml">Roulette Brandy 750ml</td>
<td col-index="2" oldval="2230.00">2230.00</td>
<td col-index="3" oldval="1.00">1.00</td>
<td col-index="4" oldval=""></td>
<td col-index="5" oldval="1">1</td>
<td col-index="6" oldval="1.00">1.00</td>
<td col-index="7" oldval="0">0</td>
<td col-index="8" oldval="0">0</td>
</tr>
<tr data-row-id="2048" >
<td col-index="0" oldval="2022-08-07">2022-08-07</td>
<th col-index="1" oldval="Brandy" tabindex="0">Port</th>
<td col-index="2" oldval="Bejois VSOP 375ML">Bejois VSOP 375ML</td>
<td col-index="2" oldval="200.00">200.00</td>
<td col-index="3" oldval="20.00">20.00</td>
<td col-index="4" oldval=""></td>
<td col-index="5" oldval="20">20</td>
<td col-index="6" oldval="20.00">20.00</td>
<td col-index="7" oldval="0">0</td>
<td col-index="8" oldval="0">0</td>
</tr>
</tbody>
<tfoot>
<tr>
<th 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>