converting excel to text file

[Guest]

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

[cfjedimaster]

Are you in CF9? If so you can read in the data. Then you could write

it out to a text file.

[Guest]

No. I am on CF8

[Nathan_Mische]

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

[Guest]

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

[ilssac]

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.

[Guest]

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

[Nathan_Mische]

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

[Guest]

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

[Nathan_Mische]

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

[Guest]

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.

[Nathan_Mische]

Can you open the files with Excel after downloading them? Are you sure they

are Excel files and not .csv or some other format?

[Guest]

yes I am able to open the excel files after downloading.