Export Data View To Excel In SharePoint 2016 / 2013

In this article, we will learn How to Export Data View To Excel In SharePoint 2013 / 2016.


Export Data View Web Part To Excel In SharePoint

In SharePoint 2013 and 2016, I have created a Data View Web Part with an external database connection as a data source using SharePoint Designer.

add data view web part in SharePoint Deisnger

But When browsing the page, I couldn’t find the SharePoint List Ribbon, also there is no option to export the data view to Excel as shown below.

Export Data View to Excel In SharePoint

You might also like to read Data View disabled in SharePoint Designer 2013

Cause

Unfortunately, there is no an OOTB option to export Data View with an external database connection in SharePoint.

How to Export Data View To Excel In SharePoint?

As a workaround to Export Data View To Excel In SharePoint, you have the below two options:

  • Use Internet Explorer.
  • Use JQuery custom code.

1) Export Data View Web Part to Excel In SharePoint using Internet Explorer

You can easily use Internet Explorer to Export the Data View to Excel by doing the following:

Steps
  • Open SharePoint Designer, Create a new web part page.
  • Add a new Data View Web Part from the database connection as a data source.
  • From the above ribbon, Click Paging > Display All Items.
Export Data View to Excel In SharePoint
  • Save and Browse the page in Internet Explorer.
  • Right-click on the data view > Select Export to Microsoft Excel.
Export Data View to Excel In SharePoint -Export To Excel Via IE
Export Data View to Excel

2) Export Data View Web Part to Excel In SharePoint using JQuery

Beside the above option, you can use table2excel JQuery to export the Data view web part to Excel, by doing the following:

Steps
  • Open SharePoint Designer, Create a new web part page.
  • Add a new Data View Web Part from the database connection as a data source.

In case of the Date View button is grayed out in SharePoint Designer check Data View is disabled in SharePoint Designer 2013.

  • From the above ribbon, Click Paging > Display All Items.
display all items for data view in SharePoint Designer

Note: In my case, I am working on Export Data View to Excel With Paging Option.

  • Set an ID for the Dataview Table that holds the Fields as shown below:
Export Data View to Excel In SharePoint - Set ID
  • Above the table add an Export button as shown below:
"input id="btnExport" type="button" value="Export DV to Excel"
Add Export button for Data View
  • Go Up, below the “meta” tag and add the below script.
    • Note: Don’t forget to change the table ID with your Table ID at $("#MQassas").table2excel({
Export Data View to Excel In SharePoint
  • Browse the page again > Click on the Export button, the data view should be now exported to excel as shown below:
Export Data View to CSV In SharePoint

By the way, you can use the above script directly in Script Editor Web Part, but any new change in the page via SharePoint designer will reset any customization (Script Editor Web Part) added via the browser.

Add script editor
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="//cdn.rawgit.com/rainabba/jquery-table2excel/1.1.0/dist/jquery.table2excel.min.js"></script>
<script> 
$(document).ready(function() { 
$("#btnExport").click(function(){ 
$("#MQassas").table2excel({ exclude: ".noExl", name: "Export DataView to Excel", filename: "myFileName" + new Date().toISOString().replace(/[\-\:\.]/g, ""),  fileext: ".xls",  exclude_img: true,  exclude_links: true,  exclude_inputs: true  });  });  });
</script>

Conclusion

The data view web part doesn’t have the full functionality ribbon like the Export to Excel feature. so here, we have provided two workarounds to can export data view to excel in SharePoint.

Applied To
  • SharePoint 2016.
  • SharePoint 2013.
See Also

Leave a Reply