How To Programmatically Export SharePoint List to Excel

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

Solution

To create this solution you should have a basic understanding about the following:

Creating SharePoint lists, able to add and remove fields as well as setting a predefine value to a field and work with list content types.
You should have "full control" permissions in the target site of the application.
That you have Visual Studio 2008 installed on the machine you are developing this solution.
You have a new blank site created for the application using unique permissions.
You have permission to deploy a webpart in your environment.

The purpose of this article is to show you how you can build granular control of the export functionality of SharePoint. With a single centralized list, you want different Excel files for each of your suppliers, as well as to distinguish the exported items from the new items in the list.

So, we start by creating a SharePoint list where you will have all requests as list items. For the presentation we have added 3 fields:

Product: is a choice field.
Quantity: is a number field.
Status: is a single line field, and is hidden in the new form by using the "hide" option in content types fields.

Users make the request by adding a new item to the list:

Now if you notice the "Status" field we have set this field with a default value of "New" and by that we know it is a new item which hasn't been exported.

Your employees need to be able to choose a set of all new requests, export them in an Excel file and also the "Status" field must be updated upon exporting the items.

A few reasons to use the following method is that you can not do this with out of the box "Export to Spreadsheet" functionality. And if you need to update the "Status" field of each item it would be time consuming to do it one by one.

FFor this reason we are going to create a webpart that will:

Give you ability to choose which requests/items to be exported.
Change the "Status" field value of the request/item upon exporting it to the Excel file.
And last, create the Excel file.

In order to make the webpart we will use Visual Studio 2008. On the Navigation Pane from the "File" menu, select "New Project". We will name this project "WPStationery". 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 WPStationeryExportToExcel

Now open the file WPStationeryToExcel.cs

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

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:

Next, we add the function Export() which will send our list items to be exported in the Excel file. (Note that we tie the function in the webpart to call a specific list name, as in our case the list name is "Stationery".) You can see this in the following line: SPList list = webE.Lists["Stationery"];

Here is the complete code of Export():

public void Export(List<int> ids)
{ 
    DataTable table = new DataTable(); 
    try 
    { 
        SPSite site = SPContext.Current.Site; 
        SPWeb web = SPContext.Current.Web; 
        SPSecurity.RunWithElevatedPrivileges(delegate() 
        { 
            using (SPSite siteE = new SPSite(site.ID)) 
             { 
                using (SPWeb webE = siteE.OpenWeb(web.ID)) 
                { 
                    webE.AllowUnsafeUpdates = true; 
                    SPList list = webE.Lists["Stationery"]; 
                    table.Columns.Add("Product", typeof(string)); 
                    table.Columns.Add("Quantity", typeof(Decimal)); 
                    DataRow newRow; 
                    GridView gv = new GridView(); 
                    foreach (SPListItem item in list.Items) 
                    { 
                        if (ids.Contains(Convert.ToInt32(item["ID"].ToString())) && (item["Status"].ToString() == "New")) 
                        { 
                             newRow = table.Rows.Add(); 
                             newRow["Product"] = item["Product"].ToString(); 
                             newRow["Quantity"] = Convert.ToDecimal(item["Quantity"].ToString()); 
                             item["Status"] = "Exported"; 
                             item.Update(); 
                        } 
                     } 
                     SPBoundField boundField = new SPBoundField(); 
                     boundField.HeaderText = "Product"; 
                     boundField.DataField = "Product"; 
                     gv.Columns.Add(boundField); 
                     boundField = new SPBoundField(); 
                     boundField.HeaderText = "Quantity"; 
                     boundField.DataField = "Quantity"; 
                     boundField.ControlStyle.Width = new Unit(120); 
                     gv.Columns.Add(boundField); 
                     gv.AutoGenerateColumns = false; 
                     gv.DataSource = table.DefaultView; 
                     gv.DataBind(); 
                     gv.AllowSorting = false; 
                     HttpContext.Current.Response.ClearContent(); 
                     HttpContext.Current.Response.ClearHeaders(); 
                     string attachment = "attachment; filename=export" + "_" + DateTime.Now.ToShortTimeString() + ".xls"; 
                     HttpContext.Current.Response.AddHeader("content-disposition", attachment); 
                     HttpContext.Current.Response.ContentType = "application/Excel"; 
                     StringWriter sw = new StringWriter(); 
                     HtmlTextWriter htw = new HtmlTextWriter(sw); 
                     gv.RenderControl(htw); 
                     HttpContext.Current.Response.Write(sw.ToString()); 
                     HttpContext.Current.Response.Flush(); 
                     HttpContext.Current.Response.Close(); 
                     HttpContext.Current.Response.End(); 
                     webE.AllowUnsafeUpdates = false; 
                } 
            } 
        }); 
    } 
    catch (Exception ex) 
    { 
        StringWriter sw = new StringWriter(); 
        HtmlTextWriter htw = new HtmlTextWriter(sw); 
        HttpContext.Current.Response.Write(ex.ToString()); 
     }

And now we need to implement the function btn_Click

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

The final step is to go to your Stationery list, from the Site Actions menu, select Edit Page, and add the new built webpart named WPStationeryExportToExcel.

Exit Edit mode and you can now test the new functionality we have just built.

Enter id's you would like to be exported separated with comma's and press Export

After you hit the Export button, refresh the page or re-open the same page and you will notice the changes on the items Status field...the id's you entered in the textbox have been exported and updated.

Conclusion

The export function can be enriched in various scenarios. You may use it in cases where you may want to restrict access or visibility of items to your employees or customers yet give them the ability to get the data sorted in a certain way. This can also be used with the need for a query file or to pay for the license for "Edit in Datasheet" feature (requiring Office Professional) in SharePoint, or in cases where you don't have a connection between two systems but you require a data feed from one application to another.