Export to excel - remove HTML tags before excel generation

[Sanjay Salunkhe]

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

[Atanas Korchev]

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

 

 

[Bob]

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!

[Dimiter Madjarov]

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

 

 

[Aaron]

[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

 

 

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

 

[Dimiter Madjarov]

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

[Ken]

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'

       }



        },

 

[Ken]

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

[Christian Gabriel]

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

[Petar]

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

[Bryon]

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]

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:

• https://dojo.telerik.com/@anton.mironov/OVakiYUJ

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]

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]

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]

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]

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]

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]

"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]

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]

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]

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]

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