Java 类org.apache.poi.ss.usermodel.DataFormatter 实例源码

@SuppressWarnings("unchecked")
private <T> List<T> processSheet(StylesTable styles, ReadOnlySharedStringsTable readOnlySharedStringsTable,
                                 Class<T> type, InputStream sheetInputStream) {

    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        PoijiHandler poijiHandler = new PoijiHandler(type, options);
        ContentHandler contentHandler =
                new XSSFSheetXMLHandler(styles, null, readOnlySharedStringsTable, poijiHandler, formatter, false);
        sheetParser.setContentHandler(contentHandler);
        sheetParser.parse(sheetSource);
        return poijiHandler.getDataset();
    } catch (ParserConfigurationException | SAXException | IOException e) {
        throw new PoijiException("Problem occurred while reading data", e);
    }
}

/**
 * Accepts objects needed while parsing.
 *
 * @param styles  Table of styles
 * @param strings Table of shared strings
 */
public XSSFSheetXMLHandlerPlus(
    StylesTable styles,
    CommentsTable comments,
    ReadOnlySharedStringsTable strings,
    XSSFSheetXMLHandlerPlus.SheetContentsHandler sheetContentsHandler,
    DataFormatter dataFormatter,
    boolean formulasNotResults) {
    this.stylesTable = styles;
    this.commentsTable = comments;
    this.sharedStringsTable = strings;
    this.output = sheetContentsHandler;
    this.formulasNotResults = formulasNotResults;
    this.nextDataType = XSSFSheetXMLHandlerPlus.xssfDataType.NUMBER;
    this.formatter = dataFormatter;
    init();
}

/**
 * Returns the number of columns present on the given row.
 *
 * @param row the row to read column count from.
 * @return the number of columns on the given row
 */
private int getColumnCount(Row row) {
    DataFormatter formatter = new DataFormatter();
    Iterator<Cell> iterator = row.iterator();
    int count = 0;

    while (iterator.hasNext()) {
        Cell cell = iterator.next();
        String value = formatter.formatCellValue(cell);

        if (value.length() > 0) {
            count++;
        } else {
            break;
        }
    }
    return count;
}

/**
 * Gets the cell value as string.
 *
 * @param cell the cell
 * @return the cell value as string
 */
public String getCellValueAsString(Cell cell) {
    if (cell == null) {
        return null;
    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        logger.info("TEST: " + cell.toString());
        return cell.toString();
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        DataFormatter formatter = new DataFormatter();
        String formattedValue = formatter.formatCellValue(cell);
        return formattedValue;
    } else {
        throw new InvalidCSVException(
                "Value stored in cell is invalid! Valid types are Numbers or Strings.");
    }
}

public HSSFEventParser(List<String> sheetNameList,DataFormatter useDataFormatter, Map<Integer,List<SpreadSheetCellDAO[]>> spreadSheetCellDAOCache, String[] sheets) {
    this.spreadSheetCellDAOCache=spreadSheetCellDAOCache;
    this.sheets=sheets;
    this.currentCellNum=0L;
    this.currentSheetIgnore=false;
    this.readCachedFormulaResult=false;
    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;
}

private Iterable<Instructor> readExcelValue(Workbook wb) {
    ArrayList<Instructor> instructors = new ArrayList<Instructor>();

    // 获取默认的表格
    Sheet sheet = wb.getSheet("Sheet1");

    int rowNumber = sheet.getPhysicalNumberOfRows();

    DataFormatter formatter = new DataFormatter();

    for (int i = 1; i < rowNumber; i++) {
        Instructor instructor = new Instructor();
        Cell cell = sheet.getRow(i).getCell(0);
        instructor.setAcademy(formatter.formatCellValue(cell));
        cell = sheet.getRow(i).getCell(1);
        instructor.setNumber(formatter.formatCellValue(cell));
        cell = sheet.getRow(i).getCell(2);
        instructor.setName(formatter.formatCellValue(cell));
        instructors.add(instructor);
    }

    return instructors;
}

