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

private T instanceObj(Map<Integer, Field> fieldMap, DataFormatter formatter, Row row) {
    T obj = ReflectUtil.newInstance(dataType);
    fieldMap.forEach((index, field) -> {
        if (field.getType() == InputStream.class) {
            convertPicture(row, obj, index, field);
            return;
        }
        Cell cell = row.getCell(index, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        if (cell == null) {
            return;
        }
        String content = formatter.formatCellValue(cell);
        if (content == null) {
            return;
        }
        content = trim.apply(content);
        context.reset(obj, field, content, row.getRowNum(), index);
        ReadConverterContext.convert(obj, context, convertContext, exceptionFunction);
    });
    return obj;
}
 

private List<String> getExcelResourceLines(String fileName) {
    List<String> expectedLines = new ArrayList<>();
    try {
        Workbook wb = WorkbookFactory.create(getClass().getResourceAsStream("/" + fileName));
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        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, evaluator);
            }
            expectedLines.add(index, rowStr);
            index++;
        }
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
    return expectedLines;
}
 

@Test
public void poi07() throws Exception {
    InputStream is = new FileInputStream("D:\\test\\styleTest.xlsx");
    Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel 2003/2007/2010 都是可以处理的
    Sheet sheet = workbook.getSheetAt(0);
    Row hssfRow = sheet.getRow(0);
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    DataFormatter formatter = new DataFormatter();
    System.out.println(hssfRow.getCell(0).getNumericCellValue());
    System.out.println(hssfRow.getCell(1).getNumericCellValue());
    System.out.println(hssfRow.getCell(2).getNumericCellValue());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(3).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(3).getCellStyle().getDataFormatString());
    isDate(hssfRow.getCell(0));
    isDate(hssfRow.getCell(1));
    isDate(hssfRow.getCell(2));
    isDate(hssfRow.getCell(3));

}
 

@Test
void testGetCellValuePreservingFormatting()
{
    List<List<String>> expectedStringData = new LinkedList<>();
    List<String> row1 = new LinkedList<>();
    row1.add("TRUE");
    row1.add("1");
    expectedStringData.add(row1);
    List<String> row2 = new LinkedList<>();
    row2.add("FALSE");
    row2.add("2");
    expectedStringData.add(row2);
    List<String> row3 = new LinkedList<>();
    row3.add("3");
    expectedStringData.add(row3);

    DataFormatter dataFormatter = new DataFormatter();
    sheetParser = new ExcelSheetParser(extractor.getSheet(AS_STRING_SHEET).get(), true, dataFormatter);
    List<List<String>> data = sheetParser.getData();
    assertEquals(expectedStringData, data);
}
 

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

	InputStream inputStream = runAndRenderReport("CurrencyFormats.rptdesign", "xlsx");
	assertNotNull(inputStream);
	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();
	}
}
 

/**
 * Parses and shows the content of one sheet
 * using the specified styles and shared-strings tables.
 *
 * @param strings          The table of strings that may be referenced by cells in the sheet
 * @param sheetInputStream The stream to read the sheet-data from.
 * @throws java.io.IOException An IO exception from the parser,
 *                             possibly from a byte stream or character stream
 *                             supplied by the application.
 * @throws SAXException        if parsing the XML data fails.
 */
private void processSheet(
        SharedStrings strings,
        XSSFSheetXMLHandler.SheetContentsHandler sheetHandler,
        InputStream sheetInputStream) throws IOException, SAXException {
    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(
                null, null, strings, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
    }
}
 

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

	InputStream inputStream = runAndRenderReport("CurrencyFormats.rptdesign", "xls");
	assertNotNull(inputStream);
	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();
	}
}
 

