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

List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DataFormatter
  6. 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:SwiftSeleniumWeb.WebHelper.java

License:Open Source License

@SuppressWarnings("null")
public static String getCellData(String reqValue, HSSFSheet reqSheet, int rowIndex,
        HashMap<String, Object> inputHashTable) throws IOException {
    HSSFCell reqCell = null;//from ww  w  .j  ava  2s .c  om
    Object actualvalue = null;
    String req = "";
    DataFormatter fmt = new DataFormatter();
    if (inputHashTable.isEmpty() == true) {
        inputHashTable = getValueFromHashMap(reqSheet);
    }
    HSSFRow rowActual = reqSheet.getRow(rowIndex);
    if (inputHashTable.get(reqValue) == null) {
        SwiftSeleniumWeb.WebDriver.report
                .setStrMessage("Column " + reqValue + " not Found. Please Check input Sheet");
        MainController.pauseFun("Column " + reqValue + " not Found. Please Check input Sheet");
    } else {
        actualvalue = inputHashTable.get(reqValue);//rowHeader.getCell(colIndex).toString();         
        if (actualvalue != null) {
            int colIndex = Integer.parseInt(actualvalue.toString());
            reqCell = rowActual.getCell(colIndex);
            if (reqCell == null) {
                System.out.println(reqValue + " is Null");
            } else {
                int type = reqCell.getCellType();
                switch (type) {
                case HSSFCell.CELL_TYPE_BLANK:
                    req = "";
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    req = fmt.formatCellValue(reqCell);
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    req = reqCell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    req = Boolean.toString(reqCell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    req = "error";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    req = reqCell.getCellFormula();
                    break;
                }
            }
        }

        else {
            req = reqCell.getStringCellValue();
            System.out.println("null");
        }
    }
    return req;
}

From source file:Tools.CompareDrotVSRoster.java

private void foregroundColorSetUpV1(Row row, XSSFWorkbook myWorkBook) {
    if (row != null) {
        Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1);
        //DataFormatter df = new DataFormatter();
        // String lastFour = df.formatCellValue(ssnCell); //return ***-**-****
        DataFormatter df = new DataFormatter();
        //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd");   
        // String lastFour = df.formatCellValue(ssnCell); 
        String lastFour = GlobalVar.last4Generator(df.formatCellValue(ssnCell));
        Cell lastNameCell = row.getCell(GlobalVar.LAST_NAME_CELL_INDEX_V1);
        String lastName = null;//from   w ww  .ja va2s .  c  om
        if (lastNameCell != null) {
            lastName = lastNameCell.getStringCellValue();
        }
        //System.out.println(lastName);
        String ssn = DB.getSSN(lastName, lastFour);
        if (ssn != null) {
            Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1);
            String ctrlNumString = ctrlNumCell.getStringCellValue();
            if (proclibrary.containsKey(ssn)) { // Map< SSN, Map<ctrlNum, signOutdate>>
                List<String> leaves = proclibrary.get(ssn);
                if (leaves.contains(ctrlNumString)) {
                    CellStyle style = createStandardStyle(myWorkBook);
                    style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
                    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    ctrlNumCell.setCellStyle(style);
                }
            } else if (rejlibrary.containsKey(ssn)) {
                List<String> leaves = rejlibrary.get(ssn);
                if (leaves.contains(ctrlNumString)) {
                    CellStyle style = createStandardStyle(myWorkBook);
                    style.setFillForegroundColor(IndexedColors.RED.getIndex());
                    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    ctrlNumCell.setCellStyle(style);
                }
            } else if (recylibrary.containsKey(ssn)) {
                List<String> leaves = recylibrary.get(ssn);
                if (leaves.contains(ctrlNumString)) {
                    CellStyle style = createStandardStyle(myWorkBook);
                    style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    ctrlNumCell.setCellStyle(style);
                }
            }
        }
    }
}

From source file:Tools.PostProcessing.java