/**
 * Format the cell content as a String, with number and date format applied.
 * @return cell content with format applied
 */
private String getFormattedText(Locale locale) {
    // is there a special date format?
    if (getResultType()==CellType.DATE) {
        DateTimeFormatter df = getCellStyle().getLocaleAwareDateFormat(locale);
        if (df != null) {
            return df.format(getDateTime());
        }
    }

    // if not, let POI do the formatting
    FormulaEvaluator evaluator = getWorkbook().evaluator;
    DataFormatter dataFormatter = getWorkbook().getDataFormatter(locale);
    try {
        return dataFormatter.formatCellValue(poiCell, evaluator);
    } catch (Exception ex) {
        return Cell.ERROR_TEXT;
    }
}

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

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

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

/**
 * Open an Excel workbook ready for conversion.
 *
 * @param file An instance of the File class that encapsulates a handle
 *        to a valid Excel workbook. Note that the workbook can be in
 *        either binary (.xls) or SpreadsheetML (.xlsx) format.
 * @throws java.io.FileNotFoundException Thrown if the file cannot be located.
 * @throws java.io.IOException Thrown if a problem occurs in the file system.
 * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown
 *         if invalid xml is found whilst parsing an input SpreadsheetML
 *         file.
 */
private void openWorkbook(File file) throws FileNotFoundException,
                                       IOException, InvalidFormatException {
    FileInputStream fis = null;
    try {
        System.out.println("Opening workbook [" + file.getName() + "]");

        fis = new FileInputStream(file);

        // Open the workbook and then create the FormulaEvaluator and
        // DataFormatter instances that will be needed to, respectively,
        // force evaluation of formulae found in cells and create a
        // formatted String encapsulating the cells contents.
        this.workbook = WorkbookFactory.create(fis);
        this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
        this.formatter = new DataFormatter(true);
    }
    finally {
        if(fis != null) {
            fis.close();
        }
    }
}

@Override
protected List<Object> parse(From fixture) {
    InputStream inputStream = fixture.getInputStream();
    Workbook workbook = createWorkbook(inputStream);

    DataFormatter dataFormatter = new DataFormatter();
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

    List<Object> mappings = new LinkedList<Object>();
    Helpers helpers = new Helpers();
    helpers.dataFormatter = dataFormatter;
    helpers.evaluator = evaluator;

    Parameters parameters = new Parameters();
    parameters.mode = fixture.getMode();

    for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);

        mappings.addAll(parseSheet(sheet, helpers, parameters));
    }

    return mappings;
}

/**
 * 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());
    }
}

/**
 * Accepts objects needed while parsing.
 *
 * @param styles  Table of styles
 * @param strings Table of shared strings
 */
public XSSFSheetXMLHandlerPlus(
    StylesTable styles,
    ReadOnlySharedStringsTable strings,
    XSSFSheetXMLHandlerPlus.SheetContentsHandler sheetContentsHandler,
    DataFormatter dataFormatter,
    boolean formulasNotResults) {
    this(styles, null, strings, sheetContentsHandler, dataFormatter, formulasNotResults);
}

/**
 * Accepts objects needed while parsing.
 *
 * @param styles  Table of styles
 * @param strings Table of shared strings
 */
public XSSFSheetXMLHandlerPlus(
    StylesTable styles,
    ReadOnlySharedStringsTable strings,
    XSSFSheetXMLHandlerPlus.SheetContentsHandler sheetContentsHandler,
    boolean formulasNotResults) {
    this(styles, strings, sheetContentsHandler, new DataFormatter(), formulasNotResults);
}

public SheetParser(Sheet sheet, int headerRow) {
    final DataFormatter formatter = new DataFormatter();
    final Map<Integer, String> columnNames = new HashMap<>();
    final int lastRow = sheet.getLastRowNum();
    sheet.getRow(headerRow).forEach(cell -> columnNames.put(cell.getColumnIndex(), formatter.formatCellValue(cell)));
    for (int index = headerRow + 1; index <= lastRow; index++) {
        rows.add(getRow(sheet, index, columnNames, formatter));
    }
}

