Create An Export to CSV/Excel Tool for Grid Views

Jonathan_Boles

Would love to see an export to CSV/Excel Tool or Task created for grid views that would export all of the current work items (even if not all shown on the 1 page in the event it is a large result of items). A bonus would be a tool or task that would allow the customization of the exported information (which fields based upon which WI type are being exported). For me as an administrator, I'd even be happy if every single field for every single work item type were exported so I could then easily take the data and work with it as needed without having to go through the back end of the system using PowerShell.

We have this request several times a week from analysts and especially IT management who would like to be able to easy pull the results they get from their custom searches into Excel to create graphs and work with the data for further analysis.

If anyone has anything that they currently use, please (I beg) feel free to share. Ultimately, though, I'd love to see something directly within the portal itself.

Julio_Grajales

Any luck with this? 

Emmanuel_Cassidy

I would to have this enabled for Knowledge Articles. would make management a lot easier.

thanks,

Mike_Storms

I asked for this also... it would be extremely beneficial... especially if you could also select the fields returned I the grid... plus include any custom extensions to search on as well.... additionally for this same type search capabilities for CI would be incredible...

Jonathan_Boles

Thanks @Julio_Grajales, @Emmanuel_Cassidy and @Mike_Storms for upvoting this - Hoping we can get additional upvotes and bring this request to light. It's something within our organization that has been requested since the initial testing of the portal. No matter how many dashboards or grid views there are, it seemspeople like pulling data into Excel (me being one of those people ).

Roland_Kind

Hi,
as a quick workaround, you can use the standard Excel / PDF grid functionality from Kendo UI.  I’ve made a quick check and it seems to work (at least in my development environment / 6.0.2).

  

custom.js

// the jszip is necessary for the Excel export – this file is already in the Kendo distribution

// there is maybe a better way to load this ….

//

var add_jszip = document.createElement('script');

add_jszip.setAttribute('src','/Scripts/kendo/2016.1.226/jszip.min.js');

document.head.appendChild(add_jszip);

 

// based on the idea of custom grid view filter from joivan_hedrick  ….

// this will place the excel and pdf button on top the of grid views …

//

$(document).ready(function ()

       {setTimeout(function() {        

            $('[data-role=grid]').each(function (index)

               { $(this).data('kendoGrid').options.excel={fileName:"CiresonPortal.xlsx", allPages:true, proxyURL: ""};

                     $(this).data('kendoGrid').options.pdf={allPages:true, proxyURL: ""};

                      var resetViewButtonParentDiv = $(".btn-clear-grid-filters").first().parent();

                      $(resetViewButtonParentDiv).append("<a class='k-button k-button-icontext k-grid-excel' href='#'><span class='k-icon k-i-excel'></span>Export to Excel</a>");                                               

                     $(resetViewButtonParentDiv).append("<a class='k-button k-button-icontext k-grid-pdf' href='#'><span class='k-icon k-i-pdf'></span>Export to PDF</a>");

                          });               

          },1000);  

 });

Jonathan_Boles

Roland_Kind said:

Hi,
as a quick workaround, you can use the standard Excel / PDF grid functionality from Kendo UI.  I’ve made a quick check and it seems to work (at least in my development environment / 6.0.2).

  

custom.js

// the jszip is necessary for the Excel export – this file is already in the Kendo distribution

// there is maybe a better way to load this ….

//

var add_jszip = document.createElement('script');

add_jszip.setAttribute('src','/Scripts/kendo/2016.1.226/jszip.min.js');

document.head.appendChild(add_jszip);

 

// based on the idea of custom grid view filter from joivan_hedrick  ….

// this will place the excel and pdf button on top the of grid views …

//

