ColdFusion - POI Excel Workbook Format as Number

Question

I'm creating an Excel file using ColdFusion and POI Workbook. The file is created great, however I have cells that contain numeric values and need to be stored as numbers for SUM and other functions to work properly.

I want to call code after the data has been added for the excel sheet that will basically loop over the data, and if the value is numeric, set the data format as numeric. That way when I open the Excel file, I can perform a SUM or whatever on that column of data.

Below is what I've tried so far, and it does "work" because all the cells have the #EBEBEB background color, however they still have the warning that says "Number stored as text" as seen below. How can I fix this?

// Create our dataFormat object
df = poiWorkbook.createDataFormat();
// Create our new style
formatNumber = poiWorkbook.createCellStyle();
formatNumber.setFillPattern( formatEvenRowRightAlignStyle.SOLID_FOREGROUND );
formatNumber.setAlignment( formatNumber.ALIGN_RIGHT );
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
formatNumber.setFillForegroundColor( XSSFColor.init(Color.decode("##EBEBEB")) );    
formatNumber.setDataFormat(df.getFormat("0.00"));
// Loop over the data and apply the format
for (x=0;x<rowCount;x++) {
    for (y=0;y<colCount;y++) {
        poiSheet.getRow(x).getCell(y).setCellStyle(formatNumber);           
    }
}

*call code after the data has been added* Any reason why? It is possible but it would be simpler, and less error prone, to format while populating the sheet. — Leigh, Sep 19 '16 at 20:10
@Leigh - well the reason is because I am building a CF Query with all my data and then adding it using the SpreadsheetAddRows(sheet,query) method. — Phil, Sep 19 '16 at 20:16
@Leigh - after I add all the data, I then loop over the workbook and apply formatting as necessary — Phil, Sep 19 '16 at 20:30
If the query column data type is numeric (not varchar or something), you should be able to call `SpreadsheetFormatColumn` for that column after adding the query data. — Leigh, Sep 19 '16 at 20:33
... though that makes me wonder about the data type of your column. IIRC, later versions of CF should detect numeric columns when using `SpreadsheetAddRows` and apply a numeric format automatically. Is it possible the column is being formatted as a string instead of a numeric type? — Leigh, Sep 19 '16 at 21:08
@Leigh - thank you for the information. You are correct, when I call the SpreadsheetFormatColumn it's not working because my original query data type is varchar. I'm updating my code to check the columns before adding them. Thank you!!! Add as answer please and I will accept. You rock on the CF stuff!! — Phil, Sep 20 '16 at 14:14

score 1 · Accepted Answer · answered Sep 24 '16 at 12:38

(Promoted from comments ...)

call code after the data has been added

Any reason why? While it is possible, it is simpler, and less error prone, to format while populating the sheet. Assuming the query column data type is numeric (not varchar or something), you should be able to call SpreadsheetFormatColumn for that column, after adding the query data.

Having said that, the fact that you are getting "Number stored as text" warnings in the first place makes me wonder about the data type of your query column. IIRC, later versions of CF should detect numeric columns when SpreadsheetAddRows is used, and apply a numeric format to those columns automatically. Is it possible your query column is being formatted as a string instead of some numeric type? As that would explain the results. The solution is to cast the column to a numeric type inside the db query.

ColdFusion - POI Excel Workbook Format as Number

1 Answers1