private Map<String, String> getRow(Sheet sheet, int index, Map<Integer, String> columnNames, DataFormatter formatter) {
    Map<String, String> values = new HashMap<>();
    for (Cell cell : sheet.getRow(index)) {
        String key = columnNames.get(cell.getColumnIndex());
        if (key != null) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC && !DateUtil.isCellDateFormatted(cell)) values.put(key, String.valueOf(cell.getNumericCellValue()));
            else values.put(key, formatter.formatCellValue(cell));
        }
    }
    return values;
}

/**
 * retrieves a row as a json object.
 *
 * @param titles the titles of the row.
 * @param row    the row to read values from.
 * @return a jsonobject that maps titles to the column values.
 */
private JsonObject getRow(String[] titles, Row row) {
    DataFormatter formatter = new DataFormatter();
    JsonObject json = new JsonObject();
    int index = 0;

    for (int i = 0; i < row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i);
        Object value = null;

        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = formatter.formatCellValue(cell);
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        value = cell.getDateCellValue().toInstant().toString();
                    } else {
                        value = cell.getNumericCellValue();
                    }
                    break;
            }
            // avoid indexing null or empty string, fails to index rows
            // when date fields are empty and can lead to mappings being
            // set up incorrectly if leading rows has missing data.
            if (value != null && !(value.toString().length() == 0)) {
                json.put(titles[index], value);
            }
        }
        index++;
    }
    return json;
}

/**
 * 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());
    }
}

private String getCellValue(Cell cell) {
    String cellValue = "";
    DataFormatter formatter = new DataFormatter();
    if (cell != null) {
        // 判断当前单元格的格式
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                cellValue = formatter.formatCellValue(cell);
            } else {
                // 双精度
                double value = cell.getNumericCellValue();
                int intValue = (int) value;
                cellValue = value - intValue == 0 ? String
                        .valueOf(intValue) : String.valueOf(value);
            }
            break;
        case Cell.CELL_TYPE_STRING:
            cellValue = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            cellValue = String.valueOf(cell.getCellFormula());
            break;
        case Cell.CELL_TYPE_BLANK:
            cellValue = "";
            break;
        case Cell.CELL_TYPE_ERROR:
            cellValue = "";
            break;
        default:
            cellValue = cell.toString().trim();
            break;
        }
    }
    return cellValue.trim();
}

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

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

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

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

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

private void testExcelFile(final String path, final DataOrientation dataOrientation) throws InvalidFormatException,
        IOException {
    ExcelFile excelFile = new ExcelFile(new File(path), dataOrientation, new DataFormatter());
    excelFile.load();
    Map<String, List<Map<String, String>>> actualData = excelFile.getData();
    assertThat(actualData).isEqualTo(expectedData);
}

private ExcelDataSource createDataSource(final String path, final DataOrientation dataOrientation) {
    Configuration config = new Configuration(Charsets.UTF_8);
    config.put("dataSource.excel.0.path", path);
    config.put("dataSource.excel.0.dataOrientation", dataOrientation.name());

    return new ExcelDataSource(config, new DataFormatter());
}

public DataFormatterEx(Locale locale, boolean emulateCsv) {
    //和暦対応
    if ("ja".equals(locale.getLanguage())) {
        if (!"JP".equals(locale.getCountry()) || !"JP".equals(locale.getVariant())) {
            locale = new Locale("ja", "JP", "JP");
        }
    }
    this.locale = locale;
    this.numberFormatter = new DataFormatter(locale, emulateCsv);
}

/**
 * Accepts objects needed while parsing.
 *
 * @param styles
 *          Table of styles
 * @param strings
 *          Table of shared strings
 */