/**
 * Converts the specified number of rows of a Excel file into JSON and returns
 * them as a String.
 *
 * @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 {
    try (Workbook wb = WorkbookFactory.create(input)) {
        // Only support single sheet files for now
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        JSONArray rowList = new JSONArray();
        String[] columns;
        for (Row row : sheet) {
            if (row.getRowNum() <= numRows) {
                //getLastCellNumber instead of getPhysicalNumberOfCells so that blank values don't shift cells
                columns = new String[row.getLastCellNum()];
                for (int i = 0; i < row.getLastCellNum(); i++ ) {
                    columns[i] = df.formatCellValue(row.getCell(i), evaluator);
                }
                rowList.add(columns);
            }
        }
        return rowList.toString();
    }
}
 

private <T> void processSheet(StylesTable styles,
                              XMLReader reader,
                              ReadOnlySharedStringsTable readOnlySharedStringsTable,
                              Class<T> type,
                              InputStream sheetInputStream,
                              Consumer<? super T> consumer) {

    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        PoijiHandler<T> poijiHandler = new PoijiHandler<>(type, options, consumer);
        ContentHandler contentHandler
                = new XSSFSheetXMLPoijiHandler(styles,
                null,
                readOnlySharedStringsTable,
                poijiHandler,
                formatter,
                false,
                options);
        reader.setContentHandler(contentHandler);
        reader.parse(sheetSource);
    } catch (SAXException | IOException e) {
        IOUtils.closeQuietly(sheetInputStream);
        throw new PoijiException("Problem occurred while reading data", e);
    }
}
 

@Override
public boolean initialize(MetaFile input, String separator) {

  if (input == null) {
    return false;
  }

  File inFile = MetaFiles.getPath(input).toFile();
  if (!inFile.exists()) {
    return false;
  }

  try {
    FileInputStream inSteam = new FileInputStream(inFile);
    book = new XSSFWorkbook(inSteam);
    if (book.getNumberOfSheets() == 0) {
      return false;
    }
    formatter = new DataFormatter();
  } catch (IOException e) {
    e.printStackTrace();
    return false;
  }

  return true;
}
 

@Test
public void poi() throws Exception {
    InputStream is = new FileInputStream("D:\\test\\styleTest.xls");
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
    HSSFRow hssfRow = hssfSheet.getRow(0);
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    DataFormatter formatter = new DataFormatter();
    System.out.println(hssfRow.getCell(0).getNumericCellValue());
    System.out.println(hssfRow.getCell(1).getNumericCellValue());
    System.out.println(hssfRow.getCell(2).getNumericCellValue());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormatString());

}
 

@Override
public String[] getNames() throws ComponentNotReadyException{
	List<String> names = new ArrayList<String>();
	Row row = (metadataRow > -1) ? sheet.getRow(metadataRow) : sheet.getRow(firstRow);
	
	if (row == null) {
		throw new ComponentNotReadyException("Metadata row (" + (metadataRow > -1 ? metadataRow : firstRow) + 
				") doesn't exist in sheet " + StringUtils.quote(sheet.getSheetName()) + "!"); 
	}

	DataFormatter formatter = new DataFormatter();
	for (int i = 0; i < row.getLastCellNum(); i++) {
		Cell cell = row.getCell(i);

		if (cell != null) {
			String cellValue = formatter.formatCellValue(cell);
			names.add(XLSFormatter.getCellCode(i) + " - " + cellValue.substring(0, Math.min(cellValue.length(), MAX_NAME_LENGTH)));
		}
	}

	return names.toArray(new String[names.size()]);
}
 

/**
 * Return the numeric value.
 *
 * @param cell the cell to extract the value from.
 * @return the numeric value from the cell.
 */
private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) {
    // Date is typed as numeric
    if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable??
        DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
        return sdf.format(cell.getDateCellValue());
    }
    // Numeric type (use data formatter to get number format right)
    DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH);

    if (cellValue == null) {
        return formatter.formatCellValue(cell);
    }

    return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}
 

