excel导入

/***************************** 导入使用步骤 *********************************/

List<Map<String, Object>> analysisExcel = null;
synchronized (this) {
    Map<String, String> dbFieldMap = getImportExcelTitleMap();
    analysisExcel = importService.getExcelContent(file, dbFieldMap, 0);
}

/**
 * 获取导出excel字段对应关系
 * @return
 */
private Map<BaseExcelKey, String> getExportExcelTitleMap() {
    Map<BaseExcelKey, String> dbFieldMap = new HashMap<>();
    int index=0;
    dbFieldMap.put(new BaseExcelKey(index++,"field1"), "字段1");
    dbFieldMap.put(new BaseExcelKey(index++,"field2"), "字段2");
    return dbFieldMap;
}

/**
 * 获取导入excel字段对应关系
 * @return
 */
private Map<String, String> getImportExcelTitleMap() {
    Map<BaseExcelKey, String> dbFieldMap = getExportExcelTitleMap();
    Map<String, String> reservedMap = new HashMap<>();
    for (BaseExcelKey key : dbFieldMap.keySet() ) {
        reservedMap.put(dbFieldMap.get(key), key.getKey());
    }
    return reservedMap;
}

@Data
public class BaseExcelKey {
    private String key;
    private int sort;

    public BaseExcelKey(int sort, String key){
        this.sort = sort;
        this.key = key;
    }
}

/***************************** 导入服务类 *********************************/
@Service
public class ImportService implements IExcelDbField {

    private Map<String, String> map = null;

    @Override
    public String getDbFieldName(String title) {
        if (map.containsKey(title)) {
            return map.get(title);
        }
        return "";
    }

    public List<Map<String, Object>> getExcelContent(MultipartFile excelFile, Map<String, String> dbFieldMap, int page) throws IOException {
        this.map = dbFieldMap;
        // 行列表
        List<Map<String, Object>> contents;
        Workbook workbook;
        try {
            // 解析 Excel 获取 Workbook
            workbook = getWorkbook(excelFile);
            contents = analysisToMap(workbook, page);
        } catch (IOException e) {
//            logger.error("获取 Excel 内容失败！", e);
            throw new IOException("获取 Excel 内容失败！", e);
        }
        return contents;
    }

    /**
     * 主体解析
     *
     * @param wb
     * @param page 页   0 开始
     * @return
     */
    private List<Map<String, Object>> analysisToMap(final Workbook wb, int page) {
        List<Map<String, Object>> result = new ArrayList<>();
        Sheet sheetAt = wb.getSheetAt(page);
        if (null == sheetAt) {
            return null;
        }
        // 遍历行Row
        int lastRowNum = sheetAt.getLastRowNum();
        List title = null;
        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            Row row = sheetAt.getRow(rowNum);
            if (row == null) {
                continue;
            }
            // 遍历列Cell
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum == -1) {
                continue;
            }
            //如果是第一排则存到标题List里
            if (rowNum == 0) {
                title = new ArrayList<>(lastCellNum);
                for (int cellNum = 0; cellNum <= lastCellNum; cellNum++) {
                    Cell hssfCell = row.getCell(cellNum);
                    if (null == hssfCell) {
                        continue;
                    }
                    title.add(getValueCell(hssfCell));
                }
            } else {
                HashMap<String, Object> rowMap = new HashMap<>(lastCellNum);
                for (int cellNum = 0; cellNum <= lastCellNum; cellNum++) {
                    Cell hssfCell = row.getCell(cellNum);
                    if (null == hssfCell) {
                        continue;
                    }

                    String keyName = cellNum < title.size() && null != title.get(cellNum)
                            ? title.get(cellNum).toString() : String.valueOf(cellNum);
                    String dbField = getDbFieldName(keyName);
                    if (StringUtils.isNotBlank(dbField)) {
                        rowMap.put(dbField, getValueCell(hssfCell));
                    }
                }
                result.add(rowMap);
            }
        }
        return result;
    }

    /**
     * 提取单元格中的值
     *
     * @param cell 每一行对象
     */
    private static Object getValueCell(Cell cell) {
        CellType cellType = cell.getCellType();
        Object cellValue = null;
        switch (cellType) {
            //String类型
            case STRING:
//                return cell.getStringCellValue();
                cellValue = cell.getRichStringCellValue().toString().trim();
                break;
            //String类型
            case ERROR:
                cellValue = cell.getErrorCellValue();
                break;
            case NUMERIC:
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    return cell.getDateCellValue();
//                }
//                HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
//                cellValue =  dataFormatter.formatCellValue(cell);

                // 获取具体类型
                String dataFormat = cell.getCellStyle().getDataFormatString();
                // 具体类型判断
                if (("General").equalsIgnoreCase(dataFormat)) {
                    cellValue = new DecimalFormat("0").format(cell.getNumericCellValue());
                } else if (("m/d/yy").equalsIgnoreCase(dataFormat)) {
                    cellValue = dateToString(cell.getDateCellValue(), "YYYY-MM-DD");
                } else {
                    cellValue = new DecimalFormat("0.00").format(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case BLANK:
                cellValue = "";
                break;
            default:
                break;
        }
        return cellValue;
    }

    private static String dateToString(Date time, String datePattern) {
        return DateFormatUtils.format(time, datePattern);
    }

    private Workbook getWorkbook(MultipartFile excelFile) throws IOException {
        // 获取文件输入流
        InputStream inputStream = excelFile.getInputStream();
        // 获取文件内容
        String fileName = excelFile.getOriginalFilename();

        // 获取文件类型
        String fileType = fileName.substring(fileName.indexOf("."));

        // 判断文件类型
        if ((".xls").equalsIgnoreCase(fileType)) {
            return new HSSFWorkbook(inputStream);
        } else if ((".xlsx").equalsIgnoreCase(fileType)) {
            return new XSSFWorkbook(inputStream);
        } else {
            throw new IOException("Excel 文件解析失败，格式错误！");
        }
    }

}