public XSSFSheetXMLHandler(StylesTable styles, ReadOnlySharedStringsTable strings, SheetHandler sheetContentsHandler, DataFormatter dataFormatter, boolean formulasNotResults)
{
  this.stylesTable = styles;
  this.sharedStringsTable = strings;
  this.output = sheetContentsHandler;
  this.formulasNotResults = formulasNotResults;
  this.nextDataType = ColumnType.NUMBER;

  this.contentFormatter = dataFormatter;
  this.cellFormatter = new DataFormatter();
}

/**
 * Accepts objects needed while parsing.
 *
 * @param styles  Table of styles
 * @param strings Table of shared strings
 * @param cols    Minimum number of columns to show
 * @param target  Sink for output
 */
public MyXSSFSheetHandler(
        StylesTable styles,
        ReadOnlySharedStringsTable strings,
        int cols,
        PrintStream target) {
    this.stylesTable = styles;
    this.sharedStringsTable = strings;
    this.minColumnCount = cols;
    this.output = target;
    this.value = new StringBuffer();
    this.nextDataType = xssfDataType.NUMBER;
    this.formatter = new DataFormatter();
}

HSSFUnmarshaller(final PoijiWorkbook poijiWorkbook, PoijiOptions options) {
    this.poijiWorkbook = poijiWorkbook;
    this.options = options;
    dataFormatter = new DataFormatter();
    casting = Casting.getInstance();
}

/**
 * Reads a list of POJOs from the given excel file.
 *
 * @param file Excel file to read from
 * @param sheetName The sheet to extract from in the workbook
 * @param reader The reader class to use to load the file from the sheet
 */
public static void process(File file, String sheetName, ZeroCellReader reader) {
    try (FileInputStream fis = new FileInputStream(file);
         OPCPackage opcPackage = OPCPackage.open(fis)) {

        DataFormatter dataFormatter = new DataFormatter();
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opcPackage);
        XSSFReader xssfReader = new XSSFReader(opcPackage);
        StylesTable stylesTable = xssfReader.getStylesTable();
        InputStream sheetInputStream = null;
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (sheets.hasNext()) {
            sheetInputStream = sheets.next();
            if (sheets.getSheetName().equalsIgnoreCase(sheetName)) {
                break;
            } else {
                sheetInputStream = null;
            }
        }

        if (Objects.isNull(sheetInputStream)) {
            throw new SheetNotFoundException(sheetName);
        }

        XMLReader xmlReader = SAXHelper.newXMLReader();
        xmlReader.setContentHandler(new XSSFSheetXMLHandler(stylesTable, strings, reader, dataFormatter, false));
        xmlReader.parse(new InputSource(sheetInputStream));
        sheetInputStream.close();
        xmlReader = null;
        sheetInputStream = null;
        stylesTable = null;
        strings = null;
        xssfReader = null;
    } catch(org.apache.poi.openxml4j.exceptions.InvalidFormatException | NotOfficeXmlFileException ife) {
        throw new ZeroCellException("Cannot load file. The file must be an Excel 2007+ Workbook (.xlsx)");
    } catch(SheetNotFoundException ex) {
        throw new ZeroCellException(ex.getMessage());
    } catch (ZeroCellException ze) {
        throw ze; // Rethrow the Exception
    } catch (Exception e) {
        throw new ZeroCellException("Failed to process file", e);
    }
}

