2
  1. Given a spreadsheet with N logical rows
  2. Where one row is totally blank*
  3. cfspreadsheet action="read" will return a query with a RecordCount of N - 1.

*A totally blank row is a row where every cell is actually blank. See CELL_TYPE_BLANK in the POI docs.

Is it possible for cfspreadsheet to include empty rows?

Leigh
  • 28,765
  • 10
  • 55
  • 103
Jared Beck
  • 16,796
  • 9
  • 72
  • 97

2 Answers2

2

No. Since spreadsheet data is not always contiguous, <cfspreadsheet action="read" query="queryName" ...> and <cfspreadsheet action="read" format="csv|html" ..> deliberately screen out blank rows to avoid including tons of white space noise. So unless a row has at least one non blank cell, it will not be detected. AFAIK, there is no setting to override that behavior. You would have to tap into the underlying POI workbook and roll-your-own.

Leigh
  • 28,765
  • 10
  • 55
  • 103
0

I set up an xls like this:

|Row1|Data1|
[blank]
|Row3|Data3|
[blank]
|Row5|Data5|

And ran this code over it:

<cfspreadsheet action="read" src="c:\temp\book1.xlsx" name="st1">
<table border="1">
<cfloop index="iRow" from="1" to="5">
    <tr>
        <cfloop index="iCol" from="1" to="2">
            <cfoutput><td>#spreadsheetGetCellValue(st1, iRow, iCol)#&nbsp;</td></cfoutput>
        </cfloop>
    </tr>
</cfloop>
</table>

And the output was:

|Row1|Data1|
[blank]
|Row3|Data3|
[blank]
|Row5|Data5|

Which is what I'd expect.

So it looks to me like the blank rows are respected just fine...?

What am I doing different from you?

Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
  • I do not think that is what he meant by "read". My take was he meant read into a `query` or `csv/html` format. Those *do* ignore all BLANK rows, probably to avoid returning tons of empty records if there is a large gap between populated data rows. The focus of `SpreadSheetGetCellValue` is a little different. So it *does* return an empty string - even when the cell is BLANK or essentially `null`. – Leigh Nov 09 '11 at 17:06
  • Sure, sorry, I did not articulate myself very well (nor indeed did I read the original post thoroughly). I mean it's not like the info *isn't available*, it just isn't available via that particular mechanism that Jared is using. So I'm suggesting using the mechanism that *does* avail the info he wants. – Adam Cameron Nov 10 '11 at 08:26
  • Ah, okay. That makes sense. Though they may still need to tap into POI to figure out where the populated data begins and ends. – Leigh Nov 10 '11 at 15:38