Joseph Michael Pesch
VP Programming

LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

by 27. December 2011 15:54

LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

One workaround for this is to convert the LINQ to Entities object to a LINQ to Objects object by adding .AsEnumerable() as in the sample below.

var qry = from itm in obj.Booksheets.AsEnumerable()
          where bks.Purchases.FirstOrDefault() == null
          select new
          {
            FormattedPrice = bks.WebPrice.ToString("#,###"),
          };

Tags:

LINQ | LINQ to Entities

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

Excel Automation Issue Access File When Running as a Windows Service

by 12. December 2011 07:30

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

Tags:

C# | Excel

CMD Script Backup Folders to Alternate Location with Dated Folder Structure

by 9. December 2011 23:52

CMD script to backup file folder (with sub-folders) to an alternate location with dated folder structure:

set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
echo hour=%hour%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
echo min=%min%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
echo secs=%secs%

set year=%date:~-4%
echo year=%year%
set month=%date:~4,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
echo month=%month%
set day=%date:~7,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%
echo day=%day%

set datetimef=%year%%month%%day%_%hour%%min%%secs%

echo datetimef=%datetimef%

mkdir d:\backup\d_inetpub_%datetimef%

xcopy d:\inetpub d:\backup\d_inetpub_%datetimef% /S /H /Y /C

Tags: