Examples of DataFormatter

org.apache.poi.ss.usermodel.DataFormatter
DataFormatter contains methods for formatting the value stored in an Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel. Supported formats include currency, SSN, percentages, decimals, dates, phone numbers, zip codes, etc.
Internally, formats will be implemented using subclasses of {@link Format}such as {@link DecimalFormat} and {@link SimpleDateFormat}. Therefore the formats used by this class must obey the same pattern rules as these Format subclasses. This means that only legal number pattern characters ("0", "#", ".", "," etc.) may appear in number formats. Other characters can be inserted before or after the number pattern to form a prefix or suffix.

For example the Excel pattern "$#,##0.00 "USD"_);($#,##0.00 "USD")" will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)". However the pattern "00-00-00" is incorrectly formatted by DecimalFormat as "000000--". For Excel formats that are not compatible with DecimalFormat, you can provide your own custom {@link Format} implementationvia DataFormatter.addFormat(String,Format). The following custom formats are already provided by this class:
```
 SSN "000-00-0000"
 Phone Number "(###) ###-####"
 Zip plus 4 "00000-0000"
 
 
```
If the Excel format pattern cannot be parsed successfully, then a default format will be used. The default number format will mimic the Excel General format: "#" for whole numbers and "#.##########" for decimal numbers. You can override the default format pattern with DataFormatter.setDefaultNumberFormat(Format). Note: the default format will only be used when a Format cannot be created from the cell's data format string.
Note that by default formatted numeric values are trimmed. Excel formats can contain spacers and padding and the default behavior is to strip them off.

Example:

Consider a numeric cell with a value 12.343 and format "##.##_ ". The trailing underscore and space ("_ ") in the format adds a space to the end and Excel formats this cell as "12.34 ", but DataFormatter trims the formatted value and returns "12.34".
You can enable spaces by passing the emulateCsv=true flag in the DateFormatter cosntructor. If set to true, then the output tries to conform to what you get when you take an xls or xlsx in Excel and Save As CSV file:
- returned values are not trimmed
- Invalid dates are formatted as 255 pound signs ("#")
- simulate Excel's handling of a format string of all # when the value is 0. Excel will output "", DataFormatter will output "0".
@author James May (james dot may at fmr dot com) @author Robert Kish
org.zkoss.poi.ss.usermodel.DataFormatter

Examples of org.apache.poi.ss.usermodel.DataFormatter

     */
    public void test47490() throws Exception {
       XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("GeneralFormatTests.xlsx");
       Sheet s = wb.getSheetAt(1);
       Row r;
       DataFormatter df = new DataFormatter();
       
       r = s.getRow(1);
       assertEquals(1.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("1", df.formatCellValue(r.getCell(2)));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "@"));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "General"));
              
       r = s.getRow(2);
       assertEquals(12.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("12", df.formatCellValue(r.getCell(2)));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "@"));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "General"));
       
       r = s.getRow(3);
       assertEquals(123.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("123", df.formatCellValue(r.getCell(2)));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "@"));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "General"));
    }

View Full Code Here

Examples of org.apache.poi.ss.usermodel.DataFormatter

               StringBuffer output) {
           this.stylesTable = styles;
           this.sharedStringsTable = strings;
           this.output = output;
           this.nextDataType = xssfDataType.NUMBER;
           this.formatter = new DataFormatter();
       }

View Full Code Here

Examples of org.apache.poi.ss.usermodel.DataFormatter

     */
    public void test47490() throws Exception {
       XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("GeneralFormatTests.xlsx");
       Sheet s = wb.getSheetAt(1);
       Row r;
       DataFormatter df = new DataFormatter();
       
       r = s.getRow(1);
       assertEquals(1.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("1", df.formatCellValue(r.getCell(2)));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "@"));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "General"));
              
       r = s.getRow(2);
       assertEquals(12.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("12", df.formatCellValue(r.getCell(2)));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "@"));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "General"));
       
       r = s.getRow(3);
       assertEquals(123.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("123", df.formatCellValue(r.getCell(2)));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "@"));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "General"));
    }

View Full Code Here

