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

Example 1 with DataFormatter

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

the class SheetUtil method getColumnWidth.

/**
     * Compute width of a column based on a subset of the rows and return the result
     *
     * @param sheet the sheet to calculate
     * @param column    0-based index of the column
     * @param useMergedCells    whether to use merged cells
     * @param firstRow  0-based index of the first row to consider (inclusive)
     * @param lastRow   0-based index of the last row to consider (inclusive)
     * @return  the width in pixels or -1 if cell is empty
     */
public static double getColumnWidth(Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow) {
    DataFormatter formatter = new DataFormatter();
    int defaultCharWidth = getDefaultCharWidth(sheet.getWorkbook());
    double width = -1;
    for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
        Row row = sheet.getRow(rowIdx);
        if (row != null) {
            double cellWidth = getColumnWidthForRow(row, column, defaultCharWidth, formatter, useMergedCells);
            width = Math.max(width, cellWidth);
        }
    }
    return width;
}

Example 2 with DataFormatter

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

the class XSSFBEventBasedExcelExtractor method processSheet.

/**
     * Processes the given sheet
     */
public void processSheet(SheetContentsHandler sheetContentsExtractor, XSSFBStylesTable styles, XSSFBCommentsTable comments, XSSFBSharedStringsTable strings, InputStream sheetInputStream) throws IOException, SAXException {
    DataFormatter formatter;
    if (getLocale() == null) {
        formatter = new DataFormatter();
    } else {
        formatter = new DataFormatter(getLocale());
    }
    XSSFBSheetHandler xssfbSheetHandler = new XSSFBSheetHandler(sheetInputStream, styles, comments, strings, sheetContentsExtractor, formatter, getFormulasNotResults());
    xssfbSheetHandler.parse();
}

Example 3 with DataFormatter

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

the class XSSFEventBasedExcelExtractor method processSheet.

/**
     * Processes the given sheet
     */
public void processSheet(SheetContentsHandler sheetContentsExtractor, StylesTable styles, CommentsTable comments, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws IOException, SAXException {
    DataFormatter formatter;
    if (locale == null) {
        formatter = new DataFormatter();
    } else {
        formatter = new DataFormatter(locale);
    }
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(styles, comments, strings, sheetContentsExtractor, formatter, formulasNotResults);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
    }
}

Example 4 with DataFormatter

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

the class CellStyleDetails method main.

public static void main(String[] args) throws Exception {
    if (args.length == 0) {
        throw new IllegalArgumentException("Filename must be given");
    }
    Workbook wb = WorkbookFactory.create(new File(args[0]));
    DataFormatter formatter = new DataFormatter();
    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        Sheet sheet = wb.getSheetAt(sn);
        System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());
        for (Row row : sheet) {
            System.out.println("  Row " + row.getRowNum());
            for (Cell cell : row) {
                CellReference ref = new CellReference(cell);
                System.out.print("    " + ref.formatAsString());
                System.out.print(" (" + cell.getColumnIndex() + ") ");
                CellStyle style = cell.getCellStyle();
                System.out.print("Format=" + style.getDataFormatString() + " ");
                System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " ");
                System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " ");
                Font font = wb.getFontAt(style.getFontIndex());
                System.out.print("Font=" + font.getFontName() + " ");
                System.out.print("FontColor=");
                if (font instanceof HSSFFont) {
                    System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb)));
                }
                if (font instanceof XSSFFont) {
                    System.out.print(renderColor(((XSSFFont) font).getXSSFColor()));
                }
                System.out.println();
                System.out.println("        " + formatter.formatCellValue(cell));
            }
        }
        System.out.println();
    }
    wb.close();
}

Example 5 with DataFormatter

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

the class TestXSSFDataFormat method testConditionalFormattingEvaluation.

@Test
public void testConditionalFormattingEvaluation() throws IOException {
    final Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61060-conditional-number-formatting.xlsx");
    final DataFormatter formatter = new DataFormatter();
    final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    final ConditionalFormattingEvaluator cfEvaluator = new ConditionalFormattingEvaluator(wb, (WorkbookEvaluatorProvider) evaluator);
    CellReference ref = new CellReference("A1");
    Cell cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("0.10", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("0.1", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A3");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("-2.00E+03", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("-2000", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A4");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("100", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    ref = new CellReference("A5");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("$1,000", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("1000", formatter.formatCellValue(cell, evaluator));
    wb.close();
}