Example usage for org.apache.poi.ss.usermodel DataFormatter DataFormatter

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel DataFormatter DataFormatter.

Prototype

public DataFormatter() 

Source Link

Document

Creates a formatter using the Locale#getDefault() default locale .

Usage

From source file:org.corpus_tools.peppermodules.spreadsheet.Spreadsheet2SaltMapper.java

License:Apache License

private void setDocMetaData(Workbook workbook) {
    Sheet metaSheet = null;//from w  w w  . j a  v  a  2s. c om

    // default ("Tabelle2"/ second sheet)
    if (getProps().getMetaSheet().equals("Tabelle2")) {
        if (workbook.getNumberOfSheets() > 1) {
            metaSheet = workbook.getSheetAt(1);
        }
    } else {
        // get corpus sheet by name
        metaSheet = workbook.getSheet(getProps().getCorpusSheet());
    }

    if (metaSheet != null) {
        DataFormatter formatter = new DataFormatter();
        // start with the second row of the table, since the first row holds
        // the name of each tier
        int currRow = 1;
        while (currRow < metaSheet.getPhysicalNumberOfRows()) {
            // iterate through all rows of the given meta informations

            Row row = metaSheet.getRow(currRow);
            Cell metaKey = row.getCell(0);
            Cell metaValue = row.getCell(1);

            if (metaKey != null && !metaKey.toString().isEmpty()) {
                if (metaValue != null && !metaValue.toString().isEmpty()) {
                    if (getDocument().getMetaAnnotation(metaKey.toString()) == null) {
                        getDocument().createMetaAnnotation(null, formatter.formatCellValue(metaKey),
                                formatter.formatCellValue(metaValue));
                    } else {
                        SpreadsheetImporter.logger
                                .warn("A meta information with the name \"" + formatter.formatCellValue(metaKey)
                                        + "\" allready exists and will not be replaced.");
                    }
                } else {
                    SpreadsheetImporter.logger
                            .warn("No value for the meta data: \"" + metaKey.toString() + "\" found.");
                }
            } else {
                if (metaValue != null && !metaValue.toString().isEmpty()) {
                    SpreadsheetImporter.logger.warn(
                            "No meta annotation name for the value \"" + metaValue.toString() + "\" found.");
                }
            }
            currRow++;
        }
    }
}

---

From source file:org.cytoscape.tableimport.internal.reader.ExcelAttributeSheetReader.java

License:Open Source License

public ExcelAttributeSheetReader(final Sheet sheet, final AttributeMappingParameters mapping,
        final CyServiceRegistrar serviceRegistrar) {
    this.sheet = sheet;
    this.mapping = mapping;
    this.startLineNumber = mapping.getStartLineNumber();
    this.parser = new AttributeLineParser(mapping, serviceRegistrar);
    this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    this.formatter = new DataFormatter();
}

---

From source file:org.cytoscape.tableimport.internal.reader.ExcelNetworkSheetReader.java

License:Open Source License

/**
 * Creates a new ExcelNetworkSheetReader object.
 *///from ww  w .  jav  a 2s.co m
public ExcelNetworkSheetReader(final String networkName, final Sheet sheet,
        final NetworkTableMappingParameters nmp, final Map<Object, CyNode> nMap,
        final CyRootNetwork rootNetwork, final CyServiceRegistrar serviceRegistrar) {
    super(networkName, null, nmp, nMap, rootNetwork, serviceRegistrar);
    this.sheet = sheet;
    this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    this.formatter = new DataFormatter();
}

---

From source file:org.cytoscape.tableimport.internal.ui.PreviewTablePanel.java

License:Open Source License

private PreviewTableModel parseExcel(final Sheet sheet, int startLine) throws IOException {
    int size = getPreviewSize();

    if (size == -1)
        size = Integer.MAX_VALUE;

    int maxCol = 0;
    final Vector<Vector<String>> data = new Vector<>();

    int rowCount = 0;
    int validRowCount = 0;
    FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    DataFormatter formatter = new DataFormatter();
    Row row;/*w  w  w.  j ava 2  s .  co m*/

    while (((row = sheet.getRow(rowCount)) != null) && (validRowCount < size)) {
        if (rowCount >= startLine) {
            final Vector<String> rowVector = new Vector<>();

            if (maxCol < row.getLastCellNum())
                maxCol = row.getLastCellNum();

            for (short j = 0; j < maxCol; j++) {
                Cell cell = row.getCell(j);
                if (cell == null || cell.getCellType() == Cell.CELL_TYPE_ERROR
                        || (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                                && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR)) {
                    rowVector.add(null);
                } else {
                    rowVector.add(formatter.formatCellValue(cell, evaluator));
                }
            }

            data.add(rowVector);
            validRowCount++;
        }

        rowCount++;
    }

    final boolean firstRowNames = importType == NETWORK_IMPORT || importType == TABLE_IMPORT;

    return new PreviewTableModel(data, new Vector<String>(), firstRowNames);
}