private void processSpreadsheetUpload(Workbook wb, List<Map<QName,String>> users)
    throws IOException
{
    if (wb.getNumberOfSheets() > 1)
    {
        logger.info("Uploaded Excel file has " + wb.getNumberOfSheets() + 
                " sheets, ignoring  all except the first one"); 
    }

    int firstRow = 0;
    Sheet s = wb.getSheetAt(0);
    DataFormatter df = new DataFormatter();

    String[][] data = new String[s.getLastRowNum()+1][];

    // If there is a heading freezepane row, skip it
    PaneInformation pane = s.getPaneInformation();
    if (pane != null && pane.isFreezePane() && pane.getHorizontalSplitTopRow() > 0)
    {
        firstRow = pane.getHorizontalSplitTopRow();
        logger.debug("Skipping excel freeze header of " + firstRow + " rows");
    }

    // Process each row in turn, getting columns up to our limit
    for (int row=firstRow; row <= s.getLastRowNum(); row++)
    {
        Row r = s.getRow(row);
        if (r != null)
        {
            String[] d = new String[COLUMNS.length];
            for (int cn=0; cn<COLUMNS.length; cn++)
            {
                Cell cell = r.getCell(cn);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
                {
                    d[cn] = df.formatCellValue(cell);
                }
            }
            data[row] = d;
        }
    }

    // Handle the contents
    processSpreadsheetUpload(data, users);
}

@Documentation(
    value = "Insert a table from an Excel .xlsx file.",
    params = {
        @Param(name = "uri", value = "The Excel .xlsx file uri, it can be relative to the template"),
        @Param(name = "sheetName", value = "The sheet name"),
        @Param(name = "topLeftCellAdress", value = "The top left cell address"),
        @Param(name = "bottomRightCellAdress", value = "The bottom right cell address"),
        @Param(name = "languageTag", value = "The language tag for the locale"),
    },
    result = "insert the table",
    examples = {
        @Example(expression = "'excel.xlsx'.asTable('Feuil1', 'C3', 'F7', 'fr-FR')", result = "insert the table from 'excel.xlsx'"),
    }
)
// @formatter:on
public MTable asTable(String uriStr, String sheetName, String topLeftCellAdress, String bottomRightCellAdress,
        String languageTag) throws IOException {
    final MTable res = new MTableImpl();

    final URI xlsxURI = URI.createURI(uriStr, false);
    final URI uri = xlsxURI.resolve(templateURI);

    try (XSSFWorkbook workbook = new XSSFWorkbook(uriConverter.createInputStream(uri));) {
        final FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
        final XSSFSheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            throw new IllegalArgumentException(String.format("The sheet %s doesn't exists in %s.", sheetName, uri));
        } else {
            final Locale locale;
            if (languageTag != null) {
                locale = Locale.forLanguageTag(languageTag);
            } else {
                locale = Locale.getDefault();
            }
            final DataFormatter dataFormatter = new DataFormatter(locale);
            final CellAddress start = new CellAddress(topLeftCellAdress);
            final CellAddress end = new CellAddress(bottomRightCellAdress);
            int rowIndex = start.getRow();
            while (rowIndex <= end.getRow()) {
                final XSSFRow row = sheet.getRow(rowIndex++);
                if (row != null) {
                    final MRow mRow = new MRowImpl();
                    int cellIndex = start.getColumn();
                    while (cellIndex <= end.getColumn()) {
                        final XSSFCell cell = row.getCell(cellIndex++);
                        if (cell != null) {
                            final MStyle style = getStyle(cell);
                            final MElement text = new MTextImpl(dataFormatter.formatCellValue(cell, evaluator),
                                    style);
                            final Color background = getColor(cell.getCellStyle().getFillForegroundColorColor());
                            final MCell mCell = new MCellImpl(text, background);
                            mRow.getCells().add(mCell);
                        } else {
                            mRow.getCells().add(createEmptyCell());
                        }
                    }
                    res.getRows().add(mRow);
                } else {
                    final int length = end.getColumn() - start.getColumn() + 1;
                    res.getRows().add(createEmptyRow(length));
                }
            }

        }
    }

    return res;
}

