1

I have a spreadsheet with the date 20160831.

Up until now I was using:

if (Cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
    if (DateUtil.isCellDateFormatted(cell)) {
        // parse the date (irrelevant to this question as ^^ is false)
    } else {
        Cell.setCellType(Cell.CELL_TYPE_STRING);
        // do some work with Cell.getStringCellValue();
    }
}

However, the string returned from Cell.getStringCellValue(), in this case, is not "20160831", but, instead, "20160930".

I have printed cell.getNumericCellValue(); which returns 2.016093E7. This is causing me to lose my "1" in "31" upon conversion to String.

I have attempted use DataFormatter in hopes of displaying "data exactly as it appears in Excel". (https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html#formatCellValue(org.apache.poi.ss.usermodel.Cell))

I have attempted to use cell.getCellDateValue() regardless, to worse effect.

I have attempted to use DecimalFormat to the same effect.

I have attempted to use NumberToTextConverter to the same effect.

I always lose my "1" in "31". I find this to be the most troubling issue at hand, although I am also getting back the incorrect month value in this date (any insight on that would also be most helpful).

Aside from manually exporting the spreadsheet to a csv file before parsing, I cannot find a way to resolve this issue. Meanwhile, the multitude of other files are working perfectly fine with the code above, as DateUtils typically returns true.

Edit:

I'm adding the full block (which isn't much more than what I originally posted, as well as a screen shot of the spreadsheet, and a copy of the printouts.

private Transitional parseValueForTransitionalWithPropertyAndVector(Cell cell,
            Transitional transitional, Property property, Vector vector) {

        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                logger.log(Level.INFO,
                        "parseValueForTransitionalWithPropertyAndVector() | cellDeterminedToHoldASerialiazedDate");
                Calendar calendar = new GregorianCalendar();
                Date date = null;
                try {
                    date = cell.getDateCellValue();
                    calendar.setTime(date);
                    if (property.getPropTypeKey() == TransitionalHelper.PROP_TYPE_KEY.PAYSTART) {
                        transitional.setPayPeriodStart(calendar);
                        logger.log(Level.INFO,
                                "parseValueForTransitionalWithPropertyAndVector() payStart[MM/DD/YYYY]: "
                                        + calendar.get(Calendar.MONTH) + "/" + calendar.get(Calendar.DAY_OF_MONTH) + "/"
                                        + calendar.get(Calendar.YEAR));
                    } else if (property.getPropTypeKey() == TransitionalHelper.PROP_TYPE_KEY.PAYEND) {
                        transitional.setPayPeriodEnd(calendar);
                        logger.log(Level.INFO,
                                "parseValueForTransitionalWithPropertyAndVector() payEnd[MM/DD/YYYY]: "
                                        + calendar.get(Calendar.MONTH) + "/" + calendar.get(Calendar.DAY_OF_MONTH) + "/"
                                        + calendar.get(Calendar.YEAR));
                    } else if (property.getPropTypeKey() == TransitionalHelper.PROP_TYPE_KEY.PAYDATE) {
                        transitional.setPayDate(calendar);
                        logger.log(Level.INFO,
                                "parseValueForTransitionalWithPropertyAndVector() payDate[MM/DD/YYYY]: "
                                        + calendar.get(Calendar.MONTH) + "/" + calendar.get(Calendar.DAY_OF_MONTH) + "/"
                                        + calendar.get(Calendar.YEAR));
                    }
                } catch (IllegalStateException e) {
                    e.printStackTrace();
                }
            } else {

                // Added to debug >>
                System.out.println("cell.getNumericCellValue(): " + cell.getNumericCellValue());
                DataFormatter formatter = new DataFormatter();
                System.out.println("DataFormatter output: " + formatter.formatCellValue(cell));
                System.out.println("DecimalFormat output: " + new DecimalFormat("0").format( cell.getNumericCellValue()));
                System.out.println("NumberToTextConverter output: " + NumberToTextConverter.toText(cell.getNumericCellValue()));
                cell.setCellType(Cell.CELL_TYPE_STRING);
                System.out.println("cell.getStringCellValue(): " + cell.getStringCellValue());
                // <<
                transitional = parseValueForTransitionalWithPropertyAndVector(cell.getStringCellValue(),
                        transitional, property, vector);
            }
        } else {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            transitional = parseValueForTransitionalWithPropertyAndVector(cell.getStringCellValue(),
                    transitional, property, vector);
        }

        return transitional;

    }`

For clarification:

A Transitional is the object the parsed values are being attached to. A Property defines how the value in the cell is parsed. A Vector is a map that shows where the value was found.

The output:

cell.getNumericCellValue(): 2.016093E7
DataFormatter output: 20160930
DecimalFormat output: 20160930
NumberToTextConverter output: 20160930
cell.getStringCellValue(): 20160930

A screenshot of the relevant cell from the spreadsheet (I cannot upload the entire spreadsheet):

anomeric
  • 688
  • 5
  • 17
  • Dates in Excel are stored as fractional days since 1900 or 1904, so will never look like a "normal" date. What happens if you force Excel to format your string as a "proper" date then use POI? – Gagravarr Dec 22 '16 at 00:46
  • That's what the DateUtils is meant to do. – anomeric Dec 22 '16 at 05:35
  • Nope, DateUtils is for converting between the special Excel way of storing dates (as detailed above) and java dates. You don't appear to have an Excel date, just a really long number! – Gagravarr Dec 23 '16 at 01:54
  • @Gagravarr: "...just a really long number". Although `Excel`'s floating point precision is really not much it is at least double precision (15 digits) and not only single precision (7 digits). So the whole thing which was told here is not possible as such. A number `20160831` **is** readable from `Excel` without losing precision. – Axel Richter Dec 23 '16 at 07:42
  • 2
    Are you sure you are reading the correct cell from the spreadsheet? I don't see how a value of 20160831 can be returned as 2.016093E7? No loss of accuracy would convert that number to 2.016093E7. Check which cell you are reading from by printing out its location using System.out.println(new CellReference(Cell).formatAsString()); – Wee Shetland Dec 23 '16 at 20:20
  • The code I've put up is written exactly the same way on the server. In the CELL_TYPE_NUMERIC block, printing cell. GetNumericCellValue shows 2.016093E7. There is a self written vector tracking system that displays the row and column index of the cell the value is retrieved from; it is the correct cell. While I understand why you say it is not possible, given the numericcellvalue is a double, it is in fact what it is printing. This is why I'm having trouble. – anomeric Dec 24 '16 at 00:00
  • There is also only one cell in each row of records with a number value. Given the system has worked across, literally, thousands of other spreadsheets I am inclined to believe there is not a problem with getting the correct cell. – anomeric Dec 24 '16 at 00:04
  • 1
    @user3019914: Please provide an Excel file uploaded and the exact code with which this data inaccuracy (20160831 -> 2.016093E7) happens. Otherwise this question is not answerable since nobody can reproducing this. – Axel Richter Dec 24 '16 at 10:06
  • All of the relevant code is already there aside from opening the workbook from a file, getting the sheet, pointing to row x and cell y, and a System.out.println(cell.getNumericCellValue);. The problem is that the sys out is printing a float instead of a double. All of the attenpted solutions have also been posted. – anomeric Dec 24 '16 at 19:41
  • "The problem is that the sys out is printing a float instead of a double." No, taking `20160831` as a float value with single precision also **cannot** lead to `2.016093E7`. Even with `20160931` the single precision value would be `2.0160932E7`. The `Cell`' s value **must** be `20160930`. You definitely have not the `Cell` you are supposing to have there. – Axel Richter Dec 25 '16 at 07:03

0 Answers0