This is a migrated thread and some comments may be shown as answers.

Grid Excel Export

7 Answers 664 Views

Grid

This is a migrated thread and some comments may be shown as answers.

John asked on 08 Sep 2015, 10:04 PM

Hi,

I have the grid looking exactly as desired,currency format right aligned and the documentation states that

Column Templates

Kendo UI Grid doesn't use column templates during Excel export - it exports only the data. The reason is simple - a column template may contain arbitrary HTML which can't be converted to Excel column values.

The format option is not used during export. Check Column Format for more info.
The template option is not used during export. Check Column Templates for more info.

my code

columns: [
{command: ["edit"], title: " ", locked: true, width: "80px"},

{field: "invAmt", width: 120, template: "#= kendo.toString(invAmt, 'C') #" , title: "invAmt", filterable: {cell: {showOperators: false}},
aggregates: ["sum"], aggregates: ["sum"],
groupHeaderTemplate: "Total Count: #=sum#",

footerTemplate: "<div class='ra'>#= kendo.toString(sum, 'C') #</div>",
groupFooterTemplate: "<div class='ra'>#= kendo.toString(sum, 'C') #</div>"
},
{field: "paidAmt", width: 120, template: "<div class='ra'>#= kendo.toString(paidAmt, 'C') #</div>" , title: "<div class='ra'>paidAmt</div>", filterable: {cell: {showOperators: false}},
aggregates: ["sum"], aggregates: ["sum"],
groupHeaderTemplate: "Total Count: #=sum#",
footerTemplate: "<div class='ra'>#= kendo.toString(sum, 'C') #</div>",
groupFooterTemplate: "<div class='ra'>#= kendo.toString(sum, 'C') #</div>"
},
{field: "savings", width: 120, template: "<div class='ra'>#= kendo.toString(savings, 'C') #</div>" , title: "<div class='ra'>savings</div>", filterable: {cell: {showOperators: false}},
aggregates: ["sum"], aggregates: ["sum"],
groupHeaderTemplate: "Total Count: #=sum#",
footerTemplate: "<div class='ra'>#= kendo.toString(sum, 'C') #</div>",
groupFooterTemplate: "<div class='ra'>#= kendo.toString(sum, 'C') #</div>"
},

yet the following gets exported to excel.

<div class='ra'>invAmt</div>
333
250
500
0
0
0
0
<div class='ra'>$1,083.00</div>
<div class='ra'>$1,083.00</div>

I guess I'm reading it wrong?

TIA
John

7 Answers, 1 is accepted

answered on 09 Sep 2015, 05:33 PM

I found a solution and modified to the following

toolbar: ["excel"],
excel: {
allPages: true
},
excelExport: function(e) {
var rows = e.workbook.sheets[0].rows;
for (var ri = 0; ri < rows.length; ri++) {
var row = rows[ri];

if (row.type == "group-footer" || row.type == "footer" || row.type == "header") {
for (var ci = 0; ci < row.cells.length; ci++) {
var cell = row.cells[ci];
if (cell.value) {
// Use jQuery.fn.text to remove the HTML and get only the text

if (cell.value)
var res = cell.value.substring(0, 5);

if ((res==='Count') || (res==='Total' ) || (res!=='<div ' )){
cell.value = cell.value;
} else {
cell.value = $(cell.value).text();
// Set the alignment
cell.hAlign = "right";
}
}
}
}
}
},

answered on 02 Mar 2016, 03:33 PM

I don't have the entire grid anymore because as I said I have already re-written it without using the wrappers. However I did still have a copy of my column configuration, below. It's the ClientFooterTemplate and ClientGroupFooterTemplate that are coming through with the HTML instead of just the value.

columns.Bound(p => p.ShipmentKey).Visible(false);

columns.Bound(p => p.AccountNumber)

.ClientFooterTemplate("Grand Total (#=count# Shipments)")

.ClientGroupFooterTemplate("Total (#=count# Shipments)");

columns.Bound(p => p.ShipDate)

.Format("{0:d}")

.HtmlAttributes(new { align = "right" })

.HeaderHtmlAttributes(new { align = "right" })

.Width(90);

columns.Bound(p => p.Amount)

.Format("{0:c}")

.HtmlAttributes(new { align = "right" })

.HeaderHtmlAttributes(new { align = "right" })

.Width(90)

.ClientFooterTemplate("<div style='float: right;'>#=kendo.toString(sum, 'c2')#</div>")

.ClientGroupFooterTemplate("<div style='float: right;'>#=kendo.toString(sum, 'c2')#</div>");

columns.Bound(p => p.Packages)

.HtmlAttributes(new { align = "right" })

.HeaderHtmlAttributes(new { align = "right" })

.ClientFooterTemplate("<div style='float: right;'>#=sum#</div>")

.ClientGroupFooterTemplate("<div style='float: right;'>#=sum#</div>");

columns.Bound(p => p.Description);