java excel 文件数据导入数据库 通用工具类

说明

1. excel中的第一行必须为表头，从第二行开始为导入的数据
2. 表头的名称必须为数据库字段名称
3. 不支持合并单元格内容。
4. 建议表头列数和数据表中 的字段数量一致。
5. 入的数据格式要和数据表约定的一致，例如：对于性别，excel中为男女，而数据库中为1和0，此时excel必须修改为1 和 0

例如 ：
t_user 表：

id	username	sex	age
1	张三	1	18

则 excel 内容应该如下：

依赖

<dependency>
   <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>
 <!--log4j2-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-log4j2</artifactId>
</dependency>

代码

public class UniversalExcelReaderUtil {
    private static final Logger log = LoggerFactory.getLogger(UniversalExcelReaderUtil.class);
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";


    /**
     * 获取excle中的数据据
     *
     * @return
     * List<List<Map<String, Object>>> 存放整个excle所有的sheet表数据，
     *             格式：
     *             [
     *                 [{},{},{}], // sheet1数据
     *                 [{},{},{}], // sheet2数据
     *                 [{},{},{}], // sheet3数据
     *                 ....
     *             ]
     *
     */
    public static List<List<Map<String, Object>>> getDataFromExcel(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        if (StringUtil.isEmpty(fileName)) {
            log.warn("文件名获取不到");
            return null;
        }
        // 获取Excel后缀名
        String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
        if (Arrays.asList(XLS, XLSX).contains(fileType) == false) {
            log.warn("文件后缀名不正确");
            return null;
        }

        // 开始获取工作簿对象
        Workbook excelObject = null;
        try {
            excelObject = getExcelObject(file.getInputStream(), fileType);
        } catch (IOException e) {
            log.error("获取工作簿对象异常：", e);
        }
        if (excelObject == null) {
            log.warn("获取工作簿对象失败");
            return null;
        }

        //开始获取excel文件中的数据
        List<List<Map<String, Object>>> lists = readExcelData(excelObject);
        return lists;
    }

    /**
     * 获取excel中的数据
     * 支持多个sheet表格
     */
    private static List<List<Map<String, Object>>> readExcelData(Workbook workbook) {
        //获取excle中的sheet表
        int numberOfSheets = workbook.getNumberOfSheets();
        if (numberOfSheets <= 0) {
            log.warn("没有读取到excle文件中的sheet表格");
            return null;
        }

        /*
            存放整个excle所有的sheet表数据，
            格式：
            [
                [{},{},{}], // sheet1数据
                [{},{},{}], // sheet2数据
                [{},{},{}], // sheet3数据
                ....
            ]
        */
        List<List<Map<String, Object>>> excelDataList = new LinkedList<List<Map<String, Object>>>();

        // 开始循环每个sheet表格
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            //当前sheet的数据
            List<Map<String, Object>> sheetData = new LinkedList<Map<String, Object>>();

            Sheet sheet = workbook.getSheetAt(sheetNum);
            if (sheet == null) {
                log.warn("读取不到第" + (sheetNum + 1) + "个工作表");
                continue;
            }

            // 开始获取工作表中的每一行数据
            // 获取第一行数据 ，一般用于表头
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (firstRow == null) {
                log.warn("解析Excel失败，在第一行没有读取到任何数据！行号:{}",firstRowNum);
            }

            int rowStart = firstRowNum + 1; // 数据开始的行
            int rowEnd = sheet.getPhysicalNumberOfRows(); // 数据结束的行

            // 循环数据开始的行和数据结束的行，其中的数据为需要的数据
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum); // 获取数据行
                if (row == null){
                    log.warn("第"+ (sheetNum + 1)+"sheet表的第"+rowNum+"行读取不到数据");
                    continue;
                }

                //自定义读取的数据列
                //todo
                Map<String, Object> map = readColumnData(row,sheet.getRow(firstRowNum)); // 每一行的数据
                sheetData.add(map);
            }

            if (sheetData != null && sheetData.size() > 0) excelDataList.add(sheetData);
        }

        return excelDataList;
    }


    /**
     * 根据文件后缀获取excel工作簿对象
     */
    private static Workbook getExcelObject(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }



    //------------------自定义读取的数据列方法-------------------
    /**
     * 读取sheet表的每一行每一列的内容
     * @param dataRow  数据行
     * @param titleRow  表头行
    */
    private static Map<String, Object> readColumnData(Row dataRow,Row titleRow){
        Map<String, Object> resMap = new LinkedHashMap<String, Object>();
        int lastTitleCells = titleRow.getPhysicalNumberOfCells();//最后一列数据列号，根据表头获取，只读取有表头的数据列
        Cell cell = null;
        for (int i = 0; i < lastTitleCells ; i++) {
            cell = dataRow.getCell(i);
            String value = convertCellValueToString(cell);
            resMap.put(titleRow.getCell(i).toString(),value);
        }
        return resMap;
    }


    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @return
     */
    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();

                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }
}

使用方式

controller 代码：

 @RequestMapping(value = "excelImportData")
 public String excelImportData(MultipartFile file) {
        log.info("上传的文件：{}, {}", file.getOriginalFilename(), file.getName());
        //获取excel中的所有数据
        List<List<Map<String, Object>>> dataFromExcel = UniversalExcelReaderUtil.getDataFromExcel(file);
        if (dataFromExcel == null || dataFromExcel.size() <= 0) {
            log.warn("读取不到表中的数据");
        }

        //整个excle的数据
        //User实体，代码省略，
        List<User> usersList = new LinkedList<User>();

        User users  = null;
        for (List<Map<String, Object>> sheetList : dataFromExcel) {
            for (Map<String, Object> dataItem : sheetList) {
                 Integer hava_description = dataItem.get("id") == null ? 0 : Integer.valueOf(String.valueOf(dataItem.get("id")));
                 
                String username= dataItem.get("username") == null ? null : String.valueOf(dataItem.get("username"));
                
                Integer sex = dataItem.get("sex") == null ? 0 : Integer.valueOf(String.valueOf(dataItem.get("sex")));
                
                 Integer age= dataItem.get("age") == null ? 0 : Integer.valueOf(String.valueOf(dataItem.get("age")));
                
                
                users = new User();
                users.setId(id);
                users.setUserName(username);
                users.setSex(sex);
                users.setAge(age);

                usersList.add(zhnsEnterpruserInterview);
            }
        }

        //log.info("要保存的数据：{}",zhnsEnterpruserInterviews);

        List<User> saveAllRes = null;
        try {
            saveAllRes = userService.saveAll(usersList);
        } catch (Exception e) {
            log.error("批量保存异常", e);
        }
        if (saveAllRes == null || saveAllRes.size() <= 0) {
            log.warn("批量保存失败");
        }
        log.info("批量保存成功，保存了{}条数据", saveAllRes.size());
        return JSON.toJSONString();
    }