Package org.apache.poi.ss.usermodel

Examples of org.apache.poi.ss.usermodel.DataFormatter$SSNFormat


    public static double getColumnWidth(Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow){
        AttributedString str;
        TextLayout layout;

        Workbook wb = sheet.getWorkbook();
        DataFormatter formatter = new DataFormatter();
        Font defaultFont = wb.getFontAt((short) 0);

        str = new AttributedString(String.valueOf(defaultChar));
        copyAttributes(defaultFont, str, 0, 1);
        layout = new TextLayout(str.getIterator(), fontRenderContext);
View Full Code Here


    @Test
    public void bug48968() throws Exception {
       HSSFWorkbook wb = openSample("48968.xls");
       assertEquals(1, wb.getNumberOfSheets());
      
       DataFormatter fmt = new DataFormatter();

       // Check the dates
       HSSFSheet s = wb.getSheetAt(0);
       Cell cell_d20110325 = s.getRow(0).getCell(0);
       Cell cell_d19000102 = s.getRow(11).getCell(0);
       Cell cell_d19000100 = s.getRow(21).getCell(0);
       assertEquals(s.getRow(0).getCell(3).getStringCellValue(), fmt.formatCellValue(cell_d20110325));
       assertEquals(s.getRow(11).getCell(3).getStringCellValue(), fmt.formatCellValue(cell_d19000102));
       // There is no such thing as 00/01/1900...
       assertEquals("00/01/1900 06:14:24", s.getRow(21).getCell(3).getStringCellValue());
       assertEquals("31/12/1899 06:14:24", fmt.formatCellValue(cell_d19000100));
      
       // Check the cached values
       assertEquals("HOUR(A1)",   s.getRow(5).getCell(0).getCellFormula());
       assertEquals(11.0,         s.getRow(5).getCell(0).getNumericCellValue(), 0);
       assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula());
View Full Code Here

        assertEquals(sheetRef.getPhysicalNumberOfRows(), sheet.getPhysicalNumberOfRows());

        // Test idea: iterate over cells in the reference worksheet, they all have the R attribute set.
        // For each cell from the reference sheet find the corresponding cell in the problematic file (with missing R)
        // and assert that POI reads them equally:
        DataFormatter formater = new DataFormatter();
        for(Row r : sheetRef){
            XSSFRow rowRef = (XSSFRow)r;
            XSSFRow row = sheet.getRow(rowRef.getRowNum());

            assertEquals("number of cells in row["+row.getRowNum()+"]",
                    rowRef.getPhysicalNumberOfCells(), row.getPhysicalNumberOfCells());

            for(Cell c :  rowRef){
                XSSFCell cellRef = (XSSFCell)c;
                XSSFCell cell = row.getCell(cellRef.getColumnIndex());

                assertEquals(cellRef.getColumnIndex(), cell.getColumnIndex());
                assertEquals(cellRef.getReference(), cell.getReference());

                if(!cell.getCTCell().isSetR()){
                    assertTrue("R must e set in cellRef", cellRef.getCTCell().isSetR());

                    String valRef = formater.formatCellValue(cellRef);
                    String val = formater.formatCellValue(cell);
                    assertEquals(valRef, val);
                }

            }
        }
View Full Code Here

            // DataFormatter instances that will be needed to, respectively,
            // force evaluation of forumlae found in cells and create a
            // formatted String encapsulating the cells contents.
            this.workbook = WorkbookFactory.create(fis);
            this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
            this.formatter = new DataFormatter();
        }
        finally {
            if(fis != null) {
                fis.close();
            }
View Full Code Here

    private void processSheet( Sheet sheet,
                               List<? extends DataListener> listeners ) {
        int maxRows = sheet.getLastRowNum();

        CellRangeAddress[] mergedRanges = getMergedCells( sheet );
        DataFormatter formatter = new DataFormatter();
        FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

        for ( int i = 0; i <= maxRows; i++ ) {
            Row row = sheet.getRow( i );
            int lastCellNum = row != null ? row.getLastCellNum() : 0;
            newRow( listeners, i, lastCellNum );

            for ( int cellNum = 0; cellNum < lastCellNum; cellNum++ ) {
                Cell cell = row.getCell( cellNum );
                if ( cell == null ) {
                    continue;
                }
                double num = 0;

                CellRangeAddress merged = getRangeIfMerged( cell,
                                                            mergedRanges );

                if ( merged != null ) {
                    Cell topLeft = sheet.getRow( merged.getFirstRow() ).getCell( merged.getFirstColumn() );
                    newCell( listeners,
                             i,
                             cellNum,
                             formatter.formatCellValue( topLeft ),
                             topLeft.getColumnIndex() );

                } else {
                    switch ( cell.getCellType() ) {
                        case Cell.CELL_TYPE_FORMULA:
                            String cellValue = null;
                            try {
                                CellValue cv = formulaEvaluator.evaluate( cell );
                                cellValue = getCellValue( cv );
                                newCell( listeners,
                                         i,
                                         cellNum,
                                         cellValue,
                                         DataListener.NON_MERGED );
                            } catch ( RuntimeException e ) {
                                // This is thrown if an external link cannot be resolved, so try the cached value
                                log.warn("Cannot resolve externally linked value: " + formatter.formatCellValue( cell ));
                                String cachedValue = tryToReadCachedValue(cell);
                                newCell( listeners,
                                         i,
                                         cellNum,
                                         cachedValue,
                                         DataListener.NON_MERGED );
                            }
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            num = cell.getNumericCellValue();
                        default:
                            if ( num - Math.round( num ) != 0 ) {
                                newCell( listeners,
                                         i,
                                         cellNum,
                                         String.valueOf( num ),
                                         DataListener.NON_MERGED );
                            } else {
                                newCell( listeners,
                                         i,
                                         cellNum,
                                         formatter.formatCellValue( cell ),
                                         DataListener.NON_MERGED );
                            }
                    }
                }
            }
View Full Code Here

        }
        finishSheet( listeners );
    }

    private String tryToReadCachedValue(Cell cell) {
        DataFormatter formatter = new DataFormatter();
        String cachedValue;
        switch (cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
                double num = cell.getNumericCellValue();
                if (num - Math.round(num) != 0) {
                    cachedValue = String.valueOf(num);
                } else {
                    cachedValue = formatter.formatCellValue(cell);
                }
                break;

            case Cell.CELL_TYPE_STRING:
                cachedValue = cell.getStringCellValue();
View Full Code Here

    private void processSheet( Sheet sheet,
                               List<? extends DataListener> listeners ) {
        int maxRows = sheet.getLastRowNum();

        CellRangeAddress[] mergedRanges = getMergedCells( sheet );
        DataFormatter formatter = new DataFormatter( Locale.ENGLISH );
        FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

        for ( int i = 0; i <= maxRows; i++ ) {
            Row row = sheet.getRow( i );
            int lastCellNum = row != null ? row.getLastCellNum() : 0;
            newRow( listeners, i, lastCellNum );

            for ( int cellNum = 0; cellNum < lastCellNum; cellNum++ ) {
                Cell cell = row.getCell( cellNum );
                if ( cell == null ) {
                    continue;
                }
                double num = 0;

                CellRangeAddress merged = getRangeIfMerged( cell,
                                                            mergedRanges );

                if ( merged != null ) {
                    Cell topLeft = sheet.getRow( merged.getFirstRow() ).getCell( merged.getFirstColumn() );
                    newCell( listeners,
                             i,
                             cellNum,
                             formatter.formatCellValue( topLeft ),
                             topLeft.getColumnIndex() );

                } else {
                    switch ( cell.getCellType() ) {
                        case Cell.CELL_TYPE_FORMULA:
                            String cellValue = null;
                            try {
                                CellValue cv = formulaEvaluator.evaluate( cell );
                                cellValue = getCellValue( cv );
                                newCell( listeners,
                                         i,
                                         cellNum,
                                         cellValue,
                                         DataListener.NON_MERGED );
                            } catch ( RuntimeException e ) {
                                // This is thrown if an external link cannot be resolved, so try the cached value
                                log.warn( "Cannot resolve externally linked value: " + formatter.formatCellValue( cell ) );
                                String cachedValue = tryToReadCachedValue( cell );
                                newCell( listeners,
                                         i,
                                         cellNum,
                                         cachedValue,
                                         DataListener.NON_MERGED );
                            }
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            num = cell.getNumericCellValue();
                        default:
                            if ( num - Math.round( num ) != 0 ) {
                                newCell( listeners,
                                         i,
                                         cellNum,
                                         String.valueOf( num ),
                                         DataListener.NON_MERGED );
                            } else {
                                newCell( listeners,
                                         i,
                                         cellNum,
                                         formatter.formatCellValue( cell ),
                                         DataListener.NON_MERGED );
                            }
                    }
                }
            }
View Full Code Here

        }
        finishSheet( listeners );
    }

    private String tryToReadCachedValue( Cell cell ) {
        DataFormatter formatter = new DataFormatter( Locale.ENGLISH );
        String cachedValue;
        switch ( cell.getCachedFormulaResultType() ) {
            case Cell.CELL_TYPE_NUMERIC:
                double num = cell.getNumericCellValue();
                if ( num - Math.round( num ) != 0 ) {
                    cachedValue = String.valueOf( num );
                } else {
                    cachedValue = formatter.formatCellValue( cell );
                }
                break;

            case Cell.CELL_TYPE_STRING:
                cachedValue = cell.getStringCellValue();
View Full Code Here

        this.extractor = extractor;
        extractor.setFormulasNotResults(false);
        extractor.setLocale(locale);
       
        if(locale == null) {
           formatter = new DataFormatter();
        } else  {
           formatter = new DataFormatter(locale);
        }
    }
View Full Code Here

     */
    public void test47490() throws Exception {
       XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("GeneralFormatTests.xlsx");
       Sheet s = wb.getSheetAt(1);
       Row r;
       DataFormatter df = new DataFormatter();
      
       r = s.getRow(1);
       assertEquals(1.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("1", df.formatCellValue(r.getCell(2)));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "@"));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "General"));
             
       r = s.getRow(2);
       assertEquals(12.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("12", df.formatCellValue(r.getCell(2)));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "@"));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "General"));
      
       r = s.getRow(3);
       assertEquals(123.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("123", df.formatCellValue(r.getCell(2)));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "@"));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "General"));
    }
View Full Code Here

TOP

Related Classes of org.apache.poi.ss.usermodel.DataFormatter$SSNFormat

Copyright © 2018 www.massapicom. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.