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

Export to excel - remove HTML tags before excel generation

10 Answers 1306 Views
Grid
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

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

0
Bryon
Top achievements
Rank 2
Iron
answered on 06 Jan 2022, 09:18 PM

After I insert this Javascript into my code...

function onExcelExport(e) {
    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.vAlign = 'top';
                cell.hAlign = 'left';
            }
        }
    }
}

I set breakpoints, and it goes through the Javascript code, but it no longer calls the C#...

        [HttpPost]
        public ActionResult Excel_Export_Save(string contentType, string base64, string fileName)
        {
            byte[] fileContents = Convert.FromBase64String(base64);
            return File(fileContents, contentType, fileName);
        }

...how do I continue on with the ProxyURL in my MVC Grid?

          .Excel(excel => excel
              .AllPages(true)
              .FileName("Report.xlsx")
              .Filterable(true)
              .ProxyURL(Url.Action("Excel_Export_Save", "Admin"))
          )
...through the Javascript now?
Anton Mironov
Telerik team
commented on 10 Jan 2022, 11:19 AM

Hi Bryon,

Thank you for the details provided.

I tried to achieve similar behavior with the following dojo example and the result is the expected one:

The application is using correctly the proxyURL and the change in the excelExport Event handler is applied.

The fastest route to getting you up and running is if you could provide a runnable, isolated, sample project. Examining this project will let us replicate the issue locally and further troubleshoot it.

As this forum post is a duplicate of another forum thread question I am pasting here the same answer. Please keep the communication in one place.

Looking forward to hearing back from you.

Kind Regards,
Anton Mironov

Bryon
Top achievements
Rank 2
Iron
commented on 01 Feb 2022, 04:18 PM

I've commented out all of my code, and it still Exports to Excel, even if there is no Method to tell it to do so in the ProxyURL of the ExcelExport. Something is going on with the Telerik Library or something. I'm using ASP.NET MVC 2017.2.504 as of right now.

C#

        //[HttpPost]
        //public ActionResult Excel_Export_Save(string contentType, string base64, string fileName)
        //{
        //    byte[] fileContents = Convert.FromBase64String(base64);
        //    return File(fileContents, contentType, fileName);
        //}

JS

//function onExcelExport(e) {
//    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.vAlign = 'top';
//                cell.hAlign = 'left';
//            }
//        }
//    }
//}
MVC/Razor
<div id="grid3P" class="row" hidden>
    @(Html.Kendo().Grid<_3PReports.Models.ViewModels._3PGridViewModel>()
        .Name("admin3PGrid")
        .ToolBar(tools =>
        {
            tools.Pdf();
            tools.Excel();
        })
        .Pdf(pdf => pdf
            .AllPages()
            .AvoidLinks()
            .PaperSize("Letter")
            .Scale(0.5)
            .Margin("0.5cm", "0cm", "0.5cm", "0cm")
            .Landscape()
            .RepeatHeaders()
            .TemplateId("page-template")
            .FileName("CIC3PReport.pdf")
            .ProxyURL(Url.Action("Pdf_Export_Save", "Admin"))
        )
        .Excel(excel => excel
            .AllPages(true)
            .FileName("CIC3PReport.xlsx")
            .Filterable(true)
            //.ProxyURL(Url.Action("Excel_Export_Save", "Admin"))
        )
        .Columns(columns =>
        {
            columns.Bound(b => b.FullWeek).Width(160).Title("Week Of");
            columns.Bound(b => b.AssociateName).Width(150).Title("Associate");
            columns.Bound(b => b.Progress).Width(500).Encoded(false).Title("Progress");
            columns.Bound(b => b.Problems).Width(250).Encoded(false).Title("Problems");
            columns.Bound(b => b.Plans).Width(500).Encoded(false).Title("Plans");
            columns.Bound(b => b.ModifiedDate).Width(155).Format("{0: MM/dd/yyyy hh:mm tt}").Title("Last Modified Date");
        })
        .HtmlAttributes(new { style = "min-height:625px; width:98%; margin:0 auto;" })
        .Pageable(pageable => pageable
            .Refresh(true)
            .PageSizes(true)
            .ButtonCount(5))
        .DataSource(dataSource => dataSource
            .Ajax()
            .Read(read => read.Action("Reports_Read", "Admin").Data("gridSearch"))
            .PageSize(10))
    //.Events(e => e.ExcelExport("onExcelExport"))
    )
</div>


Anton Mironov
Telerik team
commented on 03 Feb 2022, 11:57 AM

Hi Bryon,

Thank you for the code snippets and additional details provided.

Actually, the pointed behavior is expected. You have the Excel Toolbar set and the settings for the export. The "proxyUrl" will be used when the browser isn't capable of saving files locally. Such browsers are IE version 9 and lower and Safari. So you need to decide if this functionality has to be included as per the needs of your application. If yes - send me a runnable sample of your application along with the needed ProxyURL Method in the Controller and I will try my best to achieve the desired behavior.

