Java Code Examples for org.apache.poi.ss.usermodel.DataFormatter#formatCellValue()

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

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

}
 

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

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

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

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() != CellType.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 exist 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;
}
 

@Override
public Model convert(ExcelConfig config) throws IOException, MobiException {
    Mapping mapping = mappingFactory.getAllExisting(config.getMapping()).stream().findFirst().orElseThrow(() ->
            new IllegalArgumentException("Missing mapping object"));
    Set<Ontology> sourceOntologies = config.getOntologies().isEmpty() ? getSourceOntologies(mapping) :
            config.getOntologies();
    String[] nextRow;
    Model convertedRDF = modelFactory.createModel();
    ArrayList<ClassMapping> classMappings = parseClassMappings(config.getMapping());

    try {
        Workbook wb = WorkbookFactory.create(config.getData());
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        boolean containsHeaders = config.getContainsHeaders();
        long offset = config.getOffset();
        Optional<Long> limit = config.getLimit();
        long lastRowNumber = -1;

        //Traverse each row and convert column into RDF
        for (Row row : sheet) {
            // If headers exist or the row is before the offset point, skip the row
            if ((containsHeaders && row.getRowNum() == 0) || row.getRowNum() - (containsHeaders ? 1 : 0) < offset
                    || (limit.isPresent() && row.getRowNum() >= limit.get() + offset) || row.getLastCellNum() < 0) {
                lastRowNumber++;
                continue;
            }
            // Logging the automatic skip of empty rows with no formatting
            while (row.getRowNum() > lastRowNumber + 1) {
                LOGGER.debug(String.format("Skipping empty row number: %d", lastRowNumber + 1));
                lastRowNumber++;
            }
            //getLastCellNumber instead of getPhysicalNumberOfCells so that blank values don't cause cells to shift
            nextRow = new String[row.getLastCellNum()];
            boolean rowContainsValues = false;
            for (int i = 0; i < row.getLastCellNum(); i++) {
                nextRow[i] = df.formatCellValue(row.getCell(i), evaluator);
                if (!rowContainsValues && !nextRow[i].isEmpty()) {
                    rowContainsValues = true;
                }
            }
            //Skipping empty rows
            if (rowContainsValues) {
                writeClassMappingsToModel(convertedRDF, nextRow, classMappings, sourceOntologies);
            } else {
                LOGGER.debug(String.format("Skipping empty row number: %d", row.getRowNum()));
            }
            lastRowNumber++;
        }
    } catch (InvalidFormatException | NotImplementedException e) {
        throw new MobiException(e);
    }

    return convertedRDF;
}
 

public String formatCellValue(Cell cell, FormulaEvaluator formulaEvaluator, String locale) {
	DataFormatter formatter = getLocalizedDataFormater(locale);
	return formatter.formatCellValue(cell, formulaEvaluator);
}
 

private static String getRowIndex(final Sheet worksheet,
                                  final int firstRow,
                                  final String input,
                                  final int columnIndex,
                                  final String operator) {
    String result = "";
    double cellValueNumeric;
    String cellFormat;

    double inputNumeric = processValueInput(input);

    for (int i = firstRow; i <= worksheet.getLastRowNum(); i++) {
        Row row = worksheet.getRow(i);
        if (row == null) {
            row = worksheet.createRow(i);
        }
        if (row != null) {
            Cell cell = row.getCell(columnIndex);
            if (cell == null) {
                cell = row.createCell(columnIndex);
            }
            if (cell != null) {
                CellType cellType = cell.getCellType();
                if (cellType != CellType.ERROR) {
                    cellFormat = getCellType(cell);
                    //string comparison
                    if (cellFormat.equalsIgnoreCase("string") && inputFormat.equalsIgnoreCase("string")) {
                        DataFormatter aFormatter = new DataFormatter();
                        String aCellString = aFormatter.formatCellValue(cell);
                        if (compareStringValue(aCellString, input, operator)) {
                            result += i + ",";
                        }
                    }
                    //value input is empty, and the cell in the worksheet is in numeric type
                    else if (!cellFormat.equalsIgnoreCase(inputFormat))
                    //((cellType != CellType.STRING && inputFormat.equalsIgnoreCase("string"))||
                    //(cellType != CellType.NUMERIC && !inputFormat.equalsIgnoreCase("string")))
                    {
                        if (operator.equals("!=")) {
                            result += i + ",";
                        }
                    }

                    //numeric comparison
                    else if (cellType == CellType.NUMERIC && !inputFormat.equalsIgnoreCase("string")) {
                        cellValueNumeric = cell.getNumericCellValue();
                        //both are date or time
                        if ((cellFormat.equalsIgnoreCase("date") && inputFormat.equalsIgnoreCase("date")) ||
                                (cellFormat.equalsIgnoreCase("time") && inputFormat.equalsIgnoreCase("time")) ||
                                (cellFormat.equalsIgnoreCase("num") && inputFormat.equalsIgnoreCase("num"))) {
                            if (compareNumericValue(cellValueNumeric, inputNumeric, operator)) {
                                result += i + ",";
                            }
                        }
                    }
                }
            }
        }
    }
    if (!result.isEmpty()) {
        final int index = result.lastIndexOf(',');
        result = result.substring(0, index);
    }

    return result;
}
 

public String getCellStringValueWithFormula(Cell cell, Workbook workbook) {
    DataFormatter formatter = new DataFormatter();
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    return formatter.formatCellValue(cell, evaluator);
}
 

private static void convertXslxIntoCsv(String startDir) throws InvalidFormatException, IOException {
	
	
	File startDirectory = new File(startDir);
	String outputDir = "Y:/DIG_auftraege_archiv/tmp/StazH/match/";

	for (File file : startDirectory.listFiles()){
		if (file.isDirectory()){
			logger.debug("dir: " + file.getAbsolutePath());
			convertXslxIntoCsv(file.getAbsolutePath());
		}
		else{
			File csvFolder = new File(outputDir + file.getParentFile().getName());
			csvFolder.mkdir();
			
			String csvName = csvFolder.getAbsolutePath() + "/" + FileUtil.getBasename(file) + ".csv";
			
			if (new File(csvName).exists()){
				continue;
			}
			
			Workbook wb = new XSSFWorkbook(file);

			DataFormatter formatter = new DataFormatter();

			PrintStream out = new PrintStream(new FileOutputStream(csvName), true, "UTF-8");
			
			byte[] bom = {(byte)0xEF, (byte)0xBB, (byte)0xBF};

			out.write(bom);

			for (Sheet sheet : wb) {

			    for (Row row : sheet) {

			        boolean firstCell = true;

			        for (Cell cell : row) {

			            if ( ! firstCell ) out.print(',');

			            String text = formatter.formatCellValue(cell);

			            out.print(text);

			            firstCell = false;

			        }

			        out.println();

			    }

			}
		}
	}
		

	
}