Bug 60959 - Setting the cell to type numeric results in cell type blank
Summary: Setting the cell to type numeric results in cell type blank
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.16-dev
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
: 60958 (view as bug list)
Depends on:
Blocks:
 
Reported: 2017-04-06 13:06 UTC by Rishi Ranjan
Modified: 2017-05-29 19:51 UTC (History)
1 user (show)



Attachments
The java code which collects celltype from a cell and writes it to another cell. But the celltype NUMERIC is inherited as blank. (1.18 KB, text/x-java)
2017-04-06 13:22 UTC, Rishi Ranjan
Details
The test xlsx file (5.31 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-04-06 13:37 UTC, Rishi Ranjan
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Rishi Ranjan 2017-04-06 13:06:52 UTC
The CELL_TYPE_NUMERIC doesn't produce the numeric cell type and rather creates a cell with blank CellType.
Every other CellType :
CELL_TYPE_BLANK
CELL_TYPE_STRING
CELL_TYPE_FORMULA
CELL_TYPE_ERROR
CELL_TYPE_BOOLEAN
is working properly.

The Output of the code:
NUMERIC BLANK
NUMERIC BLANK

NUMERIC BLANK
STRING STRING
FORMULA FORMULA

STRING STRING
BLANK BLANK

NUMERIC BLANK
BOOLEAN BOOLEAN
Comment 1 Rishi Ranjan 2017-04-06 13:22:32 UTC
Created attachment 34900 [details]
The java code which collects celltype from a cell and writes it to another cell.
But the celltype NUMERIC is inherited as blank.
Comment 2 Rishi Ranjan 2017-04-06 13:37:48 UTC
Created attachment 34901 [details]
The test xlsx file
Comment 3 Javen O'Neal 2017-04-06 16:24:42 UTC
*** Bug 60958 has been marked as a duplicate of this bug. ***
Comment 4 Javen O'Neal 2017-04-06 16:28:22 UTC
What happens when you copy the cell value rather than copying the cell type without the value?
Comment 5 Dominik Stadler 2017-05-29 19:51:42 UTC
This is kind of "on purpose" right now:

    private CellType XSSFCell.getBaseCellType(boolean blankCells) {
        switch (_cell.getT().intValue()) {
            case STCellType.INT_N:
                if (!_cell.isSetV() && blankCells) {
                    // ooxml does have a separate cell type of 'blank'.  A blank cell gets encoded as
                    // (either not present or) a numeric cell with no value set.
                    // The formula evaluator (and perhaps other clients of this interface) needs to
                    // distinguish blank values which sometimes get translated into zero and sometimes
                    // empty string, depending on context
                    return CellType.BLANK;
                }
                return CellType.NUMERIC;


So a workaround is to set a value of "0" via

                if(addd == CellType.NUMERIC) {
                    cell2.setCellValue(0.0);
                }

A potential "fix" would be to set a zero-value at XSSFCell.setCellType():

                if(!_cell.isSetV()) {
                    _cell.setV("0.0");
                }

However there are likely some side-effects of doing so...