How to: Alternate to SP Timer Job – Export SharePoint List to Excel Console Application

Any SharePoint Timer Job you developed is a farm solution.  From SharePoint 2013 onward, Microsoft is recommending to avoid farm solutions.  They are already not supported in Office 365.

The following are the alternatives to the SharePoint timer job.

  • Windows Azure Web Jobs
  • Console application or Windows service running in Windows task scheduler.

Here we are discussing about the second option, creating console application and running in Windows task scheduler.  The simple application we are going see is exporting a SharePoint List to Excel using table format.

Step 1:

Create a console application using .NET Framework 4.5

Step 2:

Copy the SharePoint Client Object Model Dlls from the SharePoint 2013 server.

Export2Excel_02

Step 3:

Add the following references to the console application.

  • Microsoft.SharePoint.Client
  • Microsoft.SharePoint.Client.Runtime
  • System.Drawing
  • System.Web

Export2Excel_03

 Step 4:

Add a new item “Settings File” to store the SharePoint Server Url, List Name, Whether SharePoint Online or On-premisis, Username and Password

Export2Excel_04

 

Step 5.1:

Add the above settings in the setting file.  In our scenario we are connecting to on-premises server, so we will be setting “SPOnline” property to “False”

Export2Excel_05

 

Step 5.2:

To use the SharePoint Online (O365) using the following properties as shown in the screenshot.

Export2Excel_06

Step 6:

The following code use to connect to on-premises server or online depending on your settings.

using (SP.ClientContext clientContext = new SP.ClientContext(Settings.Default.SiteUrl))
{
if (Settings.Default.SPOnline)
{
clientContext.Credentials = new SP.SharePointOnlineCredentials(Settings.Default.username, pwdSecure);
}
else
{
clientContext.Credentials = new NetworkCredential(Settings.Default.username, pwdSecure);
}
}
 

Step 7:

Once connected to the SharePoint Online or on-premises, we connect to the SharePoint List, get the default view and retrieve the data to the data-table and then finally excel file will be created using table format.

We are not using any Excel Library to create rows and columns, for simplicity using just html.  You can enhance and change the code as per your requirements like using Excel Library to actually create the cells.

Sample List

Export2Excel_07

Excel Output

Export2Excel_08

The complete download is available at the Tech net Gallery.  Please download and rate the gallery.

Alternate to Timer Job – Console Application to Export SP List to Excel

 Step 8:

Open the windows task scheduler and select create task.  Enter the task Name, description etc..

Export2Excel_09

Step 9:

Click on Triggers tab and click on New button to create a schedule.  Schedules can be daily, weekly, monthly or yearly.  In our scenario, we are creating daily schedule as shown in the below screenshot.  You can also add the additional settings, if you want to run the job daily + every hour or minutes etc.

Export2Excel_10

 

Step 10:

Final step is to assign our console application to be running as the part of job.  Click on Actions tab and then click on new.  In the New Action window, select the program to run.

Export2Excel_11

 

Happy coding….

Conclusion

You can also use the Windows Azure Web Jobs to run your jobs.  Please refer to the following article.

SharePoint Timer Jobs running as Windows Azure Web Jobs

This entry was posted in SharePoint 2013. Bookmark the permalink.

Leave a Reply