Examples of org.apache.poi.ss.usermodel.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

           StylesTable styles,
           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);
       SAXParserFactory saxFactory = SAXParserFactory.newInstance();
       try {

View Full Code Here

    @Test
    public void bug48968() throws Exception {
       HSSFWorkbook wb = openSample("48968.xls");
       assertEquals(1, wb.getNumberOfSheets());
       
       DataFormatter fmt = new DataFormatter();


       // Check the dates
       HSSFSheet s = wb.getSheetAt(0);
       Cell cell_d20110325 = s.getRow(0).getCell(0);
       Cell cell_d19000102 = s.getRow(11).getCell(0);
       Cell cell_d19000100 = s.getRow(21).getCell(0);
       assertEquals(s.getRow(0).getCell(3).getStringCellValue(), fmt.formatCellValue(cell_d20110325));
       assertEquals(s.getRow(11).getCell(3).getStringCellValue(), fmt.formatCellValue(cell_d19000102));
       // There is no such thing as 00/01/1900...
       assertEquals("00/01/1900 06:14:24", s.getRow(21).getCell(3).getStringCellValue());
       assertEquals("31/12/1899 06:14:24", fmt.formatCellValue(cell_d19000100));
       
       // Check the cached values
       assertEquals("HOUR(A1)",   s.getRow(5).getCell(0).getCellFormula());
       assertEquals(11.0,         s.getRow(5).getCell(0).getNumericCellValue(), 0);
       assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula());

View Full Code Here

    public void testTruthFile() throws Exception {
        File truthFile = HSSFTestDataSamples.getSampleFile("54686_fraction_formats.txt");
        BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(truthFile)));
        Workbook wb = HSSFTestDataSamples.openSampleWorkbook("54686_fraction_formats.xls");
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter formatter = new DataFormatter();
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();


        // Skip over the header row
        String truthLine = reader.readLine();
        String[] headers = truthLine.split("\t");
        truthLine = reader.readLine();


        for (int i = 1; i < sheet.getLastRowNum() && truthLine != null; i++){
            Row r = sheet.getRow(i);
            String[] truths = truthLine.split("\t");
            // Intentionally ignore the last column (tika-1132), for now
            for (short j = 3; j < 12; j++){
                Cell cell = r.getCell(j, Row.CREATE_NULL_AS_BLANK);
                String formatted = clean(formatter.formatCellValue(cell, evaluator));
                if (truths.length <= j){
                    continue;
                }


                String truth = clean(truths[j]);

View Full Code Here

     */
    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

        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);
                }


            }
        }

View Full Code Here

    @Test
    public void bug48968() throws Exception {
       HSSFWorkbook wb = openSample("48968.xls");
       assertEquals(1, wb.getNumberOfSheets());
       
       DataFormatter fmt = new DataFormatter();


       // Check the dates
       HSSFSheet s = wb.getSheetAt(0);
       Cell cell_d20110325 = s.getRow(0).getCell(0);
       Cell cell_d19000102 = s.getRow(11).getCell(0);
       Cell cell_d19000100 = s.getRow(21).getCell(0);
       assertEquals(s.getRow(0).getCell(3).getStringCellValue(), fmt.formatCellValue(cell_d20110325));
       assertEquals(s.getRow(11).getCell(3).getStringCellValue(), fmt.formatCellValue(cell_d19000102));
       // There is no such thing as 00/01/1900...
       assertEquals("00/01/1900 06:14:24", s.getRow(21).getCell(3).getStringCellValue());
       assertEquals("31/12/1899 06:14:24", fmt.formatCellValue(cell_d19000100));
       
       // Check the cached values
       assertEquals("HOUR(A1)",   s.getRow(5).getCell(0).getCellFormula());
       assertEquals(11.0,         s.getRow(5).getCell(0).getNumericCellValue(), 0);
       assertEquals("MINUTE(A1)", s.getRow(6).getCell(0).getCellFormula());

View Full Code Here

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

View Full Code Here


    /**
     * Retreives the text contents of the file
     */
    public String getText() {
        DataFormatter formatter;
        if(locale == null) {
            formatter = new DataFormatter();
        } else  {
            formatter = new DataFormatter(locale);
        }


        StringBuffer text = new StringBuffer();
        for(int i=0; i<workbook.getNumberOfSheets(); i++) {
            XSSFSheet sheet = workbook.getSheetAt(i);

View Full Code Here

            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();

View Full Code Here

    @Test
    public void bug47490() 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(), 0);
       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(), 0);
       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(), 0);
       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

0 1 2 3 4 5 6 7

TOP

Related Classes of org.apache.poi.ss.usermodel.DataFormatter

org.apache.metamodel.excel.XlsxSheetToRowsHandler

org.apache.poi.hssf.usermodel.TestBugs

org.apache.poi.ss.examples.CellStyleDetails

org.apache.poi.ss.examples.ToCSV

org.apache.poi.ss.usermodel.TestFractionFormat

org.apache.poi.ss.util.SheetUtil

org.apache.poi.xssf.eventusermodel.XLSX2CSV$MyXSSFSheetHandler

org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler

org.apache.poi.xssf.extractor.XSSFEventBasedExcelExtractor

org.apache.poi.xssf.extractor.XSSFEventBasedExcelExtractor$MyXSSFSheetHandler

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.