---

From source file:org.databene.formats.xls.XLSUtil.java

License:Open Source License

/** Resolves a formula or a normal cell and formats the result as it would be displayed in Excel 
 * @param cell the cell to resolve/*from w  ww. ja v  a 2  s  .  c  o  m*/
 * @param emptyMarker the string to interpret as empty field
 * @param nullMarker the string to interpret as null value
 * @param stringPreprocessor a preprocessor to apply to the raw field values
 * @return a string representation of the cell value */
public static String resolveCellValueAsString(Cell cell, String emptyMarker, String nullMarker,
        Converter<String, ?> stringPreprocessor) {
    if (cell == null)
        return null;
    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        String content = cell.getRichStringCellValue().getString();
        if (content != null) {
            if (content.equals(emptyMarker) || content.equals("'"))
                content = "";
            else if (content.equals(nullMarker))
                content = null;
        }
        if (stringPreprocessor != null)
            content = ToStringConverter.convert(stringPreprocessor.convert(content), null);
        return content;
    } else {
        DataFormatter formatter = new DataFormatter();
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA)
            return formatter.formatCellValue(cell, createFormulaEvaluator(cell));
        else
            return formatter.formatCellValue(cell);
    }
}

---

From source file:org.dhatim.fastexcel.reader.BenchmarksTest.java

License:Apache License

private void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings,
        XSSFSheetXMLHandler.SheetContentsHandler sheetHandler, InputStream sheetInputStream)
        throws IOException, SAXException {
    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    SAXParserFactory saxFactory = SAXParserFactory.newInstance();
    saxFactory.setNamespaceAware(true);/*from   w  ww  .ja v a 2 s.  co m*/
    try {
        SAXParser saxParser = saxFactory.newSAXParser();
        XMLReader sheetParser = saxParser.getXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new IllegalStateException("SAX parser appears to be broken - " + e.getMessage());
    }
}

---

From source file:org.generationcp.middleware.operation.parser.WorkbookParser.java

License:Open Source License

public static String getCellStringValue(final Workbook wb, final Cell cell) {
    if (cell == null) {
        return null;
    }//from   w  w  w .  j av  a2  s .  c o  m
    final FormulaEvaluator formulaEval = wb.getCreationHelper().createFormulaEvaluator();
    final DataFormatter formatter = new DataFormatter();
    return formatter.formatCellValue(cell, formulaEval);
}

---

From source file:org.kitodo.production.services.data.ProcessService.java

License:Open Source License

private PdfPTable getPdfTable(List<List<HSSFCell>> rowList) {
    // create formatter for cells with default locale
    DataFormatter formatter = new DataFormatter();

    PdfPTable table = new PdfPTable(9);
    table.setSpacingBefore(20);//w w  w.ja v a 2s  .c o m
    for (List<HSSFCell> row : rowList) {
        for (HSSFCell hssfCell : row) {
            String stringCellValue = formatter.formatCellValue(hssfCell);
            table.addCell(stringCellValue);
        }
    }

    return table;
}

---

From source file:org.matonto.etl.rest.impl.DelimitedRestImpl.java

License:Open Source License

/**
 * Converts the specified number of rows of a Excel file into JSON and returns
 * them as a String.// w ww.j  ava 2s  .c  o  m
 *
 * @param input the Excel file to convert into JSON
 * @param numRows the number of rows from the Excel file to convert
 * @return a string with the JSON of the Excel rows
 * @throws IOException excel file could not be read
 * @throws InvalidFormatException file is not in a valid excel format
 */
private String convertExcelRows(File input, int numRows) throws IOException, InvalidFormatException {
    Workbook wb = WorkbookFactory.create(input);
    // Only support single sheet files for now
    Sheet sheet = wb.getSheetAt(0);
    DataFormatter df = new DataFormatter();
    JSONArray rowList = new JSONArray();
    String[] columns;
    for (Row row : sheet) {
        if (row.getRowNum() <= numRows) {
            columns = new String[row.getPhysicalNumberOfCells()];
            int index = 0;
            for (Cell cell : row) {
                columns[index] = df.formatCellValue(cell);
                index++;
            }
            rowList.add(columns);
        }
    }

    return rowList.toString();
}

---

From source file:org.matonto.etl.rest.impl.DelimitedRestImplTest.java

License:Open Source License

private List<String> getExcelResourceLines(String fileName) {
    List<String> expectedLines = new ArrayList<>();
    try {//from w  w w  .j  a va2 s . com
        Workbook wb = WorkbookFactory.create(getClass().getResourceAsStream("/" + fileName));
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        int index = 0;
        for (Row row : sheet) {
            String rowStr = "";
            for (Cell cell : row) {
                rowStr += df.formatCellValue(cell);
            }
            expectedLines.add(index, rowStr);
            index++;
        }
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
    return expectedLines;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• »