Joseph Michael Pesch
VP Programming

Excel to PDF Conversion Using Office Automation

by 31. January 2012 08:17

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).

Tags:

C# | Excel

Excel to PDF Conversion Using Office Automation and/or Open Office

by 30. January 2012 10:23

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).

Tags:

C# | Excel

C# Determine if Folder is Empty

by 28. January 2012 21:25
if (!Directory.EnumerateFileSystemEntries(FolderPath).Any())
{ 
  System.Diagnostics.Debug.WriteLine("Folder is empty");
}

Tags:

C#

SQL Server Management Studio "Invalid Object Name ..." when Object Does Exist

by 25. January 2012 10:06

I was struggling to understand why the SSMS intellisense was highlighting certain objects in my query window and indicating that they were not valid; when in fact, they did exist.  I tried closing and re-opening the studio, refreshing the local cache (Edit -> IntelliSense -> Refresh Local Cache (CRTL+Shift+R)).  All to no avail.  I finally realized the issue was due to the fact that the top of my SQL script had statements to drop and recreate some of the objects dynamically (e.g. if exists(...) drop XXX go select * into XXX from YYY).  It turns out that even though the objects actively existed in the database the fact that the script had a conditional drop statement of the objects, the IntelliSense seemed to override reality with fiction (i.e. thinking they are or will be dropped, perhaps?).  Anyway, if I simply commented out the drop statements all the errors went away.  Strange...

Tags:

SQL Server

SharePoint 2010 Default Log File Location

by 24. January 2012 07:29

Since I always seem to forget the path to the SharePoint 2010 default log folder... Here it is: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS

Tags:

Sharepoint

Transform URL List into HTML Unordered List

by 23. January 2012 16:16
This method will take a flat list of fully qualified URL's and transform them into nested bullets (i.e. HTML unordered lists).  It is a simple method to convert the flat list of URL's into a psuedo tree view.  Here is the C# code to transform the list.
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Xml;
using System.Xml.Xsl;

namespace UrlListToSiteMap
{
  public class Program
  {
    public static void Main(string[] args)
    {
      /*
         This method accepts a text file of fully qualified URLs 
         and will convert into HTML unordered list. 
      */
      string path = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
      List urlRecords = File.ReadAllLines(path + @"\UrlList.txt").ToList();
      urlRecords.Sort();
      XmlDocument doc = new XmlDocument();
      doc.AppendChild(doc.CreateNode(XmlNodeType.Element, "SiteMap", ""));
      XmlNode rootNod = doc.ChildNodes[0];
      foreach (string url in urlRecords)
      {
        XmlNode pathNod = rootNod;
        int iNod = 0;
        int iNods = url.ToLower()
          .Replace(@"http://", "").Replace("'", "").Split('/').Count();
        foreach (string nod in url.ToLower()
          .Replace(@"http://", "").Replace("'", "").Split('/'))
        {
          iNod++;
          System.Diagnostics.Debug.WriteLine(nod);
          XmlNode subNod = pathNod.SelectSingleNode("Node[@URL='" + nod + "']");
          if (subNod == null)
          {
            System.Diagnostics.Debug.WriteLine(pathNod.Name);
            string nodType = iNod.Equals(iNods) ? "Leaf" : "Node";
            subNod = doc.CreateNode(XmlNodeType.Element, nodType, "");
            XmlAttribute atr = doc.CreateAttribute("URL");
            atr.Value = nod;
            subNod.Attributes.Append(atr);
            pathNod.AppendChild(subNod);
          }
          pathNod = subNod;
        }
      }
      doc.Save(path + @"\SiteMap.xml");
      XslTransform xslt;
      xslt = new XslTransform();
      xslt.Load(path + "SiteMap.xslt");
      xslt.Transform(path + "SiteMapNew.xml", path + "SiteMap.html");
    }
  }
}

Attached is the XSLT file for the transformation. NOTE: If you only want the folders and not the actual documents, remove the "Leaf" section from the XSLT.

SiteMap.xslt (583.00 bytes)

Tags:

C# | HTML

SharePoint Deploy Workflow Feature to Server Farm

by 11. January 2012 13:24