/**
     * 根据 cell 格式，自动转换 cell 内容为 String
     *
     * @param cell
     * @param datePattern 日期格式  yyyy-MM-dd , yyyy-MM-dd HH:mm:ss  ...
     * @return
     */
    private static String getFormatCellValue(Cell cell, String datePattern) {

        //如果是日期格式，重新格式化
        if (cell.getCellTypeEnum() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {

            return DateFormatUtils.format(cell.getDateCellValue(), datePattern);

        } else //默认格式化

            return new DataFormatter().formatCellValue(cell).trim();


        /**
         * DataFormatter().formatCellValue(cell) 的源码
         */
//        if (cell == null) {
//            return "";
//        }
//
//        int cellType = cell.getCellType();
//        if (cellType == Cell.CELL_TYPE_FORMULA) {
//            if (evaluator == null) {
//                return cell.getCellFormula();
//            }
//            cellType = evaluator.evaluateFormulaCell(cell);
//        }
//        switch (cellType) {
//            case Cell.CELL_TYPE_NUMERIC :
//
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    return getFormattedDateString(cell);
//                }
//                return getFormattedNumberString(cell);
//
//            case Cell.CELL_TYPE_STRING :
//                return cell.getRichStringCellValue().getString();
//
//            case Cell.CELL_TYPE_BOOLEAN :
//                return String.valueOf(cell.getBooleanCellValue());
//            case Cell.CELL_TYPE_BLANK :
//                return "";
//            case Cell.CELL_TYPE_ERROR:
//                return FormulaError.forInt(cell.getErrorCellValue()).getString();
//        }
//        throw new RuntimeException("Unexpected celltype (" + cellType + ")");


    }

public DefaultDataHandlerImpl(ArrayList<String> rawValues, ArrayList<String> formattedValues, ReadOnlySharedStringsTable sharedStringsTable) {
    this.rawValues = rawValues;
    this.formattedValues = formattedValues;
    this.sharedStringsTable = sharedStringsTable;
    this.formatter = new DataFormatter();
}

@SuppressWarnings("null")
public static String getCellData(String reqValue, HSSFSheet reqSheet,
        int rowIndex, HashMap<String, Object> inputHashTable)
        throws IOException {
    HSSFCell reqCell = null;
    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) {
        report.setStrMessage("Column " + reqValue
                + " not Found. Please Check input Sheet");
        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;
}

private String getCellValue(Cell cell, DataFormatter dataFormatter) {
        String value = null;
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            value = Boolean.toString(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
//              value = cell.getDateCellValue().toString();
                value = dataFormatter.formatCellValue(cell);
            } else {
                value = Double.toString(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            switch (cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                value = Boolean.toString(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue().toString();
                } else {
                    value = Double.toString(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            default:
                break;
            }
        default:
            break;
        }
        // value = dataFormatter.formatCellValue(cell);
        // if ("".equals(value)) value = null;
        return value;
    }

private void processSpreadsheetUpload(Workbook wb, List<Map<QName,String>> users)
    throws IOException
{
    if (wb.getNumberOfSheets() > 1)
    {
        logger.info("Uploaded Excel file has " + wb.getNumberOfSheets() + 
                " sheets, ignoring  all except the first one"); 
    }

    int firstRow = 0;
    Sheet s = wb.getSheetAt(0);
    DataFormatter df = new DataFormatter();

    String[][] data = new String[s.getLastRowNum()+1][];

    // If there is a heading freezepane row, skip it
    PaneInformation pane = s.getPaneInformation();
    if (pane != null && pane.isFreezePane() && pane.getHorizontalSplitTopRow() > 0)
    {
        firstRow = pane.getHorizontalSplitTopRow();
        logger.debug("Skipping excel freeze header of " + firstRow + " rows");
    }

    // Process each row in turn, getting columns up to our limit
    for (int row=firstRow; row <= s.getLastRowNum(); row++)
    {
        Row r = s.getRow(row);
        if (r != null)
        {
            String[] d = new String[COLUMNS.length];
            for (int cn=0; cn<COLUMNS.length; cn++)
            {
                Cell cell = r.getCell(cn);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
                {
                    d[cn] = df.formatCellValue(cell);
                }
            }
            data[row] = d;
        }
    }

    // Handle the contents
    processSpreadsheetUpload(data, users);
}