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