Thursday, May 27, 2010

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?

3 comments:

Azadi May 27, 2010 at 11:49 PM  

the text will overflow if you use SpreadsheetSetCellValue() instead of SpreadsheetAddRow().
not sure if cf code can be posted here, but here goes:


cfSheet = SpreadsheetNew("foo");
// the text set using SpreadsheetSetCellValue will overflow - check R1C1 in the sheet
SpreadsheetSetCellValue(cfSheet, 'this text should overflow', 1, 1);
SpreadsheetSetCellValue(cfSheet, 'short text', 1, 3);
// the text set using SpreadsheetAddRow does not overflow - see R2C1 in the sheet
SpreadsheetAddRow(cfSheet, "this text should overflow 2,,short text 2", 1);
SpreadsheetSetColumnWidth(cfSheet, 1, 15);
SpreadsheetSetColumnWidth(cfSheet, 2, 15);
SpreadsheetSetColumnWidth(cfSheet, 3, 15);
saveToFile = ExpandPath("test-blank-cell.xls");
SpreadsheetWrite(cfsheet, saveToFile, true);




Azadi

cfSearching May 28, 2010 at 12:41 AM  

@Azadi,

Yes, that works thanks. But I cannot shake the feeling there should be a way to create blank cells when adding in bulk (ie by row or query). Not having to set cell values individually is one of the advantages of ..AddRow() and ...AddColumn(). It seems like there should be a way to control the empty/blank cell behavior. Either through the format or perhaps an extra option in the functions, as they did with ListToArray() in CF8.

-Leigh

cfSearching May 28, 2010 at 1:25 AM  

... I have never been fond of CF arbitrarily deciding what to do with empty elements (.. list functions anyone ?). Personally, I prefer the flexibility of having choices ;)

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep