Export to excel - remove HTML tags before excel generation

10 posts, 0 answers
  1. Sanjay Salunkhe
    Sanjay Salunkhe avatar
    5 posts
    Member since:
    Jun 2009

    Posted 13 Apr 2015 Link to this post

    Is it possible to get hold of the text that gets generated when we do grid.saveAsExcel(), so that we can remove certain html tags from the output before the excel (xlsx file) gets generated.

     

    We need to remove the html tags from the excel that gets generated.

    I am attaching a sample excel file so that you can see what (html tags) i intend to remove

  2. Atanas Korchev
    Admin
    Atanas Korchev avatar
    8462 posts

    Posted 14 Apr 2015 Link to this post

    Hello Sanjay,

    You may check the Excel Export Footer Alignment help article which shows how to remove HTML tags from certain cells. You can remove the if statement and strip the HTML via jQuery for all cells:

            var rows = e.workbook.sheets[0].rows;

            for (var ri = 0; ri < rows.length; ri++) {
              var row = rows[ri];

                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
                    cell.value = $(cell.value).text();
                    // Set the alignment
                    cell.hAlign = "right";
                  }
                }
            }

    Regards,
    Atanas Korchev
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  3. Bob
    Bob avatar
    10 posts
    Member since:
    Apr 2014

    Posted 12 Aug 2015 Link to this post

    Atanas,

     How would I do this when I am using MVC (Razor)?  I don't see a way to tie the function into the excel export.

    .Excel(excel => excel
        .FileName("Report.xlsx")
        .AllPages(true)
        .Filterable(true)
        .ProxyURL(Url.Action("ExportSave", @ViewContext.RouteData.Values["controller"].ToString()))
    )

    Thanks!

  4. Dimiter Madjarov
    Admin
    Dimiter Madjarov avatar
    2312 posts

    Posted 13 Aug 2015 Link to this post

    Hello Bob,

    The logic is executed in the ExcelExport event of the Grid. You could attach a handler to it via the Events() method.
    E.g.

    .Events(e => e.ExcelExport("onExcelExport"))

    Regards,
    Dimiter Madjarov
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  5. Christian
    Christian avatar
    4 posts
    Member since:
    Dec 2016

    Posted 26 Jan 2017 in reply to Atanas Korchev Link to this post

    Atanas Korchev said:Hello Sanjay,

    You may check the Excel Export Footer Alignment help article which shows how to remove HTML tags from certain cells. You can remove the if statement and strip the HTML via jQuery for all cells:

            var rows = e.workbook.sheets[0].rows;

            for (var ri = 0; ri < rows.length; ri++) {
              var row = rows[ri];

                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
                    cell.value = $(cell.value).text();
                    // Set the alignment
                    cell.hAlign = "right";
                  }
                }
            }

    Regards,
    Atanas Korchev
    Telerik

     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     

     

    Hi Atanas,

    I was following your suggestion above. However, I got stuck with another problem.

    Some of the cells' value which I need to clean up from HTML tags, consist of characters apart from letters and numbers.

    Example 1: "22/10/2016"

    Example 2: "Lorem ipsum dolor sitamet. <br/> Thus lorem ipsum's."

    Thus JQuery .text() doesn't seems to like these kind of input. For example 1,it's the "/" character. While for example 2,  it's the "." character and " ' " character.

    Do you have any suggestion for this case? Thank you.

     

  6. Dimiter Madjarov
    Admin
    Dimiter Madjarov avatar
    2312 posts

    Posted 30 Jan 2017 Link to this post

    Hello Christian,

    The demonstrated approach is just a sample one, for the case when there is text wrapped in HTML tags (as jQuery will not recognize any text as a valid selector). In the current case I would suggest to replace this function with a custom one that will strip the text per current requirements.

    Regards,
    Dimiter Madjarov
    Telerik by Progress
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  7. Ken
    Ken avatar
    4 posts
    Member since:
    Oct 2020

    Posted 13 Oct 2020 Link to this post

    I tried to do the programatic way, but it was not worth the trouble given the negs and debugging.. 

    the EASIEST way is this, given that most of us don't have a huge number of headers to tweak

    Just access them directly and fix them... in my case, the three below were a lot easier than some code that went through all of them and gave spurious results (including one error somehow causing it to grab stuff off the page and embed it! still have not figured out how the heck it did that, and probably never will)

          excelExport: function(e) {
            e.workbook.sheets[0].rows[0].cells[4].value = 'Card Type'
            e.workbook.sheets[0].rows[0].cells[4].value = 'Trans Date'
            e.workbook.sheets[0].rows[0].cells[4].value = 'Bureau Number'

           }



            },

     

  8. Ken
    Ken avatar
    4 posts
    Member since:
    Oct 2020

    Posted 13 Oct 2020 in reply to Ken Link to this post

    Whoops... it should be

     

          excelExport: function(e) {
            e.workbook.sheets[0].rows[0].cells[4].value = 'Card Type'
            e.workbook.sheets[0].rows[0].cells[7].value = 'Trans Date'
            e.workbook.sheets[0].rows[0].cells[9].value = 'Bureau Number'
            },

  9. Christian Gabriel
    Christian Gabriel avatar
    1 posts
    Member since:
    Sep 2017

    Posted 15 Nov 2020 in reply to Sanjay Salunkhe Link to this post

    Hello, how to remove a tag in excel export <td> <a href="#"> value 1 </a> </td>
    i am testing and developing with kendo jquery

    Thank you

  10. Petar
    Admin
    Petar avatar
    506 posts

    Posted 17 Nov 2020 Link to this post

    Hi Christian Gabriel,

    The targeted functionality is demonstrated in the example discussed above. Here is another link to the example

    To implement the targeted functionality, the below code is used. The below implementation uses the excelExport event of the Grid component. You can remove the text in yellow and implement custom logic that will use a code similar to the line in green to get only the text value of the cells which HTML tags you need to remove. 

    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") {
                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
                    cell.value = $(cell.value).text();
                    // Set the alignment
                    cell.hAlign = "right";
                  }
                }
              }
            }
    }

    I hope the above will help you implement that targeted functionality. 

    Regards,
    Petar
    Progress Telerik

    Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Back to Top