tracker issue : CF-3952934

select a category, or use search below
(searches all categories and all time range)
Title:

cfspreadsheet with header=1 doesn't throw error if spreadsheet is empty but was created with spreadsheetNew

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/NotABug

Reporter/Name(from Bugbase): colin macallister / colin macallister (colin macallister)

Created: 03/13/2015

Components: Document Management, Office Integration

Versions: 11.0

Failure Type: Incorrect w/Workaround

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Normal / Few users will encounter

Locale/System: English / Win 2008 Server x64

Vote Count: 0

Problem Description: 
If you read in a spreadsheet via cfspreadsheet with header=1, it should throw an error if there is no data in the header. It does this for pristine spreadsheets created in Excel or Libre Office. However, for spreadsheets that are empty were created with spreadsheetNew, spreadsheetAddRow, spreadsheetFormatRow, and output with spreadsheetReadBinary, no error is thrown and an empty query object is returned. Any subsequent attempt to access data by the header indices throws the error that the table is not indexable by that header value.

Steps to Reproduce: 
Create two empty spreadsheets, one from Excel and one generated by the commands listed above. To output the spreadsheet use
<cfset local.s = spreadsheetNew( "My Worksheet" ) />
<cfset spreadsheetAddRow( local.s, "ColumnA,ColumnB,ColumnC") />
<cfset spreadsheetFormatRow( local.s, { bold=true }, 1 ) />
<cfheader name="Pragma" value="" />
<cfheader name="Cache-control" value="" />
<cfheader name="Content-Disposition" value="inline; filename=myspreadsheet.xls" charset="utf-8" />
<cfcontent type="application/vnd.msexcel; charset=utf-8" variable="#spreadSheetReadBinary( local.s )#" />
<cfabort/>

Read both in with cfspreadsheet, with header=1.

Actual Result: 
The generated spreadsheet returns an empty query object for the spreadsheet, and doesn't throw an error that there is no data for a header row. Weirder, even though it reports that recordCount = 0, <cfif spreadsheet.recordCount eq 0> does not evaluate to true and looping over the query object seems to reflect the presence of two rows. For each row, every header (column) index returns an error because it doesn't exist.

Expected Result: 
If the spreadsheet is empty, regardless of the source of the xls file, cfspreadsheet header=1 should throw an error.

Any Workarounds:
I am experimenting with this to see if other ways of generating a spreadsheet cause the same problem.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3952934

External Customer Info:
External Company:  
External Customer Name: colin
External Customer Email:  
External Test Config: My Hardware and Environment details:

Server: Windows 2008, Coldfusion 11 Developer behind IIS.

Client: Windows 7, Firefox 36.01, blank spreadsheets generated by either Microsoft Office Standard 2010 (Excel version 14.0.7143.5000 (64-bit) or LibreOffice version 4.2.8.2.

Attachments:

Comments:

Colin, I am a little confused with the bug report. It says "If you read in a spreadsheet via cfspreadsheet with header=1, it should throw an error if there is no data in the header". But the code you've shared in the bug report creates a spreadsheet with one row (row 1). When this spreadsheet is read back with cfspreadsheet tag with the headerrow attribute (the report says "header", an attribute that does not exist for this tag) set to 1, the tag will treat the first row ( the one with values: "ColumnA,ColumnB,ColumnC" ) as the header, exclude it, and return an empty query with no data. However, if one removes the header row (code below) and reads the excel file the following exception is expectedly, thrown: "Row 1 specified as header row is empty. Specify a valid row number." <cfset xl_obj = spreadsheetNew( "My Worksheet" ) /> <cfset spreadsheetAddRow( xl_obj, "HeaderA,HeaderB,HeaderC",1 ,1) /> <cfset spreadsheetAddRow( xl_obj, "dataA,dataB,dataC",2 ,1) /> <cfset spreadsheetFormatRow( xl_obj, { bold=true }, 1 ) /> <!--- delete the headr row ---> <cfset SpreadsheetDeleteRow(xl_obj, 1)> <!--- generate the exxcel file ---> <cfheader name="Pragma" value="" /> <cfheader name="Cache-control" value="" /> <cfheader name="Content-Disposition" value="inline; filename=myspreadsheet.xls" charset="utf-8" /> <cfcontent type="application/vnd.msexcel; charset=utf-8" variable="#spreadSheetReadBinary( xl_obj )#" /> <!--- read the file ---> <cfset out_fl = "C:\Users\pnayak\Downloads\outdata.xls"> <cfspreadsheet action="read" src="#out_fl#" query="qryxl", headerrow=1, excludeheaderrow=true> <cfdump var=#qryxl#>
Comment by Piyush K.
8059 | March 17, 2015 11:16:10 PM GMT
Take the spreadsheet generated in my example, open it in a spreadsheet application, and delete all the data and save. Then when you upload the spreadsheet and parse it again, you will fail to get the "Row 1 specified as header row is empty" error.
Comment by External U.
8060 | March 18, 2015 12:11:02 PM GMT
Colin, Just to be clear, the issue can be only be observed if the data in the spreadsheet is deleted. However if the entire row is deleted the exception is thrown as expected. Can you confirm? create an excel file with the following code: <cfset local.s = spreadsheetNew( "Worksheet", false) /> <cfset spreadsheetAddRow( local.s, "ColumnA,ColumnB,ColumnC") /> <cfset spreadsheetFormatRow( local.s, { bold=true }, 1 ) /> <cfheader name="Pragma" value="" /> <cfheader name="Cache-control" value="" /> <cfheader name="Content-Disposition" value="inline; filename=xls_colin.xls" charset="utf-8" /> <cfcontent type="application/vnd.msexcel; charset=utf-8" variable="#spreadSheetReadBinary( local.s )#" /> Step #2: open the file with MSExcel and delete the data in the row (not the row). Step #3: Read the excel file with the following code: <cfset out_fl = "C:\Users\pnayak\Downloads\xls_colin.xls"> <cfspreadsheet action="read" src="#out_fl#" query="qryxl" headerrow=1 excludeheaderrow=true> <cfdump var=#qryxl#>
Comment by Piyush K.
8061 | July 09, 2015 05:33:50 AM GMT
When the spreadsheet is created using coldfusion I see that we are formatting the cells with bold. Now when the cells are manually deleted in spreadsheet the format is still applicable on the cells, that is why we see a query object being returned. Try the below snippet and then delete the cells , behavior will be as expected. <cfset local.s = spreadsheetNew( "Worksheet", false) /> <cfset spreadsheetAddRow( local.s, "ColumnA,ColumnB,ColumnC") /> <cfheader name="Pragma" value="" /> <cfheader name="Cache-control" value="" /> <cfheader name="Content-Disposition" value="inline; filename=xls_colin.xls" charset="utf-8" /> <cfcontent type="application/vnd.msexcel; charset=utf-8" variable="#spreadSheetReadBinary( local.s )#" />
Comment by Mayur J.
8062 | September 23, 2015 05:58:42 AM GMT