Looking forward to hearing back from you.

Best Regards,
Anton Mironov

 

Bryon
Top achievements
Rank 2
Iron
commented on 03 Feb 2022, 10:24 PM

So, it doesn't need the proxyUrl at all? Ok.

When I run the above code with the JS and the .Events (MVC/Razor) code uncommented out, it runs up until it hits a cell that has a forward slash "/" in the data. I was able to set a breakpoint in my JS code, and pinpoint that, but I have no idea why a "/" would completely cancel the code running.

function onExcelExport(e) {
    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 === row.cells[2] || cell === row.cells[3] || cell === row.cells[4]) {
                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.vAlign = 'top';
            cell.hAlign = 'left';
        }
    }
}

Anton Mironov
Telerik team
commented on 07 Feb 2022, 04:12 PM

Hi Bryon,

Thank you for the code snippet provided. It is our approach from this article and I can clearly see your point now.

In this case, the problem is in jQuery. It does not allow special symbols like "/" to be used and if you like to use them - these symbol/s should be escaped. Doing this we will ignore the JavaScript error, but will create a new issue - the jQuery selector now is unusable, as it is:



while the value is:

In order to achieve the desired behavior with the excel export and ignore the HTML approach, I would recommend replacing the forward slashes by default. Use the Action Method in the Controller that reads the data from the DataBase and replace the unacceptable for jQuery symbols.

I hope this information helps. Let me know if further assistance is needed.

Best Regards,
Anton Mironov

 

Bryon
Top achievements
Rank 2
Iron
commented on 10 Feb 2022, 03:21 PM

The data being passed through is from Saved Data by all of our IT Associates every week as a Progress Report using your Rich Text Editor.

I replaced the '/' with a '-', and it continues to run until it gets to another character that breaks it. Is there no way to pass all characters, letters and numbers as a string? Also, it appears replacing the "/" with a "-" disrupts the HTML Tags that I'm trying to remove in the first place.... EXAMPLE: "<br ->Test <br />"...therefore, not removing when called to.

How about this, is there any way whatsoever to take the HTML content created by the Rich Text Editor and saved on our Database, and Export it to Excel as HTML/Rich Text in the Cells of Excel?

Also, unlike the "Export to PDF" with its Progress Bar, there is no Progress Bar indicator whatsoever on the screen when attempting to Export to Excel. The user has no indication what is happening with their process until they get the Excel file and that's it. The rest of the time they're just looking at the Grid after clicking "Export to Excel".

Bryon
Top achievements
Rank 2
Iron
commented on 10 Feb 2022, 03:23 PM

"How about this, is there any way whatsoever to take the HTML content created by the Rich Text Editor and saved on our Database, and Export it to Excel as HTML/Rich Text in the Cells of Excel?"

This would be the preferred way to handle this HTML issue.

Anton Mironov
Telerik team
commented on 14 Feb 2022, 12:27 PM

Hi Bryon,

Thank you for the additional details provided.

The issue is caused by the jQuery "text" method.

I am still recommending making the needed changes in the BackEnd. I confirm that the pointed replacement is breaking the HTML tags, so this could be tried with a Regular Expression. This approach will provide the opportunity to only change or remove the unneeded forward slashes.

Let me know if further assistance is needed.

Looking forward to hearing back from you.

Kind Regards,
Anton Mironov

    Bryon
    Top achievements
    Rank 2
    Iron
    commented on 17 Feb 2022, 09:58 PM

    I'm sorry, I'm not understanding at all, and this is just frustrating at this point. I have HTML Tags saved from Telerik's Rich Text Editor, that exports fine to PDF, but I'm also needing it to export to Excel, and when the export to Excel occurs, it loads the HTML Tags as text in the cell. Nothing seems to work, and you want me to replace the forward slashes in the data, but it's used to create the formatted text in the Grid it displays in. I've taken screenshots that display the Grid that shows the BOLD, underlined text. It's not making any sense, and nothing is being said to make it make any sense. Really annoyed at this point.
    Bryon
    Top achievements
    Rank 2
    Iron
    commented on 18 Feb 2022, 12:57 PM

    I got the HTML Tags removed by doing this...

                    if (cell.value) {
                        // Remove HTML Tags from data in Excel
                        cell.value = cell.value.replace(/(<([^>]+)>)/ig, '');
    
                    }

    It's gonna have to do for now.
    Anton Mironov
    Telerik team
    commented on 20 Feb 2022, 12:33 PM

    Hi Bryon,

    Yes, this is the recommended approach and as I mentioned - the desired behavior could be achieved by using a Regular Expression.

    Thank you for sharing your Regular Expression with the community.

    If further assistance or information is needed, do not hesitate to contact me and the team.

    Kind Regards,
    Anton Mironov

    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
    Bryon
    Top achievements
    Rank 2
    Iron
    Share this question
    or