HI All -
I am uploading an excel file using coldfusion. Once the file has been uploaded, I want to convert it to a text file. What I am trying to do now is once the file is uploaded, I am reading the excel file and then writing the output to text file. but the write method creates all junk in the text file. What is the best way to do this?
Thanks
If you are on CF8 you may want to look into Ben Nadel's POI Utility:
http://www.bennadel.com/projects/poi-utility.htm
--Nathan
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:
<cfsavecontent variable="output">
<cfoutput query="userdata">
<table>
<tr>
<td>FirstName</td
</tr>
<tr>
<td>LastName</td>
</tr>
<tr>
<td>ABCD</td
</tr>
<tr>
<td>ADEF</td>
</tr>
</table>
</cfoutput>
</cfsavecontent>
<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
funandlearning333 wrote:
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?
Thanks
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:
http://cfsearching.blogspot.com/2008/08/how-to-install-poi-on-coldfusi on-8.html
(Note: CF 9 supports .xlsx natively via cfspreadsheet.)
--Nathan
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
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.
North America
Europe, Middle East and Africa
Asia Pacific