javascripthtmljquerydatatables
how to merge matching values of column in jQuery datatables excelHtml5
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
Solution
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>- Keep numbers which appear in both columns, in J lang
- Differentiation in J
- How to reshape an array with an arbitrary size in one dimension?
- Why is Insert (fold) right associative
- Write 4 : 'x&{.&.;: y' tacitly
- Alignment issue when printing formatted prime numbers in J language
- How can I define a verb in J that applies a different verb alternately to each atom in a list?
- How to get user input in the J programming language
- How to unbox a list of boxed lists of differing lengths in J?
- How can I fix 'noun result was required' error in J?
- In j, how can I define a verb locally in one scope and pass it to a defined adverb?
- Convert boxed array to normal array?
- Read column of CSV file as array
- Replace atom in array of strings
- What does the dyad `=` do to boxed strings?
- Index of minimum element using J
- How can I take the outer product of string vectors in J?
- Building an array of verbs in J
- Reading in multidigit command line parameter
- Amend with bond to new data shows unexpected behaviour
- How to turn a table or matrix into a (flat) list in J
- How to run dissect in J?
- How to define selection using index function in J
- How to exit the J console?
- Find 4-neighbors using J
- Writing custom verbs in J
- How do I negate a selector in J lang?
- How to use arbitrary selector in interchange in J lang?
- different result once square root is added inside tacit
- Sum of arrays with repeated indices