$(document).ready(function ()

       {setTimeout(function() {        

            $('[data-role=grid]').each(function (index)

               { $(this).data('kendoGrid').options.excel={fileName:"CiresonPortal.xlsx", allPages:true, proxyURL: ""};

                     $(this).data('kendoGrid').options.pdf={allPages:true, proxyURL: ""};

                      var resetViewButtonParentDiv = $(".btn-clear-grid-filters").first().parent();

                      $(resetViewButtonParentDiv).append("<a class='k-button k-button-icontext k-grid-excel' href='#'><span class='k-icon k-i-excel'></span>Export to Excel</a>");                                               

                     $(resetViewButtonParentDiv).append("<a class='k-button k-button-icontext k-grid-pdf' href='#'><span class='k-icon k-i-pdf'></span>Export to PDF</a>");

                          });               

          },1000);  

 });

Thanks! @Roland_Kind

Roland_Kind

Hi,
the only thing you need to do, is just using the custom.js which i have provided above
I am not a JavaScript expert - but only those few lines will do it.
Hope this helps

Mike_Storms

Awesome... So you can just place this code anywhere in custom.js?

Roland_Kind

Yes, this will just use an already implemented functionality of Kendo UI ... no additional code is needed ...

--> http://docs.telerik.com/kendo-ui/controls/data-management/grid/excel-export

Brad_McKenna

Ridiculousness! Had to try this out, and love the result.

It takes the current grid view (all pages), retains grouping structure and exports to excel in proper layout with no need to manipulate the output.

Great eye and help @Roland_kind!

Mike_Storms

Is there a way to create different grid views with more columns? Also if you custom extensions to WI or Activities how can those be added to the search criteria... Thanks!

Roland_Kind

@Mike_Storms
yes, you can create your own gridviews (at the moment I create them using the SCSM Console (or VS) and publish them to the Portal ...) - the Excel Export will also work on those views (I assume it will work on every grid view - but I have not tested this so far ...). For more Excel customization maybe the following link will help
--> http://docs.telerik.com/kendo-ui/controls/data-management/grid/excel-export#customize-the-excel-document

Jonathan_Boles

Is there a way of retro-fitting this to Portal v4.0.10?

Roland_Kind

I don't know which Kendo UI Version was distributed with Portal 4.x - if it was a newer Version (>= 2014.3.1119) than it might be possible.

Adam_Dzyacky

Running 6.0.2 in a dev capacity as well, but when throw this into custom.js pages won't load their respective content. Pulling the code all is restored to a working order - anyone else experience this?

Jonathan_Boles

Hi @Adam_Dzyacky, was getting the same thing here on 6.0 in our sandbox environment. Also tried on older version (4.0.10) after copying the files it seemed to be looking for to no avail. I managed to get it working in v6.0 for a very brief moment and got a single Excel export and then no dice.

Adam_Dzyacky

You know, it just struck me based on what you just said @jonathan_boles and what I've experienced first hand -  What are the chances that this (blank pages) is caused by another, unrelated custom.js change that we've collectively used from the community that isn't compatible with whatever version of the portal that we tried upgrading to? Just typing outloud here:

• doesn't work in your 4.x
• doesn't work in our 6.x
• experienced identical issue outside of this customization during a routine upgrade
• the only variance that exists between customers is custom.js (and clearly there is a group here that has this working without issue)

Only problem is my theory falls apart here, because I just tried dropping only this customization into custom.js with everything else gone and pages still don't load. Hrmmm....

Roland_Kind

