CF9 Question: Do Spreadsheet Functions Support "Blank Cells"?
I saw a question about spreadsheets this week that had me scratching my head. When you create a new worksheet in Excel, all of the cells are blank. So if you enter a long string in any cell, the text will overflow into the adjacent cell. (If the adjacent cell is blank.)
However, if you do something similar in ColdFusion 9, the text does not overflow into the next cell. Notice how the extra text in cell A1 is hidden, even though the adjacent cell is technically empty.
<cfscript>
cfSheet = SpreadsheetNew("foo");
SpreadsheetAddRow(cfSheet, "this text should overflow,,short text", 1);
SpreadsheetSetColumnWidth(cfSheet, 1, 15);
SpreadsheetSetColumnWidth(cfSheet, 2, 15);
SpreadsheetSetColumnWidth(cfSheet, 3, 15);
saveToFile = ExpandPath("test.xls");
SpreadsheetWrite(cfsheet, saveToFile, true);
// how about a WriteLine() function folks ... ;) ?
WriteOutput("Saved file: "& saveToFile &"<hr>");
</cfscript>
<cfheader name="Content-Disposition" value="inline; filename=#saveToFile#">
<cfcontent type="application/vnd.ms-excel" file="#saveToFile#" deleteFile="true" />
In the underlying POI library there is a special cell type for blank cells: CELL_TYPE_BLANK. In loose terms, it represents a raw cell that has never had a value. This is different from a cell whose value is set to an empty string. The value may be an empty string, but the cell still has a value. So it has a type of CELL_TYPE_STRING.
If you loop over the test file created earlier, you will see all of the cells are CELL_TYPE_STRING. Which at least explains why the first cell's text does not overflow into the next cell.
<cfscript>
// open the workbook
source = ExpandPath("test.xls");
input = createObject("java", "java.io.FileInputStream").init( source );
wb = createObject("java", "org.apache.poi.ss.usermodel.WorkbookFactory").create( input );
// get the first sheet
sheet = wb.getSheetAt(0);
// loop over each row in the sheet
rows = sheet.rowIterator();
while(rows.hasNext()) {
// get the current row
r = rows.next();
// loop over each cell in the sheet
cells = r.cellIterator();
while (cells.hasNext()) {
// get the current cell
c = cells.next();
// check the cell type (ingore error handling for brevity)
if (c.getCellType() == c.CELL_TYPE_BLANK) {
type = "CELL_TYPE_BLANK";
}
else if (c.getCellType() == c.CELL_TYPE_STRING) {
type = "CELL_TYPE_STRING";
}
else {
type = "other";
}
// display the position, cell type / value
// note: Adding +1 because POI indexes are 0-based
WriteOutput("["& r.getRowNum()+1 &"]["& c.getColumnIndex()+1 &"]=");
WriteOutput(type &" / "& c.toString() &"<hr>");
}
}
// clean up
input.close();
</cfscript>
But this raises the question, does ColdFusion 9 support "blank" cells, and if so how do you create one? I am honestly not sure. You could work around it by tapping into the undocumented getWorkBook() method of ColdFusion spreadsheet objects. It returns a reference to the underlying POI workbook. Using the workbook, you can then grab the desired rows and cells and change the cell type to CELL_TYPE_BLANK.
<cfscript>
cfSheet = SpreadsheetNew("foo");
SpreadsheetAddRow(cfSheet, "this text should overflow,,short text", 1);
SpreadsheetSetColumnWidth(cfSheet, 1, 15);
SpreadsheetSetColumnWidth(cfSheet, 2, 15);
SpreadsheetSetColumnWidth(cfSheet, 3, 15);
// work around to make cell B1 a "blank" cell
// POI row/cell indexes are 0-based !!
poiSheet = cfSheet.getWorkBook().getSheet("foo");
poiCell = poiSheet.getRow( 0 ).getCell( 1 );
poiCell.setCellType( poiCell.CELL_TYPE_BLANK );
saveToFile = ExpandPath("newTest.xls");
SpreadsheetWrite(cfsheet, saveToFile, true);
WriteOutput("Saved file: "& saveToFile &"<br>");
</cfscript>
<cfheader name="Content-Disposition" value="inline; filename=#saveToFile#">
<cfcontent type="application/vnd.ms-excel" file="#saveToFile#" deleteFile="true" />
However I have not found a documented way to do this. So does anyone know the answer to the million dollar question: does ColdFusion 9 provide a documented method for creating blank cells? My guess would be no. But I would be very happy to be proven wrong. Any spreadsheet function gurus out there?
...Read More
::
3
comments
Posts