private Map<String, Integer> generateXLSXforS1(String XLSXfileName) throws FileNotFoundException, IOException {
    Map<String, Integer> statusMap = null;
    File myFile = new File(XLSXfileName);

    FileInputStream fis = new FileInputStream(myFile);
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

    //Return first sheet from the XLSX workbook
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    List<String> errorList = new ArrayList<>();
    Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>();
    //Get iterator to all the rows in current sheet
    Iterator<Row> rowIterator = mySheet.iterator();
    int lineCount = 1;
    // Traversing over each row of XLSX file
    if (rowIterator.hasNext()) {

        Row headerRow = rowIterator.next(); //skip the header row
        Iterator<Cell> it = headerRow.cellIterator();
        int numCell = 0;
        List<String> keyList = new ArrayList<String>(); //keep track info of each column

        while (it.hasNext()) {
            keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList                  
            numCell++;//from w w w .  jav a2s  .c  o m
        }
        if (numCell == GlobalVar.LEAVE_TITLES_V2.length) {
            // System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded.");
            //                int globalCount = 1;
            DataFormatter df = new DataFormatter();
            statusMap = new TreeMap<>();
            while (rowIterator.hasNext()) {
                lineCount++;
                Row row = rowIterator.next();
                Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2);
                Cell ssnCell = row.getCell(GlobalVar.FULL_SSN_CELL_INDEX_V2);
                String fullSSN = GlobalVar.fullSSNgenerator(df.formatCellValue(ssnCell));
                colorDeletedLeaves(fullSSN, ctrlNumCell, myWorkBook, statusMap);
            }
            // output to a new xlsx file
            fis.close();
            FileOutputStream output;
            String targetFile = null;
            if (XLSXfileName.contains(".xlsx")) {
                targetFile = XLSXfileName.replace(".xlsx", "_forS1.xlsx");
            } else {
                targetFile = XLSXfileName + "_forS1.xlsx";
            }
            output = new FileOutputStream(targetFile);
            myWorkBook.write(output);
            output.close();

        } else {
            JOptionPane.showMessageDialog(null, "XLSX file format is incorrect! Must be full SSN format");
        }
    }
    return statusMap;
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoColWidthsTest.java

License:Open Source License