Hi all, thats a little bit strange, but in the meantime I have updated the loader (to check if the grid is loaded) to that one provided by joivan_hedrick  (-> https://community.cireson.com/discussion/1310/custom-grid-view-filter-search-all-grid-columns-in-a-view) - because the timing could be a problem ... I have used this in another prod env. without any problem (also 6.0.2).

btw: did you check that the file '/Scripts/kendo/2016.1.226/jszip.min.js' exists ?
and it is important, that the loading of this file is done, on the top of the custom.js file (frist three lines - it cannot be done inside a function)

Tom_Hendricks

For me, the file does exist (portal v7.0).  A reference to JSZip (object name) from kendo is not valid, however.

The issue here seems to be that jszip is supposed to load before kendo.  Since the portal uses RequireJS, there are special considerations there, as well.  I have not found the solution yet, but hopefully this leads someone in the right direction.

I am also getting errors about certain fonts not being present, etc., but for now I assume that they are other symptoms of jszip not loading in time.

Roland_Kind

Hi all,

the enclosed zip file contains the modified version (alternate loader to check if grid is valid...). in addition to that it might be necessary to "reset your view" to default - with the "reset view" button first.


I have done large exports (> 12.000 rows) without any problems.

Please note the file is named custom_.js

Tom_Hendricks

Thanks, @Roland_Kind .  This is still throwing massive (as in hundreds of lines long) errors on page load, however.  require.js line 13 (uncaught error: mismatched anonymous define() module) and kendo.all.min.js line 30 (Uncaught error: JSZip not found.  Check http://docs.telerik.com/kendo-ui/framework/excel/introduction#requirements for more details).

The kendo docs clearly point to the fact that you need to be able to load JSZip before kendo, and also need to be able to establish the relationship between JQuery, Kendo, and JSZip (in that order) using RequireJS.  The problem is that all this happens in the Cireson code before custom.js is run.

So I am really curious how it is working for you @Roland_Kind.  I can see why it isn't for me, but why would it load in a different order for you?  Answering that question may be the key to getting this to work for everyone.

Roland_Kind

Hi Tom,

thanks for the info - I have changed the loader to use require.js for loading the jszip file. I have enclosed a new version of the custom.js file. In my test environment it works without any issue - do you have the possibility to test it in your test environment ?

Tom_Hendricks

I just tested in portal 7.1.2012.  Someone with a different version should test too.

As for the results....significant progress!

1. Export to Excel now works flawlessly with no errors!
2. Export to PDF generates 3 errors in the console about not being able to find /Content/Kendo/2016.1.226/web/fonts/DejaVu/DejaVuSans.ttf. 

#2 seems like something we ought to be able to solve.  I do not have much time to spend today, but I can look at it next week.  Perhaps other experts here will have it solved by then!

Thanks for working on a fix.  This is an excellent feature you are helping us all enable!

Roland_Kind

Hi Tom,

thanks a lot for the testing and the great news :-) and I am happy to see it works now (at least for the Excel Export)

Tom_Hendricks

I can now confirm that if a DejaVuSans.ttf file is present (and a valid truetype font file) in the /Content/Kendo/2016.1.226/web/fonts/ folder, PDF export also works.

The folder exists and the license txt file for the font is present, but the TTF itself is not there, in my install.  Can anyone else confirm whether this file is present or missing for them?

Roland_Kind

Hi Tom, that are great news & it seems, that there is a difference in Version 6 and 7. In version 6 there are no errors creating the PDF and even the fonts folder does not exists (at least in 6.0.2)

Tom_Hendricks

Since I no longer have a 6.x portal to test:  Does /Content/Kendo/2016.1.226/ exist, or is it just the subfolder(s) you are referring to?  Trying to rule out a different version of Kendo.  In the 5.x portal, I can clearly see that the version of Kendo is different.

Roland_Kind

Hi Tom,

unfortunately my previous answer was incorrect - I messed up the folders

a) \Content\Kendo\2016.1.226  (for the Fonts etc. - but only the license.txt file is available - the same as you described for 7.x)
b) \Scripts\kendo\2016.1.226 (with jszip.js etc.)

both folders are present in 6.0.x - but as I have  mentioned before - I do not receive errors when creating a PDF in 6.0.x

As far as I know the dejavu fonts can be obtained from sourcefrog (MIT License) - therefore I think it is no problem applying the workaround you have mentioned (just copying the dejavusans.ttf to the appropriate directory)

For the Excel/PDF Export I have not added any files to the original Cireson/Kendo Distribution - except the custom.js file

Eugene_Rack

Hi All

I have tried this but when I check my folder structure under scripts\Kendo I have a folder 2013.3.1119 and only have jquery.min.js but no jszip.js

Roland_Kind

Hi

as far as I know you need a Portal Version 6.x or higher to use the Excel Export. It depends on a newer Kendo UI Version (>= 2014.3.1119)