-
1. Re: converting excel to text file
cfjedimaster Nov 9, 2010 8:21 AM (in response to funandlearning333)Are you in CF9? If so you can read in the data. Then you could write
it out to a text file.
-
2. Re: converting excel to text file
funandlearning333 Nov 9, 2010 8:25 AM (in response to cfjedimaster)No. I am on CF8
-
3. Re: converting excel to text file
Nathan Mische Nov 9, 2010 8:28 AM (in response to funandlearning333)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
-
4. Re: converting excel to text file
funandlearning333 Nov 9, 2010 10:22 AM (in response to Nathan Mische)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
-
5. Re: converting excel to text file
ilssac Nov 9, 2010 10:29 AM (in response to funandlearning333)funandlearning333 wrote:
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.
-
6. Re: converting excel to text file
funandlearning333 Nov 10, 2010 12:13 PM (in response to ilssac)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
-
7. Re: converting excel to text file
Nathan Mische Nov 10, 2010 12:24 PM (in response to funandlearning333)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-coldfusion-8.html
(Note: CF 9 supports .xlsx natively via cfspreadsheet.)
--Nathan
-
8. Re: converting excel to text file
funandlearning333 Nov 12, 2010 7:43 AM (in response to Nathan Mische)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
-
9. Re: converting excel to text file
Nathan Mische Nov 12, 2010 10:38 AM (in response to funandlearning333)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.
--Nathan
-
10. Re: converting excel to text file
funandlearning333 Nov 12, 2010 10:51 AM (in response to Nathan Mische)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.
-
11. Re: converting excel to text file
Nathan Mische Nov 12, 2010 11:11 AM (in response to funandlearning333)Can you open the files with Excel after downloading them? Are you sure they
are Excel files and not .csv or some other format?
-
12. Re: converting excel to text file
funandlearning333 Nov 12, 2010 11:31 AM (in response to Nathan Mische)yes I am able to open the excel files after downloading.