poi导入excel

àì夳堔傛蜴生んèń 2022-06-03 00:48 264阅读 0赞

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 + "]");
            }
        }
    }