前言

做OA管理系统的时候，刚好有个需求：数据批量导入数据库功能，一般都是用Excel文件导入，这里就以Excel为例
工具及插件
项目框架：SpringBoot + Mybatis + MySQL
第三方插件：Lombok
项目引用的是Apache poi导入Excel文件

导入的Excel 样式，如图：

注意：这里Excel中的sheet有几个个硬性要求

  1. sheet的名字无所谓，但位置一定要是放在第一个
  2. 列名顺序绝对不能乱！！！后台是获根据每个列号来获取数据的！！

前端调用

使用ajax调用后台接口，上传Excel文件

后端接口

代码中引用了lombok插件，直接使用注解的方式。
Result类 是我自己封装的一个返回结果类。

引入Maven依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!-- poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.17</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-scratchpad</artifactId>
	<version>3.17</version>
</dependency>

控制层Controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.Map;
 
@RestController
@RequestMapping("excel")
public class ExcelController {
 
	@Autowired
    private ExcelService excelService;
 
    /**
     * 批量导入
     */
    @RequestMapping("/importExcel")
    public Result<?> importExcel(@RequestParam(value = "file") MultipartFile file) {
        try {
            if (file != null){
                String result = personService.importExcel(file);
                if ("200".equals(result)){
                    return Result.succeed("导入成功");
                }else {
                    return Result.failed("导入失败");
                }
            }else {
                return Result.failed("表格为空");
            }
        }catch (Exception e){
            e.printStackTrace();
            return Result.failed("导入异常");
        }
    }
}

业务逻辑层

1
2
3
4
5
6
/**
 * 业务逻辑 接口
 */
public interface ExcelService {
	public String importExcel(MultipartFile file) throws IOException;
}

数据库数据处理的时候，逻辑上可能会比较繁琐
读取excel文件，然后一行一行的新增入库
获取Excel的时候，这里用到工具类自动分析数据类型，虽然工具类的容错率比较高，但是还会出现一些“诡异”的异常，这就需要自己手动去判断了
例如，日期格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.*;
 
/**
 * 业务逻辑 实现类
 */
@Service
public class excelServiceImpl implements HrInterviewService {
	
	// 数据库调用接口
	@Autowired
    private PersonDao personDao;
 
    @Override
    public String importExcel(MultipartFile file) throws IOException {
        InputStream in = file.getInputStream();
        XSSFWorkbook wb = new XSSFWorkbook(in);
        XSSFSheet sheet = wb.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (int i = 1; i <= lastRowNum; i++){
            XSSFRow row = sheet.getRow(i);
            /** excel取值 */
			//取日期
            Date nowDate = null;
            if (row.getCell(0) != null) {
                if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
                    String nowDateStr = row.getCell(0).getStringCellValue();
                    try {
                        nowDate = sdf.parse(nowDateStr);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                } else {
                    nowDate = row.getCell(0).getDateCellValue();
                }
            }
			//取城市
            String city = ExcelUtils.getCell(row.getCell(1));
			//取街道
            String street = ExcelUtils.getCell(row.getCell(2));
			//取姓名
            String userName = ExcelUtils.getCell(row.getCell(3));
			//取性别
            String sex = ExcelUtils.getCell(row.getCell(4));
			//取年龄
            String age = ExcelUtils.getCell(row.getCell(5));
			//取联系方式
            String telephone = null;
            if (row.getCell(6) != null){
                telephone = row.getCell(6).getRawValue();
            }
            /** Excel获取到的数据封装入库 */
            Person item = new Person();
            item.setNowDate(nowDate);
            item.setUserName(userName);
            if(sex != null){
                if ("女".equals(sex)){
                    item.setSex(0);
                }else if ("男".equals(sex)){
                    item.setSex(1);
                }
            }
            if (age != null && !"".equals(age)){
                item.setAge(Integer.valueOf(age));
            }
            item.setTelephone(telephone);
			//调用保存接口，数据添加的代码我就不提供了
			personDao.save(item);
        }
        return "200";
    }
 
}

实体类层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
 
@Data
public class Person implements Serializable{
    private static final long serialVersionUID = 1L;
    // id
    private Integer id;
	// 日期
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
    private Date nowDate;
	// 城市
    private String city;
	// 街道
    private String street;
	// 姓名
    private String userName;
	// 性别
    private Integer sex;
	// 年龄
    private Integer age;
	// 联系方式
    private String telephone;
}

工具类

这里提供了两种单元格类型分析方法，我用的第一种，当然也可以用第二种

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.prefs.BackingStoreException;
 
/**
 * Excel工具类
 */
public class ExcelUtils {
 
    /**
     * 批量导入时 判断单元格类型1
     */
    public static String getCell(XSSFCell cell) {
        if (cell == null){
            return "";
        }else {
            DecimalFormat df = new DecimalFormat("#");
            switch (cell.getCellType()) {
				// 数字
                case XSSFCell.CELL_TYPE_NUMERIC:
                    return new Double(cell.getNumericCellValue()).intValue() + "";
				// 字符串
                case XSSFCell.CELL_TYPE_STRING:
                    return cell.getStringCellValue();
				// 函数
                case XSSFCell.CELL_TYPE_FORMULA:
                    return cell.getCellFormula();
				// 空值
                case XSSFCell.CELL_TYPE_BLANK:
                    return "";
				// 布尔类型
                case XSSFCell.CELL_TYPE_BOOLEAN:
                    return cell.getBooleanCellValue() + "";
				// 异常
                case XSSFCell.CELL_TYPE_ERROR:
                    return cell.getErrorCellValue() + "";
                default:
                    return "";
            }
        }
    }
	/**
     * 批量导入时 判断单元格类型2
     */
	public static String getCell2(XSSFCell cell) {
        String cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellTypeEnum()) {
			// 数字
            case NUMERIC: 
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
			// 字符串
            case STRING: 
                cellValue = cell.getStringCellValue();
                break;
			// Boolean
            case BOOLEAN: 
                cellValue = cell.getBooleanCellValue() + "";
                break;
			// 公式
            case FORMULA: 
                cellValue = cell.getCellFormula() + "";
                break;
			// 空值
            case BLANK: 
                cellValue = "";
                break;
			// 故障
            case ERROR: 
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
 
}