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

By: Ivan Ivanov | Comments | Related: > SharePoint

Problem

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.

Solution

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.

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

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

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.

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;

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.

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.

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

Next we add the function:

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

Add the function:

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

Add the function:

public Byte[] ExportDetails(DataTable DetailsTable)

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

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

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.

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.