Joseph Michael Pesch
VP Programming

C# Upload Local Document to SharePoint Site

by 22. February 2012 10:17

Below is a sample windows service that will run daily.  This method is used to open a local Excel file, refresh it's data connection(s), save the updated file locally and finally, upload the local file to a SharePoint site.

Command prompts to Delete and Create the Windows Service:

sc delete ServiceNameHere
sc create ServiceNameHere binpath= "c:\pathtoexe\AutomationService.exe"

Sample App.Config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="FileDictionary" type="System.Configuration.DictionarySectionHandler" />
  </configSections>
  <FileDictionary>
    <add key="C:\Data\File1.xlsx" value="http://mysharepoint/sites/reports/File1.xlsx" />
    <add key="C:\Data\File2.xlsx" value="http://mysharepoint/sites/reports/File2.xlsx" />
  </FileDictionary>
</configuration>

Sample Code:

using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Net;
using System.Runtime.InteropServices;
using System.ServiceProcess;
using Microsoft.Office.Interop.Excel;

namespace AutomationService
{
  public partial class AutomationServiceInstance : ServiceBase
  {

    string path = ConfigurationManager.AppSettings["FilePath"];
    // Go back one day so we run on start...
    DateTime lastRun = DateTime.Now.AddDays(-1);

    public AutomationServiceInstance()
    {
      InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {
      System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
        , Char.ToString('\r') + Char.ToString('\n') 
        + "Starting service ... " + DateTime.Now.ToString());
      if(System.IO.File.Exists(path + @"\LastRun.txt"))
      {
        // If last run time file exists load the date/time from the file
        lastRun = Convert.ToDateTime(System.IO.File.ReadAllText(path + @"\LastRun.txt").Trim());
      }
      BackgroundWorker wkr = new BackgroundWorker();
      wkr.DoWork += TimeCycle;
      wkr.RunWorkerAsync();
    }

    protected override void OnStop()
    {
      System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
        , Char.ToString('\r') + Char.ToString('\n')
        + "Stopping service ... " + DateTime.Now.ToString());
    }


    protected void TimeCycle(object sender, DoWorkEventArgs e)
    {
      do
      {
        lastRun = Convert.ToDateTime(System.IO.File.ReadAllText(path + @"\LastRun.txt").Trim());
        System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n')
          + "Checking time ... " + DateTime.Now.ToString()
          + " lastRun: " + lastRun + "; curTime: " + DateTime.Now.ToString());
        if (!lastRun.DayOfYear.Equals(DateTime.Now.DayOfYear) 
          && DateTime.Now.TimeOfDay >= new TimeSpan(8,15,0)) ProcessIt();
        System.Threading.Thread.Sleep(60 * 1000);
      } while (true);
    }

    protected void ProcessIt()
    {
      // Key is the local source file, Value is the target web URL
      Hashtable tbl = (Hashtable)ConfigurationManager.GetSection("FileDictionary");
      foreach (DictionaryEntry de in tbl)
      {
        try
        {
          Application excel = new ApplicationClass();
          excel.DisplayAlerts = false;
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Begin processing ... " + DateTime.Now.ToString());
          string srcFile = de.Key.ToString();
          string outFile = de.Value.ToString();
          // Open the file 
          // Important, do not double up on objects in COM 
          // Notice the Workbooks variable used to open the workbook 
          // DO NOT DO THIS: Workbook wkb = excel.Workbooks.Open(srcFile, false, true); 
          Workbooks wkbs = excel.Workbooks;
          Workbook wkb = wkbs.Open(srcFile, false, true);
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     workbook open: " + DateTime.Now.ToString());
          wkb.EnableConnections();
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     connections enabled: " + DateTime.Now.ToString());
          wkb.RefreshAll();
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     workbook refreshed: " + DateTime.Now.ToString());
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Processing complete ... " + DateTime.Now.ToString());
          // Clean up to release resources 
          // IMPORTANT: This cleanup sequence was required when running 
          // this inside a service that remains open and recycles on a schedule 
          // otherwise multiple EXCEL processes would be running and resources 
          // left open causing file access problems and memory leaks. 
          GC.Collect();
          GC.WaitForPendingFinalizers();
          wkb.Close(false, Type.Missing, Type.Missing);
          Marshal.ReleaseComObject(wkb);
          Marshal.ReleaseComObject(wkbs);
          excel.Quit();
          Marshal.ReleaseComObject(excel);
          GC.Collect();
          GC.WaitForPendingFinalizers();
          if (WebClientUpload(srcFile, outFile, path))
          {
            lastRun = DateTime.Now;
            System.IO.File.WriteAllText(path + @"\LastRun.txt", lastRun.ToString());
          }
        }
        catch (Exception ex)
        {
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Exception occurred ... " + DateTime.Now.ToString());
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n') + ex.Message);
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n') + ex.StackTrace);
        }
      }
    }
    static bool WebClientUpload(string SourcePath, string TargetPath, string LogPath)
    {
      WebClient wc = new WebClient();
      wc.UseDefaultCredentials = true;
      try
      {
        byte[] response = wc.UploadFile(TargetPath, "PUT", SourcePath);
      }
      catch (WebException ex)
      {
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n')
          + "Exception occurred ... " + DateTime.Now.ToString());
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') + ex.Message);
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') + ex.StackTrace);
        return false;
      }
      return true;
    }
  }
}

Tags:

C# | Excel | Sharepoint

Comments are closed