Not able to run vbscript using cfexecute on Windows 64 bit Machine - Solution

Goal: To run VBScript using CFexecute using command base script Cscript.exe

Environment:  ColdFusion 10, Windows7 , 64 bit Machine.

My VB Script(Excel.vbs) performs simple office automation .

So first I tried with command line to run Excel.VBS.

C:\Windows\System32 > cscript //nologo D:\Excel.vbs D:\test.xls

and got expected o/p Success (Good my Excel VBS is correct)

Now, I have to see whether CF runs correctly or not.
So I use cfexecute to run Excel.VBS.

<cfexecute name = "C:\Windows\System32\CScript.exe"
            arguments = "//NoLogo D:\Excel.vbs D:\test.xls"
            variable = "savedOutput"
            timeout = "3600">
 </cfexecute>
<cfdump var="#savedOutput#">

But, I got this error message -

Error: 424 Source: Microsoft VBScript runtime error Description: Object required

Then what is wrong with ColdFusion, same VBScript is running using command line but CFExecute throws error.

So I tried two things :

•  Checked whether account which runs ColdFusion is having admin rights or not?
• Yes CF running with Admin privilege.
• Tried using batch file
  batchFile.bat :
  
  @echo off
  pushd %~dp0
  cscript cscript //nologo D:\Excel.vbs D:\test.xls
  
  
  <cfexecute name = "D:\batchFile.bat"
                     variable = "savedOutput"
                     arguments="/C /Q"
                     timeout = "3600">
   </cfexecute>
  <cfdump var="#savedOutput#">
  
  Same Error: 424 Source: Microsoft VBScript runtime error Description: Object required

So what will be the solution for this ?
After checking some threads on office automation I found this solution.

We have to add the Desktop folder in the systemprofile and system32 folder to open file by Excel if you have Windows Service or IIS on Windows 7 x64 (dev machine) and Server 2008 x64.

So I added Desktop folder under these 2 directories -
C:\Windows\System32\config\systemprofile
C:\Windows\SysWOW64\config\systemprofile

O/P: Now my cfexecute code gives success and VBScript performs desired office operation.

Also, verify that ColdFusion is allowed to "Interact with Desktop" is enabled or not on Services.

Note: If you are doing office automation then you have to add the Desktop folder inside systemProfile irrelevant of technology you are using under above Environments.

Hope it helps you :)

POI Compatibility Issues with ColdFusion

In this post I will discuss with Issues coming with the latest POI 3.10 in ColdFusion 9/10..

Recently I worked on Import/Export features of Excel documents in my project. Moreover, this excel documents are too much complex with Multiple trigger and rich with Macros.

In result, I faced lots of Issue in reading such files and faced n no of errors with POI.

Let's discuss How ColdFusion reads excel sheets  ?

What is POI ?
- POI is open source java library used to read Microsoft documents.

Why POI in ColdFusion ?
- ColdFusion ships with POI to read Excel sheets. CF also ships with Open Office, by default excel read/manipulation handle by POI. If you want to use Open Office then Configure local/remote Open Office directories under CF Admin. ColdFusion 10 comes with POI 3.6 jar files.

POI jar files present under lib directory of ColdFusion: (Below jar files comes up with POI 3.10)

1. dom4j-1.6.1.jar
2. poi.jar
3. poi-ooxml.jar
4. poi-ooxml-schemas.jar
5. poi-scratchpad.jar
6. stax-api-1.0.1.jar
7. xmlbeans-2.6.0.jar

Now let's discuss some errors which I have got while reading those XLS/XLSX files using ColdFusion 10. 

Some Errors while reading those XLS/XLSX files with ColdFusion 9/10 using inbuilt tags/function available in ColdFusion :

• (with XLS) java.lang.NullPointerException at org.apache.poi.hssf.model.LinkTable.getExternalBookAndSheetName  
• (with XLS)Duplicate PageSettingsBlock record (sid=0x89c) 
• (with XLS) Unexpected celltype (5)  
• This error may comes when you have some dirty data or cell value starting with #.
  But everything fine in my case.
• (with XLS) coldfusion.excel.ExcelProcessException: An exception occurred while using action=read. NullPointerExceptioN.
• There was nothing wrong in my cfspreadhseet syntax
  
• (with XLSX) The supplied POIFSFileSystem does not contain a BIFF8 'Workbook' entry. Is it really an excel file?
• Some of the XLSX files are failing
• (with XLSX) The Supplied spreadsheet seems to be encrypted .xlsx file. It must be decrypted before use by HSSF

