Are you in CF9? If so you can read in the data. Then you could write
it out to a text file.
No. I am on CF8
I am able to parse the excel files.i have another question though. I am trying to wrtie the results of a query to a text file as below:
<cffile action="write" output="#output#" file="file.txt">
The problem is the html is getting written to the text file. How can I make sure, i get only the header and rows values
Then don't put HTML into the output content? Whatever you put into the "output" string variable via <cfsavecontent variable="output"> is going to be written to the file with the output="#output#" parameter.
I got everything working as long as the extension is .xls
When I am trying to use .xlsx extension, I get the following error
Object Instantiation Exception. An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.
The error occurred on line 875.
Is there a work around for this problem?
The version of the POI libraries bundled with ColdFusion 8 do not support
the OOXML (.xlsx) format. To read .xlsx files you need to use the latest
version of POI, which you can find here: http://poi.apache.org/
For info on using POI with JavaLoader see:
(Note: CF 9 supports .xlsx natively via cfspreadsheet.)
I placed all the new jar files and changed the code in poiutility.cfc as below:
// Create a file input stream to the given Excel file.
LOCAL.FileInputStream = CreateObject( "java", "java.io.FileInputStream" ).Init( ARGUMENTS.FilePath );
LOCAL.WorkBookFactory = CreateObject("java", "org.apache.poi.ss.usermodel.WorkbookFactory").Init();
LOCAL.WorkBook = LOCAL.WorkBookFactory.create(LOCAL.FileInputStream);
This works fine for files saved in ealrier version of excel, and if I save the current 2010 version using .xlsx file. But when I save the excel using 'Save it as Microsoft 2000-2003 excel, and try reading it, I get an error as below:
Your InputStream was neither an OLE2 stream, nor an OOXML stream null
How are you saving it as Microsoft 2000-2003? The options I get in Excel
2010 when I choose "File -> Save As" list "Excel 97-2003 Workbook (*.xls)"
but no 2000-2003 options.
Yes I am sorry, I tested it more and this is whats happening. When I am downloading excel, I get 'Open or Save dialog box', when I open it and then save it as .xls or .xlsx, I am able to read those files with no problem (using poiutility). but when instead of opening, if I save it which gets saved a Excel 97-2003, then try to read them, I get that Invalid stream error.
Can you open the files with Excel after downloading them? Are you sure they
are Excel files and not .csv or some other format?
yes I am able to open the excel files after downloading.