Programmatically Export SharePoint List to Excel and Save it to a Document Library

By:   |   Comments   |   Related: > SharePoint


I have a small online business selling consumer goods built on SharePoint. I would like to export new requested items from a SharePoint list and send the Excel file to my suppliers. I would also like my employees to be able to manually choose which items to be exported to the Excel file...

The solution from the previous tip fulfills these requirements, but there are some disadvantages:

  • The file is stored on local user's computer/hard drive.
  • The email to supplier has to be send manually.
  • In the previous solution, first the status is changed and after that HttpContext.Current.Response is changed to display content in Excel, so if a user presses "Cancel", the status will be changed, and there won't be a file.
  • After the file is exported the user has to refresh or re-open the page to see that the status is changed.

The purpose of this article is to show how to build an automated export of SharePoint list items.

These would have different Excel files for each  supplier, and a way to distinguish the previously exported items from new items in the list.

The generated Excel files will be stored in a document library, and the status will be updated. An automated email will be sent to the supplier.

First , we create a list as described on page 1 of  the previous tip.

Now we are going to write a new web part. In order to make the web part we will use Visual Studio 2008. On the Navigation Pane from the "File" menu, select "New Project". We will name this project as "WPStationeryDocLib". Under "Project Types" select SharePoint, and under "Visual Studio installed Templates" select "WebPart Template", type the project name and press OK.

new project

Next is to select the trust level for this project, and we chose Partial Trust.

select trust level

For better visibility we rename our default name of the project folder from WebPart1 to WPStationeryExportToExcelDocLib

solution explorer

Now open the file WPStationeryExportToExcelDocLib.cs.

Next we need to add a reference to System.Data, by right-clicking on the References folder and choose from the .Net tab - System.Data and click OK.

add reference
system data

After the reference System.Data is added, add the following references as shown in the image:

-using System.Data;

-using System.Web;

-using System.IO;

-using System.Collections.Generic;

-using System.Xml;

-using System.Xml.Xsl;

-using System.Text;

-using System.Net.Mail;

using system

Here we have to add the textboxes, a label for the textboxes and a button as well as change the CreateChildControls function to be able to render the controls.

public class

Next we need to create a Document Library where the generated files will be stored. Go to the site where the new "Stationery" list is created (from the previous tip) and create a new document library, named "StationeryFiles".


In the library we have to create a column named "SupplierEmail". The users have to have Read and Add permissions on the library in order to create the file into the library.

stationery files
supplier email

Now we need to return to Visual Studio and add the function AddToDocLib(Byte[] fileBytes)

private void

Next we add the function:

private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds)

write string

Add the function:

private Byte[] ExportToExcel(DataSet dsExport, string[] sHeaders, string[] sFileds)

export to excel

Add the function:

public Byte[] ExportDetails(DataTable DetailsTable)

public byte

Now, we add the function:

public void Export(List ids, string supplierEmail)


As you can see in the code the first step is to prepare DataSet, the second step is to generate the file, the third step is to add the file in the SharePoint document library which we created and the final step is to change the status of the items. If an error occurs in any of the steps, an exception will be thrown and the status will not be changed. This is a big advantage compared to the previous approach. In this approach after exporting, we have a control in code, so we are able to do what it is necessary, depending on the requirements.

Next we add a function called 'SendMail '.

public static

Next is to implement the "btn_Click" function:


One more great advantage of this method is that we first save the files in a Sharepoint Document Library, not on the local computer. We are able to manipulate permissions and to track the time from one action to another.

Now we are ready to build our code into a solution and by right-clicking on the WPStationeryDocLib Project click "Build", then again right-click on the project, click "Package" and once again right-click on the project and click "Deploy".

solution explorer

The last step is to go to your Stationery list, from the Site Actions menu and select Edit Page, and add the new web part named WPStationeryExportToExcelDocLib.

web parts

Exit "Edit mode" from the page you have just added your web-part to. Test the new functionality we have just built. Enter the id's you would like to be exported separated by commas and press Export.


Notice that you don't need to refresh or re-open the page to see that the status change. In the previous example you had to refresh or re-open the page.

all items
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ivan Ivanov Ivan Ivanov

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms