Export Data View To Excel In SharePoint 2016 / 2013

In this article, I will show How to

Export Data View Web Part To Excel In SharePoint 2013 / 2016

Scenario

In SharePoint 2013, I created a Data View Web Part with an external database connection as a data source via SharePoint designer.

Export Data View to Excel In SharePoint

When I tried to browse the page, I couldn’t find the SharePoint List Ribbon, also there is no option to export the data view to Excel.

Export Data View to Excel In SharePoint

Cause

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

Workarounds

Option (1)  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 - With Paging.gif

  • 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


Option (2) You can use table2excel Jquery 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.

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

  • From the above ribbon, Click Paging > Display All Items.

display all items

Note: 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:

Export Data View to Excel In SharePoint


<input type="button" id="btnExport" value="Export DV to Excel" />
<hr/>

  • 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


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

  • 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 Excel In SharePoint - preview

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.

Export Data View to Excel In SharePoint


Conclusion

In this article, I have explained

How to Export Data View to Excel In SharePoint 2013 / 2016

See Also
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s