What I have tried so far with inbuilt Tags/Function available in CF :    Sucess/Failure ?

• As all issues are pointing towards POI issue. So I download the latest version of POI 3.10 and extracted all jar files and put it under \lib directory.
  
  Success: 6/5 issues with 100% success rate with  XLS solved using updated POI except encrypted error coming up with some XLSX file.
  
  Adverse effects (Failures):  
• Formatting rows/columns/cells of Excel Sheets with styles like fonts and color are not working and throw an error "Java: no Method error with fonts and color".
• Cause: ColdFusion expecting Font.getCharSet to return a byte, while the latest version of POI returns an int. Stackoverflow thread
•  Some of the XLSX file which was easily readable using POI 3.6 is now unreadable and through
  

  "org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet.getColsList()Ljava/util/List
  java.lang.NoSuchMethodError"

In the next post I will discuss some workaround for it.

Mach-II Framework - Basic beginner start.

 My journey towards learning Mach-II framework

Few days back, I have added post regarding CF frameworks.
In this post we are going to understand what Mach-II is and what this beautiful framework provides.

There is already a superb Github directory from where you can learn Mach-II.
This post will help you to get familiar with some basics of Mach-II and early hurdles you face if you are a beginner.

Below points will cover whatever I've learned so far in Mach-II.

Let's begin: -
What Mach-II  is ?

• MVC framework for ColdFusion
• Benifit of MVC : Separation of codes into different layers which help to reuse and organize your code better using the Model(mainly CFCs), View(shows your content) and Controller(which link Model and Views)
• In Mach-II controller comprises of plugins,listeners and filters
  
• Object Oriented F/W
• Event Driven Framework
• mach-ii.xml where all configuration needs to be done. 
• XML contains : Properties, Events, Listeners, Page Views, Filters, Plugins 
• II stands for Implicit Invocation
• As Mach-II is an event driven framework, so all events are handled though event handlers which in turn announced a component for which event is registered.
• Know More here Implicit-Invocation-Architectures in MVC
• Easy to maintain and provide flexibility in developing robust application.

How to setup Mach-II ?

•  First download Mach-II f/w code and skeleton from
  MachII Github or Mach-II home
•  A basic folder structure which I kept up while learning Mach-II
  
  
  
   
• Once code downloaded,  now we have to run mach-II application based on where you kept our framework files and skeleton files.
  If you are keeping f/w files outside web root,  then we have to add a mapping in CF administrator. You can also extend Mach-II f/w codes to setup your mach-ii f/w environment.
  Here is the link which shows you various ways to setup your mach-ii application
• Above Image shows my application folder comprises of both f/w codes + skeleton.
• I pointed this directory as a Virtual Directory in my IIS and named as "mach2test"
• Now we need to open /config/ mach-II.xml.  In property applicationRoot we have to specify the root of our application.
  <property name="applicationRoot" value="/mach2test" />
• Now,  once we have added value for applicationRoot, we have to add mapping or inherit f/w codes using extends attribute.
  So for this I have added "MachII.mach-ii" in application.cfc to inherit mach-ii.cfc
   <cfcomponent displayname="learnMach-ii" output="false" extends="MachII.mach-ii">
• Now code setup is over, you are ready to run your first o/p in the browser.
  http://localhost/mach2test/index.cfm. If you have downloaded Mach-II skeleton then first o/p will be Hello Mach-II!

Flow of Mach-II Application:

• All Mach-II request flows from index.cfm because it follows pattern called Front Controlerl
• Wiki: It provides a centralized entry point for handling requests.
•  As Mach-II is event driven in nature, so everything goes on by calling an event which creates event objects. Let's look for one example to get a clear Idea on Mach-II flow.
• Say for example: If you have a basic login page in your application

• URL : http://localhost/mach2test/index.cfm?event=login. So first request goes to central page index.cfm and start looking for Mach-II.xml
• As we are calling an event called login (If we don't specify any eventParameter then defaultEvent will execute) so It will look for event-handers where an event is login
• Now event handler told to open a view called login.cfm.
• In login.cfm - we have one form whose action calls "index.cfm?event=checkLogin"
• Now, once again flows go to Mach-II.xml and look for checkLogin event. checkLogin event handler calls listener loginListener which looks for validateLogin method present under loginComponent CFC which resides under the model.
•  validateLogin() return success/failure and announces an event loginSucess and shows home.cfm or  loginFailure which shows error message.

 Hope you get a basic flow of Mach-II framework.
I am still driving deeper into Mach-II framework, hope I will be coming with some more post related to Mach-II soon. 

Happy Learning :)