This is a simple method to export data directly from an ASP.Net GridView control into an Excel file (CSV formatted).

protected void ExportGridViewDataButton_Click(object sender, EventArgs e)
{
  StringBuilder sb = new StringBuilder();
  foreach (TableCell cel in this.GridViewControl.HeaderRow.Cells)
  {
    sb.Append("\"").Append(cel.Text.Replace(" ", "")).Append("\",");
  }
  sb.Append(((Char)10).ToString());
  foreach (GridViewRow row in this.GridViewControl.Rows)
  {
    foreach (TableCell cel in row.Cells)
    {
      sb.Append("\"").Append(cel.Text.Replace(" ", "")).Append("\",");
    }
    sb.Append(((Char)10).ToString());
  }
  Response.Clear();
  Response.AddHeader("Content-Disposition"
    , "attachment;filename=GridViewExport.csv");
  Response.ContentType = "text/csv";
  Response.Write(sb.ToString());
  Response.Flush();
  Response.End();
}

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;
    }
  }
}

I wrote a simple C# method to open and Excel file and save it as PDF using basic Microsoft Office automation via the Excel COM.  In my case this method was inside a Windows Service that was scheduled to refresh the external data links (to datawarehouse and SSAS cube).  The initial code was problematic in that after the initial execution the output PDF file would often be locked causing the subsequent executions to fail.  Restarting the service would release the file, so it was apparent that the method was not cleanly releasing it's resources at end of execution. After some research and testing I now have a cleanup sequence that seems to reliably releasing all the resources so the service can now run indefinitely without conflict.  Below is a subset of the C# Excel COM code that illustrates the basic steps.

As a side note, while researching this issue I found an interesting article on using OpenOffice to perform a similar operation.  In my case I'm not sure it would work (at least not without further research and extending the method), as I need to refresh external data connections (which was not a feature covered in the sample I found).  However, it did sound like an interesting option as it only requires a few DLL's and not the full application install that the Office COM automation seems to require.  I included a simple version of the OpenOffice code (along with a reference link to the original article I read) below as well.  I will try to research this option later and update my post with the findings. 

Important side note:  Running this code on a Windows 2008 Server will fail with generic file access error if you don't have the folders shown below (i.e. create empty folder to resolve the issue):

• Windows 2008 Server x64: C:\Windows\SysWOW64\config\systemprofile\Desktop

• Windows 2008 Server x86: C:\Windows\System32\config\systemprofile\Desktop

Microsoft Excel Sample Code:

// Start application
Application excel = new ApplicationClass();
// Turn off message boxes
excel.DisplayAlerts = false;

string sourceFilePath = @"C:\SourceFile.xls"; 
string targetFilePath = @"C:\TargetFile.pdf";  

// 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(sourceFilePath, false, true);
Workbooks wkbs = excel.Workbooks;
Workbook wkb = wkbs.Open(sourceFilePath, false, true);

// Refresh data connections and do some formatting
wkb.EnableConnections();
wkb.RefreshAll();
Worksheet sht = wkb.Worksheets["Summary"] as Worksheet;
sht.get_Range("A1").EntireColumn.ColumnWidth = 21;
sht.get_Range("B1", "E1").EntireColumn.AutoFit();
sht.get_Range("A1").Select();

// Delete target file
File.Delete(targetFilePath);

// Export to PDF
sht.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF
, targetFilePath, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard
, true, false, Type.Missing, Type.Missing, false, Type.Missing);

// 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();
Marshal.FinalReleaseComObject(sht);
wkb.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(wkbs);
excel.Quit();
Marshal.ReleaseComObject(excel);
GC.Collect();
GC.WaitForPendingFinalizers();

Here is a good article on cleaning up Excel COM objects: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp.

OpenOffice Sample Code:

/*
You need the following DLL's from OpenOffice and reference them in your project:

cli_basetypes.dll 
cli_cppuhelper.dll 
cli_oootypes.dll 
cli_uno.dll 
cli_ure.dll 
cli_uretypes.dll 
*/

