下面列出了org.apache.poi.ss.usermodel.DataFormatter#formatCellValue ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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();
}
}
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;
}
@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();
}
}
}
}
}