private String readCellAsString(final Cell cell) {
    if (cell == null) {
        return "";
    }

    switch (cell.getCellType()) {
        case CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case CELL_TYPE_BLANK:
            return "";
        case CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());
        case CELL_TYPE_NUMERIC:
            final DataFormatter formatter = new DataFormatter();
            return formatter.formatCellValue(cell);
        default:
            throw new RuntimeException("unknown cell type " + cell.getCellType());
    }

}
 

private String retrieveCellContent(Cell cell, Workbook wb, boolean evaluateFormulas, String formatting) {
	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
	DataFormatter formatter = new DataFormatter(true);
	String cellContent = null;
	int cellType = cell.getCellType();
	switch(cellType) {
	case Cell.CELL_TYPE_BLANK:
		break;
	case Cell.CELL_TYPE_FORMULA:
		if (evaluateFormulas) {
			cellContent = formatter.formatCellValue(cell, evaluator);
		} else {
			// Display the formula instead
			cellContent = cell.getCellFormula();
		}
		break;
	default:
		if(formatting.equalsIgnoreCase("excel")) {
			cellContent = formatter.formatCellValue(cell);
		} else if(formatting.equalsIgnoreCase("raw")) {
			// Display the raw cell contents
			switch (cellType) {
			case Cell.CELL_TYPE_NUMERIC:
				cellContent = Double.toString(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_STRING:
				cellContent = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				cellContent = Boolean.toString(cell.getBooleanCellValue());
				break;	
			}
		}
		break;
	}
	return cellContent;
}
 

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

	InputStream inputStream = runAndRenderReport("CombinedGrid.rptdesign", "xls");
	assertNotNull(inputStream);
	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();
	}
}
 

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

	InputStream inputStream = runAndRenderReport("CombinedGrid.rptdesign", "xlsx");
	assertNotNull(inputStream);
	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();
	}
}
 

/**
 * Parses and shows the content of one sheet using the specified styles and
 * shared-strings tables.
 *
 * @param styles
 * @param strings
 * @param sheetInputStream
 */
public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler,
		InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {
	DataFormatter formatter = new DataFormatter();
	InputSource sheetSource = new InputSource(sheetInputStream);
	try {
		XMLReader sheetParser = SAXHelper.newXMLReader();
		ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
		sheetParser.setContentHandler(handler);
		sheetParser.parse(sheetSource);
	} catch (ParserConfigurationException e) {
		throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
	}
}
 

public HSSFEventParser(List<String> sheetNameList,DataFormatter useDataFormatter, Map<Integer,List<SpreadSheetCellDAO[]>> spreadSheetCellDAOCache, String[] sheets) {
	this.spreadSheetCellDAOCache=spreadSheetCellDAOCache;
	this.sheets=sheets;
	this.currentCellNum=0L;
	this.cachedRowNum=0;
	this.cachedColumnNum=0;
	this.sheetList=new ArrayList<>();
	this.sheetMap=new HashMap<>();
	this.sheetSizeMap=new HashMap<>();
	this.currentSheet=0;
	this.extendedRecordFormatIndexList=new ArrayList<>();
	this.formatRecordIndexMap=new HashMap<>();
	this.sheetList=sheetNameList;
	this.useDataFormatter=useDataFormatter;
}
 

/**
 * 
 * @param sheetName        name of sheet
 * @param sheetInputStream sheet in xlsx format input stream
 * @param sst              Shared strings table of Excel file
 * @param styles           StylesTable of the document
 * @param isDate1904       date format 1904 (true) or 1900 (false)
 * @throws XMLStreamException
 */
