0

I'm creating a scheduled task which downloads an excel file from a third-party site and then updates the database accordingly.

I can successfully download the file via a url (ie http://www.example.com/myExcel.xls). But when I try to read it afterwards, using Ben Nadel's POIUtility.cfc, I get all kinds of errors. It seems due to the fact that when I download the file with CFHTTP it is saved in read-only format.

Does this behavior sound familiar to anybody or does anybody know how to save the file so it is not read-only?

Thanks in advance.

Part of the code:

<cfhttp method="get" 
     url="http://www.example.com/myExcel.xls" 
     path="#expandpath('xls')#" file="stocks.xls" />
<cfset objPOI = CreateObject("component", "POIUtility").Init()/>
<cfset arrSheets = objPOI.ReadExcel( FilePath = ExpandPath("xls/stocks.xls")
                                     , HasHeaderRow = true) />

UPDATE 22nd of june: The error I receive is the following:

Object instantiation exception.
An exception occurred while instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.
The error occurred in /Volumes/RAID/DATA/ColdFusion9/wwwroot/website/POIUtility.cfc: line 883
Called from /Volumes/RAID/DATA/ColdFusion9/wwwroot/website/autoUpdateStock.cfm: line 36
Called from /Volumes/RAID/DATA/ColdFusion9/wwwroot/website/POIUtility.cfc: line 883
Called from /Volumes/RAID/DATA/ColdFusion9/wwwroot/website/autoUpdateStock.cfm: line 36
881 :               "org.apache.poi.hssf.usermodel.HSSFWorkbook"
882 :               ).Init(
883 :                   LOCAL.ExcelFileSystem
884 :                   );
885 :

If I open the downloaded file directly from within my OS the status bar in excel also says "(read-only)". If I save the file the a different filename and then use that new filename in the POI code it works without a problem.

I had a similar error a while ago where the problem was unsupported excel-stuff in the excel file which caused the POI the give the same error. But this excel file does not contain special things like graphs etc, just plain data without datafilters.

I'm thinking maybe the source file is saved in an unsupported excel format for the POI utility but when I view the info-window of the file it says "Kind: Microsoft Excel 97-2004 workbook" which is the same as with other files I tried with (that work).

pnuts
  • 58,317
  • 11
  • 87
  • 139
Juggala
  • 21
  • 4
  • the code: – Juggala Jun 20 '12 at 13:23
  • 3
    Have you tested this theory of yours by downloading the file, making it writeable, and THEN doing the POI stuff (and if doing this, there is no error, thus demonstrating your theory to be correct)? That's fairly key to your situation. – Adam Cameron Jun 20 '12 at 13:29
  • 2
    Please give an actual error or two. Typically when you're pulling files using cfhttp, the file will be saved as read-only. However, you may need to grant execute permissions to actually read the file. Again, an actual error message would help. Can you open the file with `FileOpen( ExpandPath( "xls/stocks.xls" ) )`? – Bradley Moore Jun 20 '12 at 17:26
  • Thanks for the input. I can execute FileOpen without problems. The error I receive from the POI utility is the following:`Object instantiation exception. An exception occurred while instantiating a Java object. The class must not be an interface or an abstract class. Error: ''. 881 : "org.apache.poi.hssf.usermodel.HSSFWorkbook" 882 : ).Init( 883 : LOCAL.ExcelFileSystem 884 : ); 885 : ` – Juggala Jun 22 '12 at 10:25

1 Answers1

2

The error might reference "read-only" but I seriously doubt if that's the issue. After all CF actually writes the file to disk - so it will be able to read that same file.

The problem is more likely syntax. Have you tried ExpandPath('./xls')? I also see a semi-colon in your cfhttp call - I assume that is a typo.

Also could be your hitting the file with POI before the file handle is released by the OS. Try adding a little sleep time - 10 seconds or so - to eliminate that as a possibility.

Mark A Kruger
  • 7,183
  • 20
  • 21
  • You can sleep by calling. `CreateObject( “java” , “java.lang.Thread” ).sleep( 9000 );` The 9000 is milliseconds to sleep. – Bradley Moore Jun 20 '12 at 17:23
  • 1
    @BradleyMoore, or if using a version of CF from CF8 onwards, just use the `sleep()` function... – Adam Cameron Jun 20 '12 at 18:36
  • Thanks for the help. Mark: Syntax does not seem to be the problem, if I change the source file to a different excel file the code executes without problems. Bradley: Sleep was a good effort, but didn't make a difference.. – Juggala Jun 22 '12 at 10:28