This post covers steps used to deploy the workflow feature to a SharePoint site (or sub-site as shown in this example URL: http://ServerNameHere/sites/SubSiteNameHere).  Attached is a sample SharePoint workflow feature that can configured to perform a daily refresh of an Excel file from a SharePoint site (i.e. refreshing the data connections to update data from external sources).

To setup the Visual Studio Solution, you will need to change the URL located in the three areas shown below:

  1. Project -> Site URL -> http://ServerNameHere/sites/SubSiteNameHere
  2. Web References -> ExcelWebService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/excelservice.asmx
  3. Web References -> ListService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/lists.asmx

When deploying the workflow feature on the server (either through Visual Studio right-click and “Deploy”; or, via Power Shell script shown below) I received the following error message:

Error occurred in deployment step 'Activate Features': Feature with Id '{GUID}' is not installed in this farm, and cannot be added to this scope.

Power Shell Deployment Script:

rem NOTE: Make sure the "Sharepoint 2010 Administration" service is NOT running.
rem We are using the execadmsvcjobs to execute rather than letting th Timer service run them.
rem ADDING...
cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"
stsadm -o addsolution -filename "C:\VSS\ExcelRefreshWorkflow\bin\Debug\ExcelRefreshWorkflow.wsp"
stsadm -o deploysolution -name "ExcelRefreshWorkflow.wsp" -immediate -allowgacdeployment -allowcaspolicies
stsadm -o execadmsvcjobs
stsadm -o activatefeature -name "ExcelRefreshWorkflow_Feature1" -url http://ServerNameHere/sites/SubSiteNameHere

NOTE: This feature is being deployed to “Scope = Site”, not Farm, Web or Web Application scope.

image

If you are running a single server instance of Sharepoint this should not happen (i.e. your deployment should be completed successfully). In the case of a Sharepoint server farm you will need to perform the following steps manually to complete the installation.

  1. From the “SharePoint 2010 Central Administration” site:
    • Navigate to: “System Settings” -> “Manage farm solutions”
    • Click on “excelrefreshworkflow.wsp”
    • Click on “Deploy Solution” and complete the deployment screen (schedule or run immediately).
    • Refresh the farm solutions screen until the solution management screen until the status column shows “Deployed” (note, the “Deployed To” column should show “Globally deployed.”).
    • NOTE: Make sure the “SharePoint 2010 Timer” service is running; otherwise, this step will not complete.
  2. Open the normal SharePoint site and from the top level site:
    • Navigate to: “Site Actions” -> “Site Settings” -> “Site Collection Administration” -> “Site collection features”
    • Click the “Activate” button associated with the “ExcelRefreshWorkflow” item in the list.
    • NOTE: Repeat this step on every sub-site that you want to use the workflow in.
  3. At this point the installation is complete. You can now navigate to the sub-site and configure the workflow on the appropriate Library and activate it on the appropriate documents in the library.
    • From the sub-site home page, navigate to: “Library Tools” -> “Library” -> “Library Settings” -> “Workflow Settings”.
    • From this screen you should see the “ExcelRefreshWorkflow” item in the “Select a workflow template:” list. Select that item, complete the remaining configuration settings and click “OK”.
    • For each document desired, click on the document dropdown and select “Workflows”.
    • Click on the “ExcelRefreshWorkflow”
    • Setup the appropriate configuration settings and click the “Start Workflow” button.
    • NOTE: All activity related to the workflow (including the execution history) will be stored in the associated “Workflow History”.

Attached is a sample workflow that will open an Excel file in document library, refresh the data connection and save the file as a new version in the document library on a scheduled interval.

ExcelRefreshWorkflow.zip (422.11 kb)

Tags:

Sharepoint | Workflow Foundation

Deploying SharePoint Workflow Feature to Server Farm

by 11. January 2012 11:52

 

Assume you are trying to deploy the workflow feature to a sub-site:

(http://ServerNameHere/sites/SubSiteNameHere)

 

To setup the Visual Studio Solution, change the URL located in three areas:

1.       Project -> Site URL -> http://ServerNameHere/sites/SubSiteNameHere

2.       Web References -> ExcelWebService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/excelservice.asmx

3.       Web References -> ListService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/lists.asmx

 

When deploying the workflow feature on the server (either through Visual Studio right-click and “Deploy”; or, via Power Shell script shown below) I received the following error message:

 

Error occurred in deployment step 'Activate Features': Feature with Id '{GUID}' is not installed in this farm, and cannot be added to this scope.

 

Power Shell Deployment Script:

rem NOTE: Make sure the "Sharepoint 2010 Administration" service is NOT running.

rem We are using the execadmsvcjobs to execute rather than letting th Timer service run them.

 

rem ADDING...

cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"

stsadm -o addsolution -filename "C:\VSS\ExcelRefreshWorkflow\bin\Debug\ExcelRefreshWorkflow.wsp"

stsadm -o deploysolution -name "ExcelRefreshWorkflow.wsp" -immediate -allowgacdeployment -allowcaspolicies

stsadm -o execadmsvcjobs

stsadm -o activatefeature -name "ExcelRefreshWorkflow_Feature1" -url http://ServerNameHere/sites/SubSiteNameHere

 

NOTE: This feature is being deployed to “Scope = Site”, not Farm, Web or Web Application scope.

 

If you are running a single server instance of Sharepoint this should not happen (i.e. your deployment should be completed successfully).  In the case of a Sharepoint server farm you will need to perform the following steps manually to complete the installation.

 

1.       From the “SharePoint 2010 Central Administration” site:

a.       Navigate to: “System Settings” -> “Manage farm solutions”

b.      Click on “excelrefreshworkflow.wsp”

c.       Click on “Deploy Solution” and complete the deployment screen (schedule or run immediately).

d.      Refresh the farm solutions screen until the solution management screen until the status column shows “Deployed” (note, the “Deployed To” column should show “Globally deployed.”).

e.      NOTE: Make sure the “SharePoint 2010 Timer” service is running; otherwise, this step will not complete.

2.       Open the normal SharePoint site and from the top level site:

a.       Navigate to: “Site Actions” -> “Site Settings” -> “Site Collection Administration” -> “Site collection features”

b.      Click the “Activate” button associated with the “ExcelRefreshWorkflow” item in the list.

c.       NOTE: Repeat this step on every sub-site that you want to use the workflow in.

3.       At this point the installation is complete.  You can now navigate to the sub-site and configure the workflow on the appropriate Library and activate it on the appropriate documents in the library.

a.       From the sub-site home page, navigate to: “Library Tools” -> “Library” -> “Library Settings” -> “Workflow Settings”.

b.      From this screen you should see the “ExcelRefreshWorkflow” item in the “Select a workflow template:” list.  Select that item, complete the remaining configuration settings and click “OK”.

c.       For each document desired, click on the document dropdown and select “Workflows”.

                                                   i.      Click on the “ExcelRefreshWorkflow”

                                                 ii.      Setup the appropriate configuration settings and click the “Start Workflow” button.

                                                iii.      NOTE: All activity related to the workflow (including the execution history) will be stored in the associated “Workflow History”.

Tags:

Sharepoint

SQL Server Get First and Last Day's of Previous, Current and Next Month

by 10. January 2012 09:55

 

select

  [First Day of Previous Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(getdate())-1)

  , dateadd(mm, -1, getdate()))

  , 101)