//...
using System;
using uno.util; 
using unoidl.com.sun.star.lang; 
using unoidl.com.sun.star.uno; 
using unoidl.com.sun.star.bridge; 
using unoidl.com.sun.star.frame; 
using unoidl.com.sun.star.text; 
using unoidl.com.sun.star.beans; 
using unoidl.com.sun.star.util;  
//...
  
string sourceFilePath = "file:///C:/SourceFile.xls"; 
string targetFilePath = "file:///C:/TargetFile.pdf";  

// Start OpenOffce or get a reference to an existing session 
XComponentContext localContext = Bootstrap.bootstrap(); 
XMultiServiceFactory multiServiceFactory = (XMultiServiceFactory)localContext.getServiceManager(); 
XComponentLoader componentLoader = (XComponentLoader)multiServiceFactory.createInstance("com.sun.star.frame.Desktop");  

// Set the file open properties
PropertyValue[] openProperties = new PropertyValue[2]; 
// Hidden...
openProperties[1] = new PropertyValue(); 
openProperties[1].Name = "Hidden"; 
openProperties[1].Value = new uno.Any(true);  
// Read only...
openProperties[0] = new PropertyValue(); 
openProperties[0].Name = "ReadOnly"; 
openProperties[0].Value = new uno.Any(true); 
// Open the file 
XComponent sourceDocument = componentLoader.loadComponentFromURL(sourceFilePath, "_blank", 0, openProperties);  

// Conversion parameters - overwrite existing file, use PDF exporter 
PropertyValue[] conversionProperties = new PropertyValue[3]; 
conversionProperties[0] = new PropertyValue(); 
conversionProperties[0].Name = "Overwrite"; 
conversionProperties[0].Value = new uno.Any(true); 
conversionProperties[1] = new PropertyValue(); 
conversionProperties[1].Name = "FilterName"; 
conversionProperties[1].Value = new uno.Any("calc_pdf_Export");  

// Set PDF export parameters 
PropertyValue[] filterData = new PropertyValue[3];  
// JPEG compression quality 70 
filterData[0] = new PropertyValue(); 
filterData[0].Name = "Quality"; 
filterData[0].Value = new uno.Any(70); 
filterData[0].State = PropertyState.DIRECT_VALUE;  
// Max image resolution 300dpi 
filterData[1] = new PropertyValue(); 
filterData[1].Name = "ReduceImageResolution"; 
filterData[1].Value = new uno.Any(true); 
filterData[1].State = PropertyState.DIRECT_VALUE; 
filterData[2] = new PropertyValue(); 
filterData[2].Name = "MaxImageResolution"; 
filterData[2].Value = new uno.Any(300); 
filterData[2].State = PropertyState.DIRECT_VALUE;  
conversionProperties[2] = new PropertyValue(); 
conversionProperties[2].Name = "FilterData"; 
conversionProperties[2].Value = new uno.Any(filterData.GetType(), filterData);  
// Export the PDF 
XStorable xstorable = (XStorable)sourceDoc; 
xstorable.storeToURL(pdfFile, conversionProperties);  

// Close document 
((XCloseable)sourceDoc).close(false); 
((XCloseable)xstorable).close(false);

OpenOffice API documentation can be found here: http://www.openoffice.org/api/.  Here is a link to the artical I used for basis of OpenOffice version of code: http://stackoverflow.com/questions/769246/xls-to-pdf-conversion-inside-net).

One option to converting Excel documents to PDF is using Microsoft Office automation.  However, this requires installing the full application on the server that will be performing the automation (see this link for more details on that: ).  Another alternate solution is to switch to OpenOffice which only requires installing a few DLL's on the automation server.  Another option that I will review in the future is using the "ExcelLibrary" http://code.google.com/p/excellibrary/downloads/list

Microsoft Excel Sample Code:

// Start application
Application excel = new ApplicationClass();
// Turn off message boxes
excel.DisplayAlerts = false;

string sourceFilePath = @"C:\SourceFile.xls"; 
string targetFilePath = @"C:\TargetFile.pdf";  

