Unable to read an Excel file using cfspreadsheet

[ssmahe]

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

[JR__Bob__Dobbs]

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?

[ssmahe]

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).

[JR__Bob__Dobbs]

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

[ssmahe]

Do we have any other alternative of reading an excel file which has been created from a HTML string

[JR__Bob__Dobbs]

If you use XHTML (the HTML string is also an XML document or fragment) to create the file you could parse the contents with CF's XML related functions.

[ssmahe]

This actually did the trick for me. I am able to create the excel file using XML and POI. I am able to use the HTML string, parse it and create the excel file. However, I had to make a few changes to how the HTML string is being generated so that it confirms to the XML recommendations.

http://www.bennadel.com/blog/917-Creating-Excel-Files-With-ColdFusion-XML-And-POI.htm

[Adam Cameron.]

ssmahe wrote:

Do we have any other alternative of reading an excel file which has been created from a HTML string

The thing is, what you're creating with your code is not an Excel file: it's an HTML file.  Simply putting an XLS extension on it does not make it an Excel file.  It'll open with Excel because Excel is forgiving, and will convert the HTML to XLS binary format for you.  But unless you then re-save it with Excel, it's still just HTML.

Given you have CF9... why don't you create it as a proper XLS file in the first place, rather than just saving HTML with an XLS extension?  Or is this file being generated elsewhere?

--

Adam

[ssmahe]

The task at hand is to generate a report and display it on the browser and also give the "Export to Excel" option. The database is queried and an HTML string is created from the result-set, which is then displayed to the user. Also for a single report there are more then one result-sets and hence more then one HTML strings generated. With the "Export to Excel", I want a single Workbook with multiple worksheets (one for each result-set/HTML string).

Using <CFFFILE action=write> tag I can create the excel file from the HTML strings but cannot create multiple worksheets in the same file. <cfspreadsheet> does give the option to create multiple worksheets in the excel file but it cannot directly convert HTML strings to excel files.

So, what I am trying to do is to first create individual excels using <cffile action=write> (one for each HTML string) and then read each excel file using <cfspreadsheet> and create one workbook with multiple sheets.

I do understand that this may not be the best way of getting the job done, but I could not come up with any other alternative. Also could you please guide in how to create a proper XLS file.

Thanks

[JR__Bob__Dobbs]

You could re-factor the excel creation workflow, replacing the use of HTML strings with use of the CFSPREADSHEET tag or the spreadsheet functions. This would create an Excel file that could be read by CFSPREADSHEET. If you are having trouble using the spreadsheet related tags/functions to create an Excel file please create a new thread for this topic.

[Adam Cameron.]

Agreed, that'd be my advice.

--

Adam

[seanj67330202]

I need some help guys.

I have a question for you. I'm trying to read from an XLSX spreadsheet and the instant error I get is "empty string".

Not sure how to interpret that.

Here's my code:

  <cfset testFile = "C:\vmox\Projects\Bell Canada Data and Inventory Import\109523511-Cost overview.xlsx">

  <cfspreadsheet action="read" src="#testFile#" query="summaryData">

Message empty String

StackTrace java.lang.NumberFormatException: empty String at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1011) at java.lang.Double.parseDouble(Double.java:540) at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:196) at org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(DateUtil.java:278) at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:516) at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:480) at coldfusion.excel.Excel.getValueAt(Excel.java:984) at coldfusion.excel.Excel.getValuesAt(Excel.java:1139) at coldfusion.excel.Excel.readXLS(Excel.java:4497) at coldfusion.tagext.lang.SpreadSheetTag.readExcel(SpreadSheetTag.java:351) at coldfusion.tagext.lang.SpreadSheetTag.readExcelToQuery(SpreadSheetTag.java:445) at coldfusion.tagext.lang.SpreadSheetTag.doStartTag(SpreadSheetTag.java:100) at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2799) at cfBellImporter2ecfc1662898839$funcIMPORTBILL._factor0(C:\vmox\oss\application\obj\wem\BellImporter.cfc:180) at cfBellImporter2ecfc1662898839$funcIMPORTBILL.runFunction(C:\vmox\oss\application\obj\wem\BellImporter.cfc:176) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:2659) at cfBellImporter2ecfc1662898839$funcIMPORTBILLFORM.runFunction(C:\vmox\oss\application\obj\wem\BellImporter.cfc:44) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:655) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:444) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:414) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2432) at cfact_billImport2ecfm202714074.runPage(C:\vmox\oss\application\action\act_billImport.cfm:84) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:444) at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2799) at cf_pheader2ecfm978506549.runPage(C:\vmox\oss\application\includes\_pheader.cfm:21) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:444) at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2799) at cfbillImport2ecfm1726346326.runPage(C:\vmox\oss\application\billImport.cfm:1) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:444) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.IpFilter.invoke(IpFilter.java:64) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:443) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:112) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:151) at coldfusion.CfmServlet.service(CfmServlet.java:204) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:414) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:298) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:722)