Grid Excel Export

8 posts, 0 answers
  1. John
    John avatar
    14 posts
    Member since:
    Sep 2006

    Posted 08 Sep 2015 Link to this post

    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

  2. John
    John avatar
    14 posts
    Member since:
    Sep 2006

    Posted 09 Sep 2015 in reply to John Link to this post

    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";
                        }
                        }
                      }
                    }
                  }
                },​

  3. Kendo UI is VS 2017 Ready
  4. Jon
    Jon avatar
    21 posts
    Member since:
    Dec 2012

    Posted 23 Feb Link to this post

    I'm running into the same issue, except that I'm using the MVC wrappers to create my grid. Is there a way to get rid of the HTML in the exported Excel file? Here's an example of the output:

    Account Number Ship Date Amount Packages Description
    Account Number:
    2/3/2016 5.75 1
    2/11/2016 2.6 1 60106
    2/15/2016 3.45 1 60106
    Total (3 Shipments) <div style='float: right;'>$11.80</div> <div style='float: right;'>3</div>

  5. Alexander Popov
    Admin
    Alexander Popov avatar
    1416 posts

    Posted 26 Feb Link to this post

    Hello Jon,

    Please check the following how-to example:  Although it does not solve the same issue, it illustrates the basic concept of modifying the data during the export. 

    Regards,
    Alexander Popov
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  6. Jon
    Jon avatar
    21 posts
    Member since:
    Dec 2012

    Posted 26 Feb in reply to Alexander Popov Link to this post

    Hi Alexander,

    My question was whether there is a way to do that when using the MVC wrappers. That works fine if you're not using the wrappers.

    For now I've just re-written my page to not use the wrappers. I would still like to know if there is a way to do it with them, though.

    Thanks!

    Jon

  7. Alexander Popov
    Admin
    Alexander Popov avatar
    1416 posts

    Posted 01 Mar Link to this post

    Hi Jon,

    Thank you for clarifying. Would you please share the MVC wrappers Grid configuration which led to this behavior? So far I was not able to reproduce it on our side.

    Regards,
    Alexander Popov
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  8. Jon
    Jon avatar
    21 posts
    Member since:
    Dec 2012

    Posted 02 Mar in reply to Alexander Popov Link to this post

    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);

  9. T. Tsonev
    Admin
    T. Tsonev avatar
    2770 posts

    Posted 04 Mar Link to this post

    Hi,

    Thanks for sharing the code. The approach from the help article should be applicable in MVC as well.
    The processing is done in the excelExport handler and is executing entirely on the client-side.

    Regards,
    T. Tsonev
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
Back to Top
Kendo UI is VS 2017 Ready