// 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(sourceFilePath, false, true);
Workbooks wkbs = excel.Workbooks;
Workbook wkb = wkbs.Open(sourceFilePath, false, true);

// Refresh data connections and do some formatting
wkb.EnableConnections();
wkb.RefreshAll();
Worksheet sht = wkb.Worksheets["Summary"] as Worksheet;
sht.get_Range("A1").EntireColumn.ColumnWidth = 21;
sht.get_Range("B1", "E1").EntireColumn.AutoFit();
sht.get_Range("A1").Select();

// Delete target file
File.Delete(targetFilePath);

// Export to PDF
sht.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF
, targetFilePath, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard
, true, false, Type.Missing, Type.Missing, false, Type.Missing);

// 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();
Marshal.FinalReleaseComObject(sht);
wkb.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(wkbs);
excel.Quit();
Marshal.ReleaseComObject(excel);
GC.Collect();
GC.WaitForPendingFinalizers();

Here is a good article on cleaning up Excel COM objects: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp.

OpenOffice Sample Code:

/*
You need the following DLL's from OpenOffice and reference them in your project:

cli_basetypes.dll 
cli_cppuhelper.dll 
cli_oootypes.dll 
cli_uno.dll 
cli_ure.dll 
cli_uretypes.dll 
*/

//...
using System;
using uno.util; 
using unoidl.com.sun.star.lang; 
using unoidl.com.sun.star.uno; 
using unoidl.com.sun.star.bridge; 
using unoidl.com.sun.star.frame; 
using unoidl.com.sun.star.text; 
using unoidl.com.sun.star.beans; 
using unoidl.com.sun.star.util;  
//...
  
string sourceFilePath = "file:///C:/SourceFile.xls"; 
string targetFilePath = "file:///C:/TargetFile.pdf";  

// Start OpenOffce or get a reference to an existing session 
XComponentContext localContext = Bootstrap.bootstrap(); 
XMultiServiceFactory multiServiceFactory = (XMultiServiceFactory)localContext.getServiceManager(); 
XComponentLoader componentLoader = (XComponentLoader)multiServiceFactory.createInstance("com.sun.star.frame.Desktop");  

// Set the file open properties
PropertyValue[] openProperties = new PropertyValue[2]; 
// Hidden...
openProperties[1] = new PropertyValue(); 
openProperties[1].Name = "Hidden"; 
openProperties[1].Value = new uno.Any(true);  
// Read only...
openProperties[0] = new PropertyValue(); 
openProperties[0].Name = "ReadOnly"; 
openProperties[0].Value = new uno.Any(true); 
// Open the file 
XComponent sourceDocument = componentLoader.loadComponentFromURL(sourceFilePath, "_blank", 0, openProperties);  

// Conversion parameters - overwrite existing file, use PDF exporter 
PropertyValue[] conversionProperties = new PropertyValue[3]; 
conversionProperties[0] = new PropertyValue(); 
conversionProperties[0].Name = "Overwrite"; 
conversionProperties[0].Value = new uno.Any(true); 
conversionProperties[1] = new PropertyValue(); 
conversionProperties[1].Name = "FilterName"; 
conversionProperties[1].Value = new uno.Any("calc_pdf_Export");  

// Set PDF export parameters 
PropertyValue[] filterData = new PropertyValue[3];  
// JPEG compression quality 70 
filterData[0] = new PropertyValue(); 
filterData[0].Name = "Quality"; 
filterData[0].Value = new uno.Any(70); 
filterData[0].State = PropertyState.DIRECT_VALUE;  
// Max image resolution 300dpi 
filterData[1] = new PropertyValue(); 
filterData[1].Name = "ReduceImageResolution"; 
filterData[1].Value = new uno.Any(true); 
filterData[1].State = PropertyState.DIRECT_VALUE; 
filterData[2] = new PropertyValue(); 
filterData[2].Name = "MaxImageResolution"; 
filterData[2].Value = new uno.Any(300); 
filterData[2].State = PropertyState.DIRECT_VALUE;  
conversionProperties[2] = new PropertyValue(); 
conversionProperties[2].Name = "FilterData"; 
conversionProperties[2].Value = new uno.Any(filterData.GetType(), filterData);  
// Export the PDF 
XStorable xstorable = (XStorable)sourceDoc; 
xstorable.storeToURL(pdfFile, conversionProperties);  