, [Last Day of Previous Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(getdate()))

  , getdate())

  ,101)

, [First Day of Current Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(getdate())-1)

  , getdate())

  , 101)

, [Last Day of Current Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(dateadd(mm, 1, getdate())))

  , dateadd(mm, 1, getdate()))

  , 101)

, [First Day of Next Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(dateadd(mm, 1, getdate()))-1)

  , dateadd(mm, 1, getdate()))

  , 101)

, [Last Day of Next Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(dateadd(mm, 1, getdate())))

  , dateadd(mm, 2, getdate()))

  , 101)

 

Tags:

SQL Server

ASP.Net Compile File System Website

by 6. January 2012 16:10

Below is an example of command program to compile an ASP.Net file system web using .Net framework v4 (the rem comment lines show the value for compiling as v2 as well).

rem v2.0.50727
rem v4.0.30319
set frmwk=C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319

set src=C:\vss\MyWebSiteFolder
set dest=C:\vss\MyCompiledWebSiteFolder

del /F /Q %dest%\*.*

%frmwk%\aspnet_compiler -v /VwdSite -p %src% %dest% -c

Tags:

ASP.Net

ASP.Net Compile File System Website

by 6. January 2012 15:53

Below is an example of command program to compile an ASP.Net file system web using .Net framework v4 (the rem comment lines show the value for compiling as v2 as well).

rem v2.0.50727
rem v4.0.30319
set frmwk=C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319

set src=C:\vss\MyWebSiteFolder
set dest=C:\vss\MyCompiledWebSiteFolder

del /F /Q %dest%\*.*

%frmwk%\aspnet_compiler -v /VwdSite -p %src% %dest% -c

Tags:

ASP.Net

Excel Calculated Cell Expecting 0 and Getting Scientific Exponential Notation Instead (e.g. 1.23E+10)

by 5. January 2012 14:38

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.

Tags:

Excel

CheckSum Utility

by 2. January 2012 16:32

Below is a C# implementation of two CheckSum utilities, the SHA256Managed uses the native .Net Security.Cryptography; while the Md5SumByProcess uses a windows implementation of Unix MD5Sum.exe (zip file attached to this blog can be downloaded from SourceForge at: http://sourceforge.net/projects/unxutils/files/unxutils/current/UnxUtils.zip/download). 

using System;
using System.Diagnostics;
using System.IO;
using System.Security.Cryptography;

  class CheckSumUtil
  {
    public static string Md5SumByProcess(string file)
    {
      var p = new Process();
      p.StartInfo.FileName = "md5sum.exe";
      p.StartInfo.Arguments = file;
      p.StartInfo.UseShellExecute = false;
      p.StartInfo.RedirectStandardOutput = true;
      p.Start();
      p.WaitForExit();
      string output = p.StandardOutput.ReadToEnd();
      return output.Split(' ')[0].Substring(1).ToUpper();
    }

    public static string SHA256Managed(string file)
    {
      using(var stream = new BufferedStream(File.OpenRead(file), 1200000))
      {     
        SHA256Managed sha = new SHA256Managed();
        byte[] checksum = sha.ComputeHash(stream);
        return BitConverter.ToString(checksum).Replace("-", String.Empty);
      }
    }

  }
}

UnxUtils.zip (3.21 mb)

Tags:

C#