Export to Excel Running Slow All of a Sudden

[Jason]

Hello,

We recently updated our web app to .Net Core 2.0, and we updated all of your tools as well to their latest versions.  However, we noticed that the export to excel functionality that we have for one of our grids started running extremely slow all of a sudden, and we're not entirely sure why.  If we have hundreds of entries that we need to export, when we click the "Export to Excel" button it spends all of its time trying to transfer the data before downloading and eventually the page crashes.  I also noticed that it will try to transfer over 1GB of data which is way too much for what it should be.  We did not have this issue before updating your tools, so we were wondering if something changed with how the export to excel functionality works with your latest updates to it (such as if the syntax for it is different than what it was before)?  We did test the functionality on a small list of items, and it did export immediately.  It's only with when we have hundreds of items to export when this happens.  We checked to see if any of the .Net Core 2.0 updates or anything else new in our code could have affected the speed of it, but we couldn't find anything.

 

Thanks,

Jason

[Tsvetina]

Hi Jason,

I tested exporting a Grid with 1000 records in an ASP.NET Core project using .NET Core 2.0 and UI for ASP.NET Core 2018.2.620, but could not reproduce the slow export problem. We also do not have such an issue recently logged. I am attaching the test project to this message for reference. Can you modify it to reproduce the problem? If yes, please describe the changes that I need to make, so the issue can be reproduced and I will debug it locally to see why it occurs.
Just in case, I would suggest that you confirm JSZip is successfully loaded in the page where you are exporting the Grid.

Regards,
Tsvetina
Progress Telerik

[Jason]

Thank you for your response, and I apologize for the late response on my part.  I will check out the test project and see what I find out.

[Jason]

It definitely appears to be an issue on our end.  Do you happen to know if the export to excel function makes any server calls?  It seems as if it's an issue with network traffic, as in the Chrome debugger it shows the network attempting to transfer a lot of megabytes, which it should not be doing. 

[Tsvetina]

Hello Jason,

It is possible that a server proxy is used to save the file after export. You can tell if this is happening if you check your ProxyUrl setting and see if it matches the URL to which the large data set is transferred:

.Excel(excel => excel
    .FileName("Kendo UI Grid Export.xlsx")
    .Filterable(true)
    .ProxyURL(Url.Action("Excel_Export_Save", "Grid"))
)

The server proxy method will be called in one of the following cases:

• If the page is opened in a browser that does not support saving files directly from client script to the local file system. Such browsers are Safari and IE9.
• If ForceProxy(true) setting is added to your Grid Excel configuration.

Let me know if you think this is what the request is triggered from.

Regards,
Tsvetina
Progress Telerik

[Jason]

We are actually not using the .ProxyUrl setting of the excel export.  We instead have an Javascript function that handles it:

function excelExport(e) {
            var sheet = e.workbook.sheets[0];
            for (var i = 0; i < sheet.rows.length - 1; i++) {
                sheet.rows[i + 1].cells[0].value = '@Model.JobNo-' + sheet.rows[i + 1].cells[0].value;
            }

        }

I'm curious if the reason why it's taking so long to load is because it's trying to build the entire grid of 8000+ items before actually exporting, but we've been using this Javascript function for months and haven't had any issues with it until recently.

[Tsvetina]

Hello Jason,

The purpose of the ProxyUrl setting is to allow saving of the exported file in browsers that do not support saving files from the client. It is not related to any logic that can be performed in the excelExport event. 

Now, that you say that the Grid actually has 8000 data rows, this could lead to a considerable slow down when AllPages() setting is applied. This setting causes the Grid to make a request for all data items without paging parameters. Then, the Grid generates the Excel content on the client, which for 8000 rows could be quite memory-consuming. In scenarios with that many rows, we advise on using server-side Excel export:
Grid / Server Export

Now, about the >1GB data transfer. What I am thinking is that the backend that you are working with may have changed to return more information in the data items that the Grid is bound to. For example, if you have a lot of nesting in the data or if data items include images in base64 format, then a single data item can become quite big. If you check what URL the request is made to, you should be able to determine why the response is so large.

Regards,
Tsvetina
Progress Telerik

[Jason]

Right, I meant to say that we aren't using the ProxyUrl because we are on supported browsers.  I will look into the server-side Excel export as well.  

Our grid only has six columns of data for each row, although we do have a partial view nested inside each row that drops down when you click on the row.  This feature has been a part of this application for quite a while though so I don't necessarily think this could be the issue because that data in the partial view is not being exported.  I will check with my manager though and see if anything changed within the backend, and I'll follow up with you to let you know what my results are.

[Tsvetina]

Hi Jason,

You can check what the data returned from the server looks like in the browser developer tools (F12 key) Network tab. Refresh the page with the Network tab open and inspect the content of the Grid Read request. You can see if there are any fields that are not used in the Grid but have big values. 

The templates content of the Grid is not exported, so the partial that you describe should not be related to the performance problem.

You can also try to create a copy of your project using dummy data values and if the problem is reproduced in it, open a support ticket and send us the project.

Regards,
Tsvetina
Progress Telerik

[Jason]

Tsvetina,

We ended up solving the issue.  Basically, our grid loads a giant list of claims, and the Claim class contains tons of data ranging from documents to activities and whatnot (stuff that is not important to exporting).  So, one of our devs ended up creating a Flat Claim class, with only the data that is necessary to load on the grid and export to Excel, and now the grid only loads from that class and therefore only exports that data.  This is especially nice because our grid used to take a few seconds to load, and now it loads instantly.  So it was definitely an issue of having too much data trying to be transferred.  Thanks for your help!

-Jason

[Peter]

Hi, Jason. I am also facing the problem of slow excel export. I found it goes to query backend data when I press export button. Could you share how to trace and solve the issue?