poi导入excel

pox导入excel

@FunctionalInterface
public interface ImportHandlerCallback<V> {
    /**
     * 数据校验、VO构建
     *
     * @param error_index 出现错误或警告的行索引
     * @param values      根据树据行解析出的数组
     * @return
     */
    V preHandle(int error_index, String[] values);
}

public abstract class ImportHandler<V> {
    /**
     * @param filename              文件名
     * @param is                    InputStream
     * @param importHandlerCallback
     * @return
     */
    public abstract List<V> handle(String filename, InputStream is, ImportHandlerCallback<V> importHandlerCallback);
}

public class ExcelImportHandler<V> extends ImportHandler<V> {
    private static final Logger logger = LoggerFactory.getLogger(ExcelImportHandler.class);

    @Override
    public List<V> handle(String filename, InputStream is, ImportHandlerCallback<V> importHandlerCallback) {
        if (filename == null || filename.length() == 0){
            throw new RuntimeException("文件名为空，导入操作失败！");
        }
        logger.info("开始解析Excel文件：" + filename);
        if (is == null){
            throw new RuntimeException("文件流为空，导入操作失败！");
        }
        if (importHandlerCallback == null) {
            throw new RuntimeException("无预处理参数配置，导入操作失败！");
        }
        Workbook workBook;
        try {
            String file_suffix = ExcelUtil.getExtensionName(filename);
            if (!ExcelUtil.isExcel(file_suffix)){
                throw new RuntimeException("请上传后缀:[xls,xlsx]文件！");
            }
            workBook = ExcelUtil.getWeebWork(is, file_suffix);
        } catch (IOException e) {
            throw new RuntimeException("读取文件工作簿失败，导入操作失败！");
        }
        if (workBook == null) {
            throw new RuntimeException("工作簿为空，导入操作失败！");
        }
        Sheet sheet = workBook.getSheetAt(0);
        if (sheet == null) {
            throw new RuntimeException("Sheet为空，导入操作失败！");
        }
        // 获取表格中的数据，按数据行构造VOList对象
        List<V> preExecution = new ArrayList<V>();
        Iterator<Row> it = sheet.rowIterator();
        int row_index = 0;
        int cell_count = 0;
        while (it.hasNext()) {
            Row row = it.next();
            //跳过第一行
            if (row.getRowNum() == 0) {
                row_index++;
                cell_count = row.getLastCellNum();
                continue;
            }
            String[] tmp = new String[cell_count];
            for (int i = 0; i < cell_count; i++) {
                Cell cell = row.getCell(i);
                String value = ExcelUtil.getCellValue(cell);
                if (value == null || value.length() == 0){
                    logger.warn("Excel解析空数据警告。【行索引：" + (row_index + 1) + "，列索引：" + (i + 1) + "】");
                }
                tmp[i] = value;
            }
            V v = importHandlerCallback.preHandle((row_index + 1), tmp);
            row_index++;
            preExecution.add(v);
        }
        return preExecution;
    }
}

public class ExcelUtil {
    public static final String[] excel_suffixes = new String[]{"xls", "xlsx"};


    public static Workbook getWeebWork(InputStream is, String file_suffix) throws IOException {
        Workbook workbook = null;
        if (null != is) {
            if (file_suffix.toLowerCase().equals("xls")) {
                workbook = new HSSFWorkbook(is);
            }
            if (file_suffix.toLowerCase().equals("xlsx")) {
                workbook = new XSSFWorkbook(is);
            }
        }
        return workbook;
    }

    public static String getCellValue(Cell cell) {
        String value = null;
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: // 数字
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue());
                        value = sdf.format(date);
                    } else {
                        value = cell.getNumericCellValue() + "";
                        value = BigDecimal.valueOf(Double.parseDouble(value)).stripTrailingZeros().toPlainString();
                    }
                    break;
                case Cell.CELL_TYPE_STRING: // 字符串
                    value = cell.getStringCellValue() + "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN: // Boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case Cell.CELL_TYPE_FORMULA: // 公式
                    value = cell.getCellFormula() + "";
                    break;
                case Cell.CELL_TYPE_BLANK: // 空值
                    break;
                case Cell.CELL_TYPE_ERROR: // 故障
                    break;
                default:
                    break;
            }
        }
        return value == null ? "" : value;
    }

    public static String getExtensionName(String filename) {
        if (filename != null && filename.length() > 0) {
            int dot = filename.lastIndexOf('.');
            if (dot > -1 && dot < (filename.length() - 1)) {
                return filename.substring(dot + 1).toLowerCase();
            }
        }
        return null;
    }

    public static boolean isExcel(String file_suffix) {
        if (file_suffix == null) return false;
        for (String s : excel_suffixes) {
            if (file_suffix.equals(s)) {
                return true;
            }
        }
        return false;
    }

    public static Date formatDate(String date) {
        try {
            if (date == null || date.length() == 0) return null;
            date = date.replace("/", "-").replace("年", "-").replace("月", "").replace("日", "");
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            if (date.indexOf("-") <= 0) df = new SimpleDateFormat("yyyyMMdd");
            return df.parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException("日期格式错误:[" + date + "]");
        }
    }
}