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

Example 6 with DataFormatter

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

the class TestBugs method test57925.

@Test
public void test57925() throws IOException {
    Workbook wb = HSSFTestDataSamples.openSampleWorkbook("57925.xls");
    wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        for (Row row : sheet) {
            for (Cell cell : row) {
                new DataFormatter().formatCellValue(cell);
            }
        }
    }
    wb.close();
}

Example 7 with DataFormatter

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

the class TestXSSFBReader method getSheets.

private List<String> getSheets(String testFileName) throws Exception {
    OPCPackage pkg = OPCPackage.open(_ssTests.openResourceAsStream(testFileName));
    List<String> sheetTexts = new ArrayList<String>();
    XSSFBReader r = new XSSFBReader(pkg);
    //        assertNotNull(r.getWorkbookData());
    //      assertNotNull(r.getSharedStringsData());
    assertNotNull(r.getXSSFBStylesTable());
    XSSFBSharedStringsTable sst = new XSSFBSharedStringsTable(pkg);
    XSSFBStylesTable xssfbStylesTable = r.getXSSFBStylesTable();
    XSSFBReader.SheetIterator it = (XSSFBReader.SheetIterator) r.getSheetsData();
    while (it.hasNext()) {
        InputStream is = it.next();
        String name = it.getSheetName();
        TestSheetHandler testSheetHandler = new TestSheetHandler();
        testSheetHandler.startSheet(name);
        XSSFBSheetHandler sheetHandler = new XSSFBSheetHandler(is, xssfbStylesTable, it.getXSSFBSheetComments(), sst, testSheetHandler, new DataFormatter(), false);
        sheetHandler.parse();
        testSheetHandler.endSheet();
        sheetTexts.add(testSheetHandler.toString());
    }
    return sheetTexts;
}

Example 8 with DataFormatter

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

the class TestXSSFCell method testMissingRAttributeBug54288.

@Test
public void testMissingRAttributeBug54288() throws IOException {
    // workbook with cells missing the R attribute
    XSSFWorkbook wb = (XSSFWorkbook) _testDataProvider.openSampleWorkbook("54288.xlsx");
    // same workbook re-saved in Excel 2010, the R attribute is updated for every cell with the right value.
    XSSFWorkbook wbRef = (XSSFWorkbook) _testDataProvider.openSampleWorkbook("54288-ref.xlsx");
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFSheet sheetRef = wbRef.getSheetAt(0);
    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);
            }
        }
    }
    wbRef.close();
    wb.close();
}

Example 9 with DataFormatter

use of org.apache.poi.ss.usermodel.DataFormatter in project tdi-studio-se by Talend.

the class ExcelReader method call.

public Object call() throws Exception {
    OPCPackage pkg = null;
    try {
        if (fileURL != null) {
            pkg = OPCPackage.open(fileURL);
        } else {
            pkg = PackageHelper.open(is);
        }
        XSSFReader r = new XSSFReader(pkg);
        StylesTable styles = r.getStylesTable();
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
        sheetContentsHandler = new DefaultTalendSheetContentsHandler(cache);
        DataFormatter formatter = new DataFormatter();
        boolean formulasNotResults = false;
        XMLReader parser = XMLReaderFactory.createXMLReader();
        ContentHandler handler = new TalendXSSFSheetXMLHandler(styles, strings, sheetContentsHandler, formatter, formulasNotResults);
        parser.setContentHandler(handler);
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) r.getSheetsData();
        //            List<InputStream> iss = new ArrayList<InputStream>();
        LinkedHashMap<String, InputStream> issmap = new LinkedHashMap<String, InputStream>();
        while (sheets.hasNext()) {
            InputStream sheet = sheets.next();
            String sheetName = sheets.getSheetName();
            boolean match = false;
            for (int i = 0; i < sheetNames.size(); i++) {
                if ((asRegexs.get(i) && sheetName.matches(sheetNames.get(i))) || (!asRegexs.get(i) && sheetName.equals(sheetNames.get(i)))) {
                    match = true;
                    //                        iss.add(sheet);
                    issmap.put(sheetName, sheet);
                    break;
                }
            }
            if (!match) {
                sheet.close();
            }
        }
        if (issmap.size() < 1) {
            throw new RuntimeException("No match sheets");
        }
        for (InputStream is : issmap.values()) {
            try {
                InputSource sheetSource = new InputSource(is);
                sheetSource.setEncoding(charset);
                parser.parse(sheetSource);
            } finally {
                is.close();
            }
        }
    } finally {
        if (pkg != null) {
            pkg.revert();
        }
        cache.notifyErrorOccurred();
    }
    return null;
}

Example 10 with DataFormatter

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

the class ToCSV method openWorkbook.

/**
     * Open an Excel workbook ready for conversion.
     *
     * @param file An instance of the File class that encapsulates a handle
     *        to a valid Excel workbook. Note that the workbook can be in
     *        either binary (.xls) or SpreadsheetML (.xlsx) format.
     * @throws java.io.FileNotFoundException Thrown if the file cannot be located.
     * @throws java.io.IOException Thrown if a problem occurs in the file system.
     * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown
     *         if invalid xml is found whilst parsing an input SpreadsheetML
     *         file.
     */
private void openWorkbook(File file) throws FileNotFoundException, IOException, InvalidFormatException {
    FileInputStream fis = null;
    try {
        System.out.println("Opening workbook [" + file.getName() + "]");
        fis = new FileInputStream(file);
        // Open the workbook and then create the FormulaEvaluator and
        // 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(true);
    } finally {
        if (fis != null) {
            fis.close();
        }
    }
}