public XSSFPullParser(String sheetName, InputStream sheetInputStream, SharedStringsTable sst, StylesTable styles,
		DataFormatter dataFormatter, boolean isDate1904) throws XMLStreamException {
	this.sheetName = sheetName;
	this.xer = StaxHelper.newXMLInputFactory().createXMLEventReader(sheetInputStream);
	this.nextBeingCalled = false;
	this.finalized = false;
	this.nextRow = 1;
	this.currentRow = 1;
	this.sst = sst;
	this.styles = styles;
	this.dataFormatter = dataFormatter;
	this.isDate1904 = isDate1904;
}
 

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

	InputStream inputStream = runAndRenderReport("BackgroundColours.rptdesign", "xls");
	assertNotNull(inputStream);
	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();
	}
}
 

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

	InputStream inputStream = runAndRenderReport("AutoColWidths.rptdesign", "xlsx");
	assertNotNull(inputStream);
	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();
	}
}
 

private ValueTuple toTuple(Iterator<Cell> cellIterator) {
	Value[] values = new Value[fileHeading.getDegree() - ((duplicates == DuplicateHandling.DUP_COUNT || duplicates == DuplicateHandling.AUTOKEY) ? 1 : 0)];
	int index = 0;
	DataFormatter formatter = new DataFormatter();
	while (cellIterator.hasNext() && index < values.length) {
		Cell cell = cellIterator.next();
		values[index] = ValueCharacter.select(generator, formatter.formatCellValue(cell));
		index++;
	}
	for (; index < values.length; index++)
		values[index] = ValueCharacter.select(generator, "");
	return new ValueTuple(generator, values);
}
 

public String getCellStringValue(Cell cell) {
    DataFormatter formatter = new DataFormatter();
    return formatter.formatCellValue(cell);
}
 

/**
 * Parses and shows the content of one sheet using the specified styles and
 * shared-strings tables.
 *
 * @param styles
 * @param strings
 * @param sheetInputStream
 */
public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler,
                         InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {
    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
    }
}
 

public ExcelMatrixReader(InputStream inputStream, boolean useXlsx, int sheetNumber) throws IOException {

        this.sheetIndex = sheetNumber;
        this.workbook = useXlsx ? new XSSFWorkbook(inputStream)
                : new HSSFWorkbook(new BufferedInputStream(inputStream));
        inputStream.close();
        formulaEvaluator =  workbook.getCreationHelper().createFormulaEvaluator();
        sheet = workbook.getSheetAt(sheetIndex);
        rowIterator = sheet.iterator();
        this.dataFormatter = new DataFormatter(Locale.US, false, true);
        readRecord();
    }
 

@Test
public void test3() throws IOException {
    String file = "D:\\test\\dataformat1.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    Cell cell = xssfSheet.getRow(0).getCell(0);
    DataFormatter d = new DataFormatter();
    System.out.println(d.formatCellValue(cell));
}
 

@Test
public void test355() throws IOException, InvalidFormatException {
    File file = TestFileUtil.readFile("dataformat" + File.separator + "dataformat.xlsx");
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    DataFormatter d = new DataFormatter(Locale.CHINA);

    for (int i = 0; i < xssfSheet.getLastRowNum(); i++) {
        Row row = xssfSheet.getRow(i);
        System.out.println(d.formatCellValue(row.getCell(0)));
    }

}
 

@Test
public void test3556() throws IOException, InvalidFormatException {
    String file = "D://test/dataformat1.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    DataFormatter d = new DataFormatter(Locale.CHINA);

    for (int i = 0; i < xssfSheet.getLastRowNum(); i++) {
        Row row = xssfSheet.getRow(i);
        System.out.println(d.formatCellValue(row.getCell(0)));
    }

}
 

private void print(Cell cell) {
    System.out.println(
        DateUtil.isADateFormat(cell.getCellStyle().getDataFormat(), cell.getCellStyle().getDataFormatString()));
    System.out.println(cell.getCellStyle().getDataFormat());
    System.out.println(cell.getCellStyle().getDataFormatString());
    DataFormatter f = new DataFormatter();
    System.out.println(f.formatCellValue(cell));
    if (cell.getCellStyle().getDataFormatString() != null) {

    }
    ExcelStyleDateFormatter ff = new ExcelStyleDateFormatter(cell.getCellStyle().getDataFormatString());

}