package fr.openwide.core.jpa.more.util.init.util; import java.text.DecimalFormat; import java.text.DecimalFormatSymbols; import java.text.NumberFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.GregorianCalendar; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.commons.lang3.EnumUtils; import org.apache.poi.ss.formula.eval.NotImplementedException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import com.google.common.collect.Sets; import fr.openwide.core.spring.util.StringUtils; public final class WorkbookUtils { public static final NumberFormat DECIMAL_FORMAT; static { DecimalFormatSymbols decimalFormatSymbols = DecimalFormatSymbols.getInstance(); // period character must be used as decimal separator, otherwise BigDecimal creation will fail. decimalFormatSymbols.setDecimalSeparator('.'); DECIMAL_FORMAT = new DecimalFormat("#.######", decimalFormatSymbols); } // Allow to get cached values from cells if the formula cannot be evaluated // We may want to allow only given features. private static final Set<FormulaFeature> ALLOWED_NOT_IMPLEMENTED_FORMULA_FEATURES = Sets.newHashSet(); public static void addAllowedNotImplementedFormulaFeature(FormulaFeature feature) { ALLOWED_NOT_IMPLEMENTED_FORMULA_FEATURES.add(feature); } public static void removeAllowedNotImplementedFormulaFeature(FormulaFeature feature) { ALLOWED_NOT_IMPLEMENTED_FORMULA_FEATURES.remove(feature); } public static void clearAllowedNotImplementedFormulaFeatures() { ALLOWED_NOT_IMPLEMENTED_FORMULA_FEATURES.clear(); } public static List<Map<String, Object>> getSheetContent(Sheet sheet) { FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); List<Map<String, Object>> content = new ArrayList<Map<String, Object>>(); Map<Integer, String> header = new HashMap<Integer, String>(); for (Row row : sheet) { if (row.getRowNum() == 0) { for (Cell cell : row) { header.put(cell.getColumnIndex(), getCellValue(formulaEvaluator, cell).toString()); } } else { Map<String, Object> line = new HashMap<String, Object>(); for (Cell cell : row) { Object cellValue = getCellValue(formulaEvaluator, cell); if (cellValue != null) { line.put(header.get(cell.getColumnIndex()), cellValue); } } content.add(line); } } return content; } private static Object getCellValue(FormulaEvaluator formulaEvaluator, Cell cell) { Object cellPrimitiveValue = getCellPrimitiveValue(cell, cell.getCellType()); if (cellPrimitiveValue != null) { return cellPrimitiveValue; } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return getCellValueFromFormula(formulaEvaluator, cell); } return null; } private static Object getCellValueFromFormula(FormulaEvaluator formulaEvaluator, Cell cell) { try { CellValue cellValue = formulaEvaluator.evaluate(cell); if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { Calendar calendar = GregorianCalendar.getInstance(); calendar.setTime(DateUtil.getJavaDate(cellValue.getNumberValue())); return calendar.getTime(); } else { return DECIMAL_FORMAT.format(cellValue.getNumberValue()); } } else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) { if (StringUtils.hasText(cellValue.getStringValue())) { return cellValue.getStringValue(); } } } catch (NotImplementedException e) { // If formula use Excel features not implemented in POI (like proper), // we can retrieve the cached value (which may no longer be correct, depending of what you do on your file). FormulaFeature feature = EnumUtils.getEnum(FormulaFeature.class, e.getCause().getMessage()); if (ALLOWED_NOT_IMPLEMENTED_FORMULA_FEATURES.contains(feature)) { return getCellPrimitiveValue(cell, cell.getCachedFormulaResultType()); } else { throw e; } } return null; } private static Object getCellPrimitiveValue(Cell cell, int cellType) { if (cellType == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { Calendar calendar = GregorianCalendar.getInstance(); calendar.setTime(DateUtil.getJavaDate(cell.getNumericCellValue())); return calendar.getTime(); } else { return DECIMAL_FORMAT.format(cell.getNumericCellValue()); } } else if (cellType == Cell.CELL_TYPE_STRING) { if (StringUtils.hasText(cell.getStringCellValue())) { return cell.getStringCellValue(); } } return null; } public enum FormulaFeature { PROPER; } private WorkbookUtils() { } }