Joseph Michael Pesch
VP Programming

Windows Automation Service to Refresh Excel Workbook Data and Email PDF Export

by 12. December 2011 08:33

This was built to refresh an Excel file that has pivot tables built agains SSAS cube, format the spreadsheet, export it to PDF and email the PDF file. 

IMPORTANT NOTE: See this blog entry for potential issue when running Excel Automation code inside of a Windows Service: http://blog.tutorem.com/post/2011/12/12/Excel-Automation-Issue-Access-File-When-Running-as-a-Windows-Service.aspx

using System;
using System.ComponentModel;
using System.Configuration;
using System.ServiceProcess;
using Microsoft.Office.Interop.Excel;

namespace AutomationService
{
  public partial class AutomationServiceInstance : ServiceBase
  {

    string path = @"C:\ExcelAutomation";
    // Go back one day so we run on start...
    DateTime lastRun = DateTime.Now.AddDays(-1);
    Application excel = new ApplicationClass();

    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
        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)
    {
      excel.DisplayAlerts = false;
      do
      {
        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()
    {
      try
      {
        System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') 
          + "Begin processing ... " + DateTime.Now.ToString());
        string srcFile = path + @"\SourceFile.xlsx";
        string outFile = path + @"\OutputFile.pdf";
        Workbook wkb = excel.Workbooks.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());
        Worksheet sht = wkb.Worksheets["Pipeline Totals"] as Worksheet;
        // Format column A to a fixed width
        sht.get_Range("A1").EntireColumn.ColumnWidth = 20;
        // Format columns B through Z to auto fit width
        sht.get_Range("B1", "Z1").EntireColumn.AutoFit();
        // Navigate to first cell
        sht.get_Range("A1").Select();
        System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') 
          + "     formatting complete: " + DateTime.Now.ToString());
        sht.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF
          , outFile, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard
          , true, false, Type.Missing, Type.Missing, false, Type.Missing);
        System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') 
          + "     export complete: " + DateTime.Now.ToString());
        wkb.Close(false, Type.Missing, Type.Missing);
        excel.Quit();
        MailUtil.SendMail("sender@domain.com", "Sender Name"
          , ConfigurationManager.AppSettings["EmailDistributionListTo"]
          , ConfigurationManager.AppSettings["EmailDistributionListCC"]
          , ConfigurationManager.AppSettings["EmailDistributionListBCC"]
          , "Excel Automation Report", false, "", outFile);
        System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') 
          + "     mail sent: " + DateTime.Now.ToString());
        sht = null;
        wkb = null;
        lastRun = DateTime.Now;
        System.IO.File.WriteAllText(path + @"\LastRun.txt", lastRun.ToString());
        System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') 
          + "Processing complete ... " + DateTime.Now.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);
      }
    }

  }
}

Tags:

C# | Excel

Pingbacks and trackbacks (1)+

Comments are closed