Examples with DataFormatter org.apache.poi.ss.usermodel.DataFormatter used on opensource projects

Example 11 with DataFormatter

use of org.apache.poi.ss.usermodel.DataFormatter in project poi by apache.

the class XLSX2CSV method processSheet.

/**
     * Parses and shows the content of one sheet
     * using the specified styles and shared-strings tables.
     *
     * @param styles The table of styles that may be referenced by cells in the sheet
     * @param strings The table of strings that may be referenced by cells in the sheet
     * @param sheetInputStream The stream to read the sheet-data from.

     * @exception java.io.IOException An IO exception from the parser,
     *            possibly from a byte stream or character stream
     *            supplied by the application.
     * @throws SAXException if parsing the XML data fails.
     */
public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler, InputStream sheetInputStream) throws IOException, SAXException {
    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
    }
}

Example 12 with DataFormatter

use of org.apache.poi.ss.usermodel.DataFormatter in project poi by apache.

the class TestBugs method bug48968.

@Test
public void bug48968() throws Exception {
    TimeZone userTimeZone = LocaleUtil.getUserTimeZone();
    LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET"));
    try {
        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());
        assertEquals(39.0, s.getRow(6).getCell(0).getNumericCellValue(), 0);
        assertEquals("SECOND(A1)", s.getRow(7).getCell(0).getCellFormula());
        assertEquals(54.0, s.getRow(7).getCell(0).getNumericCellValue(), 0);
        // Re-evaluate and check
        HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
        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());
        assertEquals(39.0, s.getRow(6).getCell(0).getNumericCellValue(), 0);
        assertEquals("SECOND(A1)", s.getRow(7).getCell(0).getCellFormula());
        assertEquals(54.0, s.getRow(7).getCell(0).getNumericCellValue(), 0);
        // Push the time forward a bit and check
        double date = s.getRow(0).getCell(0).getNumericCellValue();
        s.getRow(0).getCell(0).setCellValue(date + 1.26);
        HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
        assertEquals("HOUR(A1)", s.getRow(5).getCell(0).getCellFormula());
        assertEquals(11.0 + 6.0, s.getRow(5).getCell(0).getNumericCellValue(), 0);
        assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula());
        assertEquals(39.0 + 14.0 + 1, s.getRow(6).getCell(0).getNumericCellValue(), 0);
        assertEquals("SECOND(A1)", s.getRow(7).getCell(0).getCellFormula());
        assertEquals(54.0 + 24.0 - 60, s.getRow(7).getCell(0).getNumericCellValue(), 0);
        wb.close();
    } finally {
        LocaleUtil.setUserTimeZone(userTimeZone);
    }
}

Example 13 with DataFormatter

use of org.apache.poi.ss.usermodel.DataFormatter in project poi by apache.

the class XSSFExcelExtractor method getText.

/**
     * Retrieves the text contents of the file
     */
public String getText() {
    DataFormatter formatter;
    if (locale == null) {
        formatter = new DataFormatter();
    } else {
        formatter = new DataFormatter(locale);
    }
    StringBuffer text = new StringBuffer();
    for (Sheet sh : workbook) {
        XSSFSheet sheet = (XSSFSheet) sh;
        if (includeSheetNames) {
            text.append(sheet.getSheetName()).append("\n");
        }
        // Header(s), if present
        if (includeHeadersFooters) {
            text.append(extractHeaderFooter(sheet.getFirstHeader()));
            text.append(extractHeaderFooter(sheet.getOddHeader()));
            text.append(extractHeaderFooter(sheet.getEvenHeader()));
        }
        // Rows and cells
        for (Object rawR : sheet) {
            Row row = (Row) rawR;
            for (Iterator<Cell> ri = row.cellIterator(); ri.hasNext(); ) {
                Cell cell = ri.next();
                // Is it a formula one?
                if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    if (formulasNotResults) {
                        String contents = cell.getCellFormula();
                        checkMaxTextSize(text, contents);
                        text.append(contents);
                    } else {
                        if (cell.getCachedFormulaResultTypeEnum() == CellType.STRING) {
                            handleStringCell(text, cell);
                        } else {
                            handleNonStringCell(text, cell, formatter);
                        }
                    }
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    handleStringCell(text, cell);
                } else {
                    handleNonStringCell(text, cell, formatter);
                }
                // Output the comment, if requested and exists
                Comment comment = cell.getCellComment();
                if (includeCellComments && comment != null) {
                    // Replace any newlines with spaces, otherwise it
                    //  breaks the output
                    String commentText = comment.getString().getString().replace('\n', ' ');
                    checkMaxTextSize(text, commentText);
                    text.append(" Comment by ").append(comment.getAuthor()).append(": ").append(commentText);
                }
                if (ri.hasNext()) {
                    text.append("\t");
                }
            }
            text.append("\n");
        }
        // add textboxes
        if (includeTextBoxes) {
            XSSFDrawing drawing = sheet.getDrawingPatriarch();
            if (drawing != null) {
                for (XSSFShape shape : drawing.getShapes()) {
                    if (shape instanceof XSSFSimpleShape) {
                        String boxText = ((XSSFSimpleShape) shape).getText();
                        if (boxText.length() > 0) {
                            text.append(boxText);
                            text.append('\n');
                        }
                    }
                }
            }
        }
        // Finally footer(s), if present
        if (includeHeadersFooters) {
            text.append(extractHeaderFooter(sheet.getFirstFooter()));
            text.append(extractHeaderFooter(sheet.getOddFooter()));
            text.append(extractHeaderFooter(sheet.getEvenFooter()));
        }
    }
    return text.toString();
}