I was a little surprised to see how inconsistent the behaviour of setCellType() is. Here is a table of what POI currently does (Row headings are 'from data type' column headings are 'to data type'):
From\To num str bool err num - OK OK 1 str 2 - OK 1 bool OK OK - 1 err 3 OK 4 - Notes: 1 HSSF sets the cell to #VALUE!, XSSF has bug (exception) 2 HSSF IllegalStateException, XSSF sets cell to 0.0 3 HSSF IllegalStateException, XSSF bug (exception) 4 HSSF and XSSF set cell to FALSE 5 HSSF sets vell to #VALUE!, XSSF sets cell to #NULL! Conversions involving blank seem to be mostly correct. Setting any cell type to blank clears it. Setting any blank cell to another type results in the default value for that type (however HSSF and XSSF disagree on what the default error value is). Before making any change I thought it would be a good idea to gather some input from interested parties. We can probably all agree that the behaviour should be more consistent. Firstly we need to decide on whether/how much Cell.setCellType should perform automatic type conversion: (A) never automatically convert data (only valid transitions are to/from blank). (B) only perform conversions that can never fail (any type to string, number to boolean). POI is currently closest to this. (C) always attempt conversion of data types. There are a few other related details to decide: - What action should be taken when attempting a prohibited conversion? (e.g. throw an exception / set default value) - When conversion requires parsing, how strict should it be? (e.g. is "$2,500" valid number, or is "no" a valid boolean value?) - Should an explicit convertCellType() method be added? In my opinion, I think that poi should be changed to option (C) - always attempt conversion, with strict parsing. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