// Close document 
((XCloseable)sourceDoc).close(false); 
((XCloseable)xstorable).close(false);

OpenOffice API documentation can be found here: http://www.openoffice.org/api/.  Here is a link to the artical I used for basis of OpenOffice version of code: http://stackoverflow.com/questions/769246/xls-to-pdf-conversion-inside-net).

When adding up several cells of positive and negative decimals (that total 0) I was instead getting a scientific exponential notation like 1.23E+10.  To solve this problem I changed the "Number Format" property on the calculated cell from "General" to "Number" with a fixed set of decimal places and the issue went away.

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);
      }
    }

  }
}

I wrote an Excel automation process that worked fine as a Console Application, then I converted it to a Windows Service and it would error out at the point of trying to open the file with the following file access error shown below. 

To see a copy of the complete Windows Service code view this blog entry: http://blog.tutorem.com/post/2011/12/12/Windows-Automation-Service-to-Refresh-Excel-Workbook-Data-and-Email-PDF-Export.aspx

--------------------------------------------------------------------------------------------------
SOURCE: Microsoft Office Excel
MESSAGE: Microsoft Office Excel cannot access the file 'C:\PathToFile\File.xlsx'.

There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
--------------------------------------------------------------------------------------------------

This did not make sense as nothing changed between the Console Application code and the Windows Service code.  So, I assumed it was a permission issue.  Tried all sorts of permissions tests with folders/file, as well as the windows service login account, etc.  Still no luck.  Then found a post with a simple solution shown below:

http://social.msdn.microsoft.com/For...?prof=required

For 64-bit (x64), create this folder:
C:\Windows\SysWOW64\config\systemprofile\Desktop

For 32-bit (x86), create this folder:
C:\Windows\System32\config\systemprofile\Desktop

Here are a couple of custom number formats that allow formatting Excel for Millions.

Sample Number: 1,598,999

Use this format string: $#,##0.0,, ;[Red](#,##0.0,,);- ;

To display this: $1.6

Use this format string: $#,##0.0,,\M ;[Red](#,##0.0,,\M);- ;

To display this: $1.6M

using System;

namespace TempConsole

{

  class Program

  {

    enum DayOfWeek { Sunday = 1, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday };

    static void Main(string[] args)

    {

      Console.Title = "Sample Iterating Enum";

      foreach (DayOfWeek t in Enum.GetValues(typeof(DayOfWeek)))

      {

        Console.WriteLine(t.ToString() + " = " + (int)t);

      }

      Console.ReadKey();

    }

  }

}

Excerpt from: http://www.switchonthecode.com/tutorials/csharp-tutorial-image-editing-rotate

private Bitmap rotateImage(Bitmap b, float angle)
{
  //create a new empty bitmap to hold rotated image
  Bitmap returnBitmap = new Bitmap(b.Width, b.Height);
  //make a graphics object from the empty bitmap
  Graphics g = Graphics.FromImage(returnBitmap);
  //move rotation point to center of image
  g.TranslateTransform((float)b.Width/2, (float)b.Height / 2);
  //rotate
  g.RotateTransform(angle);
  //move image back
  g.TranslateTransform(-(float)b.Width/2,-(float)b.Height / 2);
  //draw passed in image onto graphics object
  g.DrawImage(b, new Point(0, 0));
  return returnBitmap;
}

This formula will take the Date value from cell A1 and convert it to either the word null if no date is in the cell or a string representation of the date in small quotes (usefull for generating SQL insert/update strings):

=IF(A1="","null","'"&TEXT(MONTH(A1),"00") & "-" & TEXT(DAY(A1),"00") & "-" & TEXT(YEAR(A1), "0000")&"'")