Skip to main content
S
ssmahe
Participating Frequently
January 11, 2011
Question

Unable to read an Excel file using cfspreadsheet

  • January 11, 2011
  • 1 reply
  • 11327 views

Hi,

     I am trying to read an Excel file using CFSPREADSHEET. This file has been created using the CFFILE ACTION="WRITE" tag. However every time I try to do this I get an Exception that says "An error occurred while reading the Excel: java.lang.IllegalArgumentException:  Your InputStream was neither an OLE2 stream, nor an OOXML stream."

     I created a duplicate of the Excel file manually (copy-paste) and I was able to successfully read the duplicate Excel using the CFSPREADSHEET tag. When I checked the properties of both the original and duplicate excels, there was no difference except for their size. The one that was created using the CFFILE ACTION="WRITE" option was 12 KB and the duplicate was 24 KB.

     Could some one please let me know why I am getting the exception when trying to read the Excel using CFSPREADSHEET and how I can overcome it.

Thanks in advance

    This topic has been closed for replies.

    1 reply

    J
    JR__Bob__Dobbs
    Inspiring
    January 13, 2011

    Based on the error message I would guess that the content of the target Excel file is NOT a binary Excel 97 formatted file.  You stated that the file was created by  CFFILE ACTION="WRITE".  Can you describe how the file was created?

    S
    ssmaheAuthor
    Participating Frequently
    January 17, 2011

    I Excel file was created from an HTML string. This is the code

    <CFSAVECONTENT variable = "QueryPC_Output">

        <TABLE Border>

              <CFINCLUDE template = "../ReportPreference/Report_TH.cfm"> <!--- These tags create a report Table from a query --->

              <CFINCLUDE template = "../ReportPreference/Report_TD.cfm">

        </TABLE>

    </CFSAVECONTENT>

    <CFSET QueryPCFn = #ExpandPath(".")# & "\QueryPC.xls">

    <cftry>

         <CFFILE ACTION="Write" File="#QueryPCFn#" OUTPUT="#QueryPC_Output#">

         <cfcatch type="any"><cfoutput>Exception occured : #cfcatch.Detail# # cfcatch.Message# </cfoutput></cfcatch>

    </cftry>

    The HTML string is being generated successfully. The Excel file created from the HTML string using the CFFILE tag has the desired content.

    Also the Excel file created has hyperlinks in them, but so does the one I created manually (which the CFSPREADSHEET tag is able to read successfully).

    J
    JR__Bob__Dobbs
    Inspiring
    January 17, 2011

    I suspect that the CFSPREADSHEET tag requires that the excel file contain the binary format used by excel, not an HTML string.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices