Help Question for bot

[jay41]

Hi bot how can I add empty row after the row 2 and before the header and data in excel export.
here is my code customize: function () in datatable

var sheet = xlsx.xl.worksheets['sheet1.xml'];
var downrows = 2;
var clRow = $('row', sheet);

clRow.each(function () {
var attr = $(this).attr('r');
var ind = parseInt(attr);
ind = ind + downrows;
$(this).attr("r",ind);
});

$('row c ', sheet).each(function () {
var attr = $(this).attr('r');
var pre = attr.substring(0, 1);
var ind = parseInt(attr.substring(1, attr.length));
ind = ind + downrows;
$(this).attr("r", pre + ind);
});

function row(index,data) {
msg='<row r="'+index+'">'
for(i=0;i<data.length;i++){
var key=data.k;
var value=data.v;
msg += '<c t="inlineStr" r="' + key + index + '" s="">';
msg += '<is>';
msg += '<t>'+value+'</t>';
msg+= '</is>';
msg+='</c>';
}
msg += '</row>';
return msg;
}

var r1 = row(1, [{ k: 'A', v: 'Total Transaction: ' }, { k: 'B', v: totaltransactions }]);
var r2 = row(2, [{ k: 'A', v: 'Total Amount: ' }, { k: 'B', v: totalamount }]);

sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2 + sheet.childNodes[0].childNodes[1].innerHTML;

 

[🤖]

To add an empty row after row 2 and before the header and data in Excel export using the provided code, you can make the following modifications:

var sheet = xlsx.xl.worksheets['sheet1.xml'];
var downrows = 3; // Increase by 1 to account for the empty row
var clRow = $('row', sheet);

clRow.each(function () {
  var attr = $(this).attr('r');
  var ind = parseInt(attr);
  ind = ind + downrows;
  $(this).attr("r", ind);
});

$('row c ', sheet).each(function () {
  var attr = $(this).attr('r');
  var pre = attr.substring(0, 1);
  var ind = parseInt(attr.substring(1, attr.length));
  ind = ind + downrows;
  $(this).attr("r", pre + ind);
});

function row(index, data) {
  msg = '<row r="' + index + '">';
  for (i = 0; i < data.length; i++) {
    var key = data[i].k;
    var value = data[i].v;
    msg += '<c t="inlineStr" r="' + key + index + '" s="">';
    msg += '<is>';
    msg += '<t>' + value + '</t>';
    msg += '</is>';
    msg += '</c>';
  }
  msg += '</row>';
  return msg;
}

var r1 = row(1, [{ k: 'A', v: 'Total Transaction: ' }, { k: 'B', v: totaltransactions }]);
var r2 = row(2, [{ k: 'A', v: 'Total Amount: ' }, { k: 'B', v: totalamount }]);
var emptyRow = row(3, [{ k: 'A', v: '' }, { k: 'B', v: '' }]); // Add an empty row

sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2 + emptyRow + sheet.childNodes[0].childNodes[1].innerHTML;

This code adds an empty row after row 2 by incrementing the row index (ind) and updating the XML attributes accordingly. It also creates a new emptyRow variable using the row function to generate an empty row with empty cell values. Then, it includes the emptyRow variable in the innerHTML assignment to add the empty row to the exported Excel file.

 

[jay41]

To add an empty row after row 2 and before the header and data in Excel export using the provided code, you can make the following modifications:

var sheet = xlsx.xl.worksheets['sheet1.xml'];
var downrows = 3; // Increase by 1 to account for the empty row
var clRow = $('row', sheet);

clRow.each(function () {
  var attr = $(this).attr('r');
  var ind = parseInt(attr);
  ind = ind + downrows;
  $(this).attr("r", ind);
});

$('row c ', sheet).each(function () {
  var attr = $(this).attr('r');
  var pre = attr.substring(0, 1);
  var ind = parseInt(attr.substring(1, attr.length));
  ind = ind + downrows;
  $(this).attr("r", pre + ind);
});

function row(index, data) {
  msg = '<row r="' + index + '">';
  for (i = 0; i < data.length; i++) {
    var key = data[i].k;
    var value = data[i].v;
    msg += '<c t="inlineStr" r="' + key + index + '" s="">';
    msg += '<is>';
    msg += '<t>' + value + '</t>';
    msg += '</is>';
    msg += '</c>';
  }
  msg += '</row>';
  return msg;
}

var r1 = row(1, [{ k: 'A', v: 'Total Transaction: ' }, { k: 'B', v: totaltransactions }]);
var r2 = row(2, [{ k: 'A', v: 'Total Amount: ' }, { k: 'B', v: totalamount }]);
var emptyRow = row(3, [{ k: 'A', v: '' }, { k: 'B', v: '' }]); // Add an empty row

sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2 + emptyRow + sheet.childNodes[0].childNodes[1].innerHTML;

This code adds an empty row after row 2 by incrementing the row index (ind) and updating the XML attributes accordingly. It also creates a new emptyRow variable using the row function to generate an empty row with empty cell values. Then, it includes the emptyRow variable in the innerHTML assignment to add the empty row to the exported Excel file.

Nice its working. How to Bold the text Total Transaction: and Total Amount:

 

[🤖]

Nice its working. How to Bold the text Total Transaction: and Total Amount:

To bold the text "Total Transaction:" and "Total Amount:" in the Excel export, you can modify the code by adding a style for the bold font. Here's an updated version of the code:

var sheet = xlsx.xl.worksheets['sheet1.xml'];
var downrows = 3; // Increase by 1 to account for the empty row
var clRow = $('row', sheet);

clRow.each(function () {
  var attr = $(this).attr('r');
  var ind = parseInt(attr);
  ind = ind + downrows;
  $(this).attr("r", ind);
});

$('row c ', sheet).each(function () {
  var attr = $(this).attr('r');
  var pre = attr.substring(0, 1);
  var ind = parseInt(attr.substring(1, attr.length));
  ind = ind + downrows;
  $(this).attr("r", pre + ind);
});

function row(index, data) {
  msg = '<row r="' + index + '">';
  for (i = 0; i < data.length; i++) {
    var key = data[i].k;
    var value = data[i].v;
    if (index === 1 || index === 2) {
      msg += '<c t="inlineStr" r="' + key + index + '" s="1">'; // Apply style for bold font
    } else {
      msg += '<c t="inlineStr" r="' + key + index + '" s="">';
    }
    msg += '<is>';
    msg += '<t>' + value + '</t>';
    msg += '</is>';
    msg += '</c>';
  }
  msg += '</row>';
  return msg;
}

var r1 = row(1, [{ k: 'A', v: 'Total Transaction: ' }, { k: 'B', v: totaltransactions }]);
var r2 = row(2, [{ k: 'A', v: 'Total Amount: ' }, { k: 'B', v: totalamount }]);
var emptyRow = row(3, [{ k: 'A', v: '' }, { k: 'B', v: '' }]); // Add an empty row

// Define the style for bold font
var styles = '<cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="0" applyFill="0" applyBorder="0" applyAlignment="0"/><xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="1" applyFill="0" applyBorder="0" applyAlignment="0"><font><b/></font></xf></cellXfs>';
// Update the styles in the worksheet
sheet.childNodes[0].childNodes[3].innerHTML = styles;

sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2 + emptyRow + sheet.childNodes[0].childNodes[1].innerHTML;

In this code, we added a new if condition in the row function to check if the row index is 1 or 2. If it is, we assign a style (s="1") to the cell to make the font bold. Additionally, we added the styles variable to define the style for the bold font. Finally, we updated the innerHTML of the cellXfs element in the worksheet to include the new style.