@Test
public void testRunReport() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("AutoColWidths.rptdesign", "xlsx");
    assertNotNull(inputStream);//from w w w  .  j a va 2  s . c o  m
    try {

        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("AutoColWidths Test Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(23, this.firstNullRow(sheet));

        assertEquals(6127, sheet.getColumnWidth(0));
        assertEquals(2048, sheet.getColumnWidth(1));
        assertEquals(4999, sheet.getColumnWidth(2));
        assertEquals(3812, sheet.getColumnWidth(3));
        assertEquals(3812, sheet.getColumnWidth(4));
        assertEquals(2048, sheet.getColumnWidth(5));
        assertTrue((sheet.getColumnWidth(6) > 3000) && (sheet.getColumnWidth(6) < 3200));
        assertTrue((sheet.getColumnWidth(7) > 2100) && (sheet.getColumnWidth(7) < 2900));
        assertEquals(2048, sheet.getColumnWidth(8));

        DataFormatter formatter = new DataFormatter();

        assertEquals("1", formatter.formatCellValue(sheet.getRow(2).getCell(1)));
        assertEquals("2019-10-11 13:18:46", formatter.formatCellValue(sheet.getRow(2).getCell(2)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(2).getCell(3)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(2).getCell(4)));
        assertEquals("false", formatter.formatCellValue(sheet.getRow(2).getCell(5)));

    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.BackgroundFormatsTests.java

License:Open Source License

@Test
public void testRunReportXlsx() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("BackgroundColours.rptdesign", "xlsx");
    assertNotNull(inputStream);/*from   w ww .j  a v  a  2s  . c  o m*/
    try {

        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Background Colours Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(3, this.firstNullRow(sheet));

        DataFormatter formatter = new DataFormatter();

        assertEquals("1", formatter.formatCellValue(sheet.getRow(1).getCell(1)));
        assertEquals("2019-10-11 13:18:46", formatter.formatCellValue(sheet.getRow(1).getCell(2)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(1).getCell(3)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(1).getCell(4)));
        assertEquals("false", formatter.formatCellValue(sheet.getRow(1).getCell(5)));

        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(0).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(1).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(2).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(3).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(4).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(5).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(6).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        /*
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(0).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(1).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(2).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(3).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(4).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(5).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(6).getCellStyle().getFillForegroundColorColor()));
        */
        assertEquals("FFFF0000",
                ((XSSFColor) sheet.getRow(1).getCell(1).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FFFFA500",
                ((XSSFColor) sheet.getRow(1).getCell(2).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FFFFFF00",
                ((XSSFColor) sheet.getRow(1).getCell(3).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF008000",
                ((XSSFColor) sheet.getRow(1).getCell(4).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF0000FF",
                ((XSSFColor) sheet.getRow(1).getCell(5).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF800080",
                ((XSSFColor) sheet.getRow(1).getCell(6).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(1).getCell(7).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());

    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.BackgroundFormatsTests.java

License:Open Source License

@Test
public void testRunReportXls() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("BackgroundColours.rptdesign", "xls");
    assertNotNull(inputStream);//from w  ww .  j  a v a  2 s . c  o  m
    try {

        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Background Colours Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(3, this.firstNullRow(sheet));

        DataFormatter formatter = new DataFormatter();

        assertEquals("1", formatter.formatCellValue(sheet.getRow(1).getCell(1)));
        assertEquals("2019-10-11 13:18:46", formatter.formatCellValue(sheet.getRow(1).getCell(2)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(1).getCell(3)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(1).getCell(4)));
        assertEquals("false", formatter.formatCellValue(sheet.getRow(1).getCell(5)));

        assertEquals("FFFF:0:0",
                ((HSSFColor) sheet.getRow(1).getCell(1).getCellStyle().getFillForegroundColorColor())
                        .getHexString());
        assertEquals("FFFF:A5A5:0",
                ((HSSFColor) sheet.getRow(1).getCell(2).getCellStyle().getFillForegroundColorColor())
                        .getHexString());
        assertEquals("FFFF:FFFF:0",
                ((HSSFColor) sheet.getRow(1).getCell(3).getCellStyle().getFillForegroundColorColor())
                        .getHexString());
        assertEquals("0:8080:0",
                ((HSSFColor) sheet.getRow(1).getCell(4).getCellStyle().getFillForegroundColorColor())
                        .getHexString());
        assertEquals("0:0:FFFF",
                ((HSSFColor) sheet.getRow(1).getCell(5).getCellStyle().getFillForegroundColorColor())
                        .getHexString());
        assertEquals("8080:0:8080",
                ((HSSFColor) sheet.getRow(1).getCell(6).getCellStyle().getFillForegroundColorColor())
                        .getHexString());
        assertEquals("0:0:0",
                ((HSSFColor) sheet.getRow(1).getCell(7).getCellStyle().getFillForegroundColorColor())
                        .getHexString());

    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.CurrencyFormatsTest.java

License:Open Source License

@Test
public void testRunReportXls() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("CurrencyFormats.rptdesign", "xls");
    assertNotNull(inputStream);//from   ww w .  j a va 2 s.  c o  m
    try {

        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Currency Formats Test Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(5, this.firstNullRow(sheet));

        DataFormatter formatter = new DataFormatter();

        assertEquals("3141.59", formatter.formatCellValue(sheet.getRow(1).getCell(1)));
        assertEquals("$3141.59", formatter.formatCellValue(sheet.getRow(2).getCell(1)));
        assertEquals("3141.59", formatter.formatCellValue(sheet.getRow(3).getCell(1)));
        assertEquals("3141.59", formatter.formatCellValue(sheet.getRow(4).getCell(1)));
    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.CurrencyFormatsTest.java

License:Open Source License

@Test
public void testRunReportXlsx() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("CurrencyFormats.rptdesign", "xlsx");
    assertNotNull(inputStream);/*  w w  w  . j  ava 2s . c om*/
    try {

        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Currency Formats Test Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(5, this.firstNullRow(sheet));

        DataFormatter formatter = new DataFormatter();

        assertEquals("3141.59", formatter.formatCellValue(sheet.getRow(1).getCell(1)));
        assertEquals("$3141.59", formatter.formatCellValue(sheet.getRow(2).getCell(1)));
        assertEquals("3141.59", formatter.formatCellValue(sheet.getRow(3).getCell(1)));
        assertEquals("3141.59", formatter.formatCellValue(sheet.getRow(4).getCell(1)));
    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.GridsTests.java

License:Open Source License

@Test
public void testRunReportXlsx() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("CombinedGrid.rptdesign", "xlsx");
    assertNotNull(inputStream);/*  w ww .  ja  v a 2s .co m*/
    try {

        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Combined Grid Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(3, this.firstNullRow(sheet));

        DataFormatter formatter = new DataFormatter();

        assertEquals(
                "This is a label\nHeading 1\nThis is text\nHeading 2\nStyles\nBold, Italic, Bold and italic and finally Underline.\n Oh\n Dear\nIsle of Mann\nPlain text.\nAnd this is a label",
                formatter.formatCellValue(sheet.getRow(0).getCell(1)));
        assertEquals(CellStyle.ALIGN_GENERAL, sheet.getRow(0).getCell(1).getCellStyle().getAlignment());
        assertEquals(14, sheet.getRow(0).getCell(1).getRichStringCellValue().numFormattingRuns());
        assertEquals("Hello", formatter.formatCellValue(sheet.getRow(1).getCell(0)));
        assertEquals("End", formatter.formatCellValue(sheet.getRow(2).getCell(0)));

    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.GridsTests.java

License:Open Source License

@Test
public void testRunReportXls() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("CombinedGrid.rptdesign", "xls");
    assertNotNull(inputStream);/*  w  w w  .  ja  v  a 2 s .c o  m*/
    try {

        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Combined Grid Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(3, this.firstNullRow(sheet));

        DataFormatter formatter = new DataFormatter();

        assertEquals(
                "This is a label\nHeading 1\nThis is text\nHeading 2\nStyles\nBold, Italic, Bold and italic and finally Underline.\n Oh\n Dear\nIsle of Mann\nPlain text.\nAnd this is a label",
                formatter.formatCellValue(sheet.getRow(0).getCell(1)));
        assertEquals(CellStyle.ALIGN_GENERAL, sheet.getRow(0).getCell(1).getCellStyle().getAlignment());
        assertEquals(13, sheet.getRow(0).getCell(1).getRichStringCellValue().numFormattingRuns());
        assertEquals("Hello", formatter.formatCellValue(sheet.getRow(1).getCell(0)));
        assertEquals("End", formatter.formatCellValue(sheet.getRow(2).getCell(0)));

    } finally {
        inputStream.close();
    }
}