Examples of org.apache.poi.ss.usermodel.DataFormatter

    /**
     * [Bug 49928] formatCellValue returns incorrect value for \u00a3 formatted cells
     */
    public void test49928(){
        XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("49928.xlsx");
        DataFormatter df = new DataFormatter();


        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFCell cell = sheet.getRow(0).getCell(0);
        XSSFCellStyle style = cell.getCellStyle();


        String poundFmt = "\"\u00a3\"#,##0;[Red]\\-\"\u00a3\"#,##0";
        // not expected normally, id of a custom format should be gerater 
        // than BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX
        short  poundFmtIdx = 6;


        assertEquals(poundFmt, style.getDataFormatString());
        assertEquals(poundFmtIdx, style.getDataFormat());
        assertEquals("\u00a31", df.formatCellValue(cell));




        XSSFDataFormat dataFormat = wb.createDataFormat();
        assertEquals(poundFmtIdx, dataFormat.getFormat(poundFmt));
        assertEquals(poundFmt, dataFormat.getFormat(poundFmtIdx));

View Full Code Here

Examples of org.apache.poi.ss.usermodel.DataFormatter

     */
    public void test47490() throws Exception {
       XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("GeneralFormatTests.xlsx");
       Sheet s = wb.getSheetAt(1);
       Row r;
       DataFormatter df = new DataFormatter();
       
       r = s.getRow(1);
       assertEquals(1.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("1", df.formatCellValue(r.getCell(2)));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "@"));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "General"));
              
       r = s.getRow(2);
       assertEquals(12.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("12", df.formatCellValue(r.getCell(2)));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "@"));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "General"));
       
       r = s.getRow(3);
       assertEquals(123.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("123", df.formatCellValue(r.getCell(2)));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "@"));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "General"));
    }

View Full Code Here

Examples of org.apache.poi.ss.usermodel.DataFormatter

    private void processSheet(Sheet sheet,
                              List< ? extends DataListener> listeners) {
        int maxRows = sheet.getLastRowNum();


        CellRangeAddress[] mergedRanges = getMergedCells(sheet);
        DataFormatter formatter = new DataFormatter();


        for ( int i = 0; i <= maxRows; i++ ) {
            Row row = sheet.getRow( i );
            int lastCellNum = row != null ? row.getLastCellNum() : 0;
            newRow( listeners, i, lastCellNum );


            for ( int cellNum = 0; cellNum < lastCellNum; cellNum++ ) {
                Cell cell = row.getCell(cellNum);
                if (cell == null)
                    continue;
                double num = 0;


                CellRangeAddress merged = getRangeIfMerged( cell,
                                                            mergedRanges );


                if ( merged != null ) {


                    Cell topLeft = sheet.getRow(merged.getFirstRow()).getCell(merged.getFirstColumn());
                    newCell( listeners,
                             i,
                             cellNum,
                             formatter.formatCellValue(topLeft),
                             topLeft.getColumnIndex() );
                } else {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        num = cell.getNumericCellValue();
                    }
                    if ( num - Math.round(num) != 0 ) {
                        newCell(listeners, i, cellNum, String.valueOf(num), DataListener.NON_MERGED );
                    } else {
                        newCell( listeners,
                                 i,
                                 cellNum,
                                 formatter.formatCellValue(cell),
                                 DataListener.NON_MERGED );
                    }
                }
            }
        }

View Full Code Here

Examples of org.apache.poi.ss.usermodel.DataFormatter

   public XSSFSheetXMLHandler(
           StylesTable styles,
           ReadOnlySharedStringsTable strings,
           SheetContentsHandler sheetContentsHandler,
           boolean formulasNotResults) {
       this(styles, strings, sheetContentsHandler, new DataFormatter(), formulasNotResults);
   }

View Full Code Here

Examples of org.apache.poi.ss.usermodel.DataFormatter

     */
    public void test47490() throws Exception {
       XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("GeneralFormatTests.xlsx");
       Sheet s = wb.getSheetAt(1);
       Row r;
       DataFormatter df = new DataFormatter();
       
       r = s.getRow(1);
       assertEquals(1.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("1", df.formatCellValue(r.getCell(2)));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "@"));
       assertEquals("1", df.formatRawCellContents(1.0, -1, "General"));
              
       r = s.getRow(2);
       assertEquals(12.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("12", df.formatCellValue(r.getCell(2)));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "@"));
       assertEquals("12", df.formatRawCellContents(12.0, -1, "General"));
       
       r = s.getRow(3);
       assertEquals(123.0, r.getCell(2).getNumericCellValue());
       assertEquals("General", r.getCell(2).getCellStyle().getDataFormatString());
       assertEquals("123", df.formatCellValue(r.getCell(2)));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "@"));
       assertEquals("123", df.formatRawCellContents(123.0, -1, "General"));
    }

View Full Code Here

Examples of org.apache.poi.ss.usermodel.DataFormatter

   public XSSFSheetXMLHandler(
           StylesTable styles,
           ReadOnlySharedStringsTable strings,
           SheetContentsHandler sheetContentsHandler,
           boolean formulasNotResults) {
       this(styles, strings, sheetContentsHandler, new DataFormatter(), formulasNotResults);
   }

View Full Code Here

Examples of org.zkoss.poi.ss.usermodel.DataFormatter

      final CellValue cv = BookHelper.evaluate(book, cell);
      cellType = cv.getCellType();
    } else if (cellType == Cell.CELL_TYPE_STRING) {
      return cell.getRichStringCellValue();
    }
      final String result = new DataFormatter(ZssContext.getCurrent().getLocale(), false).formatCellValue(cell, cellType); //ZSS-68
    return newRichTextString(cell, result);
  }

View Full Code Here

0 1 2 3 4 5

TOP

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.