Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: > Sharepoint

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


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.

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

create
stationery

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)

foreach
filebytes

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:

sender

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

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.

stationery

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


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools