This is a migrated thread and some comments may be shown as answers.
Export to excel - remove HTML tags before excel generation
9 Answers 203 Views
This is a migrated thread and some comments may be shown as answers.
Sanjay Salunkhe
Top achievements
Rank 1
Sanjay Salunkhe asked on 13 Apr 2015, 06:30 PM

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

9 Answers, 1 is accepted

Sort by
0
Atanas Korchev
Telerik team
answered on 14 Apr 2015, 09:46 AM
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!
 
0
Bob
Top achievements
Rank 2
answered on 12 Aug 2015, 01:57 PM

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!

0
Dimiter Madjarov
Telerik team
answered on 13 Aug 2015, 08:41 AM

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!
 
0
Christian
Top achievements
Rank 1
answered on 27 Jan 2017, 01:05 AM

[quote]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!
 

[/quote]

 

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.

 

0
Dimiter Madjarov
Telerik team
answered on 30 Jan 2017, 09:48 AM

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.
0
Ken
Top achievements
Rank 1
answered on 13 Oct 2020, 06:40 PM

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'

       }



        },

 

0
Ken
Top achievements
Rank 1
answered on 13 Oct 2020, 06:40 PM

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'
        },

0
Christian Gabriel
Top achievements
Rank 1
answered on 16 Nov 2020, 03:23 AM

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

0
Petar
Telerik team
answered on 17 Nov 2020, 01:06 PM

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/.

Tags
Grid
Asked by
Sanjay Salunkhe
Top achievements
Rank 1
Answers by
Atanas Korchev
Telerik team
Bob
Top achievements
Rank 2
Dimiter Madjarov
Telerik team
Christian
Top achievements
Rank 1
Ken
Top achievements
Rank 1
Christian Gabriel
Top achievements
Rank 1
Petar
Telerik team
Share this question
or